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

Язык структурированных запросов (SQL) - это язык программирования специального назначения, используемый для хранения, манипулирования и извлечения данных из баз данных.

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

Нахождение файла my.cnf

  • В системах Linux файл конфигурации MySQL обычно хранится в: /etc/mysql/my.cnf
  • В системах Windows файл конфигурации MySQL обычно хранится в: C:\Windows\my.ini

Перед внесением любых изменений в конфигурацию MySQL создайте резервную копию файла my.cnf:

Если вы не можете найти my.cnf, вы также можете запросить у MySQL список всех мест, где он ищет my.cnf (или my.ini в Windows). Однако это не SQL-запрос. Скорее, выполните:

или, до версии 5.7 MySQL:

В самых первых строках вы найдете сообщение со списком всех мест my.cnf, которые он ищет:

Параметры по умолчанию считываются из следующих файлов в указанном порядке:

  • /etc/my.cnf
  • /etc/mysql/my.cnf
  • /usr/etc/my.cnf
  • ~/.my.cnf

Или, в Windows:

Параметры по умолчанию считываются из следующих файлов в указанном порядке:

  • C:\Windows\my.ini
  • C:\Windows\my.cnf
  • C:\my.ini
  • C:\my.cnf
  • C:\Program Files\MySQL\MySQL Server 8.0\my.ini
  • C:\Program Files\MySQL\MySQL Server 8.0\my.cnf

