Пол Намуаг. Шпаргалка по производительности MySQL

Это перевод статьи Paul Namuag. MySQL Performance Cheat Sheet.

Содержание

Содержание
Введение
Системные переменные
InnoDB
MyISAM
Сохранение и восстановление буферного пула
Оборудование
Операционная система
Дополнительные материалы

Введение

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

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

Давайте опишем эти ключевые области MySQL в общих чертах.

Системные переменные

В MySQL есть много изменяемых переменных. Часть переменных можно поменять динамически с помощью выражения SET. Для изменения других нужно задать новые значения в файле конфигурации (например, в /etc/my.cnf, /etc/mysql/my.cnf) и перезапустить сервер. Давайте рассмотрим простейшие способы оптимизации сервера.

sort_buffer_size

Эта переменная задаёт размер буфера сортировки. Если потребуется сортировка, то эта переменная ограничивает размер памяти для сортировки. Учтите, что эта переменная действует на каждый из обрабатываемых запросов (или подключений). При увеличении размера буфера и количества подключений, в которых выполняется сортировка, расход памяти возрастает. За частотой использования буферов сортировки можно наблюдать с помощью глобальной переменной состояния Sort_merge_passes. Если её значение велико, стоит подумать об увеличении значения системной переменной sort_buffer_size. И наоборот - может потребоваться уменьшить её значение. Если указать слишком маленькое значение, а в системе выполняются тяжёлые запросы, то сортировка строк будет выполняться медленнее, потому что данные будут считываться с дисков в непредсказуемом порядке, что может повлечь за собой снижение производительности. Если приложение спроектировано так, что извлекает много данных, требующих сортировки, то лучше исправить запросы или воспользоваться инструментами вроде Redis для кэширования результатов запросов. В настоящее время в MySQL 8.0 значение переменной по умолчанию равно 256 килобайт. Можно поменять это значение на более подходящее, но только при наличии большого количества запросов с сортировкой.

read_buffer_size

В документации MySQL написано, что для каждого запроса, производящего последовательно сканирование таблицы, выделяется буфер чтения. Системная переменная read_buffer_size задаёт размер этого буфера. Буфер используется таблицами MyISAM, но переменная влияет на все типы хранилищ. В случае таблиц MEMORY она используется для определения размера блока памяти.

По сути, каждый поток, выполняющий последовательное сканирование таблицы MyISAM, выделяет буфер указанного размера (в байтах) для каждой сканируемой таблицы. Она влияет на все типы хранилищ (включая InnoDB), поэтому может быть полезна для запросов, сортирующих строки с помощью ORDER BY, и для кэширования их индексов во временных файлах. Если используется много последовательных сканирований, массовые вставки в разделы таблиц, кэширование результатов вложенных запросов, то разумно будет увеличить значение переменной. Значение этой переменной должно быть кратным 4 килобайтам. Если указать значение, не кратное 4 килобайтам, то оно будет округлено вниз до ближайшего кратного 4 килобайтам. Учтите, что при высоких значениях будет потребляться больше памяти сервера. Рекомендуется не менять её значение без соответствующих измерений производительности и наблюдений за средой выполнения.

read_rnd_buffer_size

Эта переменная используется при чтении строк из таблиц MyISAM в отсортированном виде после операции сортировки по ключу. Строки читаются через этот буфер без обращений к диску. В документации сказано, что чтение строк в произвольном порядке или из таблицы MyISAM в отсортированном виде после операции сортировки по ключу, выполняется через этот буфер (размер которого определяется этой переменной) для избежания обращений к диску. Настройка больших значений этой переменной может довольно сильно улучшить производительность ORDER BY. Однако, этот буфер выделяется для каждого клиента, поэтому не стоит присваивать этой глобальной переменной большое значение. Лучше изменять эту переменную на уровне сеанса только от тех клиентов, которым нужно выполнить тяжёлые запросы. Но эта рекомендация не относится к MariaDB, особенно если используется MRR. MariaDB использует mrr_buffer_size, а MySQL использует read_buffer_size и read_rnd_buffer_size.

join_buffer_size

