Оптимизация MySQL для работы с Magento. Оптимизация mysql для работы с сайтом

Настройка my.cnf для стабильной работы Mysql

Один из важных моментов в скорости работы сайта, это правильная оптимизация конфигурационного файла базы данных my.cnf, с которым многие сталкиваются в процессе оптимизации системных ресурсов. Зачастую многие приведенные в сети мануалы рассказывают о настройках которые были применены к слабым конфигурациям и это не дает нужного ответа.

Я обычно использую сервера на уровне ксеонов с 32 гигабайтами памяти, поэтому найти нужную конфигурацию настроек mysql достаточно трудно, отсюда и попытки настроить самому методом тыка и проб. Что из этого получилось сегодня я постараюсь рассказать.

Исходные данные для настройки

Итак рассматриваем систему с установленным ISP manager на котором стоит Centos и MariaDB. Задача, оптимизировать работу Mysql и ускорить тем самым обработку запросов на сайтах. Для начала я приведу, пример своего my.cnf который находится по адресу etc/my.cnf, если у вас стоит Debian то смотреть надо в папке другой. Итак вот так выглядит настроенный файл, но иногда я все таки еще изменяю некоторые настройки, о которых расскажу ниже.

[mysqld]#open_files_limit = 2000

local-infile=0 innodb_file_per_table = 1

pid-file = /var/run/mysqld/mysqld.pid datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ignore-db-dir=lost+found max_allowed_packet = 1024M skip-external-locking skip-name-resolve

key_buffer = 2G

key_cache_division_limit = 70

thread_stack = 192K

tmp_table_size = 2G

max_heap_table_size = 2G

key_buffer_size = 4G

sort_buffer_size = 1G

read_buffer_size = 1G

read_rnd_buffer_size = 2G

myisam-recover = BACKUPmax_connections = 500table-cache = 120000table-open-cache = 120000

thread-cache-size = 500

interactive-timeout = 360

query_cache_limit = 12M

query_cache_size = 4G

join_buffer_size = 512M

#log_slow_queries = /var/log/mysql/mysql-slow.log

#long_query_time = 75#log-queries-not-using-indexes

expire_logs_days = 10

max_binlog_size = 100M

innodb_buffer_pool_size = 4G

innodb_buffer_pool_instances = 4

innodb_flush_log_at_trx_commit = 2

innodb_flush_method = O_DIRECT

symbolic-links=0

bind-address = 127.0.0.1

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

Тюнинг базы данных Mysql варианты

Итак что я меняю и что вижу при этом. Для начала выведу основные параметры которые считаю спорными в настройке.

key_buffer = 2G

key_cache_division_limit = 70

thread_stack = 192K

tmp_table_size = 2G

max_heap_table_size = 2G

key_buffer_size = 4Gsort_buffer_size = 1G

read_buffer_size = 1G

read_rnd_buffer_size = 2G myisam-recover = BACKUP

max_connections = 500

table-cache = 120000

table-open-cache = 120000

thread-cache-size = 500

interactive-timeout = 360

query_cache_limit = 12M

query_cache_size = 4G

join_buffer_size = 512M

Разбор параметров тюнинга Mysql

Разберёмся по порядку с каждым параметром настройки и вопросами которые есть при этом. Итак по пунктам.

key_buffer = 2Gkey_buffer_size = 4GТак и не смог я понять, различаются ли эти два параметра или первый является устаревшим значением второго.

max_connections = 500 и thread-cache-size = 500 По замерам выходило, что не более 90 одновременных подключений, так и поставил 500 с запасом. Тут следует учесть что следующий параметр thread-cache-size должен быть одинаковым числом с максимальным соединением. Поэтому там также стоит 500.

table-cache = 120000 и table-open-cache = 120000 Здесь я поставил по 120000, так как таблиц у меня достаточно много, если у вас не много сайтов, то этот параметр можно не повышать.

interactive-timeout = 360Установил в 360, чтобы снимались запросы, которые находятся без активности 6 минут или 360 секунд.

