Главная | Контакты | Настройки

СМЕНИТЬ ПАЛИТРУ:

MySQL - настройка после установки

Как правило, сразу после установки mysql… сразу начинают использовать. Но настройки "из коробки" подходят для небольших проектов с небольшой интенсивностью относительно простых запросов. Отсюда низкая производительность базы. А ведь достаточно поменять всего несколько настроек, чтобы ситуация изменилась кардинально. Сразу оговорюсь, речь будет идти в первую очередь про MyISAM, так как именно этот тип таблицы до сих пор используется наиболее часто (отчасти тут виноваты и настройки по умолчанию - именно этот движок таблиц установлен изначально для создания таблиц без указания типа).

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

query_cache_size=64M

Такое значение подойдет для большинства серверов mysql, на нагруженных серверах можно его еще несколько увеличить. По опыту на сервер с несколькими тысячами баз значение в 128М было более чем подходящим.

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

max_heap_table_size=32M
tmp_table_size=32M

Значений в 32М более чем достаточно. Указать нужно именно две переменные, так как размер временной таблицы в памяти определяется меньшей из max_heap_table_size и tmp_table_size. Для создания временных таблиц в памяти mysql использует движок MEMORY, как раз максимальный размер MEMORY таблиц и определяется max_heap_table_size. tmp_table_size используется при определении движка, который будет использоваться для временных таблиц. Если таблица помещается в tmp_table_size и меньше max_heap_table_size - будет использоваться MEMORY, если нет - будет использоваться MYISAM на жестком диске.

Самый важный параметр для MyISAM - размер памяти, выделяемый для хранения индексов. Индексы (или ключи) по своему назначению аналогичны оглавлению книги. С их помощью можно найти нужную страницу без просмотра всего содержимого книги. Что во много раз ускоряет выполнение запросов. Но индексы - это тоже файлы, которые нужно читать с диска, что долго. И чтобы оптимизировать этот процесс MyISAM выделяет отдельный буфер в памяти, в который сохраняет значения уже прочитанных индексов. Т.е. при следующем обращении к этому индексу жесткий диск уже не будет задействован, что во много раз повышает скорость выполнения запроса.

Размер буфера под хранение индексов MyISAM очень легко определить - достаточно определить их размер на файловой системе. MyISAM хранит индексы в файлах с расширением .MYI, сумма размеров всех файлов индексов во всех базах даст идеальный размер буфера в памяти. Подсчитать общий размер очень просто.

Первым делом определяем каталог с базами, для этого в консоли mysql вводим:
mysql> SHOW VARIABLES LIKE 'datadir';

Переходим в указанный каталог и считаем общий размер всех файлов:
# cd /var/lib/mysql/
# du -ch */*.MYI
:
54M total
Т.е. 54М будет достаточно, чтобы поместить все индексы в базу, можно взять 64 Мб с небольшим запасом на рост. 64М легко выделить почти на любой системе. Как же быть с базами, у которых индексы занимают несколько Гб? На 32х битных системах в этом плане все совсем плохо из-за ограничения по памяти в 4Гб (даже PAE не решает вопрос, так как остается ограничение на максимальный размер процесса в памяти, а mysql процесс монолитный).
Опишу наиболее плохой сценарий использования буфера под индексы. Представьте ситуацию, сервер выполнил редкий запрос, который потребовал считать достаточно много индексов с диска. MyISAM не может точно определить, будут ли эти индексы использоваться впредь, потому "на всякий случай" помещает их в буфер под хранение индексов. Но для их хранения приходится вытеснять из памяти индексы других запросов, которые могут выполняться куда чаще. В результате, серверу пришлось прочитать с диска индексы для этого редкого запроса, да еще потом считать с диска индексы часто используемых запросов, которые были вытеснены этим большим и редким запросом.
Чтобы избежать чрезмерного числа операций чтения жесткого диска MyISAM использует механизм разделения буфера памяти индексов на "горячий" и "холодный". При первом чтении индекса он попадает в холодный кеш. Если индекс используется еще раз, то он перемещается в горячий кеш. Если памяти не хватает, то вытесняются только те значения индексов, которые находятся в холодной части кеша. Т.е. часто используемые индексы остаются в памяти.
Размер буфера под индексы определяется переменной key_buffer_size, процентное разделение на холодный и горячий буферы задается key_cache_division_limit.
key_buffer_size=128M
key_cache_division_limit=70

Как правило, для небольших и средних сайтов (и соответственно баз mysql) хватает 128M под буфер индексов, key_cache_division_limit=70 - выделяет 70% под горячий кеш и 30% пол холодный из общего объема буфера индексов.
Вот этих пяти переменных достаточно, чтобы значительно улучшить работу сервера MySQL: значительно снизить нагрузку на диски и вместе с тем ускорить выполнение запросов во много раз.