Значение по умолчанию - 256 килобайт. Минимальный размер буфера, используемого для простого просмотра индекса, просмотра индекса по диапазону и соединения таблиц, не использующих индексы, и потому использующих полный просмотр таблицы. Также используется оптимизацией BKA (которая по умолчанию отключена). Если невозможно добавить индексы, увеличьте это значение для ускорения соединений без индексов. Однако, при больших значениях может возникнуть проблема с памятью. Имейте в виду, что для каждой пары таблиц, соединяемых без индексов, используется по одному буферу. При соединении нескольких таблиц без индексов может потребоваться несколько буферов соединений. Лучше не трогать это значение и увеличивать его только на уровне сеанса (с помощью выражения SET SESSION) для сеансов, требующих соединений огромных таблиц без индексов. На 64-битных платформах Windows обрезает значения больше 4 гигабайт до 4 гигабайта минус один байт, выводя предупреждение.

max_heap_table_size

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

tmp_table_size

Максимальный размер временных таблиц в оперативной памяти (не типа MEMORY). Если переменная max_heap_table_size окажется меньше, то ограничивающей будет она. При превышении предельного размера таблица в оперативной памяти будет автоматически преобразована во временную таблицу на диске. Увеличьте значение tmp_table_size (и, при небходимости, max_heap_table_size), если имеется много запросов со сложными GROUP BY и достаточно места в оперативной памяти. Сравнить количество созданных внутренних временных таблиц на диске и общее количество внутренних временных таблиц можно с помощью переменных Created_tmp_disk_tables и Created_tmp_tables. В программе ClusterControl это можно сделать через график Панель -> Временные объекты (Dashboard -> Temporary Objects).

table_open_cache

Если имеется много часто используемых таблиц, то можно увеличить значение этой переменной. Она влияет на все потоки, то есть на все подключения. Она указывает максимальное количество таблиц, которое сервер может держать открытыми в одном из экземпляров кэша таблиц. Однако при увеличении этого значения увеличивается количество файловых дескрипторов, требуемых mysqld, поэтому стоит также проверить значение переменной open_files_limit и проверить, насколько велики значения мягкого и жёсткого лимитов в операционной системе *nix. Понять, стоит ли увеличить размер кэша таблиц, можно с помощью переменной состояния Opened_tables. Если значение Opened_tables большое, а команда FLUSH TABLES (которая принудительно закрывает все таблицы и открывает их снова) выполняется не часто, то стоит увеличить значение переменной table_open_cache. Если часто происходят обращения к большому количеству таблиц, а значение table_open_cache мало, то это может сказаться на производительности сервера. Если в списке процессов MySQL много процессов находится в состоняии Opening tables (открытие таблиц) или Closing tables (закрытие таблиц), то настало время настроить значение этой переменной, приняв во внимание перечисленные выше замечания. С помощью приложения ClusterControl это можно сделать через Dashboards -> Table Open Cache Status или Dashboards -> Open Tables. Здесь можно найти дополнительную информацию.

table_open_cache_instances

Настройка этой переменной должна помочь увеличить масштабируемость, и, конечно, производительность, за счёт снижения конкуренции между сеансами. Значение этой переменной ограничивает количество экземпляров кэша таблиц. Кэш открытых таблиц может быть разделён на несколько меньших экземпляров размером table_open_cache / table_open_cache_instances. Для выполнения выражений DML нужно заблокировать только один экземпляр кэша. Благодаря этому доступ к кэшу распределяется между экземплярами, позволяя поднять производительность операций с кэшем, если есть несколько сеансов, обращающихся к таблицам. (Выражения DDL по-прежнему требуют блокировки всего кэша, но такие выражения встречаются намного реже, чем выражения DML.) В системах с 16 или более ядрами рекомендуется использовать значение 8 или 16.

table_definition_cache

В кэше определений таблиц хранятся выражения CREATE TABLE для ускорения открытия таблиц, каждой таблице соответствует одна запись. Если таблиц очень много, имеет смысл увеличить значение этой переменной. Кэш определений таблиц занимает мало места и не использует файловые дескрипторы, в отличие от обычного кэша таблиц. Для выбора подходящего значения Пётр Зайцев (Peter Zaitsev) из Percona предлагает воспользоваться следующей формулой:

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

Однако стоит учесть, что значение по умолчанию в соответствии с этой формулой, ограничено 2000.

MIN(400 + table_open_cache / 2, 2000)

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

max_allowed_packet

Максимальный размер SQL-запроса или возвращаемых строк для каждого из подключений. Последний раз значение переменной было увеличено в MySQL 5.6, а в MySQL 8.0 (по меньшей мере в 8.0.3) текущее значение по умолчанию - 64 мегабайт. Может понадобиться увеличить его, если в базе данных есть огромные строки BLOB, которые нужно отправлять или читать. В случае, если используется версия 8.0, можно оставить значение по умолчанию. В более старых версиях используется значение по умолчанию 4 мегабайта, поэтому стоит обратить внимание на ошибки ER_NET_PACKET_TOO_LARGE. Наибольший возможный размер пакета, который может быть передан между клиентом и сервером MySQL 8.0, составляет 1 гигабайт.

