Несмотря на то, что в mySQL существует довольно много настроек, которые Вы можете изменить, есть набор действительно очень важных характеристик, которые обязательно нужно оптимизировать под собственный сервер. Обычно после такой небольшой настройки производительность сервера заметно увеличивается.

Любой тюнинг 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’;

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

  • key_buffer_size — крайне важная настройка при использовании MyISAM-таблиц. Установите её равной около 30-40% от доступной оперативной памяти, если используете только MyISAM. Правильный размер зависит от размеров индексов, данных и нагрузки на сервер — помните, что MyISAM использует кэш операционной системы (ОС), чтобы хранить данные, поэтому нужно оставить достаточно места в ОЗУ под данные, и данные могут занимать значительно больше места, чем индексы. Однако обязательно проверьте, чтобы всё место, отводимое директивой key_buffer_size под кэш, постоянно использовалось — нередко можно видеть ситуации, когда под кэш индексов отведено 4 ГБ, хотя общий размер всех .MYI-файлов не превышает 1 ГБ. Делать так совершенно бесполезно, Вы только потратите ресурсы. Если у Вас практически нет MyISAM-таблиц, то key_buffer_size следует выставить около 16-32 МБ — они будут использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске.
  • innodb_buffer_pool_size — не менее важная настройка, но уже для InnoDB, обязательно обратите на неё внимание, если собираетесь использовать в основном InnoDB-таблицы, т.к. они значительно более чувствительны к размеру буфера, чем MyISAM-таблицы. MyISAM-таблицы в принципе могут неплохо работать даже с большим количеством данных и при стандартном значении key_buffer_size, однако mySQL может сильно «тормозить» при неверном значении innodb_buffer_pool_size. InnoDB использует свой буфер для хранения и индексов, и данных, поэтому нет необходимости оставлять память под кэш ОС — устанавливайте innodb_buffer_pool_size в 70-80% доступной оперативной памяти (если, конечно, используются только InnoDB-таблицы). Относительно максимального размера данной опции — аналогично key_buffer_size — не стоит увлекаться, нужно найти оптимальный размер, найдите лучшее применение доступной памяти.
    Mysql рекомендует устанавливать этот параметр в 80% от доступной оперативной памяти. Однако это приемлемо на небольших конфигурациях (до 8Гб оперативной памяти). Если же речь идет о более мощном железе — можно выбирать значения в 90%…95% от всей памяти.

    innodb_buffer_pool_size = 240G

    # Для сервера с 256Гб оперативки можно смело ставить в 95%

    Правила выбора значения

    • Чем больше, тем лучше.
    • Если сервер уходит в SWAP, стоит уменьшать значения.
  • innodb_buffer_pool_instances  — очень важный параметр, определяющий количество инстансов, которые могут существовать, по умолчанию значение параметра равно 1, более оправданным является значение вычисляемое как сумма innodb_buffer_pool_size в гигабайтах и ядер CPU, деленная пополам.innodb_buffer_pool_size in Gb + CPUs)/2

    Об этом параметре можно почитать дополнительно

  • innodb_additional_mem_pool_size — данная опция практически никак не влияет на производительность mySQL, однако рекомендую оставлять для InnoDB около 20 МБ (или чуть больше) под различные внутренние нужды.
  • innodb_log_file_size — крайне важная настройка в условиях баз данных с частыми операциями записи в таблицы, в особенности при больших объёмах. Большие размеры увеличивают быстродействие, однако будьте осторожны — увеличится и время восстановления данных. Я обычно выставляю значение около 64-512 МБ в зависимости от размера сервера.
  • innodb_log_buffer_size — стандартное значение данной опции вполне подойдёт для большинства систем со средним количеством операций записи и небольшими транзакциями. Если же в Вашей системе бывают всплески активности, или Вы активно работаете с BLOB-данными, то рекомендую немного увеличить значение innodb_log_buffer_size. Однако не переусердствуйте — слишком большое значение будет пустой тратой памяти: буфер сбрасывается каждую секунду, поэтому Вам не понадобится больше места, чем требуется в течение этой секунды. Рекомендуемое значение — около 8-16 МБ, а для небольших баз — и того меньше.
  • innodb_flush_log_at_trx_commit — жалуетесь, что InnoDB работает в 100 раз медленнее MyISAM? Вероятно, Вы забыли про настройку innodb_flush_log_at_trx_commit. Значение по умолчанию «1» означает, что каждая UPDATE-транзакция (или аналогичная команда вне транзакции) должна сбрасывать буфер на диск, что достаточно ресурсоёмко. Большинство приложений, в особенности ранее использовавшие таблицы MyISAM, будут хорошо работать со значением «2» (т.е. «не сбрасывать буфер на диск, только в кэш ОС»). Лог, однако, всё равно будет сбрасываться на диск каждые 1-2 секунды, поэтому в случае аварии Вы потеряете максимум 1-2 секунды обновлений. Значение «0» повысит производительность, но Вы рискуете потерять данные даже при аварийной остановке mySQL-сервера, в то время как при установке значение innodb_flush_log_at_trx_commit в «2» Вы потеряете данные только при аварии всей операционной системы.
  • table_cache — открытие таблиц может быть весьма ресурсоёмко. К примеру, MyISAM-таблицы помечают заголовки .MYI файлов как «используемые в текущий момент». Обычно не рекомендуется открывать таблицы слишком часто, поэтому лучше, чтобы кэш был достаточных размеров, чтобы держать все Ваши таблицы открытыми. Для этого используется некоторое количество ресурсов ОС и оперативной памяти, однако это обычно не является существенной проблемой для современных серверов. Если у Вас несколько сотен таблиц, то стартовым значением для опции table_cache может быть«1024» (помните, что каждое соединение требует свой собственный дескриптор). Если у Вас ещё больше таблиц или очень много соединений — увеличьте значение параметра. Я видел mySQL сервера со значением table_cache равной 100 000.
  • thread_cache — создание/уничтожение потоков также является ресурсоёмкой операцией, которая происходит при каждой установке соединения и каждом разрыве соединения. Я обычно выставляю эту опцию равную 16. Если у Вашего приложения могут быть скачки количество конкурентных соединений и по переменной Threads_Created виден быстрый рост количества потоков, то стоит увеличить значение thread_cache. Цель — не допускать создания новых потоков в условиях нормального функционирования сервера.
  • query_cache_size — если Ваше приложение много и часто читает данные, и при этом у Вас нет кэша на уровне приложения, эта опция может очень помочь. Не ставьте здесь слишком большое значение, так как обслуживание большого кэша запросов будет само по себе затратным. Рекомендуемое значение — от 32 до 512 МБ. Не забудьте проверить, насколько хорошо используется кэш запросов — в некоторых условиях (при небольшом количестве хитов в кэше, т.е. когда практически не выбираются одинаковые данные) использование большого кэша может ухудшить производительность.

