Оптимизация MySQL для работы с Magento
Еще одним удобством работы с VPS, да и с любым другим выделенным сервером, является гибкость в настройках и оптимизации, в том числе сервера баз данных MySQL.
Разумеется, на любом приличном хостинге MySQL должен быть настроен так, чтобы сайты, размещенные на нем могли функционировать.
Но, во-первых, никто не будет делать оптимизацию сервера под какой-то один конкретный сайт со всеми его заморочками. Думаю это и невозможно в принципе, особенно если на сервере хостятся сайты использующие разные системы и у них разная нагрузка/посещаемость.
Во-вторых, одно дело настроить сервер так чтобы сайты могли открываться, а другое дело чтобы они могли быстро открываться. А скорость работы сайта является одним из приоритетных факторов не только для пользователей, но и для поисковиков.
Поэтому тюнинг MySQL является неотъемлемой частью оптимизации сервера и настройки будут различаться, не только в зависимости от конфигурации самого сервера, но и от того, какой сайт на нем размещен, какова структура его базы данных, какова его посещаемость.
Мы используем VPS-ы для хостинга сайтов на базе Magento, так что структура баз данных примерно везде одинакова, что несколько упрощает задачи по настройке производительности.
Чтобы не гадать какие настройки будут лучше для сервера я использую скрипт tuning-primer.sh.
Попробовав его запустить на сервере под управлением CentOS + cPanel он у меня стал ругаться на то что не установлен калькулятор bc.
Поставил:
# yum install bc
Для того чтобы данные по текущей производительности были более ли менее верными, сервер должен работать не менее 48 часов.
Ниже я привожу свой пример файла настроек my.cnf и поясню несколько наиболее важных настроек.
Повторюсь, он оптимизирован для VPS на котором работает сайт на базе Magento.
Мой VPS имеет следующие данные:
1 CPU
1Gb RAM
40Gb HDD
[mysqld]
max_connections = 30 # это число обычно равно значению MaxClients в настройках Apache
max_user_connections = 20 # сколько одновременных подключений сервер готов терпеть
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
table_cache = 1024 # должно быть не больше значения table_definition_cache
table_definition_cache = 1024
tmp_table_size = 16M # должно быть не больше значения max_heap_table_size
max_heap_table_size = 16M
thread_concurrency = 2 # расчитывается по формуле 2 * (кол-во CPU)
thread_cache_size = 16
interactive_timeout = 100
wait_timeout = 15
connect_timeout = 10
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 64M
query_cache_size = 64M
query_cache_type = 1
character-set-server=utf8
collation-server=utf8_general_ci
innodb_thread_concurrency = 3 # расчитывается по формуле 2 * (кол-во CPU) + (кол-во дисков)
innodb_buffer_pool_size = 384M # можно установить на значение до 70% от всей имеющейся памяти, но только в том случае, если памяти имеется больше 2Gb.
innodb_additional_mem_pool_size = 2M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 100
[mysqld_safe]
open_files_limit = 4250 #В принципе это значение по умолчанию и можно было бы его и не указывать. Оно должно быть примерно в 3 раза больше значения table_cache
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 256M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
Как вообще правильно расчитать максимальные значения некоторых настроек?
Кроме того, что есть статистика сервера и скрипт tuning-primer.sh, который подскажет желаемые значения, нужно еще учитывать сколько процессоров и сколько памяти вообще есть у сервера.
Потому как какой смысл ставить значение max_connections = 300, если сервер физически не сможет их обслужить и так или иначе упадет или выжрет весь своп.
Также, нет никакого резона выставлять значения в разы превышающие реально используемые, так как сервер просто займет под себя всю доступную память и при этом для других процессов ничего не останется.
Для расчета максимального количества используемой MySQL сервером памяти можно использовать следующую формулу:
key_buffer_size+(read_buffer_size+sort_buffer_size)*max_connections=Total Memory
Понятно, что Total Memory не может быть равна физически доступной памяти сервера, ведь есть и другие сервисы и процессы, которым необходима память и которые, точно также резервируют некоторое ее количество под свои нужды.
Оптимально, если MySQL будет использовать не более 50% доступной физической памяти.
Таким образом можно видеть, на что мы, собственно, можем расчитывать имея на борту всего 1Gb памяти. Реально - не на многое. С другой стороны, если мы дошли до того, что сервер использует более 20 одновременных подключений, очевидно, что сайт стал действительно популярным. А так как речь идет о сайте электронной коммерции, т.е. таком, который создан для того чтобы приносить прибыль, думаю будет не сложно увидеть связь между быстродействием сервера, возможностью обслужить всех клиентов и количеством продаж.
Разумеется, на любом приличном хостинге MySQL должен быть настроен так, чтобы сайты, размещенные на нем могли функционировать.
Но, во-первых, никто не будет делать оптимизацию сервера под какой-то один конкретный сайт со всеми его заморочками. Думаю это и невозможно в принципе, особенно если на сервере хостятся сайты использующие разные системы и у них разная нагрузка/посещаемость.
Во-вторых, одно дело настроить сервер так чтобы сайты могли открываться, а другое дело чтобы они могли быстро открываться. А скорость работы сайта является одним из приоритетных факторов не только для пользователей, но и для поисковиков.
Поэтому тюнинг MySQL является неотъемлемой частью оптимизации сервера и настройки будут различаться, не только в зависимости от конфигурации самого сервера, но и от того, какой сайт на нем размещен, какова структура его базы данных, какова его посещаемость.
Мы используем VPS-ы для хостинга сайтов на базе Magento, так что структура баз данных примерно везде одинакова, что несколько упрощает задачи по настройке производительности.
Чтобы не гадать какие настройки будут лучше для сервера я использую скрипт tuning-primer.sh.
Попробовав его запустить на сервере под управлением CentOS + cPanel он у меня стал ругаться на то что не установлен калькулятор bc.
Поставил:
# yum install bc
Для того чтобы данные по текущей производительности были более ли менее верными, сервер должен работать не менее 48 часов.
Ниже я привожу свой пример файла настроек my.cnf и поясню несколько наиболее важных настроек.
Повторюсь, он оптимизирован для VPS на котором работает сайт на базе Magento.
Мой VPS имеет следующие данные:
1 CPU
1Gb RAM
40Gb HDD
[mysqld]
max_connections = 30 # это число обычно равно значению MaxClients в настройках Apache
max_user_connections = 20 # сколько одновременных подключений сервер готов терпеть
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
table_cache = 1024 # должно быть не больше значения table_definition_cache
table_definition_cache = 1024
tmp_table_size = 16M # должно быть не больше значения max_heap_table_size
max_heap_table_size = 16M
thread_concurrency = 2 # расчитывается по формуле 2 * (кол-во CPU)
thread_cache_size = 16
interactive_timeout = 100
wait_timeout = 15
connect_timeout = 10
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 64M
query_cache_size = 64M
query_cache_type = 1
character-set-server=utf8
collation-server=utf8_general_ci
innodb_thread_concurrency = 3 # расчитывается по формуле 2 * (кол-во CPU) + (кол-во дисков)
innodb_buffer_pool_size = 384M # можно установить на значение до 70% от всей имеющейся памяти, но только в том случае, если памяти имеется больше 2Gb.
innodb_additional_mem_pool_size = 2M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 100
[mysqld_safe]
open_files_limit = 4250 #В принципе это значение по умолчанию и можно было бы его и не указывать. Оно должно быть примерно в 3 раза больше значения table_cache
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 256M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
Как вообще правильно расчитать максимальные значения некоторых настроек?
Кроме того, что есть статистика сервера и скрипт tuning-primer.sh, который подскажет желаемые значения, нужно еще учитывать сколько процессоров и сколько памяти вообще есть у сервера.
Потому как какой смысл ставить значение max_connections = 300, если сервер физически не сможет их обслужить и так или иначе упадет или выжрет весь своп.
Также, нет никакого резона выставлять значения в разы превышающие реально используемые, так как сервер просто займет под себя всю доступную память и при этом для других процессов ничего не останется.
Для расчета максимального количества используемой MySQL сервером памяти можно использовать следующую формулу:
key_buffer_size+(read_buffer_size+sort_buffer_size)*max_connections=Total Memory
Понятно, что Total Memory не может быть равна физически доступной памяти сервера, ведь есть и другие сервисы и процессы, которым необходима память и которые, точно также резервируют некоторое ее количество под свои нужды.
Оптимально, если MySQL будет использовать не более 50% доступной физической памяти.
Таким образом можно видеть, на что мы, собственно, можем расчитывать имея на борту всего 1Gb памяти. Реально - не на многое. С другой стороны, если мы дошли до того, что сервер использует более 20 одновременных подключений, очевидно, что сайт стал действительно популярным. А так как речь идет о сайте электронной коммерции, т.е. таком, который создан для того чтобы приносить прибыль, думаю будет не сложно увидеть связь между быстродействием сервера, возможностью обслужить всех клиентов и количеством продаж.