skip_name_resolve

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

max_connections

Это количество разрешённых подключений к серверу MySQL. Если вы столкнулись с ошибкой MySQL "Too many connections" - слишком много подключений, стоит подумать об увеличении значения этой переменной. Значение по умолчанию - 151, чего обычно недостаточно для баз данных в рабочей эксплуатации. Если на сервере есть свободные ресурсы, то можно подумать об её увеличении (но не тратьте ресурсы впустую, особенно на выделенном сервере MySQL). Учтите, что нужно иметь достаточное количество файловых дескрипторов, в противном случае они могут закончиться. В таком случае подумайте о настройке мягкого и жёсткого лимитов вашей операционной системы *nix и задайте большее значение для open_files_limit в MySQL (лимит по умолчанию - 5000). Учтите, что довольно часто приложение не закрывает подключение к базе данных корректным образом, из-за чего большое значение max_connections может приводить к недоступности или высокой нагрузке на сервер. Для решения этой проблемы используйте пул подключений на уровне приложения.

thread_cache_size

Это кэш для предотвращения частого создания потоков. Когда клиент отключается, а количество потоков в кэше меньше thread_cache_size, то клиентский поток помещается в кэш. Новый поток создаётся, если кэш пуст, а в противном случае поток из кэша используется повторно. При частом установлении новых подключений для ускорения производительности эту переменную можно увеличить. При хорошей реализации потоков прирост в производительности не будет заметным. Но если на сервер поступают сотни новых подключений в секунду, стоит увеличить значение thread_cache_size, чтобы большинство новых подключений использовали потоки из кэша. Эффективность кэша потоков можно оценить по разнице между переменными состояния Connections и Threads_created. В соответствии с формулой, приведённой в документации, подходящим значением будет 8 + (max_connections / 100).

query_cache_size

В некоторых случаях эта переменная - злейший враг. В системах, имеющих высокую нагрузку по чтению, эта переменная приводит к замедлению, что было подтверждено тщательными тестами производительности, проделанными, например, Percona. Чтобы отключить кэш запросов, этой переменной и переменной query_cache_type нужно назначить значение 0. К счастью, в MySQL 8.0 это уже сделано. Команда MySQL прекратила поддержку кэша запросов, поскольку он действительно может вызывать проблемы с производительностью. Я согласен с публикацией в их блоге, где утвреждается, что улучшение производительнсоти маловероятно. Для кэширования запросов, при необходиости, лучше воспользоваться Redis или ProxySQL.

InnoDB

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

innodb_buffer_pool_size

Эта переменная похожа на буфер ключа MyISAM, но предоставляет гораздо больше функций. Поскольку InnoDB в значительной степени опирается на буферный пул, ему стоит предоставить 70%-80% объёма памяти сервера. Также желательно, чтобы набор данных умещался в буферный пул, но больший размер буферного пула будет избыточным. Увидеть значение размера буферного плуа в ClusterControl можно на графике Dashboards -> InnoDB Metrics -> InnoDB Buffer Pool Pages. То же самое можно увидеть с помощью команды SHOW GLOBAL STATUS с указанием переменных Innodb_buffer_pool_pages*.

innodb_buffer_pool_instances

В случае конкурирующей нагрузки настройка этой переменной может улучшить параллелизм и снизить конкуренцию различных потоков чтения/записи кэшированных страниц. Переменная innodb_buffer_pool_instances принимает значения между 1 и 64. Каждая сохраняемая или читаемая из буферного пула страница через хэш-функцию сопоставляется одному из экземпляров буферного пула. Каждый экземпляр буферного пула обладает собственными списком свободных элементов, списокм элементов, подлежащих записи, данными о последнем использовании элемента и всеми другими структурами данных буферного пула и защищён своим собственным мутексом. Учтите, что эта опция действует при innodb_buffer_pool_size >= 1 гигабайт и этот объём делится между экземплярами буферного пула.

innodb_log_file_size

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

