Оптимизация производительности MySQL

Настройки MySQL по-умолчанию зачастую не оптимальны, они предназначены для начальных конфигураций с малыми ресурсами, около 512 МБ. Именно поэтому всегда настройки необходимо оптимизировать под каждую конкретную инсталляцию. Процесс оптимальной настройки Mysql состоит из двух частей — первоначальная настройка и корректировка параметров во время работы. Настройки нужно вносить в файл my.cnf по мере их уточнения.

Любой тюнинг MySQL должна начинаться с определения преобладающего количества таблиц определенного типа — InnoDB или MyISAM. Существуют настройки общие, а существуют и специфичные для разных типов таблиц. Для начала разберемся, как MySQL работает с памятью.

Выделение памяти в MySQL

Работа с потоками

Работа с памятью играют значительную роль для скорости и эффективности обработки параллельных транзакций и исполнения больших SQL запросов. Каждый поток (тред) использует память для клиентских соединений, и это потоки используют общую базовую память. Есть переменные thread_stack (стек потоков), net_buffer_length (для буфера соединений и буфера результата), которые динамически увеличивают свое значение до значения max_allowed_packet, когда это требуется и не влияют на общую утилизацию памяти. Буфер результата урезается до значения net_buffer_length после каждого SQL выражения. Когда поток больше не требуется, память, выделенная на данный поток, освобождается и возвращается системе до момента, пока поток не вернется в кэш потоков. Каждое поток соединения также использует память для для вычисления дайджестов SQL выражений. Параметр max_digest_length в байтах указывает на количество памяти, которое сервер выделяет на сессию.

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

Работа с таблицами

MySQL использует память и дескрипторы для кеширования таблиц. Структуры обработчиков для всех используемых таблиц сохраняются в кэше таблиц и управляются как «первым пришел — первым ушел» (FIFO). Системная переменная table_open_cache определяет начальный размер кэша таблицы. MySQL также требует памяти для кеша дескрипторов таблиц. Системная переменная table_definition_cache определяет количество дескрипторов таблиц, которые могут храниться в кэше дескрипторов таблиц. Если вы используете большое количество таблиц, вы можете создать большой кэш дескрипторов таблиц, чтобы ускорить открытие таблиц. Кэш дескрипторов таблицы занимает меньше места и не использует файловые дескрипторы, в отличие от кеша таблицы.

MySQL также выделяет память для временных таблиц, если она не становится слишком большой (определяется tmp_table_size и max_heap_table_size). Если внутренняя временная таблица в памяти становится слишком большой (определено в системных переменных tmp_table_size и max_heap_table_size), MySQL автоматически преобразует таблицу из формата в памяти в формат на диске. Если вы используете таблицы MEMORY и для переменной max_heap_table_size установлено очень большое значение, это приводит к потреблению большого объема памяти, поскольку системная переменная max_heap_table_size определяет, насколько велика таблица, и преобразование в дисковый формат отсутствует.

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

Пулы и буферы

Запросы JOIN, запросы к кэшу, сортировки, кэш таблиц, дескрипторы таблиц потребляют память постоянно, но они настраиваются с помощью системных переменных. В большинстве случаев, такие переменные привязаны к типу хранилища - InnoDB или MyISAM. Когда экземпляр mysqld появляется в хост-системе, MySQL выделяет буферы и кеши для повышения производительности операций с базой данных на основе значений, установленных в конкретной конфигурации.

Например, наиболее распространенными переменными, которые каждый администратор баз данных устанавливает в InnoDB, являются переменные innodb_buffer_pool_size и innodb_buffer_pool_instances, которые связаны с распределением памяти пула буферов, в котором хранятся кэшированные данные для таблиц InnoDB. Желательно, если у вас большой объем памяти и вы ожидаете обрабатывать большие транзакции, установив innodb_buffer_pool_instances для улучшения параллелизма путем разделения пула буферов на несколько экземпляров пула буферов.

В то время как для MyISAM вы должны иметь дело с key_buffer_size, чтобы обрабатывать объем памяти, который будет обрабатывать буфер ключей. MyISAM также выделяет буфер для каждого параллельного потока, который содержит структуру таблицы, структуры столбцов для каждого столбца и буфер размером 3 * N (где N — максимальная длина строки, не считая столбцов BLOB). MyISAM также поддерживает один дополнительный буфер строк для внутреннего использования.