Возможно, что ни в одном из этих мест нет файла my.cnf. Вы можете создать файл самостоятельно - используйте один из образцов конфигурационных файлов, поставляемых с MySQL (в Linux - смотрите файлы /usr/share/mysql/*.cnf и используйте тот, который подходит вам - скопируйте его в /etc/my.cnf, а затем измените по необходимости).

Также обратите внимание, что существует опция командной строки --defaults-file, которая может определить пользовательский путь к файлу my.cnf или my.ini. Например, в случае MySQL 5.5 на Windows она указывает на файл my.ini в каталоге данных, который обычно не указывается в mysqld --help --verbose.

Перезапуск MySQL

Лучшая практика предлагает вносить небольшие изменения по одному за раз, а затем контролировать работу сервера после каждого изменения. После каждого изменения следует перезапускать MySQL:

Для дистрибутивов, использующих systemd:

Для дистрибутивов с другими системами init:

При изменении значений в файле my.cnf убедитесь, что изменяемая строка не была закомментирована с префиксом #.

Механизмы хранения данных

InnoDB - это механизм хранения данных, используемый по умолчанию в базе данных MySQL с версии 5.5 вместо MyISAM. Он ориентирован на надежность и производительность, имеет блокировку на уровне строк, внешние ключи и поддержку транзакций. Он следует модели ACID (atomicity, consistency, isolation, durability), которая гарантирует, что все транзакции обрабатываются правильно и без ошибок.

MyISAM - это механизм хранения данных по умолчанию для систем управления реляционными базами данных MySQL версий до 5.5. Он основан на старом коде ISAM, но имеет множество полезных расширений. Основным недостатком MyISAM является отсутствие поддержки транзакций. Версии MySQL 5.5 и выше перешли на движок InnoDB для обеспечения ограничений ссылочной целостности и более высокого параллелизма.

Aria - это механизм хранения данных для систем управления реляционными базами данных MariaDB и MySQL. Его цель - создать безопасную в плане сбоев альтернативу MyISAM. Он еще не транзакционный, но в будущем планируется добавить поддержку транзакций баз данных. Долгосрочная цель состоит в том, чтобы Aria стала транзакционным и нетранзакционным механизмом хранения данных по умолчанию для MariaDB. Aria не поставляется с MySQL или Percona Server, но включена в релиз TokuDB для MariaDB.

В этой статье мы сосредоточимся исключительно на InnoDB. Если вы все еще используете MyISAM, настоятельно рекомендуется перевести ваш механизм хранения данных на InnoDB.

Оптимизация директив

Примечание: переменные должны быть настроены в секции [mysqld] вашего файла my.cnf

join_buffer_size

Минимальный размер буфера, который используется для обычного сканирования индексов, сканирования индексов диапазона и объединений, которые не используют индексы и, таким образом, выполняют полное сканирование таблицы. Как правило, лучший способ получить быстрые соединения - добавить индексы.

Увеличьте значение join_buffer_size, чтобы получить более быстрое полное соединение, когда добавление индексов невозможно. Для каждого полного соединения между двумя таблицами выделяется один буфер соединения. Для сложного соединения между несколькими таблицами, для которых не используются индексы, может потребоваться несколько буферов соединения.

Измените конфигурацию MySQL для регистрации запросов без индексов, так вы сможете найти такие запросы и добавить индексы и/или изменить приложение, которое посылает такие больные запросы. Вы должны включить "log-queries-not-using-indexes" Затем поищите неиндексированные соединения в журнале медленных запросов.

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

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

В отличие от многих буферов, которые выделяются на поток (т.е. на соединение), этот буфер выделяется на соединение на поток.

Максимально допустимое значение для join_buffer_size и sort_buffer_size - 4GB-1. Большие значения допустимы для 64-битных платформ (кроме 64-битной Windows, для которой большие значения усекаются до 4GB-1 с предупреждением). Однако такие большие значения никогда не должны использоваться.

В качестве эмпирического правила вы должны установить 1M на каждый гигабайт используемой памяти в MySQL. Таким образом, 1M для 1GB, 2M для 2GB, 4M для 4GB.

sort_buffer_size

Каждая сессия, которой необходимо выполнить сортировку, выделяет буфер такого размера. Это означает, что всякий раз, когда запрос должен отсортировать строки, значение этой переменной используется для ограничения размера, который необходимо выделить. sort_buffer_size не является специфическим для какого-либо механизма хранения и применяется в общем случае для оптимизации.

Первое, что вам нужно знать, это то, что sort_buffer_size - это буфер для каждой сессии. То есть эта память выделяется на каждое соединение/поток.

Во-вторых, внутри использования ОС, независимо от MySQL, существует порог > 256K. Если для буфера установлено значение больше 256K, то для выделения памяти используется mmap() вместо malloc(). На самом деле - это фишка libc malloc и она настраивается, но по умолчанию используется 256K. Буфер больше 256К приводит к 37-кратному замедлению. Это относится ко всем буферам на сессию, а не только к буферу сортировки. Недавно я слышал, что этот предел может быть 512K.

Если вы видите много sort_merge_passes в секунду в выводе SHOW GLOBAL STATUS, вы можете рассмотреть возможность увеличения значения sort_buffer_size для ускорения операций ORDER BY или GROUP BY, которые нельзя улучшить с помощью оптимизации запросов или улучшенного индексирования. Выделяется весь буфер, даже если он не весь необходим, поэтому глобальное увеличение размера буфера приведет к замедлению большинства запросов, выполняющих сортировку. Лучше всего увеличивать его как настройку сессии, и только для тех сессий, которым нужен больший размер.

В заключение, в Linux существуют пороговые значения 256 КБ и 2 МБ, при которых большие значения могут значительно замедлить распределение памяти, поэтому вам следует подумать о том, чтобы оставаться ниже одного из этих значений. Экспериментируйте, чтобы найти оптимальное значение для вашей рабочей нагрузки.

read_buffer_size

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

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

  • Для кэширования индексов во временном файле (не во временной таблице), при сортировке строк по ORDER BY.
  • Для массовой вставки в разделы.
  • Для кэширования результатов вложенных запросов.

и еще один специфический для движка хранения способ: определение размера блока памяти для таблиц MEMORY.

Максимально допустимое значение для read_buffer_size - 2 ГБ.

read_rnd_buffer_size

Размер буфера read_rnd_buffer_size используется после сортировки, при чтении строк в отсортированном порядке. Если вы используете много запросов с ORDER BY, увеличение этого параметра может повысить производительность. Помните, что, в отличие от key_buffer_size и table_cache, этот буфер выделяется для каждого потока. Эта переменная была переименована из record_rnd_buffer в MySQL 4.0.3. По умолчанию она имеет тот же размер, что и read_buffer_size.

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

Максимально допустимое значение для read_rnd_buffer_size составляет 2 ГБ. Правилом является выделение 1 КБ на каждый 1 МБ памяти на сервере, например, 1 МБ на машине с 1 ГБ памяти.

Однако следует учитывать, что это не относится к MariaDB, особенно при использовании преимуществ MRR. MariaDB использует mrr_buffer_size, в то время как MySQL использует read_buffer_size и read_rnd_buffer_size.

key_buffer_size

Значение key_buffer_size - это размер буфера, используемого с индексами. Чем больше буфер, тем быстрее завершится команда SQL и будет возвращен результат. Правилом является установка размера key_buffer_size на уровне не менее четверти, но не более половины общего объема памяти на сервере. В идеале он должен быть достаточно большим, чтобы вместить все индексы (общий размер всех файлов .MYI на сервере).

Простой способ проверить фактическую производительность буфера заключается в исследовании четырех дополнительных переменных:

  • key_read_requests
  • key_reads
  • key_write_requests
  • key_writes

Если вы разделите значение key_read на значение key_reads_requests, результат должен быть меньше 0,01. Также, если разделить значение key_write на значение key_writes_requests, результат должен быть меньше 1.

Максимальный размер переменной key_buffer_size составляет 4 ГБ на 32-битных машинах и больше для 64-битных машин. MySQL рекомендует, чтобы размер key_buffer_size был меньше или равен 25% оперативной памяти на вашей машине. Это также зависит от других процессов, использующих память на машине, поэтому имеет смысл проверить, есть ли у вас постоянно 25% свободной памяти, используя команду Linux free.

Следует ли вам увеличить размер key_buffer_size?

Сначала мы должны просмотреть некоторую статистику по ключевому буферу. Войдите в свой инструмент администрирования mysql:

Затем, когда появится приглашение mysql, выполните следующую команду:

SHOW STATUS LIKE "key%";

Две переменные, представляющие интерес, это key_reads и key_read_requests.

  • Key_reads: Эта переменная представляет собой количество чтений с диска, а не из кэша.
  • Key_read_requests: Представляет собой количество запросов на чтение блока из кэша.

следующая переменная представляет собой процент попадания в кэш:

key_cache_hit_percentage = (1 - Key_reads/Key_read_requests) * 100

Если ваш процент key_cache_hit_percentage высок, вам не нужно увеличивать размер key_buffer_size, так как это не окажет большого влияния. В моем случае процент попадания ключа в кэш составляет около 98%, что довольно много. Поэтому увеличение размера ключевого_буфера не сильно поможет.

Посмотрите, какой процент попадания вы получаете, и если значение близко к 90-100%, то у вас уже оптимизирован размер key_buffer_size. Если же он меньше, то увеличение буфера ключей может оказать огромную помощь.

Заметим еще раз, MySQL рекомендует, чтобы значение буфера составляло 25% или меньше от размера физической оперативной памяти.

Прежде чем вы решите увеличить key_buffer_size, вы должны проверить свободную память с помощью free -m

max_user_connections

Одной из распространенных причин простоя MySQL является исчерпание соединений.

Вы когда-нибудь видели эту ошибку:

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

Одним из способов ограничения использования ресурсов сервера MySQL является установка глобальной системной переменной max_user_connections в ненулевое значение. Это ограничивает количество одновременных подключений, которые могут быть сделаны любой учетной записью, но не накладывает никаких ограничений на то, что клиент может делать после подключения.

Если учетная запись пользователя имеет ненулевое ограничение ресурсов max_user_connections, значение сессии max_user_connections устанавливается равным этому ограничению.

В противном случае, значение max_user_connections сессии устанавливается на глобальное значение.

Решение, к которому прибегают некоторые люди, заключается в том, чтобы увеличить max_connections до какого-то очень большого числа, чтобы MySQL "никогда" не исчерпал количество соединений. Однако это может вызвать проблемы с использованием ресурсов - если большое количество соединений становится действительно активным, оно может использовать много памяти и вызвать своппинг сервера MySQL или быть убитым процессом OOM killer, или вызвать очень низкую производительность из-за высокой конкуренции.

Есть лучшее решение: использовать различные учетные записи пользователей для различных скриптов и приложений и реализовать ограничение ресурсов для них. В частности, установите max_user_connections.

Этот подход (доступный с MySQL 5.0) имеет множество преимуществ:

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

В дополнение к настройке max_user_connections для определенных учетных записей вы можете установить его глобально в my.cnf следующим образом:

max_connections

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

Максимальное количество соединений, которое может поддерживать MySQL, зависит от качества библиотеки потоков на данной платформе, объема доступной оперативной памяти, того, сколько оперативной памяти используется для каждого соединения, нагрузки от каждого соединения и желаемого времени отклика.

Linux или Solaris должны поддерживать от 500 до 1000 одновременных соединений в обычном режиме и до 10 000 соединений, если у вас есть много гигабайт оперативной памяти и нагрузка от каждого соединения низкая или время отклика нетребовательное.

Увеличьте это число, если вы получаете следующую ошибку:

max_allowed_packet

Максимальный размер одного пакета или любой сгенерированной/промежуточной строки. Размер max_allowed_packet является переменной сессии, а также переменной только для чтения. В MySQL вы можете столкнуться с ошибкой 1153: Получен пакет размером больше, чем 'max_allowed_packet' байт, когда размер пакета слишком мал.

Наибольший возможный пакет, который может быть передан на сервер или клиент MySQL, составляет 1 ГБ. Значение по умолчанию составляет 64 МБ.

В старых версиях MySQL значение по умолчанию составляло 4 МБ, поэтому вы можете позаботиться об этом в случае возникновения ошибки ER_NET_PACKET_TOO_LARGE.

Вы должны увеличить значение по умолчанию, если вы используете большие колонки BLOB (Binary Large OBject) или длинные строки. Оно должно быть таким же большим, как самый большой BLOB, который вы хотите использовать. Значение должно быть кратно 1024; не кратные значения округляются до ближайшего кратного. Предлагаемое значение:

или в байтах:

Для проверки текущего значения max_allowed_packet используется команда show variables:

max_allowed_packet

query_cache_size

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

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

Решение о том, стоит ли увеличивать размер переменной query_cache_size, можно легко принять с помощью нескольких диагностических сессий mysql.

Следует ли вам увеличить размер query_cache_size?

query_cache_size был удален в MySQL 8.

Сначала войдите в интерпретатор командной строки mysql:

Это запросит у вас пароль, а затем покажет вам командную строку mysql.

Используйте следующий сеанс, чтобы получить статистику по кэшу запросов:

query_cache

Qcache_free_memory показывает, что в настоящее время имеется около 8 МБ свободной памяти из доступных 16 МБ. Это означает, что около половины размера query_cache_size доступно для кэширования результатов большего количества запросов!

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

В данном случае у нас около 2 ГБ доступной памяти. Сначала мы должны проверить, сколько у нас свободной памяти:

query_cache_type

query_cache_type был удален в MySQL 8.

При тестировании производительности mysql с помощью mysqltuner.pl, если в строке "query cache efficiency" стоит "0", это означает, что вы должны включить механизм кэширования запросов, поскольку он отключен.

query_cache_limit

query_cache_limit был удален в MySQL 8.

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

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

skip-external-locking

внешняя блокировка отключена по умолчанию. Вам необходимо включить внешнюю блокировку:

  • Если вы запускаете несколько серверов, которые используют один и тот же каталог базы данных (не рекомендуется), на каждом сервере должна быть включена внешняя блокировка.
  • Если вы используете myisamchk для выполнения операций обслуживания таблиц MyISAM, вы должны либо убедиться, что сервер не запущен, либо что на сервере включена внешняя блокировка, чтобы он блокировал файлы таблиц по мере необходимости, чтобы координировать с myisamchk доступ к таблицам. То же самое справедливо и для использования myisampack для упаковки таблиц MyISAM.

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

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

При отключенной внешней блокировке для использования myisamchk необходимо либо остановить сервер на время выполнения myisamchk, либо заблокировать и промыть таблицы перед запуском myisamchk. Чтобы избежать этого требования, используйте операторы CHECK TABLE и REPAIR TABLE для проверки и восстановления таблиц MyISAM.

slow_query_log

MySQL сообщает, сколько времени ушло на выполнение запроса.

Шаг 1: Включите slow_query_log, задайте slow_query_log_file и установите log_query_time в my.cnf:

В разделе [mysqld] добавьте/отредактируйте следующие переменные:

log_query_time установлен на 0 (ноль). Любой запрос MySQL, занимающий более 0 секунд, будет записываться в журнал.

Шаг 2: Создайте файл /var/log/mysqld.slow.query.log и установите системного пользователя mysql в качестве владельца.

Шаг 3: Перезапустите сервер MySQL

Шаг 4: Просмотрите журнал медленных запросов с помощью команды tail.

Пример записи на сервере выглядит следующим образом:

table_open_cache

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

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

По умолчанию это значение равно 64.

table_open_cache_instances

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

Кэш открытых таблиц может быть разделен на несколько меньших экземпляров кэша:

table_open_cache / table_open_cache_instances

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

Значение 8 или 16 рекомендуется для систем, которые обычно используют 16 или более ядер.

table_definition_cache

Кэшировать определения таблиц, т.е. здесь кэшируется CREATE TABLE, чтобы ускорить открытие таблиц, и только одна запись на таблицу. Целесообразно увеличить это значение, если у вас большое количество таблиц. Кэш определения таблицы занимает меньше места и не использует файловые дескрипторы, в отличие от обычного кэша таблиц.

Percona рекомендует использовать приведенную ниже формулу для баз данных с менее чем 50.000 таблиц:

Количество определяемых пользователем таблиц + 10%

Обратите внимание, что значение по умолчанию основано на следующей формуле с ограничением до 2000:

MIN(400 + table_open_cache / 2, 2000)

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

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

Кроме того, table_definition_cache определяет мягкое ограничение на количество табличных пространств innoDB file_per-table, которые могут быть открыты одновременно, которое также контролируется innodb_open_files, и фактически, используется наибольшее значение между этими переменными, если обе они установлены. Если ни одна из переменных не установлена, используется table_definition_cache, которая имеет более высокое значение по умолчанию.

Если количество открытых дескрипторов файлов табличных пространств превышает предел, определенный table_definition_cache или innodb_open_files, механизм LRU ищет в списке LRU файлов табличных пространств файлы, которые полностью очищены и в настоящее время не расширяются. Этот процесс выполняется каждый раз при открытии нового табличного пространства. Если нет "неактивных" табличных пространств, файлы табличных пространств не закрываются. Поэтому имейте это в виду.

thread_cache_size

Если у вас загруженный сервер, который получает много быстрых подключений, установите кэш потоков настолько высоким, чтобы значение Threads_created в SHOW STATUS перестало увеличиваться. Это снимет часть нагрузки с центрального процессора.

Самый простой способ определить нужный размер - следить за увеличением переменной состояния Threads_created:

mysqladmin -u root -p -r -i 1 ext | grep Threads_created

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

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

Основываясь на значениях из примера выше, имеет смысл установить размер thread_cache_size где-то между 20 и 30.

created_tmp_disk_tables

created_tmp_disk_tables - это количество неявных временных таблиц на диске, созданных при выполнении операторов, а "created_tmp_tables" - это таблицы в памяти. Очевидно, что плохо, если постоянно приходится обращаться к диску, а не к памяти.

 max_heap_table_size

max_heap_table_size - это максимальный размер в байтах, который разрешено увеличивать таблицам MEMORY, созданным пользователем. Это полезно, когда ваше приложение работает с таблицами движка хранения MEMORY. Установка переменной во время работы сервера не влияет на существующие таблицы, если они не были созданы заново или изменены.

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

 tmp_table_size

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

Вам следует увеличить значение tmp_table_size (и max_heap_table_size, если необходимо), если вы делаете много продвинутых запросов GROUP BY и у вас приличный объем доступной памяти.

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

При каких обстоятельствах?

Когда запрос выполняет объединение или сортировку (через ORDER BY) без использования индекса, в памяти должна быть сформирована временная таблица. Это приведет к увеличению Created_tmp_tables.

Что если временная таблица вырастет до количества байт в tmp_table_size и ей потребуется больше места? Происходит следующая последовательность событий:

  • Обработка запроса должна остановиться
  • Создать временную таблицу на диске
  • Перенести содержимое временной таблицы в памяти во временную таблицу на диске
  • Удалить временную таблицу из памяти
  • Обработка запросов продолжается с использованием временной таблицы на диске

Этот процесс увеличивает количество created_tmp_disk_tables

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

wait_timeout

Количество секунд, в течение которых сервер ожидает активности на неинтерактивном соединении, прежде чем закрыть его.

По умолчанию переменная wait_timeout равна 28800 секунд, что составляет 8 часов. Это очень много.

Установка слишком низкого значения wait_timeout (например, 30, 60, 90) может привести к появлению сообщений об ошибке "MySQL ушел". Так что вам придется решать для вашей конфигурации.

 skip_name_resolve

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

Подумайте о том, чтобы включить эту переменную, если вам не нужно разрешение DNS и воспользоваться преимуществами улучшения производительности MySQL, когда этот поиск DNS отключен. Примите во внимание, что эта переменная не является динамической, поэтому требуется перезагрузка сервера, если вы установите ее в конфигурационном файле MySQL. Вы можете опционально запустить демон mysqld, передав опцию --skip-name-resolve, чтобы включить это.

Директивы оптимизации - InnoDB

innodb_buffer_pool_size

innodb_buffer_pool_size - это, безусловно, самый важный параметр для производительности Innodb, и он должен быть установлен правильно. Эта переменная сообщает MySQL, сколько памяти он может использовать. Если оставить значение по умолчанию 8M, это сильно замедляет производительность в большинстве случаев. Вы захотите отдать всю память, которая не нужна для других сервисов, буферному пулу Innodb.

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

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

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

Сначала введите следующий SQL-запрос:

Это даст вам RIBPS (рекомендуемый размер буферного пула InnoDB) на основе всех данных и индексов InnoDB с дополнительными 60%.

innodb_buffer_pool_size

Попробуйте увеличить размер innodb_buffer_pool_size

Дале

После перезапуска запустите mysql на неделю или две. Затем выполните следующий запрос:

Это даст вам количество страниц данных InnoDB, которые находятся в буферном пуле InnoDB.

Иногда InnoDB может потребовать дополнительные 10% к значению innodb_buffer_pool_size. В документации MySQL говорится:

  • Чем больше вы установите это значение, тем меньше дискового ввода-вывода потребуется для доступа к данным в таблицах. На выделенном сервере баз данных вы можете установить это значение до 80% от объема физической памяти машины. Будьте готовы уменьшить это значение, если возникнут другие проблемы:
  • Конкуренция за физическую память может вызвать подкачку в операционной системе.
    InnoDB резервирует дополнительную память для буферов и управляющих структур, так что общее выделенное пространство примерно на 10% больше указанного размера.
  • Адресное пространство должно быть непрерывным, что может быть проблемой в системах Windows с библиотеками DLL, которые загружаются по определенным адресам.
  • Время инициализации буферного пула примерно пропорционально его размеру. На больших установках это время инициализации может быть значительным. Например, на современном сервере Linux x86_64 инициализация пула буферов размером 10 ГБ занимает около 6 секунд.

innodb_buffer_pool_instances

Для вашей параллельной рабочей нагрузки установка этой переменной может улучшить параллельность и уменьшить количество ошибок при чтении/записи кэшированных страниц разными потоками. Минимальное значение innodb_buffer_pool_instances должно лежать между 1 (минимум) и 64 (максимум).

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

Обратите внимание, что эта опция действует только тогда, когда innodb_buffer_pool_size >= 1GiB и его размер разделен между экземплярами буферного пула.

 innodb_flush_method

Важным параметром, который вы должны добавить в файл my.cnf, является innodb_flush_method=O_DIRECT. Обычно O_DIRECT используется, когда приложение, например, база данных, выполняет собственное кэширование в пространстве пользователя. Это предотвратит кэширование операционной системой данных, которые уже кэшированы.

Однако следует избегать использования O_DIRECT на файловых системах EXT2, EXT3 и jfs без журналирования - запись в один и тот же файл сериализуется и не может происходить одновременно.

Проблема сериализации также становится очевидной, когда sync_binlog = 1. Производительность значительно снижается в обоих случаях. Соответствующая установка innodb_file_per_table помогает уменьшить проблему при использовании нежурналируемой файловой системы.

 innodb_adaptive_flushing

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

Vногопоточность innoDB

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

Большинство людей не знают об этих возможностях и вполне довольны тем, что InnoDB просто выполняет ACID-совместимые транзакции. Если вы изменяете любую из этих опций, вы делаете это на свой страх и риск.

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

Несмотря на документацию, лучше всего оставить innodb_thread_concurrency на 0 (бесконечный параллелизм). Таким образом, InnoDB сама решит, какое количество innodb_concurrecy_tickets лучше открыть для данного экземпляра MySQL.

Установив innodb_thread_concurrency = 0, вы можете установить innodb_read_io_threads и innodb_write_io_threads (см. ниже) на максимальное значение 64. Это позволит задействовать больше ядер:

innodb_io_capacity

Значение innodb_io_capacity должно быть настроено на количество IOPS, которое может выдержать ваш дисковый массив. Или, на самом деле, количество IOPS, которое вы хотите, чтобы потреблял MySQL (потому что вы можете не захотеть, чтобы это было 100% дисковой емкости). Значение по умолчанию innodb_io_capacity = 200, что рекомендуется для HDD серверов.

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

innodb_file_per_table

innodb_file_per_table включен по умолчанию, начиная с MySQL 5.6. Обычно это рекомендуется, так как позволяет избежать огромного общего табличного пространства и освободить место при удалении или усечении таблицы. Отдельное табличное пространство также полезно для схемы частичного резервного копирования Xtrabackup.

innodb_thread_concurrency

устанавливает верхнюю границу числа одновременных потоков, которые InnoDB может держать открытыми. Лучшее круглое число для этого - (2 X количество CPU) + количество дисков.

Рекомендуется позволить движку управлять параллелизмом, оставив значение по умолчанию (которое равно нулю), чтобы предупредить InnoDB Storage Engine о необходимости поиска оптимального количества потоков для среды, в которой он работает. Если вы видите проблемы с параллелизмом, вы можете настроить эту переменную.

 innodb_concurrency_tickets

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

 innodb_commit_concurrency

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

innodb_thread_sleep_delay

задает количество миллисекунд, в течение которых поток InnoDB может находиться в спящем режиме, прежде чем снова встать в очередь InnoDB. По умолчанию 10000 (10 секунд).

innodb_read_io_threads и innodb_write_io_threads

(оба с версии MySQL 5.1.38) выделяют указанное количество потоков для чтения и записи. По умолчанию это 4, а максимум - 64. Они также могут быть бесполезны в файловых системах, которые не позволяют параллельную запись в один и тот же файл более чем одним потоком (особенно если у вас относительно мало таблиц и/или вы используете глобальные табличные пространства).

innodb_replication_delay

накладывает задержку потока на ведомое устройство при достижении innodb_thread_concurrency.

innodb_read_ahead_threshold

позволяет линейное чтение заданного количества экстентов (64 страницы [страница = 16K]) перед переключением на асинхронное чтение.

innodb_log_file_size

Эта переменная представляет собой файл журнала в группе журналов. Совокупный размер лог-файлов (innodb_log_file_size * innodb_log_files_in_group) не может превышать максимального значения, которое чуть меньше 512 ГБ. По словам Вадима, больший размер файла журнала лучше для производительности, но у него есть недостаток (существенный), о котором нужно беспокоиться: время восстановления после сбоя. Вам нужно найти баланс между временем восстановления в редких случаях аварийного восстановления и максимальной пропускной способностью во время пиковых операций. Это ограничение может привести к 20-кратному увеличению времени восстановления после сбоя!

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

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

innodb_log_buffer_size

Для экономии дискового ввода-вывода InnoDB записывает данные об изменениях в буфер журнала lt и использует значение innodb_log_buffer_size, имеющее по умолчанию значение 8MiB.

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

innodb_flush_log_at_trx_commit

Когда innodb_flush_log_at_trx_commit установлен в 1, буфер журнала промывается при каждой фиксации транзакции в файл журнала на диске и обеспечивает максимальную целостность данных, но это также влияет на производительность.

Установка значения 2 означает, что буфер журнала сбрасывается в файловый кэш ОС при каждой фиксации транзакции. Значение 2 является оптимальным и повышает производительность, если вы можете ослабить свои требования ACID и можете позволить себе потерять транзакции на последнюю секунду или две в случае сбоя ОС.

innodb_stats_on_metadata

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

innodb_dedicated_server

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

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

MySQLTuner

Оптимизация работы MySQL

Понравилась статья? Поделиться с друзьями:
Добавить комментарий