Большие значения лучше подходят для журналов транзакций InnoDB, требовательного к стабильности и хорошей производительности записи. Чем выше значение, тем реже нужно сохранять контрольные точки из буферного пула, что снижает интенсивность дискового ввода-вывода. Но процесс восстановления при аварийном завершении работы (сбое или принудительном завершении, из-за нехватки оперативной памяти или случайности) будет очень долгим. В лучшем случае в промышленной эксплуатации этот объём должен составлять 1-2 гигабайта, но при необходимости можно использовать и другие значения. После изменения настроек для оценки времени восстановления после сбоев полезно оценивать изменения в производительности.

innodb_log_buffer_size

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

innodb_flush_log_at_trx_commit

Если innodb_flush_log_at_trx_commit принимает значение 1, то при каждой фиксации транзакции происходит запись журнала на диск, что позволяет достичь максимальной целостности данных, но также приводит к снижению производительности. Значение 2 означает, что при каждой фиксации транзакции журнал записывается в файловый кэш операционной системы. Подразумевается, что 2 даёт оптимальную производительность, если требования ACID не так важны и в случае аварии операционной системы допустимо потерять транзакции за последнюю секунду или две.

innodb_thread_concurrency

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

innodb_flush_method

Стоит опробовать разные значения этой переменной и выставить значение, лучше подходящее для используемого оборудования. Если используется RAID-контроллер с кэшем и батареей, DIRECT_IO поможет снизить количество операций ввода-вывода. Прямой ввод-вывод не кэшируется и позволяет избегать двойной буферизации в буферном пуле и кэше файловой системы. Если в качестве дисков используется SAN, то для нагрузки с преобладающими операциями чтения и выражениями SELECT быстрее может оказаться O_DSYNC.

innodb_file_per_table

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

innodb_stats_on_metadata

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

innodb_io_capacity

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

innodb_write_io_threads

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

innodb_adaptive_flushing

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

innodb_dedicated_server

Эта переменная появилась в MySQL 8.0, действует глобально и требует перезапуска MySQL, т.к. не является динамической переменной. Согласно документации, эту переменную желательно включать только на сервере, выделенном для MySQL. Не стоит включать её на совместно используемых системах или при совместном использовании ресурсов с другими приложениями. При включении этой перменной, InnoDB выполняет автоматическую настройку значений переменных innodb_buffer_pool_size, innodb_log_file_size, innodb_flush_method для обнаруженного объёма памяти. При использовании этой переменной для упомянутых выше переменных невозможно применить желаемые значения.

MyISAM

key_buffer_size

В настоящее время в MySQL по умолчанию используется хранилище InnoDB. Если MyISAM не используется в качестве промышленного хранилища для вашего приложения (но кто сейчас использует MyISAM в промышленном окружении?), то значение по умолчанию для key_buffer_size можно уменьшить. Если есть много оперативной памяти, то можно выделить 1% от её объёма или 256 мегабайт, а остальную память оставить для кэша операционной системы и буферного пула InnoDB.

Другие рекомендации для повышения производительности

slow_query_log

Конечно, эта переменная не поможет увеличить производительность сервера MySQL. Тем не менее, эта переменная может помочь в анализе медленно выполняющихся запросов. Если значение равно 0 или OFF, то журнал отключен. Для включения нужно задать значение 1 или ON. Значение по умолчанию зависит от того, была ли указана опция slow_query_log. Место назначения для вывода журналов управляется с помощью системной переменной log_output. Если её значение равно NONE, то запись в журнал не происходит, даже если журнал включен. Указать имя файла или место назначения для журнала запросов можно с помощью переменной slow_query_log_file.

long_query_time

Каждый раз, когда выполнение запроса занимает больше времени, чем указанное в этой переменной количество секунд, сервер увеличивает значение переменной состояния Slow_queries и записывает этот запрос в журнал, если включен журнал медленных запросов. Это значение соответствует не времени процессора, а реальному времени, поэтому время на низкозагруженных системах может быть меньше, чем на высоконагруженных. Минимальное значение и значение по умолчанию для long_query_time равны соответственно 0 и 10. Обратите внимание, что в журнал не попадут запросы, даже если они заняли много времени, если переменная min_examined_row_limit больше нуля, а количество просмотренных строк было меньше её значения.

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

sync_binlog

Эта переменная задаёт частоту записи binlog на диск. По умолчанию (для версий, начиная с 5.7.7) переменная имеет значение 1, при котором запись на диск происходит перед подтверждением транзакции. Однако, это оказывает негативное воздействие на производительность из-за более частых операций записи. Если нужно обеспечить строгое соответствие принципам ACID на подчинённых серверах, то это самое безопасное значение. Другой вариант - значение 0, которое отключает синхронизацию на диск. В таком случае запись на диск происходит через кэш операционной системы, который периодически записывается на диск. При указании значения больше 1 binlog записывается на диск после накопления N групп фиксаций, где N > 1.