Каждый запрос, который выполняет последовательное сканирование таблицы, выделяет буфер для чтения. Переменная read_buffer_size определяет размер такого буфера. При чтении строк в произвольной последовательности (например, после сортировки) может быть выделен буфер случайного чтения, чтобы избежать обращений к диску. Системная переменная read_rnd_buffer_size определяет размер буфера.

Мониторинг

MySQL также имеет Performance Schema, которая является функцией для мониторинга действий MySQL на низком уровне. Как только она включена (а он настройка по-умолчанию), то она постепенно динамически выделяет память, масштабируя использование памяти до фактической нагрузки сервера, вместо того, чтобы выделять требуемую память во время запуска сервера. После выделения памяти она не освобождается до перезапуска сервера. Именно поэтому, Performance Schema постоянно отъедает все больший кусок памяти и его не возвращает, что может сказаться на производительности сервера. Многие рекомендуют отключать Performance Schema полностью.

Буферы и настройки InnoDB

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

• InnoDB выделяет память для всего пула буферов при запуске сервера. Системная переменная innodb_buffer_pool_size определяет размер пула буферов. Обычно рекомендуемое значение innodb_buffer_pool_size составляет от 50 до 75% системной памяти (при условии, что она не используется другими сервисами). innodb_buffer_pool_size можно настраивать динамически, пока сервер работает без его перезагрузки.
• В системах с большим объемом памяти вы можете улучшить параллелизм, разделив пул буферов на несколько экземпляров пула буферов. Системная переменная innodb_buffer_pool_instances определяет количество экземпляров буферного пула. По умолчанию значение параметра равно 1, но более оправданным является значение вычисляемое как сумма innodb_buffer_pool_size в гигабайтах и ядер CPU, деленная пополам: (innodb_buffer_pool_size in Gb + CPUs)/2
• Слишком маленький пул буферов может вызвать чрезмерное перемешивание, поскольку страницы удаляются из пула буферов только для того, как они могут потребоваться снова через короткое время.
• Слишком большой пул буферов может вызвать своппинг из-за конкуренции за память. Если сервер уходит в SWAP, стоит уменьшать значения.

Прочие параметры:

