AddThis Smart Layers

четверг, 24 апреля 2014 г.

Оптимизация MySQL для работы с Magento. Ревизия 2

Время несется с сумасшедшей скоростью. Казалось бы, буквально недавно я писал об оптимизации настроек MySQL для серверов Magento, а оказывается прошло уже полтора года с тех пор.

Что же изменилось с тех пор и почему имеет смысл провести ревизию настроек?
На самом деле, тот факт что Magento добралась до версии 1.8.1.0 на момент написания этого поста абсолютно не влияет на изменения в настройках MySQL.


Да, система стала еще стабильнее, еще быстрее, остается все меньше неоптимизированных запросов. Но вместе с этим это все та же старая добрая Magento первого поколения. И до тех пор пока не выйдет Magento 2.0 мы все также будем оптимизировать сервер баз данных.

Кроме того, если бы дело было в самой Magento. К сожалению, далеко не все разработчики сторонних расширений умеют оптимизировать свои запросы, использовать индексы, триггеры, временные таблицы.

В результате даже довольно мощный сервер может загнуться уже при десятке одновременных пользователях, если не оптимизировать настройки MySQL.

Одним из изменений стал выход MySQL 5.6.x в котором, кроме различных оптимизаций в самом ядре произошли изменения в различных директивах настроек.

И второй существенной причиной стал существенный прорыв в технологиях виртуализации серверов и, как результат снижение их стоимости.

На сегодняшний день абсолютно не имеет смысл экономить на ресурсах сервера, когда стоимость виртуального сервера с 2-4 гигабайтами памяти и с 2-4 процессорами начинается с $20 в месяц.

Таким образом, мы в полной мере можем использовать память сервера для хранения и быстрого доступа к SQL запросам. Наличие же нескольких процессоров позволит быстрее обрабатывать те запросы, которые не были закешированы.

Итак, ниже речь будет идти об оптимизации сервера MySQL 5.6

Используемая конфигурация виртуального сервера (VPS):
Кол-во CPU: 2
Объем памяти RAM: 4Gb

[mysqld]
max_connections = 50
character-set-server = utf8
collation-server = utf8_general_ci
default-storage-engine = InnoDB
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
max_allowed_packet = 64M
query_cache_type = 1
query_cache_limit = 128M
query_cache_size = 256M
innodb_thread_concurrency = 5
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_log_buffer_size = 256M
innodb_sort_buffer_size = 2M
innodb_lock_wait_timeout = 120
tmp_table_size = 1024M
max_heap_table_size = 1024M
table_open_cache = 5000
wait_timeout = 30
interactive_timeout = 30

[mysqldump]
max_allowed_packet = 64M
quick

Теперь попробуем разобраться что здесь к чему и почему.

max_connections
Количество максимальных коннектов по умолчанию 151.
Много это или мало?
Это сильно зависит от трафика на сервер, от скорости обработки запросов и от оптимизации сайта.
Если сайт хорошо оптимизирован, используются различные технологии кеширования страниц, то количество запросов к базе данных существенно сокращается.
При этом, большую роль играет оптимизация запросов к базе данных. В идеале не должно быть запросов, время выполнения которых занимает более 0.5 секунды.
Если сервер достаточно мощный, большинство запросов закешированы, для хранения данных используется память RAM, количество одновременных подключений будет умеренным.

Для примера возьму сайт одного из наших клиентов.
Среднесуточная посещаемость сайта составляет порядка 5000 посетителей.
При этом, кол-во одновременных подключений к базе данных не превышает 50.

Нужно ли оставлять значение max_connections по умолчанию? Да, только в случае, когда есть избыток памяти RAM. Потому что некоторые используемые в нашей конфигурации настройки не являются глобальными и при запуске сервера будут помножены на максимальное заданное количество подключений и зарезервированы. Другими словами, не важно какая посещаемость будет у сайта и какие другие процессы потребуют дополнительных ресурсов, львиную долю памяти будет всегда занимать сервер баз данных.

Поэтому имеет все же смысл снизить значение max_connections до реального.

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

Например, мы больше не будем изменять параметр key_buffer, который к тому же в MySQL 5.6 теперь называется  key_buffer_size. Потому что этот параметр актуален только для таблиц MyISAM, коих в Magento кот наплакал. Кроме того, значение по умолчанию для данного аттрибута 8Mb, что более чем достаточно.
Аналогично поступим с параметром myisam_sort_buffer_size.

thread_cache_size
Этот параметр начиная с версии MySQL 5.6.8 принимает значение -1 (autosized).

thread_concurrency
Также более неактуален.

table_cache 
Был переименован в table_open_cache. Значение этого параметра сильно зависит от количества баз данных и таблиц в них.

table_definition_cache
Более нет необходимости указывать, так как он начиная с версии MySQL 5.6.8 принимает значение -1 (autosized).

open_files_limit 
Теперь настраивается автоматически.

innodb_additional_mem_pool_size 
Был упразднен начиная с версии MySQL 5.6.3.

innodb_thread_concurrency
Все также расчитывается по формуле 2 * (кол-во CPU) + (кол-во дисков). Так как по сравнению с предыдущей ревизией количество CPU у нас увеличилось, то используем значение 5.

max_connect_errors
Начиная с версии MySQL 5.6.6 значение по умолчанию для данного параметра 100. Более чем достаточно.

tmp_table_size
Так как сейчас у нас есть в наличии значительно больше памяти, мы можем себе позволить хранить намного больше временных таблиц в памяти.
Значение этого параметра сильно зависит от количества баз данных и таблиц в них.

max_heap_table_size
Все также должен иметь значение аналогичное tmp_table_size.

innodb_log_file_size
Расчитывается по формуле 1/N от размера innodb_buffer_pool_size, где N значение параметра innodb_log_files_in_group, который в свою очередь имеет по умочанию 2.
Таким образом, если мы выделяем для innodb_buffer_pool_size 1024Mb памяти, то для параметра innodb_log_file_size можно выделить четверть от этого значения, т.е. 256Mb.

innodb_log_buffer_size
Позволяет выполнять большие транзакции, в том числе операции с большим количеством строк, без необходимости записывать лог в файл и использовать файловую систему сервера. Однозначно имеет смысл использовать быструю память RAM, вместо относительно медленной файловой системы.

innodb_sort_buffer_size
Этот параметр появился только начиная с версии MySQL 5.6.4. Его значение по умолчанию 1Mb, попробуем задать ему 2Mb.

Как можно видеть, чем больше памяти RAM имеется в наличие, тем шире возможности для оптимизации сервера баз данных.

Так что евреи, не жалейте заварки не нужно экономить на дешевой памяти. Чем больше памяти, тем меньше будет использоваться медленная файловая система сервера, а соответственно сайт будет работать в разы быстрее.

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

Нет и не может быть универсальных настроек, коих можно найти в гугле в избытке.

Для удобства тестирования настроек очень удобно пользоваться следующими инструментами:
MySQLTuner
MySQL Performance Tuning Primer
MySQL Report

Основным источником информации по всем параметрам настроек является официальный сайт.
Server System Variables
InnoDB Startup Options and System Variables






Комментариев нет :

Отправить комментарий