Домой Администрирование Настройка производительности MySQL

Настройка производительности MySQL

Задача составить производительную конфигурацию для MySQL сервера, вещь довольно не тривиальная, так как файл конфигурации сервера имеет свыше 450 изменяемых параметров, которые к большому сожалению никак не классифицированы и в большинстве своем, даже не описаны в фале конфигурации по умолчанию.

Поэтому основной проблемой становится принятия решения, что же необходимо изменить и как это отразится на результате. Крайне полезной для меня стал материал статьи “17 ключевых параметров настройки MySQL сервера“, которая доступна на английском языке и послужила отправной точкой для настройки моего сервера.  Данная статья стала записками тех ключевых моментов, которые я вынес при настройке своих серверов.

Первым моментом, который стоит запомнить, что метод “проб и ошибок” основанный на изменении нескольких параметров с последующей оценкой того как себя ведет сервер – НЕ РАБОТАЕТ! И проблема здесь заключается в том, что в момент, когда вы производите оценку, того, как работает ваше приложение, ситуация уже могла кардинально изменится. К тому же при оценке производительности  нельзя полагаться на ощущениях, а также стоит учитывать такие понятия как “прогрев” (прогрев – некоторый набор операций, который как бы разогревает сервер, например это те операции которые производятся пока не заполняться различные кэши) и накладные расходы.

При этом автор в своей статье указывает, что когда речь заходит о производительности MySQL, то есть только 17 конфигурационных параметров которые должны быть корректно настроены. И, вы можете легко определить правильные значения для большинства из них (однако стоит понимать как это сделать правильно, так как некоторые из них взаимосвязаны и не могут изменятся по отдельности)!

Есть также несколько простых правил при работе с конфигурацией MySQL:

  1. Никогда не доверяйте первому ответу найденному в Google и тем более не изменяйте параметры сразу. А также запоминайте, что вы меняете;
  2. Не гонитесь за тонкой настройкой (ка правило 10-15 параметров дадут максимальный эффект, а изменение остальных не принесет дополнительных преимуществ , но может навредить).В большинстве случаев, если изменение основных параметров не принесло пользы, то проблема возможно в чем то другом, это могут быть плохие запросы, нехватка ресурсов и т.п.
  3. Изменяйте только один параметр за раз. Это правило касается уже настроенных систем, в таких случая хорошим тоном, будет изменить только один параметр и выждать некоторое время для оценки его влияния, но для нового сервера, можно сразу внести множество изменений за раз.
  4. Не забывайте вносить изменения в файл конфигурации. Начиная с версии MySQL 5.7 появилась возможность вносить изменения на ходу (без перезагрузки), но в таком случае эти изменения не вносятся в файл конфигурации и будет утеряны при перезагрузке.
  5. Внимательно изменяйте файл, что бы не внести в него дубликаты значений. Это важный момент, так как если в файле находится два одинаковых параметра, то будет использовано последнее определенное значение и при этом сервер не выдаст никаких предупреждений, а вы можете изменять не то значение. Стоит также заметить, что для сервера символы тире “-”  и подчеркивание “_” взаимозаменяемые, так что “innodb-log-file-size” и “innodb_log_file_size” – это один и тот же параметр.
  6. Нельзя просто взять и удвоить значения всех буферов. Некоторые из них являются локальными, а некоторые глобальными, некоторые связаны с механизмом хранения данных, а некоторые из них распространяются на весь сервер. Так что если вы пытаетесь увеличить выделение памяти на какой то буфер сервера, стоить проверить, а есть ли в этом смысл (все наиболее распространенные буфера приведены в этой статье, остальные как правило не имеет значительного влияния).
  7. Используйте правильный раздел  – [mysqld]. В файле конфигурации есть множество разделов, такие как [mysql], [client], [mysqld_safe] и другие, если вы запишите переменные в не тот раздел, то изменения не окажут никакого влияния. Исключения это использование скрипта mysqld_multi, тогда изменения необходимо вносить в разные разделе, свои для каждого экземпляра сервера.

Далее представлен список ключевых переменных MySQL, которые необходимы для настройки, чтобы получить максимальную производительность от вашего сервера MySQL:

1. DEFAULT_STORAGE_ENGINE – выбирайте правильную систему хранения данных

Если ваш сервер версии 5.6 или 5.7, то все таблицы создаются в системе InnoDB, если нет то укажите систему хранения по умолчанию default_storage_engine=’InnoDB’, так как InnoDB является лучшим механизмом хранения для MySQL (а также для Percona сервера и MariaDB)  – он поддерживает транзакции, высокий параллелизм, имеет потрясающую производительность (при правильной настройке).

Посмотреть список доступных систем хранения можно запросом: SELECT engine, support, transactions, xa FROM information_schema.engines;

Объем данных занимаемый различными системами хранения:

Список таблиц в системе хранения MyISAM:

Подготовка списка запросов на преобразование таблиц в InnoDB:

Для того, чтобы все таблицы создавались в определенном хранилище можно использовать переменную enforce_storage_engine = InnoDB (доступную в Percona и MariaDB).

2. INNODB_BUFFER_POOL_SIZE – получите максимум памяти

Это наиболее важная переменная для системы хранения InnoDB. На самом деле, если вы используете InnoDB в качестве основного механизма хранения, то для вас – это самая важная переменная MySQL.

По существу, innodb_buffer_pool_size определяет, сколько памяти MySQL следует выделить для пула буферов InnoDB, а это место где InnoDB хранит кэшированные данные, вторичные индексы, грязные данных (которые были изменены, но еще не записаны на диск), а также различные внутренние структуры, такие как адаптивный хеш индекс.

Если у Вас отдельный сервер MySQL, то ему оптимально установить размер innodb_buffer_pool_size от 75% до 80% от размера оперативной памяти (почему не 100, а просто потому, что серверу необходимо оставить память и для других вещей такие как внутренние структуры и буфера других типов MySQL, системные буфера, а также структуры операционной системы). Если же на сервере работают другие службы, то в этом случае стоит произвести некоторые вычисления.

Например запрос:

Позволит оценить размеры ваших данных и предположить о необходимом размере буфера. Стоит также отметить, что нет необходимости в размещении в этом буфере абсолютно всех данных, а только той части которая обрабатывается. Для начала можно установить размер буфера в половину от суммарного объема данных размещенных в хранилище InnoDB (если все это разумно умещается в памяти) и после чего подождать некоторое время, а дальше используя следующую команду можно оценить, насколько правильно установлен размер буфера:

