Оптимизация работы MySQL

Портал > База знаний > Bitrix > Оптимизация работы MySQL

Оптимизация работы MySQL является важнейшей составляющей в оптимизации системы в целом, так как Битрикс активно работает с базой данных.

Mysql поддерживает две самые популярные системы хранения данных - InnoDB и MyISAM. Основным недостатком MyISAM является блокировка на уровне таблицы при выполнении одновременных операций чтения и записи. При большой нагрузке MySQL именно MyISAM таблицы становятся основным узким местом в системе, мешая увеличивать утилизацию сервера и число обрабатываемых запросов. Это также приводит к увеличению времени генерации страницы за счет ожидания используемых таблиц на уровне MySQL.

Формат InnoDB обеспечивает надежное хранение данных, транзакционность и блокирование данных на уровне строк.

Для перевода таблиц базы данных, необходимо создать дамп базы данных и его копию, чтобы в случае возникновения проблем можно было всегда иметь рабочий дамп:

#mysqldump -u root --opt -R database > database.sql && cp database.sql database_backup.sql

Следующая команда позволит перевести все таблицы MyISAM в формат данных InnoDB:

#find database.sql -type f -exec sed -i 's#MyISAM#InnoDB#g' '{}' \;

Восстанавливаем дамп базы данных:

#mysql -u root database < database.sql

Сервер баз данных Mysql читает конфигурацию из следующих файлов /etc/my.cnf, /etc/mysql/conf.d/bvat.cnf, /etc/mysql/conf.d/z_bx_custom.cnf. При этом настройки в файле /etc/mysql/conf.d/bvat.cnf конфигурируются Битрикс автоматически при загрузке сервера в зависимости от количества ресурсов сервера. Настройки сервера можно изменить в файлах /etc/mysql/conf.d/z_bx_custom.cnf и /etc/my.cnf. В большинстве случаев автоматические настройки не требуют корректировки, но все же некоторые настройки, такие как уменьшение размеров буферов следует произвести.

Рассмотрим основные настройки для систем хранения данных MyISAM и InnoDB.

Оптимизация MySQL для MyISAM

Основными параметрами для MyISAM являются key_buffer_size (буфер для работы с ключами и индексами) и sort_buffer_size (буфер для сортировки).

key_buffer_size = 64M
sort_buffer_size = 32M

При наличии 16Гб памяти и более, можно увеличить key_buffer_size до 128M-256M.

Следующие параметры которые нужно изменить это кэш запросов query_cache_size, ограничение на кэшируемый элемент query_cache_limit, кэш открытых таблиц table_open_cache.

table_open_cache = 4096
query_cache_limit = 1M
query_cache_size = 128M
query_cache_type = 1
thread_cache_size = 16
join_bufer_size = 1M
max_heap_table_size = 128M
tmp_table_size = 128M

Значение query_cache_size не рекомендуется устанавливать больше 256M. Параметр thread_cache_size указывает количество тредов (threads), уходящих в кеш при отключении клиента. При наличии 32Гб памяти и более следует увеличить thread_cache_size до 32, table_open_cache устанавливается в диапазоне 4096-8192.

Размера max_heap_table_size и tmp_table_size в 128 М будет достаточно для большинства проектов. Значение join_bufer_size в большинстве случаев не стоит увеличивать больше 2 М.

Оптимизация MySQL для InnoDB

Стандартно все таблицы и индексы хранятся в одном файле, но можно использовать опцию innodb_file_per_table для установки хранения каждой таблицы в отдельном файле. Таким образом это не позволит разрастаться базе данных до огромных размеров.

Значение innodb_open_files и table_open_cache рассчитывается как количество таблиц во всех базах, умноженное на 2, ориентировочно рекомендуется устанавливать оба значения в 4096 или 8192.

innodb_file_per_table = 1
table_open_cache = 4096
innodb_open_files = 4096

При использовании только InnoDB часть опций требует корректировки:

key_buffer_size = 32M
max_allowed_packet = 1M
sort_buffer_size = 2M
read_buffer_size = 256K
read_rnd_buffer_size = 1M
thread_stack = 128K
query_cache_limit = 1M
query_cache_size = 0
query_cache_type = 1
thread_cache_size = 32
max_heap_table_size = 128M
tmp_table_size = 128M

При работе с InnoDB мы отключаем query_cache_size установкой его значения в 0, исключающее лишние действия по работе с кэшем, что особенно важно при активной работе с большими объемами данных.

Важнейшей настройкой MySQL при работе с InnoDB является innodb_buffer_pool_size, устанавливается по принципу "чем больше, тем лучше". Рекомендуется выделять до 50 % от имеющейся памяти на сервере.

innodb_buffer_pool_size = 5120M

Установка большого размера innodb_log_file_size может привести к увеличению быстродействия, при этом увеличится время восстановления данных, выберите от 256M до 1G.

Внимание! При изменении параметра innodb_log_file_size остановите MySQL, сделайте резервную копию файлов ib_logfile<n> (файлы чаще всего в /var/lib/mysql/), измените значение параметра innodb_log_file_size и запустите MySQL. В результате MySQL создаст новый лог-файл указанного в конфигурации размера.

Изменим значение innodb_flush_log_at_trx_commit = 0, таким образом буфер не будет сбрасываться на диск, а только в кеш операционной системы.

Перенесем временные файлы Mysql в оперативную память — в файле /etc/my.cnf укажем

tmpdir = /dev/shm

Значение innodb_open_files и table_open_cache рассчитывается как количество таблиц во всех базах, умноженное на 2, ориентировочное значение обеих опций 4096 или 8192.

table_open_cache = 4096
innodb_open_files = 4096

Количество потоков ввода/вывода файлов в InnoDB задается опциями innodb_read_io_threads, innodb_write_io_threads, обычно этому параметру присваивается значение 4 или 8, на быстрых SSD-дисках установите в 16. Значение innodb_thread_concurrency установите в количество ядер * 2.

innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_thread_concurrency = 32

После внесения всех изменений перезапускаем mysqld:

systemctl restart mysqld или service mysqld restart

Часто возникющая проблема - не меняется параметр table_open_cache из-за неверных системных лимитов в самой ОС. Изменяем значение LimitNOFILE:

#vi /usr/lib/systemd/system/mysqld.service
LimitNOFILE = 50000

Применяем значение:

# systemctl daemon-reload
# systemctl restart mysqld

Изменяем лимит открытых файлов в системе (добавим строки):

#vi /etc/security/limits.conf
root soft nofile 100000
root soft nofile 100000

Динамически изменим текущий лимит:

#ulimit -n 100000

Для версии MySQL 5.7+ также потребуется указать дополнительные опции:

performance_schema = OFF
skip-log-bin
sync_binlog = 0

После внесения всех изменений перезапускаем mysqld:

#systemctl restart mysqld или service mysqld restart

Добавить в избранное Распечатать статью

Также читают

Настройка почтового сервера для БИТРИКС окружения (просмотров: 3855)

Настройка почты в «Битрикс: Веб-окружение» (просмотров: 4028)

Оптимизация производительности сайта на Битрикс (просмотров: 11911)

Установка SSL-сертификата Let’s Encrypt на сервер с «Битрикс: Веб-окружение» (просмотров: 1933)

Выполнение всех агентов на cron (просмотров: 2143)