Давайте разберемся с множеством вариантов оптимизации mysql сервера.
Оптимизация my.cnf
Начнём с оптимизации настроек файла my.cnf. Настройка некоторых параметров может повысить производительность в несколько раз!
Перед началом настройки необходимо уточнить объём установленной оперативной памяти и ядер процессора, к примеру с использованием htop.
Также необходимо узнать количество таблиц в нашей бд и их типы. Для этого используем mysql tuner, скачаем данный скрипт и запустим его (понадобится доустановить perl, если он не установлен на сервере):
wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Интересующая нас часть вывода скрипта:
В самом конце содержатся рекомендации, по оптимизации:
Описание опций и рекомендации
Рассмотрим параметры которые будем настраивать и их возможные значения:
- skip-external-locking — убирает внешнюю блокировку, что быстрее.
- skip-name-resolve — позволяет MySQL избегать ответа на запрос DNS при проверке подключения клиентов к серверу MySQL (Таким образом, сервер MySQL будет использовать только IP -адреса, а не имена хостов, что немного, но быстрее).
- binlog_cache_size — размер кэша для хранения изменений в двоичном журнале. Задает размер только для кэша транзакций.
- innodb_stats_on_metadata = 0 (OFF) — для ускорения работы с
INFORMATION_SCHEMA, SHOW TABLE STATUS или SHOW INDEX отключим обновление статистики при выполнении таких операций. - query_cache_size = 128M
query_сache_type = 1
— кэши запросов. 1 — в принципе включен, 128M ограничение. Не рекомендуется ставить выше 256M , т.к это может привести к блокировке таблиц. - cache_size = 0
— т.к. в рассматриваемом примере больше InnoDB таблиц, то вписываем 0. С версии MySQL 5.6 query_cache_size отключен, а с версии 8.0 удален. - innodb_file_per_table = 1 — т.к. стандартно все таблицы и индексы хранятся в одном файле используем значение 1.
- innodb_open_files и table_open_cache — обычно оба значения выставляют равными 4096 или 8192, рассчитывается, как количество таблиц во всех базах, умноженное на 2.
- innodb_buffer_pool_size — устанавливается по принципу «чем больше, тем лучше» при большем количестве InnoDB таблиц. Рекомендуется выделять до 70-80% оперативной памяти сервера.
- innodb_log_file_size — влияет на скорость записи, устанавливает размер лога операций (операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый. Значением параметра задается размер одного файла.
Установка большого размера innodb_log_file_size может привести к увеличению быстродействия, но при этом увеличится время восстановления данных, выберите от 256M до 1G.
ВНИМАНИЕ!️ При изменении параметра innodb_log_file_size остановите MySQL, сделайте резервную копию файлов ib_logfile-n (файлы чаще всего лежат в /var/lib/mysql/), измените значение параметра innodb_log_file_size и запустите MySQL. В результате
MySQL создаст новый лог-файл указанного в конфигурации размера.
- innodb_log_buffer_size — размер буфера транзакций. Обычно рекомендуется не применять, если не используете BLOB и TEXT больших размеров.
- innodb_flush_method — определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узлов, стоит выбирать между ODSYNC и ODIRECT, — первый параметр быстрее, второй безопаснее.
- key_buffer_size — буфер для работы с ключами и индексами, и sort_buffer — буфер для сортировки. Если Вы не используете MyISAM таблицы, рекомендуется установить размер key_buffer_size в 32Мб для хранения индексов временных таблиц.
- thread_cache_size — параметр указывает количество тредов (threads), уходящих в кеш при отключении клиента. При новом подключении тред не создается, а берется из кеша, что позволяет экономить ресурсы при больших нагрузках.
- innodb_flush_log_attrx_commit — может повысить пропускную способность записи данных в базу в сотни раз. Он определяет, будет ли Mysql сбрасывать каждую операцию на диск (в файл лога).
innodb_flush_log_at_trx_commit = 1 используется для случаев,
когда сохранность данных — это приоритет номер один.
innodb_flush_log_at_trx_commit = 2 для случаев, когда небольшая потеря данных не критична.
Есть еще 0 (ноль) — самый производительный, но небезопасный вариант. - max_connections — если вы получаете ошибки "Too many connections", вписанное значение стоит увеличить.
- innodb_read_io_threads , innodbwrite_io_threads — отвечают за количество потоков ввода/вывода файлов в InnoDB, обычно этим параметрам присваиваются значение 4 или 8 , на быстрых SSD -дисках установите в 16.
- innodb_thread_concurrency — установите в количество ядер * 2.
Примеры готовых конфигураций
Представленные ниже примеры необходимо внести в файл конфигурации: к примеру с помощью редактора nano:
nano /etc/my.cnf
Для 1Гб оперативной памятиДля сервера с 1Гб оперативной памяти
Для сервера с 2Гб оперативной памяти
Для сервера с 4Гб оперативной памяти
Для сервера с 8Гб оперативной памяти
Для сервера с 16Гб оперативной памяти
Для сервера с 32Гб оперативной памяти
Для сервера с 64Гб оперативной памяти
Для сервера с 128Гб оперативной памяти
Если в вашем конфиге уже есть настройка thread_concurrency — закомментируйте её при использовании готовых конфигураций.
После сохранения внесенных изменений требуется перезапустить mysql выполнив:
/etc/init.d/mysql restart
или
service mysql restart
При использовании готовых конфигураций, представленных выше, рекомендуется после их применения запустить mysqltuner и выполнить рекомендованные им действия.
Подписывайтесь на мой канал и ставьте лайки!