Оптимизация mysql

Применимость: Mysql

Слова для поиска: оптимизация mysql, тормозит mysql

В этой статье будут описаны различные настройки влияющие на на производительность MySQL

skip-name-resolve - Не производится разрешения имен хостов. Все значения в столбце Host в таблицах привилегий должны быть IP-адресами или значениями localhost. Это сильно увеличивая быстродействие запросов за счет выключения постоянных DNS запросов при “внешних” соединениях с mysql

skip-locking – Запрет внешней блокировки существенно повысит скорость работы. Нельзя использовать когда с одной базой работают одновременно 2 сервера.

low-priority-updates — эта опция снижает приоритет операций INSERT/UPDATE по сравнению с SELECT. По умолчанию UPDATE запросы имеют высший приоритет, чем SELECT. Актуально, если данные важно быстрее прочитать, чем быстрее записать. Рекомендуется использовать эту опцию.

skip-networking — не использовать сеть, т.е. вообще не обрабатывать TCP/IP соединения. Общение с сервером при этом будет происходить исключительно через сокет. Рекомендуется, если нет ПО, которое использует только TCP/IP для связи с сервером.

bind-address — интерфейс, который будет слушать сервер. В целях безопасности лучше установить здесь 127.0.0.1, если клиент не использует внешние соединения с сервером

max_allowed_packet — максимальный размер данных, в одном запосе. Следует увеличить, если появляется ошибка «Packet too large». Не следует делать чрезмерно большого размера, в большинстве случаев достаточно 8M

max_connections — максимальное количество параллельных соединений к серверу. Следует увеличить , если появляется проблема «Too many connections». Для VPS VDS параемерт не должен превышать 300

max_join_size — запрещает SELECT операторы, которые предположительно будут анализировать более указанного числа строк или больше указанного числа поисков по диску. Значение по умолчанию более 4 миллиардов, поэтому следует его уменьшить до 200 000 - 300 000 на один процессор

max_sort_length – Защита от кривых архитектур БД, когда не стоят адекватные лимиты по индексам сортировки текстовых полей. Параметр определяет, сколько байтов следует использовать при сортировке значений BLOB или TEXT (обрабатываются только первые max_sort_length байтов каждого значения, остальные игнорируются). Рекомендуется выставлять значение 256.

wait_timeout – Время в секундах, на протяжении которого сервер ожидает активности соединения прежде, чем закрыть его. Рекомендуется установить значение равное времени выполнения php скрпитов.

thread_cache_size=12 Определяет, сколько потоков должно сохраняться в кэше для повторного использования. После отключения клиента потоки клиента помещаются в кэш, если там не больше потоков, чем thread_cache_size. Все новые потоки сначала берутся из кэша, и только когда кэш становится пустым, создаются новые потоки. Значение этой переменной можно увеличить, чтобы повысить производительность, если создается много новых соединений (если потоки хорошо организованы, обычно заметного улучшения производительности не наблюдается). Насколько эффективен текущий кэш потоков, можно определить по разнице между Connections и Threads_created. Если есть возможность, рекомендуется установить это значение не меньше, чем значение переменной Max_used_connections. Если значение этой переменной больше 128, рекомендуется ограничиться этим значением

query_cache_limit — максимальный размер кэшируемого запроса. Результаты, превышающие это значение, не кэшируются (по умолчанию – 1Мб). Рекомендованно выставлять значения от 2 до 6Мб

query_cache_min_res_unit — минимальный размер хранимого в кэше блока, определяется как

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

query_cache_type —

• 0 - MySQL сервер кэширует запросы и не извлекает результаты из кэша. Однако, это не освобождает буфер кэша запросов, для этого следует установить переменную query_cache_size в значение 0.
• 1 - MySQL-сервер будет кэшировать все запросы, кроме начинающихся с конструкции SELECT SQL_NO_CACHE.
• 2 - MySQL-сервер будет кэшировать только запросы, начинающихся с конструкции SELECT SQL_CACHE

key_buffer_size — размер буфера, выделяемого под индексы и доступного всем потокам. Весьма важная настройка, влияющая на производительность. Значение по умолчанию 8 МБ, его однозначно стоит увеличить. Рекомендуется 15-30% от общего объема ОЗУ, однако нет смысла устанавливать больше, чем общий размер всех .MYI файлов. Наблюдайте за переменными состояния Key_reads и Key_read_requests, отношение Key_reads/Key_read_requests должно быть как можно меньше (< 0,01). Если это отношение велико, то размер буфера стоит увеличить. Если нет времени на анализ нету то можно воспользоваться формулой

myisam_sort_buffer_size — размер буфера, выделяемого MyISAM для сортировки индексов при REPAIR TABLE или для создания индексов при CREATE INDEX, ALTER TABLE. Значение по умолчанию 8 МБ, его стоит увеличить вплоть до 15-25% ОЗУ. Выигрыш в производительности соответственно будет только при выполнении вышеупомянутых запросов

net_buffer_length — объем памяти, выделяемый для буфера соединения и для буфера результатов на каждый поток. Буфер соединения будет указанного размера и буфер результатов будет такого же размера, т.е. на каждый поток будет выделен двойной размер net_buffer_length. Размер по умолчанию 16 КБ. В случае ограниченной памяти или использования только небольших запросов значение можно уменьшить.

read_buffer_size — каждый поток при последовательном сканировании таблиц выделяет указанный объем памяти для каждой таблицы. Как показывают тесты, это значение не следует особо увеличивать. Размер по умолчанию 128 КБ, оптимальными являются 32КБ 64КБ 128КБ для очень больших запросов 256КБ

read_rnd_buffer_size — актуально для запросов с «ORDER BY», т.е. для запросов, результат которых должен быть отсортирован и которые обращаются к таблице, имеющей индексы. Значение по умолчанию 256 КБ, рекомендуется его увеличить до 1 МБ или выше, если позволяет память. Следует учесть, что указанное значение памяти также выделяется на каждый поток

sort_buffer_size — каждый поток, производящий операции сортировки (ORDER BY) или группировки (GROUP BY), выделяет буфер указанного размера. Значение по умолчанию 2 МБ, если вы используете указанные типы запросов и если позволяет память, то значение стоит увеличить. Большое значение переменной состояния Sort_merge_passes указывает на необходимость увеличения sort_buffer_size.

table_cache (table_open_cache с версии 5.1.3) — количество кэшированных открытых таблиц для всех потоков. Открытие файла таблицы может быть достаточно ресурсоемкой операцией, поэтому лучше держать открытые таблицы в кэше. Значение по умолчанию 64, его лучше всего увеличить до общего количества таблиц, если их количество в допустимых рамках. Переменная состояния Opened_tables позволяет отслеживать число таблиц, открытых в обход кэша, желательно, чтобы ее значение было как можно ниже

tmp_table_size — максимальный размер памяти, выделяемой для временных таблиц, создаваемых MySQL для своих внутренних нужд. Это значение также ограничивается переменной max_heap_table_size, поэтому в итоге будет выбрано минимальное значение из max_heap_table_size и tmp_table_size, а остальные временные таблицы будут создаваться на диске. Значение по умолчанию зависит от системы, можно установить его равным 32 МБ и понаблюдать за переменной состояния Created_tmp_disk_tables, значение должно быть как можно меньше

• недостаточно производительности сервера mysql

Актуальность: 2013/02/13 15:21