query_cache_limit = 12Mquery_cache_size = 4Gjoin_buffer_size = 512MСледующие три параметра настроил исходя из следующих наблюдений. Пробовал ставить query_cache_size от 2 до 6 гигабайт, в итоге оптимально показалось 4. Обработка запросов до 12 мегабайт мне вполне хватало, поэтому оставил 12. Но есть такое мнение, что большой query_cache_size на самом деле сильно грузит систему и желательно держать кеш в memcashed, на практике я не заметил особо, чтобы он забирал мощность, а вот при проверке кеша, обнаружил, что много запросов проходит через него.

sort_buffer_size = 1Gread_buffer_size = 1Gread_rnd_buffer_size = 2GБуфера поставил побольше, так как несколько баз имеют большой размер, хотя есть риск переполнения памяти, тем не менее они настолько не забивали память.

ok2web.ru

Оптимизация работы СУБД MySQL | Все что вы хотели знать о хостинге и сайтостроении

Как правило, знакомство с принципами работы SQL состоит из изучения актуальных на текущий момент методов работы с базами. Отыскав информацию о том, как организовать соединение сайта с базой, сделать запрос, добавить новую запись в БД, многие веб-мастера не пытаются проводить оптимизацию процессов, происходящих при работе с MySQL. В результате, пользователь решает насущную проблему – интерфейс сайта интегрируется с базой данных, но может столкнуться с трудностями при изменении условий функционирования виртуального сервера, например при резком увеличении нагрузки.

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

Данные для хранения в MySQL

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

Составление запросов для базы данных

Удобным инструментом для поиска информации в базе данных является ключ LIMIT для функции SELECT, который позволяет получать не все данные, хранящиеся в MySQL, а только их определенную порцию.

Рассмотрим пример создания запросов к категории товаров интернет-магазина. Как правило, в подобных случаях информация выдается по 10 позиций, после изучения которых пользователю предлагается получить доступ к следующей десятке. Гораздо проще для этого использовать не выборку и фильтрацию вывода скриптом, а запрос вида:

Select good, price from boks limit 20,10

В этом примере «20» — позиция, с которой начнется выдача записей, «10» — количество записей, которые будут выданы.

Составляя запрос к базе данных (SQL queries), запрашивайте только действительно нужную в текущий момент информацию. Например: в базе имеется 10 полей. По запросу:

Select * from table_name

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

select field1, field2 from table_name

Это избавит MySQL от выполнения лишней работы.

Ключ WHERE позволяет сортировать выводимую информацию по некоторому шаблону:

Select title from books where author=’Иванов‘

По этому запросу пользователь получить названия книг под авторством Иванова.

Ключ LIKE можно использовать для того, чтобы расширить поиск по предыдущему запросу:

Select title from books where author like=’Иванов’

В этом случае будут выданы названия тех книг, в которых значение поля author начинается с ‘Иванов’.

Операции, требующие большого количества ресурсов

Некоторые операции отличаются большой ресурсоемкостью. Например, в отличие от обычного запроса SELECT, операция DISTINCT потребляет гораздо больше процессорного времени. Эта ресурсоемкая операция не просто выполняет поиск информации, но и анализирует полученные данные на уникальность. Для этого приходится выполнять множество подстановок, сравнений и расчетов. Причем при увеличении объема базы, рост количества ресурсов, необходимых для выполнения DISTINCT, происходит очень и очень быстро.

Использование индексов

Индексы позволяют ускорить процесс поиска по заданному значению одного из полей базы. Без использования индексов, MySQL просматривает все поля – с первой до последней записи, сопоставляя выбранное значение с исходным. Естественно, что время, необходимое на выполнение выборки, напрямую зависит от размеров обрабатываемой таблицы. Если рассматриваемый столбец таблицы имеет индекс, MySQL совершает быстрое позиционирование к заданному столбцу, не просматривая остальные данные.

Соединение с MySQL-сервером