Общие параметры  (не имеющие отношения к типу движка)

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 сводится к подбору оптимальных значений в конфигурационном файле сервера баз данных, которые соответствуют выделяемым серверу ресурсам и типу таблиц, которые используются.

Параметры кэширования запросов, которые могут задаваться в my.cnf


thread_cache_size (default 0)

количество потоков, которое сервер должен кэшировать для повторного использования, хорошее значение для начала — 4


table_cache/tables_open_cache(default 64)

количество открытых таблиц для всех потоков


query_cache_limit (default 1M)

максимальный размер запроса который может быть помещен к кеш


query_cache_size (default 0)

поскольку здесь 0, следующий параметр работать не будет
4-8М хорошие значения, количество памяти для кэширования запросов


query_cache_type (default 1=on)

0=off, 2=on if needed

Буферы MySQL

key_buffer_size (default 8M)

буфер для индексов таблиц MYISAM, 24-48М нормальные значения

read_buffer_size (default 128K)

последовательный буфер потоков


join_buffer_size (default 128K)

используется под JOIN без индексов, лучшим решением (вместо использования параметра) является ораганизация базы с джойнами которые могут быть проиндексированы — если такой возможности нет джойны ускоряются параметром join_buffer_size

Другие переменные

connect_timeout (MySQL pre-5.1.23: default 5, MySQL 5.1.23+: default 10)
количество секунд по прошествии которых сервер баз данных будет выдавать ошибку, при активном веб-сервере значение можно уменьшать чтобы увеличить скорость работы, на медленной машине — можно увеличивать