Вывод данной команды показывает количество операций чтения данных с диска в буферный пул (за секунду) первое значение это количество чтений которое было произведено от момента запуска сервера до момента запуска команды, а дальше идут значения за последнюю секунду. В примере значения довольно высокие (но сервер от которого приведен пример имеет дисковую систему способную обработать 4000 операций ввода-вывода в секунду), если бы это была система OLTP, то лучше бы было увеличить значение буфера и добавить больше оперативной памяти к серверу, если это необходимо.

Если у вас нет доступа к командной строке, то настоятельно рекомендуется все же его получить, так как это дает вам больше возможностей. Но есть альтернатива GUI MySQL Workbench, в разделе “Быстродействие”, вы увидите, “InnoDB buffer pool disk reads” (операций чтения в буфер InnoDB), а также “InnoDB Disk Reads” (InnoDB чтения с диска), которые большую часть времени связаны друг с другом.

Начиная с версии 5.7 можно изменить значение на лету командой:  mysql> SET GLOBAL innodb_buffer_pool_size=size_in_bytes; , в более старших версиях приведется перезагружать сервер.

3. INNODB_LOG_FILE_SIZE – место под журнал транзакций (другое название “журнал отката”)

Этот параметр устанавливает размер файлов журнала транзакций InnoDB. (В версии до 5.6.8 значение по умолчанию 5Мб которое катастрофически снижает производительность сервера, в более поздних версиях увеличено до 48Мб, что для высоко нагруженных систем все равно очень мало). Журнал транзакций используется в двух случаях: для отката транзакций (операция отмены изменений Undo) и для применения изменений в случае сбоя целостности БД (операция внесения изменений – Redo).

При использовании хранилища, которое поддерживает транзакции изменения данных не записываются прямо в хранилище, а изменяются в буфере и в журнал транзакций заносится информация об операции (при этом запись в хранилище теперь считается “грязной”), а  значение которое было до изменения данных заносится в сегмент отката (rollback segment). Далее если по какой то причине происходит отмена транзакции, то выполняется операция отмены Undo, которая возвращает данные в исходное состояние (что происходит очень просто так как данные не были записаны, то данные в памяти заменяются данными из сегмента отката, а транзакция отмечается как отмененная, при этом не происходит дисковых операций связанных с записью данных).

При этом если транзакция завершается успешно сервер, все равно не производит запись на диск мгновенно (это было бы очень не эффективно). Вместо данных на диск записывается только журнал выполненных транзакций (это протокол действий Redo), а все изменения живут в “грязных” страницах, до тех пор пока сервер не решит записать их на диск (при этом на диск запишется сразу блок изменений за раз). При этом если сервер сломается пока данные находятся в памяти (в “грязных” страницах), то все изменения  будут утеряны. Однако сервер при запуске проверяет состояние журнала транзакций и состояние базы данных и если обнаруживается расхождение, то по журналу транзакций сервер выполняет все не завершенные операции восстанавливая тем самым целостность БД.

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

Запись замедляется так как при небольшом журнале транзакций он будет выступать в качестве буфера для записи. И если у вас есть много записей, MySQL может оказаться не в состоянии очищать буфер данные достаточно быстро, что снижает производительность записи. Большие файлы журналов, с другой стороны, дают вам много места, которые может быть использовано перед тем как его придется очищать. Это, в свою очередь, позволяет InnoDB заполнять больше страниц в памяти (например, изменить несколько записей, которые находятся на той же странице, или даже изменить одну и ту же запись несколько раз), а также, для случая магнитных дисков, позволяет упорядочивать грязные страницы. Что касается восстановления после сбоя – большие файлы журнальном означает больше данных, которые необходимо будет обработать прежде чем сервер сможет начать работать, поэтому восстановление после сбоя происходит медленнее.

Теперь разберем как же задать размер для журнала транзакций. К часть. есть простое практическое правило: размер вашего журнала должен быть достаточен, что бы записать 1-2 часа интенсивных операций. Для того, что бы выяснить числовое значение можно выполнить следующие операции:

В этом примере основываясь на 60 секундах замеров операций сервера, получается, что сервер записывает 2.6Гб за час. В этом случае, если значение innodb_log_files_in_group, не было изменено (по умолчанию 2, это минимальное значение которое требует InnoDB), то установка значения innodb_log_file_size=2560M будет означать наличие 5Гб  под журнал транзакций (как раз на 2-а часа записи), который будет распределен по двум файлам.

Изменение значение innodb_log_file_size происходит следующим образом: изменяет значение в my.cnf; далее останавливаете сервер; убеждаетесь, что сервер корректно завершил свою работу (по log файлу не должно быть ошибок); удаляет старые файлы журналов, например командой “rm -f /var/lib/mysql/ib_logfile*”; и выполняет запуск сервера (который займет некоторое время для создания новых журналов).

В общем случае значение innodb_log_file_size в диапазоне от 1 до 2Гб дает очень хорошую производительность, почти при любой нагрузке.

Стоит также упомянуть параметр innodb_log_buffer_size, как правило в реальности используется только малая часть этого буфера, для проведения небольших транзакций, прежде чем они будут записаны на диск. Конечно если ваши транзакции затрагивают большие объемы данных который будут изменятся, то имеет смысл увеличить значение по умолчанию, но если вы используете autocommit  или ваши транзакции затрагивают только несколько килобайт данных то имеет смысл оставить буфер в размере по умолчанию (8Мб до версии 5.7.5 и 16Мб для более поздних).

4. INNODB_FLUSH_LOG_AT_TRX_COMMIT

По умолчанию innodb_flush_log_at_trx_commit установлено в значение 1 , которое указывает серверу выполнять сброс данных на диск и синхронизацию после завершения каждой транзакции. И в случае если вы используете режим autocommit, то любой единичный запрос INSERT, UPDATE или DELETE считается завершенной транзакцией.

Синхронизация это очень дорогая операция (особенно нет write-back кеша), поскольку она включает фактическую синхронную запись на диск, поэтому всякий раз когда это возможно рекомендуется избегать конфигурации по умолчанию.

Есть два альтернативных значения: 0 – означает запись на диск без синхронизации (то есть без совершения реальной операции ввода-вывода) и 2 – означает нет записи и нет синхронизации (опять нет операции ввода-вывода). Если установлено  значение 0 или 2, то синхронизация выполняется один раз в секунду, главным недостатком такого подхода является то, что вы можете потерять последнюю  секунду изменений в случае сбоя сервера. Конечно для банков такой подход не приемлем но для веб сервера, вполне допустим. Разница между значениями 0 и 2 не значительна и заключается в том, что при значении 0  данные помещаются в системный кеш и если из строя вышел только MySQL, а не весь сервер, то данные не потеряются, а будут записаны на диск системой.

