Настройка MySQL

Не знаю почему, но по умолчанию настройки MySQL рассчитаны на десктопы 90-х годов. Например, 8Mb памяти под индексы InnoDB. Помните, как Билл Гейтс заявил, что «640 Кб памяти должно хватать каждому». Дефолтные настройки MySQL из этой серии.

Для начала моя выжимка из конфига (4G RAM, AMD Athlon 64 X2 Dual 5600+)

# ТОЛЬКО UTF! ТОЛЬКО ХАРДКОР!
collation_server=utf8_general_ci
character_set_server=utf8
default-character-set = utf8

# по умолчанию пускай будет InnoDB
default-storage-engine = InnoDB

key_buffer_size = 512M
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 16M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8

join_buffer_size = 8M
sort_buffer_size = 8M
read_rnd_buffer_size = 8M
tmp_table_size = 64M
max_heap_table_size = 32M
table_cache = 256

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1

query_cache_type       = 2
query_cache_limit        = 1M
query_cache_size        = 32M

Как посмотреть что с базой что-то не в порядке
Самый быстрый вариант зайти в phpMyAdmin во вкладку «Текущее состояние MySQL»
Там вы увидите подсказки, какие настройки нужно подкрутить.

Сами настройки можно посмотреть в том же phpMyAdmin во вкладке «Системные переменные».

У MySQL есть несколько настроек, с помощью которых можно разогнать базу до первой космической. Во-первых, настройки по хранению индексов в памяти. Мало того, что индексы значительно ускоряют выборки, но если их хранить в памяти, а не на диске (где они обычно лежат), то профит будет значительным.

key_buffer_size = 512M
Таким образом выделяем 512 Mb под индексы таблиц MyISAM. Дело в том, что у меня половина баз в MyISAM (так исторически сложилось). На 99,9% эти базы используются на чтение, так что переходить на InnoDB смысла нет.

innodb_buffer_pool_size = 512M
Такой же объем памяти выделяем на таблицы InnoDB.
Тут нужно знать меру. Если у вас 1 база размером 100 Mb, то нет смысла выделять 1 Гб памяти – она всё равно не будет использована.
Во-вторых, нужно смотреть не на размер таблицы, а на размер индексов. Пример из жизни: таблица 300 000 комментариев весит 300 Мб, а ее индексы занимают в 15 раз меньше, что вполне логично, так как обычно индексы расставляются на числовые и временные столбцы, а не на текст. Посмотреть это опять же можно в phpMyAdmin

innodb_additional_mem_pool_size = 16M
Размер памяти, выделяемый InnoDB для хранения различных внутренних структур.

innodb_flush_method = O_DIRECT
Тут мы вырубаем буферизацию таблиц для файловой системы и говорим MySQL обращаться к файлам напрямую.

innodb_flush_log_at_trx_commit = 2
При каждой транзакции MySQL пишет лог и сбрасывает на диск (значение 1). Значение 2 – сбрасываем в память. Мне не критично потерять транзакции за последние 2 секунды в случае падения сервера.

join_buffer_size = 8M
Память для запросов с джойнами, когда объединение происходит без использования индексов.

sort_buffer_size = 8M
read_rnd_buffer_size = 8M
Полезно для запросов с сортировкой ORDER BY и группировкой GROUP BY. При малом значении сортировка идет во временной таблице на диске.

tmp_table_size = 64M
max_heap_table_size = 32M
Настройки для хранения временных таблиц в памяти. Временные таблицы часто образуются при больших джойнах.

table_cache = 256
Максимальное число одновременно открытых таблиц.

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
Пишем в лог медленные запросы

query_cache_type = 2
query_cache_limit = 1M
query_cache_size = 32M
Кэширование запросов внутри MySQL

Вроде всё.