• innodb_additional_mem_pool_size — данная опция практически никак не влияет на производительность mySQL, однако рекомендую оставлять для InnoDB около 20 МБ (или чуть больше) под различные внутренние нужды.
• innodb_log_file_size — крайне важная настройка в условиях баз данных с частыми операциями записи в таблицы, в особенности при больших объёмах. Она устанавливает размер лога операций (так операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый.  Большие размеры увеличивают быстродействие, однако будьте осторожны — увеличится и время восстановления данных. Подумайте над использованием реплик для обеспечения доступности, чтобы не зависеть от времени восстановления базы данных. Я обычно выставляю значение около 64-512 МБ в зависимости от размера сервера. Так, при значении

  innodb_log_file_size = 512M

  два файла дадут размер лога в 2x512M = 1G.
• innodb_log_buffer_size — это размер буфера транзакций, которые не были еще закомичены. Значение этого параметра стоит менять в случаях, если вы используете большие поля вроде BLOB или TEXT. Стандартное значение данной опции 1M вполне подойдёт для большинства систем со средним количеством операций записи и небольшими транзакциями. Если же в Вашей системе бывают всплески активности, или Вы активно работаете с BLOB-данными, то рекомендую немного увеличить значение innodb_log_buffer_size. Однако не переусердствуйте — слишком большое значение будет пустой тратой памяти: буфер сбрасывается каждую секунду, поэтому Вам не понадобится больше места, чем требуется в течение этой секунды. Рекомендуемое значение — около 8-16 МБ, а для небольших баз — 1-2М.

  innodb_log_buffer_size = 2M

• 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» вы потеряете данные только при аварии всей операционной системы.
• innodb_flush_method - Этот параметр определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узов, вы будете выбирать между O_DSYNC и O_DIRECT. Помните об обязательном использовании резервных узлов (например, реплик)

innodb_flush_method = O_DSYNC

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

innodb_file_per_table = ON

Настройки MyISAM

• key_buffer_size (default 8M) — крайне важная настройка при использовании MyISAM-таблиц. Установите её равной около 20-40% от доступной оперативной памяти, если используете только MyISAM. Правильный размер зависит от размеров индексов, данных и нагрузки на сервер — помните, что MyISAM использует кэш операционной системы (ОС), чтобы хранить данные, поэтому нужно оставить достаточно места в ОЗУ под данные, и данные могут занимать значительно больше места, чем индексы. Однако обязательно проверьте, чтобы всё место, отводимое директивой key_buffer_size под кэш, постоянно использовалось — нередко можно видеть ситуации, когда под кэш индексов отведено 4 ГБ, хотя общий размер всех .MYI-файлов не превышает 1 ГБ. Делать так совершенно бесполезно, Вы только потратите ресурсы. Если у Вас практически нет MyISAM-таблиц, то key_buffer_size следует выставить около 16-32 МБ — они будут использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске. Памяти под индексы выделяется достаточно если отношение значений Key_reads/Key_read_request оказывается < 0,01.

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

  SHOW STATUS LIKE «Key%»;

  Также в выводе будут значения Key_write_requests и Key_writes.
• table_cache / tables_open_cache (default 64)- количество открытых таблиц для всех потоков. Открытие таблиц может быть весьма ресурсоёмко. К примеру, MyISAM-таблицы помечают заголовки .MYI файлов как «используемые в текущий момент». Обычно не рекомендуется открывать таблицы слишком часто, поэтому лучше, чтобы кэш был достаточных размеров, чтобы держать все Ваши таблицы открытыми. Для этого используется некоторое количество ресурсов ОС и оперативной памяти, однако это обычно не является существенной проблемой для современных серверов. Если у Вас несколько сотен таблиц, то стартовым значением для опции table_cache может быть«1024» (помните, что каждое соединение требует свой собственный дескриптор). Если у Вас ещё больше таблиц или очень много соединений — увеличьте значение параметра. Я видел mySQL сервера со значением table_cache равной 100 000.
  Чтобы выявить необходимое значение нужно выполнить запрос

  SHOW STATUS LIKE «Opened_tables%»;

  Затем установить значение переменной несколько больше значения в выводе:
  +—————+——-+
| Variable_name | Value |
+—————+——-+
| Opened_tables | 1756 |
+—————+——-+

  Например, для 200 конкурентных соединений, укажите размер кэша таблиц не менее 200 * N, где N — максимальное количество таблиц на соединение в любом из выполняемых вами запросов. Вы также должны зарезервировать некоторые дополнительные файловые дескрипторы для временных таблиц и файлов. Если у вас в запросе джойнятся 4 таблицы, то вам нужно значение не менее 200*4 = 800. Подробнее в официальной документации.
• thread_cache — создание/уничтожение потоков также является ресурсоёмкой операцией, которая происходит при каждой установке соединения и каждом разрыве соединения. Я обычно выставляю эту опцию равную 16. Если у Вашего приложения могут быть скачки количество конкурентных соединений и по переменной Threads_Created виден быстрый рост количества потоков, то стоит увеличить значение thread_cache. Цель — не допускать создания новых потоков в условиях нормального функционирования сервера.
• thread_cache_size (default 0) - количество потоков, которое сервер должен кэшировать для повторного использования (т.е. количество потоков которые не создаются вновь при каждом запросе), хорошее значение для начала — 4.Вычисляется как [Connections - Threads_created] (должно быть примерно равно или чуть больше значения Max_used_connections)
  

  SHOW STATUS LIKE «Max_used_connections%»;

• query_cache_size (default 0) — если Ваше приложение много и часто читает данные, и при этом у Вас нет кэша на уровне приложения, эта опция может очень помочь. Не ставьте здесь слишком большое значение, так как обслуживание большого кэша запросов будет само по себе затратным. Рекомендуемое значение — от 4 до 512 МБ, 4-8 МБ достаточно для начала. Не забудьте проверить, насколько хорошо используется кэш запросов — в некоторых условиях (при небольшом количестве хитов в кэше, т.е. когда практически не выбираются одинаковые данные) использование большого кэша может ухудшить производительность. если значение = 0, то следующий параметр работать не будет.
  Есть также рекомендация не полагаться на этот механизм и оставить значение = 0. На практике он работает очень неэффективно. Так, весь кеш запросов для определенной таблицы сбрасывается всякий раз, когда в таблицу вносится хотя бы одно изменение.
• query_cache_type (default 1=on) — включение кэша запросов. 0=off, 2=on if needed
• query_cache_limit (default 1M) - максимальный размер запроса который может быть помещен к кеш.
• myisam_sort_buffer_size=128М - Буфер для создания индексов и REPAIR, а также ALTER TABLE в myisam таблицах.

Общие буферы MySQL

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