Если же вы предпочтете значение 1, то стоит обратить внимание на параметр sync_binlog, которые связан с данным.

5. SYNC_BINLOG – для продолжительных журналов

В общем то в сети есть огромное количество информации о взаимосвязи между sync_binlog и innodb_flush_log_at_trx_commit, но в упрощенном варианте все сводится к:

  1. если сервер не имеет подчиненных серверов и вы не выполняете резервных копий, то установите значение в 0 и наслаждайтесь хорошей производительностью.
  2. если сервер не имеет подчиненных серверов и но вы выполняете резервные копий, однако для вас не критично потерять несколько последних секунд изменений, то установите значение в 0 и для лучшей производительности.
  3. если у вас есть подчиненные сервера и вы делаете резервные копии, при этом для вас важна согласованность подчиненных серверов и/или резервных копий на каждый момент времени (путем последовательного резервированого копирования и банарных журналов),  и вы также используете innodb_flush_log_at_trx_commit = 1, то вы должны серьезно рассмотреть вопрос об установке значения sync_binlog = 1.

Проблема в том, что sync_binlog = 1 имеет большую цену, так как каждая транзакция будет вызывать синхронизацию на диск бинарных журналов. Стоит заметить, что новые версии MySQL (такие как 5.6, 5.7, MariaDB и Percona Server)  группируют операции синхронизации данных и бинарных журналов, что дает низкую стоимость sync_binlog = 1, но более старые версии выполняют их отдельно, что может снизить производительность, поэтому анализируйте дисковые операции.

6. INNODB_FLUSH_METHOD – шанс избавится от двойного буферизирования

Установите innodb_flush_method = O_DIRECT, что  бы избежать двойного буферизирования (первый раз MySQL, а второй раз системой). Вы не должны использовать O_DIRECT, только если ваша система его не поддерживает, но операционные системы Linux отлично с ним справляются.

Без непосредственной записи все изменения в БД записываются в кэш операционной системы, а уже из него переносятся на диск, что в итоге выглядит как наличие двух копий данных одной в буфере InnoDB ,а  второй в кэше ОС. И это означает, что вы можете потерять до 50% памяти, если ваш буфер ограничен 50% доступной памяти. А если нет, то может возникнуть свапирование из-за высокого давления на кэш ОС.

Другими словами, установите innodb_flush_method = O_DIRECT.

7. INNODB_BUFFER_POOL_INSTANCES – способ уменьшит глобальную конкуренцию за мониторы блокировок

Начиная с версии 5.5 введены экземпляры буферного пула в качестве средства для снижения внутренней конкуренции за ресурсы блокировок и улучшает масштабируемость.  В версии 5.5 дает улучшение производительности только в некоторой степеи, но начиная с 5.6 занчительно его влияние становится более значительным. Поэтому для версии 5.5 хорошим значнием будет  innodb_buffer_pool_instances = 4, а для версии 5.6 и 5.7 можно установить 8-16 экземпляров пула.

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

8. INNODB_THREAD_CONCURRENCY – получите больший контроль над вашими потоками

Часто рекомендуется установить значение innodb_thread_concurrency = 0 и забыть о нем. Но это так только если у вас имеется небольшая или умеренная нагрузка на сервер. Но если вы приближаетесь к точке насыщения вашего процессора или подсистемы ввода-вывода, а особенно если у вас есть случайные всплески, при которых система должна работать надлежащим образом, то рекомендуется рассмотреть параметр innodb_thread_concurrency. Этот параметр задает InnoDB сколько потоков могут выполнятся параллельно (иными словами он управляет параллелизмом). Если значение установлено в 0, то управление параллелизмом отключено (в этом случае nnoDB будет обрабатывать все запросы сразу, в том порядке как они приходят в и столько, сколько это необходимо).

Это нормально, если у вас есть 32 процессорных ядер и 4 запросов. Но представьте, что у вас есть 4 ядра процессора и 32 запросов – если вы позволите 32 запросом работать параллельно, вы нарываетесь на неприятности. Поскольку эти 32 запросы будут обслуживаться только 4 ядрами процессора, они будут, по крайней мере, в 8 раз медленнее, чем обычно (а на самом деле, более чем в 8 раз медленнее), так как каждый из этих запросов будет иметь собственные внешние и внутренние блокировки, которые создают возможности для скапливания всех запросов.

Также стоит упомянуть, что есть еще две другие переменные innodb_thread_sleep_delay и innodb_concurrency_tickets – которые помогут вам взять под свой контроль параллелизм. Значение с innodb_thread_concurrency может быть изменено в режиме реального времени командой:  SET global innodb_thread_concurrency=X; Для большинства серверов и возможных нагрузок значение 8 хорошая отправная точка и когда вы видите, что сервер выдерживает нагрузку и придел аппаратных средств еще не достигнут, то можно постепенно увеличивать значение. Для того, что бы увидеть состояние потоков выполните команду:   show engine innodb status\G  и найдите строку ROW OPERATIONS, похожую на:  22 queries inside InnoDB, 104 queries in queue .

9. SKIP_NAME_RESOLVE – отмените обратное разрешение имен

Параметр skip_name_resolve отключает разрешение имен DNS для входящих соединений. И хотя как правило такие операции выполняются крайне быстро, а результаты еще и кешируются, но в том, случае если по каким то причинам связь с сервером DNS пропадет, то все новые соединения, которые не прошли аутентификацию будут замедлены, так как они будут осуществлять попытку произвести разрешение DNS (а при рабочем сервере будут ждать наступления таймаута, для такой операции). Так, что если вы не используете разрешение на основе hosts, то лучше не дожидаться такого момента.

10. INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX – контроль над операциями ввода вывода

Переменная innodb_io_capacity контролирует как много операций запросов на запись за секунду будет совершать сервер при выполнении операций сброса на диск “грязных” данных.

Переменная innodb_io_capacity_max  контролирует как много операций запросов на запись за секунду будет совершать сервер при выполнении операций сброса на диск “грязных” данных, но в случае пиковых нагрузок.

