MySQL server и его настройка, тюнинг MySQL

В 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';

 

MySQL server настройка, тюнинг mysql

 

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

 

 

Конфигурация MySQL в my.cnf

Ниже рассматриваются основные опции при конфигурации MySQL с преимущественным использованием InnoDB.

 

Все дальнейшие модификации производятся в конфигурационном файле /etc/mysql/my.cnf

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 — максимальное количество записей, которые могут обрабатываться единовременно. Своего рода «защита от дупака» при использовании JOIN.

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.