max_connect_errors
 (default 10)
максимальное количество единовременных соединений с сервером баз данных с хоста запрос блокируется если он прерывается запросами с того же хоста до момента окончания обработки запроса)
блокируются навсегда, очистить можно только из командной оболочки MySQL:

FLUSH HOSTS;

В случае атаки на сервер нужно уменьшать (5) чтобы отсекать попытки соединения, при большой активности веб-сервера можно увеличивать

max_allowed_packet (default 1M)

максимальный размер пакета, при подключении

tmp_table_size (system-specific default)

16М — довольно много
максимальный размер памяти выделяемой под хранение временных данных

Переменные, использование которых не требуется

thread_concurrency
используется только на Solaris, на linux mysql 5.5+ при использовании переменной MySQL не сможет запуститься

innodb_thread_concurrency

похожая на предыдущую переменная, является, тем не менее, совсем другой и относится к innodb

skip-locking
сейчас параметр называется skip-external-locking (по умолчанию используется начиная с MySQL 4.0 — непосредственное указание в конфиге не требуется)

Образцы конфигурационных файлов MySQL

Найти их можно в каталоге /usr/share/mysql

my-huge.cnf
my-large.cnf
my.small.cnf
my-innodb-heavy-4G.cnf
my-medium.cnf

Использование этих шаблонов не всегда оправдано поскольку в них применяется очень большое количество переменных использование которых в конфиге никак не объясняется, при недостаточном понимании структуры файла шаблоны лучше не использовать.

Также в шаблонах по умолчанию применяются thread_concurrency, что означает невозможность запуска MySQL с шаблонных конфигом в принципе

Также возможно использование скрипта: MySQLTunner.pl (для его правильной работы возможно понадобится дополнительно установить пакет bc)

Общая оптимизация

mysqltuner.pl

wget mysqltuner.pl

chmod 755 mysqltuner.pl

Использование

./mysqltuner.pl

или

perl mysqltuner.pl

Сервер баз данных должен работать минимум 24 часа до запуска данной утилиты

mysqlidxchk

Анализирует общие логи или логи медленных запросов и ищет неиспользуемые индексы, их потребуется удалить — это позволит увеличить скорость работы базы данных

wget http://hackmysql.com/scripts/mysqlidxchk-1.1

chmod 755 mysqlidxchk* mysqlidxchk

Использование

./mysqlidxchk —general /var/lib/mysql/ general.log

Парсинг лог-файлов

mysqlsla

Определяет на основании анализа логов медленных запросов и общих логов пользователей которые используют базу больше всего (в процентном отношении)

wget http://hackmysql.com/scripts/mysqlsla

chmod 755 mysqlsla*

mv mysqlsla* mysqlsla

Использование (логирование должно быть включено)

./mysqlsla -lt general /var/lib/mysql/general.log

./mysqlsla -lt slow /var/lib/mysql/slow.log

Формирование отчетов о статусе

mysqlreport

Утилита интерпретирует вывод SHOW STATUS и составляет на основании собранной статистики отчет о работе MySQL

wget http://hackmysql.com/scripts/mysqlreport

chmod 755 mysqlreport

Исопльзование
./mysqlreport

Инструмент довольно сложен в использовании и предполагает хорошие знания MySQL

Онлайн калькулятор максимального потребления памяти MySQL