Установка соединения – процесс, требующи немалого времени. Нередко соединение устанавливается дольше, чем обрабатывается запрос. Логично постараться свести потребность в соединении с сервером к минимуму. Для этого не стоит обрывать его после каждой выполненной операции.

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

articles-hosting.ru

Оптимизация MySQL для работы с Magento: isranet

Еще одним удобством работы с VPS, да и с любым другим выделенным сервером, является гибкость в настройках и оптимизации, в том числе сервера баз данных MySQL.

Разумеется, на любом приличном хостинге MySQL должен быть настроен так, чтобы сайты, размещенные на нем могли функционировать.Но, во-первых, никто не будет делать оптимизацию сервера под какой-то один конкретный сайт со всеми его заморочками. Думаю это и невозможно в принципе, особенно если на сервере хостятся сайты использующие разные системы и у них разная нагрузка/посещаемость.Во-вторых, одно дело настроить сервер так чтобы сайты могли открываться, а другое дело чтобы они могли быстро открываться. А скорость работы сайта является одним из приоритетных факторов не только для пользователей, но и для поисковиков.

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

Мы используем VPS-ы для хостинга сайтов на базе Magento, так что структура баз данных примерно везде одинакова, что несколько упрощает задачи по настройке производительности.

Чтобы не гадать какие настройки будут лучше для сервера я использую скрипт tuning-primer.sh.

Попробовав его запустить на сервере под управлением CentOS + cPanel он у меня стал ругаться на то что не установлен калькулятор bc.Поставил:# yum install bc

Для того чтобы данные по текущей производительности были более ли менее верными, сервер должен работать не менее 48 часов.

Ниже я привожу свой пример файла настроек my.cnf и поясню несколько наиболее важных настроек.Повторюсь, он оптимизирован для VPS на котором работает сайт на базе Magento.

Мой VPS имеет следующие данные:1 CPU1Gb RAM40Gb HDD

[mysqld]max_connections = 30 # это число обычно равно значению MaxClients в настройках Apachemax_user_connections = 20 # сколько одновременных подключений сервер готов терпетьkey_buffer = 256Mmyisam_sort_buffer_size = 64Mjoin_buffer_size = 2Mread_buffer_size = 2Msort_buffer_size = 2Mread_rnd_buffer_size = 2Mtable_cache = 1024 # должно быть не больше значения table_definition_cachetable_definition_cache = 1024tmp_table_size = 16M # должно быть не больше значения max_heap_table_sizemax_heap_table_size = 16Mthread_concurrency = 2 # расчитывается по формуле 2 * (кол-во CPU)thread_cache_size = 16interactive_timeout = 100wait_timeout = 15connect_timeout = 10max_allowed_packet = 64Mmax_connect_errors = 10query_cache_limit = 64Mquery_cache_size = 64Mquery_cache_type = 1character-set-server=utf8collation-server=utf8_general_ciinnodb_thread_concurrency = 3 # расчитывается по формуле 2 * (кол-во CPU) + (кол-во дисков)innodb_buffer_pool_size = 384M # можно установить на значение до 70% от всей имеющейся памяти, но только в том случае, если памяти имеется больше 2Gb.innodb_additional_mem_pool_size = 2Minnodb_flush_log_at_trx_commit = 0innodb_flush_method = O_DIRECTinnodb_lock_wait_timeout = 100

[mysqld_safe]open_files_limit = 4250 #В принципе это значение по умолчанию и можно было бы его и не указывать. Оно должно быть примерно в 3 раза больше значения table_cache

[mysqldump]quickmax_allowed_packet = 16M

[myisamchk]key_buffer = 256Msort_buffer = 64Mread_buffer = 16Mwrite_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 одновременных подключений, очевидно, что сайт стал действительно популярным. А так как речь идет о сайте электронной коммерции, т.е. таком, который создан для того чтобы приносить прибыль, думаю будет не сложно увидеть связь между быстродействием сервера, возможностью обслужить всех клиентов и количеством продаж.

isranet.livejournal.com