Здесь стоит заметить, что операции чтения связанные с таким запросом как SELECT всегда выполняются с максимально возможным количеством запросов к диску, что бы в кратчайшее время вернуть результат. Однако операции записи связаны с фоновым процессом, который периодически проверяет как много данных необходимо сбросить на диск, но он может сбросить на диск не более чем такое количество данных для которых нужно не более innodb_io_capacity запросов на запись. Сюда же относятся и операции с буфером изменений (где хранятся вторичные ключи грязных страниц, пока они не будет сброшены на диск.

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

Хорошим решением будет измерение пропускной способности на случайных операциях записи дисковой системы и затем установка этого значения как innodb_io_capacity_max, а значение innodb_io_capacity в этом случае необходимо установить на уровне 50-75%.

В некоторых случаях можно предположить количество операций ввода-вывода, которые за секунду может выполнить дисковая система. Так например для системы основанной на 8-ми HDD с скоростью вращения 15К построенных в RAID10 это значение составляет около 1000 случайных записей за секунду и для такой системы подходящими значениями будут innodb_io_capacity=600 и innodb_io_capacity_max=1000. Многие SSD могут обрабатывать 4000-10000 случайных операций записи за секунду.

Не совсем корректные значения для этих переменных не являются критичными, но стоит отметить, что значения по умолчанию 200 и 400 соответственно, могут ограничить производительность сервера (но при этом существует запас который можно было бы использовать).

11. INNODB_STATS_ON_METADATA – выключите метаданные

Если версия вашего сервера 5.6 или 5.7 вы не должны изменять значение по умолчанию для innodb_stats_on_metadata вообще. Однако для версий 5.5 или 5.1 рекомендуется выключить метаданные, что позволит таким командам как show table status и запросам к INFORMATION_SCHEMA выполнять практически мгновенно, вместо нескольких секунд выполнения. Еще одно замечание начиная с версии 5.1.32 эта переменная может быть изменена на лету.

12. INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN & INNODB_BUFFER_POOL_LOAD_AT_STARTUP

Две переменные innodb_buffer_pool_dump_at_shutdown и innodb_buffer_pool_load_at_startup не относятся к настройкам производительности, но могут значительно повлиять на процесс перезапуска сервера (например в случае внесения изменений в конфигурацию). Когда обе эти опции включены то сервер при завершении своей работы сбрасывает содержимое буферов (и что более важно даже закешированные страницы) в специальный файл. А при запуске создается специальный поток который считывает все эти данные обратно в память, что может в 3-5 раз сократить время прогрева сервера.

Несколько замечаний:

  1. фактически копируются не все данные которые содержатся в буферах, а копируются идентификаторы табличных пространств и идентификаторы страниц, чего достаточно что бы найти информацию на диске. При этом загрузка страниц будет выполнятся таким образом, что бы выполнить ее максимально последовательно, а не сотнями случайных чтений;
  2. загрузка содержимого происходит в фоновом режиме, поэтому сервер не ждет пока дынные будет загружены, а сразу же начинает отрабатывать запросы;
  3. Начиная с версии 5.7.7 сбрасывается только 25% страниц из недавно использованных буферов, но есть переменная innodb_buffer_pool_dump_pct контролирующая этот процент. Рекомендуется значение 75-100%.
  4. Этот параметр поддерживается только с версии 5.6 или в Percona.

13. INNODB_ADAPTIVE_HASH_INDEX_PARTS – разделите барьеры

Если вы выполняете множество SELECT запросов (и все остальное прекрасно настроено) то адаптивный хэш индексов (Adaptive Hash Index) будет вашим бутылочным горлышком, в котором застрянет производительность. Адаптивные хэш индексы являются динамическими индексами, которые поддерживаются внутри InnoDB, и улучшают производительность наиболее часто используемых запросов. Эта функция может быть отключена с помощью перезапуска сервера, но по умолчанию он включен во всех версиях MySQL. И хотя это довольно сложная технология, в большинстве случаев она дает хорошее ускорение для многих типов запросов. Но только, пока у вас не слишком много запросов, к базе данных, в какой момент такие запросы начинают тратить слишком много времени ожидать получения доступа ко внутренним  структурам (которые блокируются различными барьерами и мониторами разделяемого доступа).

Если вы обладатель версии 5.7 (или MariaDB начиная с версии 10.2.2) то у вас есть переменная innodb_adaptive_hash_index_parts, которая по умолчанию имеет значение 8 и выполняет разделения адаптивного хеша на указанное количество частей, что позволяет снизить конкуренцию за доступ. Однако для всех более ранних версий нет способа контроля за количеством разделов и существует только один глобальный блок который контролирует доступ к адаптивному хэшу и все запросы упираются в него.

Для версий 5.1 – 5.6 при тысячах отдельных запросов в секунду, самым простым решением будет переход на Percona и включение разделения кеша (параметр innodb_adaptive_hash_index_partitions, однако стоит заметить, что этот же параметр есть в MariaDB но имеет смысл только для XtraDB).

14. QUERY_CACHE_TYPE – включить или выключить?

Многие авторы указывают на то, что кэш запросов это полезно и вы должны его использовать. Ну, иногда это правда – иногда бывает полезно. Но только, когда у вас есть относительно легкая нагрузка и особенно если нагрузка в значительной степени только читает и очень мало или практически не выполняет запись. И если это ваш случай , установите query_cache_type = ON и query_cache_size = 256M и все. Однако следует отметить, вы никогда не должны установить размер кэша запросов намного выше указанного, или вы столкнетесь с серьезным спадом производительности, который возникают из-за проверки его действительности. (эта проблема связана с одним глобальным монитором, который контролирует доступ к кэшу запросов и она пока не решена).

В остальных случаях вы должны установить значение query_cache_size = 0 и что важно еще и установить значение query_cache_type = OFF (перезагрузить сервер) и таким образом запретить использование кеша запросов вообще.

Минимальный размер кеша запросов 40Кб, кроме того значение всегда округляется до ближайшего кратного 1024 байтам.  Значение по умолчанию 1M (>= MariaDB 10.1.7), 0 (<= MariaDB 10.1.6).

query_cache_type – имеет три значения: 0 -отключено (для конфигурационного файла допустимы только числовые значения), 1 – кеш включен и 2 -включен по требованию, только для запросов с пометкой SQL_CACHE.

Посмотреть состояние кеша запросов можно командой:

которая выдает информацию а количестве запросов добавленных в кеш(Qcache_inserts), о количестве запросов которые данные для которых взяты из кеша (Qcache_hits), а также о количестве записей кеша удаленных из-за не хватки памяти (Qcache_lowmem_prunes). Пример демонстрирует плохую настройку так как количество ударений из кеша превышает количество запросов которое в кеш попадает. Высокое значение Qcache_free_blocks относительно Qcache_total_blocks может означать фрагментирование кеша, которое лечится командой  FLUSH QUERY CACHE; Очистка кеша, команда:  RESET QUERY CACHE  или  FLUSH TABLES . Установка query_cache_type или query_cache_size в значение 0, отключает кеш но не освобождает структуры памяти, только одновременная установка полностью отключает кеш и освобождает все структуры связанные с ним. Переменные динамические, но в случае отключение кеша, его включение только через перезагрузку.

Другие связанные переменные:

  • query_cache_limit — максимальный размер кэшируемого запроса.
  • query_cache_min_res_unit — минимальный размер хранимого в кэше блока.
  • query_cache_wlock_invalidate — определяет будут ли данные браться из кеша, если таблица, к которым они относятся, заблокирована на чтение.

Кэш запросов можно представить себе как хэш-массив, ключами которого являются запросы, а значениями — результаты запросов. Кроме результатов, MySQL хранит в кэше список таблиц, выборка из которых закэширована. Если в любой из таблиц, выборка из которой есть в кэше, проиcходят изменения, то MySQL удаляет из кэша такие выборки. Также MySQL не кеширует запросы, результаты которых могут измениться. При запуске MySQL выделяет блок памяти размером в query_cache_size. При выполнении запроса, как только получены первые строки результата сервер начинает кэшировать их: он выделяет в кэше блок памяти, равный query_cache_min_res_unit, записывает в него результат выборки. Если не вся выборка поместилась в блок, то сервер выделяет следующий блок и так далее. В момент начала записи MySQL не знает о размере получившейся выборки, поэтому если записанный в кэш размер выборки больше, чем query_cache_limit, то запись прекращается и занятое место освобождается, следовательно, если вы знаете наперед, что результат выборки будет большим, стоит выполнять его с директивой SQL_NO_CACHE.

15. INNODB_CHECKSUM_ALGORITHM – скрытый трюк аппаратной оптимизации

Большинство современных процессоров поддерживают инструкции расчета CRC32 и MySQL может использовать их для значительного повышения скорости расчета контрольных сумм InnoDB. Две переменные, которые могут активировать такую функциональность:

  1. innodb_checksum_algorithm=crc32
  2. innodb_log_checksum_algorithm=crc32 (доступно в MariaDB и в версии MySQL 5.7)

Начиная с версии 5.7.7 innodb_checksum_algorithm=crc32 установлено по умолчанию, а начиная с версии 5.7.9 innodb_log_checksum_algorithm не доступен. В версии 5.6 существует только переменная innodb_checksum_algorithm и задание значения crc32 включает аппаратную оптимизацию для расчета контрольных сумм страниц (или записей журнала), а так как контрольные суммы вычисляются при каждом обращение к страницам как то на запись так и на чтение то это значительное ускорение.

Значение параметров можно менять динамически.

16. TABLE_OPEN_CACHE_INSTANCES

Начиная с MySQL 5.6.6, кэш-таблиц может быть разбит на несколько разделов, и если вы используете 5.6 или более поздней версии, то безусловно, лучше сделать это.

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

Начиная с MySQL 5.7.8, table_open_cache_instances = 16 является значением по умолчанию, и это, хорошая отправная точка как для версий 5.6 и 5.7.

17. INNODB_READ_IO_THREADS & INNODB_WRITE_IO_THREADS

Это далеко не самый важный параметр. Начиная с версии 5.5 на Linux используется асинхронный ввыод-вывод и параметры innodb_read_io_threads и innodb_write_io_threads влияют на фоновую активность такую как контрольные точки (сброс грязных страниц на диск), операции объединения буфера изменений, а также иногда при упреждающем чтении.

Таким образом, это не ключевые переменные, для настройки, но совместить их значения  с количеством дисков является хорошей идеей. Так, например, на RAID10 с 8 дисками, вы можете установить innodb_read_io_threads = 8 и innodb_write_io_threads = 4. Если у вас есть SSD, то подходящие значения из диапазона 16-32, но не ждите большой разницы в производительности, если только ваш сервер не выполняет чрезвычайно много тяжелых операций записи на диск, и это является является узким местом.

Набор дополнительных переменных также влияющих на производительность

Эти переменные автор не рассматривает в своей статье, о 17 основных параметрах, но с некоторыми из них приходится сталкиваться, так, что я пометил себе их смысл и влияние на сервер, особенно часто они встречаются в старых материалах по производительности, и часто упоминаются для хранилищ MyISAM.

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

Значения переменных которые задают потребление памяти каждым потоком

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

bulk_insert_buffer_size размер в байтах буфера, который выделяется для каждого потока и ускоряет блочные операторы вставки данных в таблицы размещенные хранилищах MyISAM и Aria. Значение 0 отключает кеш, значение по умолчанию 8388608. Переменная динамическая.

join_buffer_size – минимальный размер буфера в байтах для запросов которые не используют индекс а выполняют полное сканирование таблицы. Увеличение этого значения укоряет запросы с join (если установить индексы нельзя) но за это придется платить потерями памяти, при этом этот параметр всегда задает минимальное значение которое выделится для каждого потока. Оптимально установить это значение не большим для всего сервера и увеличивать его для сессий где это необходимо. Значение по умолчанию 262144 (256Кб) (>=MariaDB 10.1.7), 131072 (128Кб) (<=MariaDB 10.1.6), для Mysql 256Кб. Переменная динамическая. В MariaDB есть параметр join_buffer_space_limit, который устанавливает максимальный размер буфера в байтах, и по умолчанию 1024*128*10 (переменная динамическая и может изменятся в сессиях). Значение по умолчанию 128Кб.

sort_buffer_size – Значение указывает на размер буфера используемого при сортировке не зависимо от типа хранилища. Если значение sort_merge_passes в выводе команды SHOW GLOBAL STATUS и если оно высоко, то вам необходимо улучшить индексы в таблицах или увеличить это значение. Значение 16К это минимум. При этом как правило лучше для сервера задавать не большие значения, а увеличивать их для сессий (переменная динамическая и может изменятся в сессиях). Значение по умолчанию 2Мб.

read_buffer_size – каждый поток выполняющий последовательное сканирование (для MyISAM, Aria и MERGE таблиц) выделяет буфер указанного в байтах размера для каждой сканируемой таблицы . Увеличьте, это значение если вы выполняете много последовательных сканирований. Значения всегда округляются до ближайшего кратного 4KB. Также используется в ORDER BY для кэширования индексов во временном файле (не во временных таблицах), для кэширования результатов вложенных запросов, для объемных вставок в разделы, а также для определения размера блока памяти MEMORY таблиц. Максимально доступное значение 2Гб (переменная динамическая и может изменятся в сессиях). Значение по умолчанию 128Кб.

read_rnd_buffer_size – Размер в байтах буфера используется при чтении строк из таблицы MyISAM в отсортированном порядке во время операций сортировки ключей. Большие значения улучшают ORDER BY производительности, хотя и достаточно увеличить размер в сессии, где это необходимо избегая чрезмерного использования памяти. Другие механизмы хранения используют это значения в механизмах оптимизации множественно-диапазонного чтения (Multi-Range Read). Максимально доступное значение 2Гб (переменная динамическая и может изменятся в сессиях). Значение по умолчанию 256Кб.

binlog_cache_size – Размер кэша для хранения изменений в двоичные журналы регистрации транзакций (переменная может быть изменена на лету). Бинарный кэш журнала выделяется для каждого клиента, если сервер поддерживает любые транзакционные механизмы хранения, и если на сервере включен двоичный журнал(–log-bin опция). Если вы часто используете крупные транзакции, вы можете увеличить этот кэш, чтобы получить более высокую производительность. Переменные состояния Binlog_cache_use и Binlog_cache_disk_use могут быть полезны для настройки размера этой переменной. binlog_cache_size устанавливает размер только для кэша транзакций; размер кэша операторов определяется системной переменной binlog_stmt_cache_size.

thread_stack – Размер стека для каждого потока в байтах. По умолчанию 192кб и 256 КБ для 64-битных систем, что достаточно для нормальной работы. Если размер стека потока слишком мал, он ограничивает сложность операторов SQL, которые сервер может обрабатывать, и глубину рекурсии хранимых процедур, а также другие операции которые расходуют много памяти стека (переменная не может быть изменена на лету).

Управление памятию

max_heap_table_size – задает максимальный размер в байтах для таблицы размещенной в памяти и создаваемых пользователем  (переменная динамическая и может изменятся в сессиях). Маленькие значения max_heap_table_size и tmp_table_size также ограничивают размеры таблиц в памяти. Для таблиц созданных пользователем вставка данных выше этого ограничения будет вызывать ошибку о переполнении хранилища, для внутренних таблиц будет происходить перенос таблицы на диск.

tmp_table_size — максимальный размер памяти, выделяемой для временных таблиц, создаваемых MySQL для своих внутренних нужд. Это значение также ограничивается переменной max_heap_table_size, поэтому в итоге будет выбрано минимальное значение из max_heap_table_size и tmp_table_size, а остальные временные таблицы будут создаваться на диске. Значение по умолчанию зависит от системы, попробуйте установить его равным 32 МБ и понаблюдать за переменной состояния Created_tmp_disk_tables, ее значение должно быть как можно меньше.

key_buffer_size — размер буфера, выделяемого под индексы и доступного всем потокам. Весьма важная настройка, влияющая на производительность. Значение по умолчанию 8 МБ, его можно увеличить. Рекомендуется устанавливать не больше, чем общий размер всех .MYI файлов. Наблюдайте за переменными состояния Key_reads и Key_read_requests, отношение Key_reads/Key_read_requests должно быть как можно меньше (< 0,01). Если это отношение велико, то размер буфера стоит увеличить. Если Вы используете только MyIsam таблицы, устанавливайте это значение в 30%…40% всей доступной оперативной памяти на сервере. MyIsam использует кеш операционной системы для данных, поэтому учтите, что оставшаяся свободная память понадобится именно для этого. Если же MyIsam таблиц у Вас немного и их совокупный размер маленький — оставьте это значение в пределах 32M (но не выставляйте значение в 0!).

table_cache (table_open_cache с версии 5.1.3) — количество кэшированных открытых таблиц для всех потоков. Открытие файла таблицы может быть достаточно ресурсоемкой операцией, поэтому лучше держать открытые таблицы в кэше. Следует учесть, что каждая запись в этом кэше использует системный дескриптор, поэтому возможно придется увеличивать ограничения на количество дескрипторов (ulimit). Значение по умолчанию 64, его лучше всего увеличить до общего количества таблиц, если их количество в допустимых рамках. Переменная состояния Opened_tables позволяет отслеживать число таблиц, открытых в обход кэша, желательно, чтобы ее значение было как можно ниже.

Управление запросами

sql_big_selects – если значение установлено в 0, то сервер не пропускает запросы не удовлетворяющие max_join_size, если значение 1 то max_join_size игнорируется. Установка этого свойства автоматически связана с max_join_size.

max_join_size – указывает на то, что запрос не будет выполнен если ему необходимо обработать более чем указанное количество строк или их комбинаций или выполнить более чем указанное количество дисковых поисков (переменная динамическая и может изменятся в сессиях). Переменная может предотвратить выполнение на сервере плохо составленных запросов. Изменение значение этого параметра на отличное от значения по умолчанию 18446744073709551615 (>= MariaDB/MySQL 5.5.0), 4294967295 (< MariaDB/MySQL 5.5.0) автоматически установит переменную sql_big_selects=0. Если при этом sql_big_selects будет установлен снова, то max_join_size – игнорируется. Кроме того ограничение заданное этой переменной игнорируется если запрос уже размещен к кэше запросов.

max_sort_length — указывает, сколько байт из начала полей типа BLOB или TEXT использовать при сортировке. Значение по умолчанию 1024, если вы опасаетесь некорректно спроектированных таблиц или запросов, то следует его уменьшить.

log_slow_queries — указывает серверу логировать долгие («медленные») запросы (выполняющиеся дольше long_query_time). В качестве значения передается полное имя файла (например /var/log/slow_queries).

long_query_time — если запрос выполняется дольше указанного времени (в секундах), то он будет считаться «медленным».

Управление сетевой активностью

max_allowed_packet – задает максимальный размер пакета или строки в байтах. Буфер инициализируется размером net_buffer_length и дальше может выростать до max_allowed_packet. Переменная глобальная и динамическая.

net_read_timeout – задает время в секундах которое сервер ждет при чтении в случае отсутствия данных, по умолчанию 30. (переменная динамическая и может изменятся в сессиях).

net_write_timeout – задает время в секундах которое сервер ждет при записи, прежде чем операция будет признана не удачной, по умолчанию 60. (переменная динамическая и может изменятся в сессиях).
wait_timeout — время в секундах, в течение которого сервер ожидает активности соединения, прежде чем прервет его. В общем случае 30 секунд будет достаточно.

Управление потоками

thread_cache_size — указывает число кэшируемых потоков. После обработки запроса сервер не будет завершать поток, а разместит его в кэше, если число потоков, находящих в кэше меньше, чем указанное значение. Значение по умолчанию 0, увеличьте его до 8 или сразу до 16. Если наблюдается рост значения переменной состояния Threads_Created, то следует еще увеличить thread_cache_size.
thread_concurrency — актуально только для Solaris/SunOS вопреки тому, что пишут в сети. «Подсказывает» системе сколько потоков запускать одновременно, выполняя вызов функции thr_setconcurrency. Рекомендованное значение — двойное или утроенное число ядер процессора.

Таймауты

interactive_timeout — время в секундах, в течение которого сервер ожидает активности со стороны интерактивного соединения (использующего флаг CLIENT_INTERACTIVE), прежде чем закрыть его.

Профилирование

profiling – значение 1 активирует профилирование, а 0 – отключает. По умолчанию 0 (переменная динамическая и может изменятся в сессиях).

Вычисление потребления памяти

Вся память которую может потребить сервер вычисляется по формуле:  общая память сервера = key_buffer_size+query_cache_size+tmp_table_size+innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+ max_connections*(sort_buffer_size+ read_buffer_size+read_rnd_buffer_size+join_buffer_size+thread_stack+binlog_cache_size)

С чего начать оптимизацию

Итак, вы определили, какие запросы выполняются при генерации страницы. Дальше возможны варианты:

  1. Есть тяжелые запросы, занимающие сотни миллисекунд.
  2. Запросов много, но все они выполняются достаточно быстро.

В первом случае можно попробовать оптимизировать отдельные запросы. Здесь поможет SQL-оператор EXPLAIN и знания об индексах. Это решение применимо ко всем сайтам, в том числе размещенным на виртуальном хостинге.

Во втором случае имеет смысл заняться углубленным анализом логов и тонкой настройкой MySQL.

Чтобы оптимизировать тяжелый запрос, сначала его нужно исследовать. Для этого допишите перед SELECT слово EXPLAIN, и MySQL покажет план выполнения запроса. В первую очередь интерес представляет информация об использовании индексов.

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

Индексы – ключ к высокой производительности MySQL, их важность увеличивается по мере роста объема данных в базе. Индексы нужно создавать для столбцов, по которым

  • производится поиск в части WHERE
  • соединяются таблицы при JOIN
  • сортируются и группируются записи при ORDER BY и GROUP BY
  • производится поиск MIN() и MAX()

Индексы могут быть составными, в этом случае важен порядок столбцов.

Разбирая вывод EXPLAIN, обратите особое внимание на столбцы

  • type (значение ALL – плохо)
  • key (NULL – плохо)
  • ref (NULL – плохо)
  • extra (Using filesort, Using temporary, Using where – плохо)

Чем смотреть состояние сервера

Команда  show status\G  выдает статистику работы сервера.

Команда  show engine innodb status\G  выдает статистику работы InnoDB  системы хранения сервера.

Команда  mysqladmin ext -ri1 | grep Innodb_buffer_pool_reads  выдает количество InnoDB чтений.

На заре становления MySQL, единственных средством для анализа производительности сервера был анализ вывода команд аналогичных   show global status;  и хотя сегодня существуют и другие механизмы расширенного анализа медленных запросов (доступны в Percona сервере) и начиная с версии 5.6 существует специальная PERFORMANCE_SCHEMA, все равно желательно владеть анализом основных метрик сервера.

Для того, что бы получить основные метрики сервера можно выполнить запрос     show global status;  или использовать команду  mysqladmin ext  в консоли. Команда отобразит состояние каждого счетчика для всех системных переменных. Стоит отметить, что не все переменные являются счетчиками, некоторые из них отображают только текущие значения. Выполнение следующей команды позволит вам получить значение переменных за указанный период времени (в примере 60 секунд):  $ mysqladmin ext -i60 -c3 , где ключ с3 указывает, что команда выведет три замера за указанный период времени.

Так как значения счетчиков не самое интересное, зачастую необходимо получить дельту между этими значениями, для этого служит команда:  $ mysqladmin ext -ri60 -c3 , опция -r указывает на необходимость вычисления изменения значений за указанный период. Другим более красивым вариантом станет применение утилиты pt-mext (из пакета Percona-toolkit), которая позволяет красиво отформатировать вывод:  pt-mext -r -- mysqladmin ext -i60 -c3 .

Часто не очень удобно смотреть большое количество параметров на экране, поэтому желательно производить запись результатов в файл.

Что позволяет получить срезы а затем проанализировать их  pt-mext.

Какие ключевые параметры необходимо анализировать

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

Счетчики Com_*

Это счетчики Com_begin, Com_commit, Com_delete, Com_insert, Com_select, Com_update – которые указывают на, то как много запросов соответствующего типа выполняется за секунду в конкретный момент времени (эти значения коррелируют со всеми остальными параметрами).

Временные таблицы

Всегда желательно оценить соотношение временных таблиц создаваемых на диске к общему количеству создаваемых временных таблиц, для этого служат следующие счетчики: Created_tmp_disk_tables и Created_tmp_tables.

Обратите внимание, что очень часто причиной создания временных таблиц на диске, не слишком маленькое значение для tmp_table_size или max_heap_table_size, а то, что столбцы различного размера, используются в запросах, например текст или BLOB столбцы, которые не могут быть записаны в фиксированный размер столбца временных таблиц. Для них, временная таблица на диске будет создаваться, даже если временные таблица будет содержать только одну запись. Во других случае, создания множества временных таблицы можно избежать путем анализа запросов, которые их создают и исправления неэффективных планов выполнения либо добавлением индексов или изменением самих запросов.

Счетчики Handler_*

Это счетчики внутренних операций, которые вычисляются для каждого единичного запроса. Наиболее интересные их них это: Handler_read_first, Handler_read_key, Handler_read_next, Handler_read_prev, Handler_read_rnd_next.

  • Handler_read_first – количество раз, когда была прочитана первая запись в индексе. Если это значение велико, это предполагает, что сервер делает полное сканирование индекса; например, SELECT col1 FROM foo, предполагая, что col1 проиндексирован.
  • Handler_read_key – количество запросов на чтение строки, основанных на ключе. Если это значение велико, это является хорошим показателем того, что ваши таблицы проиндексированы надлежащим образом для ваших запросов.
  • Handler_read_last – количество запросов на чтение последнего ключа в индексе. Для запросов с ORDER BY, сервер будет выдавать первый ключ запроса с последующим обращение к нескольким последующим ключам, а для запросов с ORDER BY DESC, сервер выдает последний ключ запроса, за которой следует несколько предыдущего ключей.
  • Handler_read_next – количество запросов на чтение следующей строки в порядке расположения ключей. Это значение увеличивается на единицу, если вы запрашиваете столбец индекса с ограничением диапазона или если вы делаете индексированное сканирование.
  • Handler_read_prev – количество запросов на чтение предыдущей строки по порядку. Этот метод чтения в основном используется для оптимизации ORDER BY … DESC.
  • Handler_read_rnd – количество запросов на чтение строки, основанных на фиксированной позиции. Это значение является высоким, если вы делаете много запросов, требующих сортировки результатов. Возможно также у вас есть много запросов, требующих сканирование целых таблиц или у вас есть соединения, которые не используют ключи надлежащим образом.
  • Handler_read_rnd_next – количество запросов на чтение следующей строки в файле данных. Это значение является высоким, если вы делаете много сканирований таблиц. Как правило, это говорит о том, что ваши таблицы не проиндексированы надлежащим образом или ваши запросы не используют индексы которые у вас есть.

Наиболее интересно получить значение этих счетчиков для конкретного запроса, что можно сделать так:

Что позволит вам понять, что делает ваш запрос и что в нем можно оптимизировать.

Счетчики Innodb_*

Система хранения InnoDB имеет множество встроенных счетчиков, получить значения их всех можно командой: show engine innodb status\G , где наиболее значимые счетчики:

  • Innodb_buffer_pool_pages_flushed – количество страниц сбрасываемых из пула буферов – хороший способ контролировать активность операций сброса страниц;
  • Innodb_buffer_pool_reads – количество дисковых операций чтение в буферный пул – смотрите насколько близко значение к пределу случайных чтений которые может выполнить ваш диски на самом деле;
  • Innodb_data_fsyncs – количество вызовов Fsync () – смотрите, что бы значение не было слишком высоким для вашего оборудование;
  • Innodb_data_pending_ * – показатели, показывающий количество вызовов ожидающих Fsync, для чтения или записи – потенциально могут показать насыщенные ресурсов системы ввода-вывода;
  • Innodb_data_reads/writes – количество случайных операций  чтения / записи для системы дискового ввода-вывода для файлов данных;
  • Innodb_history_list_length – показатели указывающие количество транзакций после которых не произведена очистка;
  •  Innodb_ibuf_merges – количество вставок данных в буфер изменений, большое значение указывает на интенсивные всплески операций ввода-вывода;
  • Innodb_log_waits – указывает количество раз когда размера буфера оказалось не достаточно (хороший индикатор указывающий на необходимость увеличения innodb_log_buffer_size);
  • Innodb_lsn_current – количество байтов, записанных в журнал транзакций. Помогает настроить файлы журнала транзакций;
  • Innodb_mutex_os_waits – если это значение большое то в системе присутствует состояние гонок за блокируемые ресурсы;
  • Innodb_rows_* – помогает понять внутреннюю активность хранилища, указывают на количество операций чтения, вставки, удаления или обновления строк;
  • Innodb_row_lock_time* – показывает, сколько времени тратится на ожидания получения блокировок (тут скорее имеется в виду мониторы блокировок охраняющие доступ к разделяемому ресурсу).

Обратите внимание, что некоторые из этих переменных доступны только в Percona Server. Если вы не можете найти переменную на вашем сервере, проверяйте показатели состояние командой: show engine innodb status\G ,- что не так удобно, зато там Вы найдете все о работе InnoDB.

Раздел Opened_*

Эти показатели позволяют оценить установки для файлового кеша. В идеале, Opened_tables и Opened_table_definitions не должно быть большими или вообще равными 0.

Счетчики Select

Есть несколько интересных Select_ * счетчиков на которые стоит обратить внимание:

  • Select_full_join – показывает количество запросов, которые сделали сканирование таблицы во время объединения (даже если join_buffer был использован). Параметр может означать наличие плохих запросов или в некоторых случаях наличие небольших таблиц для которых объединение выполняться быстро (поэтому они не оптимизируются).
  • Select_full_range_join – количество объединений, которые использовали поиск по диапазону на табличные ссылки. Такие запросы не частые, но всегда вредные.
  • Select_rang – это частый шаблон для запросов использующих диапазоны.
  • Select_range_check – количество соединений без ключа с дополнительной проверкой использования ключей при каждом чтении строки. Хороший индикатор плохого индексирования таблиц.
  • Select_scan – полное сканирование таблиц в объединениях. Также указывает на проблемы с корректным построением индексов.

Если любое из ваших значений  Select_full_join, Select_range_check или Select_scan относительно высокие, то у вас есть довольно плохой с индексации, и настоятельно рекомендуется делать оптимизацию запросов.

Счетчики потоков Threads_*

Один из этих счетчиков является основным показателем каких-либо проблем с производительностью в MySQL, и очень часто используются для устранения неполадок. Вот, что показывают эти счетчики:

  • Threads_cached – показатель  показывающий текущее число кэшированных потоков. Не очень интересный показатель.
  • Threads_connected – количество потоков которые в данный момент подключены к серверу. Больше количество нитей это не обязательно плохо так как многие нити могут просто спать, а наличие большого числа спящих нитей не всегда критично.
  • Threads_created – счетчик, показывающий число нитей, созданных из-за числа недостаточного числа
    кэшированных нитей. Если это высоко, стоит рассмотреть вопрос об увеличении thread_cache_size.
  • Threads_running – наконец, это самый интересный счетчик из всех. Это показатель
    показывающий количество потоков, которые в настоящее время выполняется внутри MySQL. Эти нити могут выполнять что угодно, от ожидания в очереди на доступ к диску, но если это значение аномально высоко (а особенно если оно превышает количество ядер процессора) то это означает серьезные проблемы производительности.

Утилиты которые могут пригодится для анализа состояния сервера: это системы мониторинга такие как Cacti и Zabbix, а также утилиты такие как Percona-toolkit и innotop

Заключение

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

По материалам www.speedemy.com/mysql/

Также для мониторинга работы сервера удобно использовать phpMyAdmin, интерес представляют вкладки Состояние и Переменные. Дополнительно phpMyAdmin дает советы по тюнингу тех или иных переменных в зависимости от параметров работы сервера.

Обсуждение закрыто.