Тюнинг MySQL — подбор опции в зависимости от движка (MyISAM или InnoDB) и количества ресурсов, которым располагает сервер.
В MySQL существует 2 основных движка: InnoDB и MyISAM. Таблицы баз данных обоих типов могут существовать на одном сервере. При необходимости их можно конвертировать, использование MyISAM оправдано при преобладающем количестве операций с данными одного вида: например SELECT или INSERT.
Во всех остальных случаях выгоднее использовать InnoDB — этот движок обеспечивает лучшую сохранность данность и более высокую скорость работы с ними (блокируется при операции с таблицей строка, а не вся таблица как в случае с MyISAM).
Любой тюнинг MySQL — любая тонкая настройка должна начинаться с определения преобладающего количества таблиц определенного типа.
Определение типа таблиц MySQL
Делается это при помощи запроса вида (для innodb):
SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';
И (для myisam):
SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'myisam';
Стоит выполнить оба запроса и оценить количество таблиц, относящихся к каждому движку.
Конфигурация в my.cnf и тюнинг MySQL для InnoDB
Ниже подробно рассматриваются основные опции при конфигурации MySQL с преимущественным использованием InnoDB. Он применяется гораздо чаще, затем идет блок про общие для любого движка параметры. В конце статьи есть краткая информация по MyISAM.
Все дальнейшие модификации производятся в конфигурационном файле /etc/mysql/my.cnf (или другом файле, который подключается в /etc/mysql/my.cnf, можно найти нужный файл рекурсивным поиском по [mysqld])
innodb_buffer_pool_size — размер буфера под InnoDB таблицы и индексы. При преобладании InnoDB таблиц стоит устанавливать значение равным 80% общего количества ОЗУ (8 Гб для сервера с 10 Гб RAM является нормой). Для более мощных серверов данное значение можно увеличивать еще вплоть до 95% доступной RAM.
innodb_buffer_pool_instances — очень важный параметр, определяющий количество инстансов, которые могут существовать, по умолчанию значение параметра равно 1, более оправданным является значение вычисляемое как сумма innodb_buffer_pool_size в гигабайтах и ядер CPU, деленная пополам.
innodb_buffer_pool_size in Gb + CPUs)/2
Об этом параметре можно почитать дополнительно
Рассмотренные выше опции оказывают на производительность работы базы самое большое значение.
innodb_flush_log_at_trx_commit — значение устанавливается в 0, 1, 2. 0 означает, что лог сбрасывается на диск раз в секунду, вне зависимости от транзакций. При 1 лог сбрасывается при каждой завершенной транзакции. 2 — лог хранится в ОЗУ. Быстрее всего сервер баз данных будет работать при 0.
innodb_log_buffer_size — размер буфера лога 1-8 Мб являются хорошими значениями
innodb_log_file_size — максимальный размер каждого лог-файла. Можно увеличивать значение, это даст большую производительность поскольку не нужно будет ротировать лог слишком часто, открывать и закрывать новые файлы.
Общие параметры (не имеющие отношения к типу движка)
max_connections=2000 — устанавливаем параметр минимальным возможным при необходимости экономить ресурсы сервера, при возникновении в логе записей вида 'Too many connections…' увеличиваем значение. 4000 клиентов является максимумом. Можно довести максимальное количество клиентов до 7000, но для стандартных сборок 4000 является пределом.
key_buffer=1024M — размер буфера под индексы в оперативной памяти. Оптимальное значение 20-25% доступной RAM. Если значение параметра слишком маленькое — данные начнут писаться в SWAP, что снизит скорость работы в разы.
Памяти под индексы выделяется достаточно если отношение значений Key_reads/Key_read_request оказывается < 0,01
Узнать значения параметров можно выполнив в консоли сервера баз данных запрос
SHOW STATUS LIKE 'Key%';
Также в выводе будут значения Key_write_requests и Key_writes
table_cache=2048 – максимальное число открытых таблиц для всех потоков.
Увеличение параметра означает увеличение файловых дескрипторов, используемых для работы MySQL.
Чтобы выявить необходимое значение нужно выполнить запрос
SHOW STATUS LIKE 'Opened_tables%';
Затем установить значение переменной несколько больше значения в выводе:
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Opened_tables | 1756 |
+—————+——-+
open_files_limit = 2048
Утсанавливать значение стоит опирясь на существующее количество открытых файлов MySQL
lsof -u mysql | wc -l
В конфигурационном файле задается большее значение.
sort_buffer=128M – ускоряет запросы ORDER BY или GROUP BY. Устанавливается не очень большим (как правило — в зависимости от среды)
Выделяемое количество ОЗУ будет определяться как значение sort_buffer, умноженное на Threads_running
SHOW STATUS LIKE 'Threads_running%';
record_buffer=720M — хорошим значеинем будет эквивалент sort_buffer умноженный на 4-6.
query_cache_limit=2M – максимальный размер результата выборки (или другого запроса), который будет кэшироваться. Значение можно увеличивать, по умолчанию установлен 1 Мб.
max_join_size=1000000 — максимальное количество записей, которые могут обрабатываться единовременно.
thread_cache_size=64 — задает колчество потоков обработки данных в кэше, т.е. количество потоков которые не создаются вновь при каждом запросе. Можно увеличивать параметр, это положительно скажется на быстродействии
Вычисляется как Connections — Threads_created (должно быть примерно равно — чуть больше значения Max_used_connections)
SHOW STATUS LIKE 'Max_used_connections%';
Настройки для MyISAM
Буфер для создания индексов и REPAIR, а также ALTER TABLE в myisam таблицах
myisam_sort_buffer_size=128М
Тюнинг MySQL сводится к подбору оптимальных значений в конфигурационном файле сервера баз данных, которые соответствуют выделяемым серверу ресурсам и типу таблиц, которые используются.
Также читайте общую статью по оптимизации MySQL и возможных значениях параметров (ведение логов, кэширование).
И про анализ запросов с EXPLAIN.