Сохранение и восстановление буферного пула

Довольно востребованна функция для производственных баз данных - это разогрев после холодного запуска или перезапуска. Сохранив текущий буферный пул перед перезапуском, можно загрузить его содержимое после перезапуска. Таким образом можно избежать необходимости разрогрева кэша базы данных. Отметим, что эта функция появилась в MySQL версии 5.6, но, что удивительно, она также доступна в Percona Server 5.5. Для включения этой функции задайте значения для обеих переменных: innodb_buffer_pool_dump_at_shutdown = ON и innodb_buffer_pool_load_at_startup = ON.

Оборудование

Сейчас в 2019 году оборудование стало намного лучше. У самого MySQL нет жёстких требований к оборудованию, они зависят от требований базы данных. Вряд ли вы читаете эту заметку для того, чтобы проверить, работает ли MySQL на Intel Pentium с частотой 200 мегагерц.

Что касается процессоров, то для последних версий MySQL, начиная с версии 5.6, лучше всего подойдут быстрые многоядерные процессоры. Процессоры Intel Xeon/Itanium могут быть дороги, но компьютерные платформы на их основе проверены на масштабируемость и надёжность. Amazon использует архитектуру ARM на серверах EC2. Лично я не пробовал запускать MySQL на архитектуре ARM и не слышал о таких попытках, но существуют тесты производительности, сделанные несколько лет назад. Современные процессоры могут изменять свою частоту в соответствии с температурой, загрузкой и политикой экономии электроэнергии операционной системы. В операционной системе на основе Linux могут использоваться разные настройки регуляции процессора, которые можно узнать или установить следующим образом:

echo performance | sudo tee /sys/devices/system/cpu/cpu[0-9]*/cpufreq/scaling_governor

Что касается памяти, то важно, чтобы её объём был большим и мог уместить рабочее подмножество данных. Убедитесь, что настроили swappiness = 1. Для этого воспользуйтесь утилитой sysctl или файлом в procfs. Сделать это можно следующим образом:

$ sysctl -e vm.swappiness
vm.swappiness = 1

Задать значение 1 можно следующим образом:

$ sudo sysctl vm.swappiness=1
vm.swappiness = 1

Также будет очень разумным для управления памятью отключить THP (Transparrent Huge Pages - прозрачные огромные страницы). В прошлом я сталкивался с проблемами высокой нагрузки на процессор и думал, что они были вызваны дисковым вводом-выводом. Я отключил THP, т.к. проблемы была связана с потоком khugepaged, который динамически выделял память в процессе работы. Также памть может быстро переместиться в THP в процессе дефрагментации ядра. Обычно огромные страницы памяти распределяются при запуске и не меняются в процессе работы. Проверить и отключить их можно следующим образом:

$ cat /sys/kernel/mm/transparent_hugepage/enabled
$ echo "never" > /sys/kernel/mm/transparent_hugepage/enabled

Что касается дисков, то важно чтобы они имели хорошую пропускную способность. При наличии батарейного модуля для баз данных лучше всего использовать RAID10. С появлением флеш-дисков, предоставляющих высокую пропускную сопсобность и высокую скорость чтения/записи, важно, чтобы он мог управлять дисками под высокой нагрузкой и дисковым вводом-выводом.

Операционная система

Большинство промышленных систем с MySQL работают на Linux. Так происходит потому, что производительность MySQL была проверена и протестирована в Linux и, таким образом, фактически является стандартом для установки MySQL. Конечно, ничто не препятствует его использованию на платформах Unix или Windows. Но при возникновении каких-либо проблем будет проще получить помощь, если используемая платформа протестирована и широко распространена в сообществе. В большинстве случаев используются системы RHEL/Centos/Fedora и Debian/Ubuntu. В некоторых производственных средах, насколько мне известно, используется Amazon Linux, как в AWS Amazon.

Очень важно использовать файловую систему XFS или Ext4. Конечно, у обеих из них есть свои слабые и сильные стороны. Кто-то говорит, что XFS опережает Ext4, но есть и отчёты, в соответствии с которыми Ext4 опережает XFS. К этому списку можно добавить ZFS, которая составит им хорошую альтернативу. Отличные материалы по этой теме можно найти в презентации Джервина Риала (Jervin Real) из Percona есть, которую он сделал для конференции по ZFS.