Настройка MySQL InnoDB для серверов на 1-8 ГБ

На сервере MySQL с 1-8 ГБ памяти почти всё решают три настройки: innodb_buffer_pool_size, max_connections и innodb_redo_log_capacity. В MySQL 8.4 есть ещё две, о которые спотыкаются маленькие серверы и которых никогда не замечают большие: temptable_max_ram, чьё новое значение по умолчанию никогда не бывает меньше 1 ГБ, и двоичный журнал, который включён по умолчанию и хранит историю за 30 дней. Задайте buffer pool примерно в половину памяти на сервере с 1-2 ГБ и в 60-70% на серверах побольше, держите число соединений в десятках, а не в сотнях, задайте redo log в сотни мегабайт, ограничьте временные таблицы в памяти и сократите срок хранения двоичного журнала. Всё остальное либо нормально по умолчанию, либо стоит меньше, чем один отсутствующий индекс.

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

Что предполагают значения по умолчанию#

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

Значение buffer pool по умолчанию в 128 МБ - самый наглядный пример значения, которое никому не стоит оставлять. На сервере с 4 ГБ оно отдаёт 90% памяти кэшу операционной системы, который InnoDB всё равно обходит, потому что 8.4 по умолчанию использует innodb_flush_method=O_DIRECT в Linux. Данные, не поместившиеся в buffer pool, читаются с диска, а память, за которую вы платите, простаивает.

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

Бюджет памяти#

Представьте память MySQL из трёх частей: то, что выделяется один раз (buffer pool, буфер журнала, performance schema), то, что каждое соединение может занять, пока выполняет запрос, и то, что нужно операционной системе. Худший случай выглядит примерно так:

buffer pool+ log buffer (64 MB in 8.4)+ performance_schema (often 100-250 MB)+ max_connections x (per-thread buffers + thread stack)+ in-memory temporary tables+ a margin for the OS and everything else

Буферы на соединение по умолчанию небольшие - sort_buffer_size 256 КБ, join_buffer_size 256 КБ, read_buffer_size 128 КБ, read_rnd_buffer_size 256 КБ, thread_stack 1 МБ - и выделяются, только когда они нужны запросу. Поэтому простаивающее соединение дёшево, а занятое - несколько мегабайт. Опасность - в их глобальном повышении: sort_buffer_size = 64M выглядит безобидно, пока пятьдесят соединений не начнут сортировать одновременно. Повышайте их на уровне сессии для того запроса, которому это нужно:

SET SESSION sort_buffer_size = 16 * 1024 * 1024;SELECT ... ORDER BY ...;   -- the one report that sorts a lot

Чтобы увидеть, куда на самом деле уходит память на работающем сервере, схема sys сводит воедино инструменты памяти performance schema:

SELECT event_name, current_allocFROM sys.memory_global_by_current_bytesLIMIT 10;SELECT * FROM sys.memory_global_total;

innodb_buffer_pool_size#

Buffer pool кэширует страницы таблиц и индексов. Попадание - это чтение из памяти, промах - чтение с диска. На выделенном сервере баз данных это самый крупный потребитель памяти, а привычный совет «70-80% ОЗУ» предполагает большую машину, где оставшиеся 20% - это несколько гигабайт. На маленькой фиксированные расходы, описанные выше, съедают большую долю, поэтому процент снижается.

Это отправные точки, а не истина. Базе данных меньше buffer pool не нужен пул побольше - проверьте, сколько данных у вас на самом деле:

SELECT table_schema,       ROUND(SUM(data_length + index_length) / 1024 / 1024) AS size_mbFROM information_schema.tablesGROUP BY table_schemaORDER BY size_mb DESC;

Если вся база данных занимает 300 МБ, buffer pool в 1 ГБ вмещает её целиком, а остальное пропадает зря. Если это 20 ГБ на сервере с 4 ГБ, важно, помещается ли рабочий набор - строки, которые затрагиваются за обычный час. Это покажет доля промахов:

SELECT  (SELECT variable_value FROM performance_schema.global_status   WHERE variable_name = 'Innodb_buffer_pool_reads') AS disk_reads,  (SELECT variable_value FROM performance_schema.global_status   WHERE variable_name = 'Innodb_buffer_pool_read_requests') AS logical_reads;

disk_reads, делённое на logical_reads, на прогретом сервере должно быть намного меньше 1%. Снимите значения дважды с интервалом в час и сравнивайте разницу, а не итоги с момента запуска. Устойчиво высокое соотношение на загруженной системе - честный сигнал, что рабочий набор не помещается: либо не хватает индекса, либо тариф слишком мал.

В 8.x размер buffer pool можно менять на лету. Размер округляется вверх до кратного innodb_buffer_pool_chunk_size (128 МБ), умноженного на innodb_buffer_pool_instances, который в 8.4 равен 1 для пулов размером 1 ГБ и меньше, а для больших вычисляется из размера и числа CPU:

SET PERSIST innodb_buffer_pool_size = 2560 * 1024 * 1024;SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

Изменение размера занимает некоторое время и ненадолго блокирует доступ к перемещаемым страницам; делайте это в спокойное время.

innodb_dedicated_server=ON велит MySQL самому подобрать размер buffer pool (50% памяти от 1 до 4 ГБ, 75% выше) и redo log. Размер подбирается по обнаруженной памяти, а документация не обещает, что обнаружение учитывает лимит контейнера, поэтому в контейнере задавайте значения явно.

innodb_redo_log_capacity#

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

Начиная с MySQL 8.0.30 размер redo log задаётся одной переменной, innodb_redo_log_capacity, и она динамическая. Старая пара innodb_log_file_size и innodb_log_files_in_group объявлена устаревшей; если они заданы, а innodb_redo_log_capacity нет, сервер выводит ёмкость из них, но в новой конфигурации стоит использовать одну настройку. Файлы redo лежат в #innodb_redo внутри каталога данных.

SET PERSIST innodb_redo_log_capacity = 512 * 1024 * 1024;

Значение по умолчанию в 100 МБ слишком мало для всего, кроме лёгкой нагрузки на запись. Полезный способ подобрать размер - измерить, сколько redo пишет ваш пиковый час:

SELECT variable_value INTO @a FROM performance_schema.global_statusWHERE variable_name = 'Innodb_redo_log_current_lsn';DO SLEEP(60);SELECT ROUND((variable_value - @a) / 1024 / 1024, 1) AS redo_mb_per_minuteFROM performance_schema.global_statusWHERE variable_name = 'Innodb_redo_log_current_lsn';

Запустите это в загруженное время. Ёмкость, вмещающая 30-60 минут пиковой записи, - это с запасом. На маленьком диске ограничьте её: 2 ГБ redo на томе в 10 ГБ - это пятая часть вашего места. Таблица выше в этих рамках остаётся.

Соединения, потоки и таймауты#

Каждое соединение - это поток со своим стеком и буферами. Сервер с одним-двумя vCPU не может выполнять одновременно больше горстки запросов; соединения сверх этого ждут и при этом держат память. max_connections = 151 на сервере с 1 ГБ - обещание, которое память не сможет выполнить, если все соединения разом станут занятыми.

• Сначала задайте размер пулов приложения. Десять соединений на процесс приложения покрывают большинство веб-нагрузок.
• Сложите все процессы, воркеры и cron-задания, которые подключаются. Эта сумма плюс запас - и есть max_connections.
• MySQL резервирует одно дополнительное соединение сверх лимита для учётной записи с CONNECTION_ADMIN, так что root всё равно сможет войти и разобраться в инциденте «Too many connections».
• thread_cache_size (по умолчанию размер подбирается автоматически) сохраняет потоки для повторного использования; не трогайте его.

SET PERSIST max_connections = 60;SHOW GLOBAL STATUS LIKE 'Max_used_connections';SHOW GLOBAL STATUS LIKE 'Threads_connected';

Max_used_connections - максимум с момента запуска, и он показывает, приближаетесь ли вы вообще к лимиту. wait_timeout (28800 секунд) закрывает простаивающие соединения через восемь часов; снижение примерно до 600 возвращает соединения, утёкшие из приложений, которые открывают их и бросают, но убедитесь, что ваш пул пересоздаёт соединения быстрее. Полное обоснование небольших лимитов и пулов - в статье лимиты соединений MySQL и пулы.

Временные таблицы и значение temptable по умолчанию в 8.4#

Запросы с GROUP BY, DISTINCT, UNION, производными таблицами или некоторыми сортировками строят внутренние временные таблицы. MySQL 8 держит их в памяти с движком TempTable в пределах temptable_max_ram суммарно, а сверх этого сбрасывает на диск во временные таблицы InnoDB.

В 8.4 значением temptable_max_ram по умолчанию стали 3% общей памяти, но никогда не меньше 1 ГБ и никогда не больше 4 ГБ. На сервере с 1 или 2 ГБ этот нижний порог означает, что временным таблицам разрешено занимать столько же памяти, сколько весь тариф, или половину. Тогда один плохой отчётный запрос может вытолкнуть сервер за лимит. Ограничьте значение тем, что ваш бюджет способен выдержать:

SET PERSIST temptable_max_ram = 64 * 1024 * 1024;   -- 1 GB serverSET PERSIST tmp_table_size = 32 * 1024 * 1024;

Сброс на диск медленнее, но переживаем; нехватка памяти - ни то ни другое. Запросы, строящие большие временные таблицы, помечены в EXPLAIN как Using temporary, и обычно это те же запросы, которые всплывают в журнале медленных запросов. SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/temptable%' показывает, сколько движок держит прямо сейчас.

Надёжность, сброс и двоичный журнал#

innodb_flush_log_at_trx_commit = 1sync_binlog = 1innodb_flush_method = O_DIRECTinnodb_io_capacity = 10000

innodb_flush_log_at_trx_commit = 1 сбрасывает redo log на диск при каждом коммите, так что подтверждённая транзакция переживает сбой. Значение 2 пишет при коммите, но сбрасывает раз в секунду: это реальное ускорение для нагрузок с интенсивной записью, и при этом падение машины (а не только MySQL) может потерять примерно секунду коммитов. Повредить базу данных это не может. Поэтому так разумно делать для журналирования и аналитики и неправильно - для заказов и платежей. sync_binlog - тот же компромисс для двоичного журнала.

innodb_io_capacity сообщает InnoDB, с какой скоростью можно сбрасывать данные в фоне. Значение 200 по умолчанию в 8.0 рассчитано на вращающиеся диски и слишком мало для NVMe; если у вас 8.0, поднимите его примерно до 2000. Значение 10000 в 8.4 предполагает быструю флеш-память, а NVMe именно она и есть.

Двоичный журнал на маленьком диске заслуживает отдельного разговора. Он включён по умолчанию начиная с 8.0, записывает каждое изменение для репликации и восстановления на момент времени и хранит 30 дней. На базе с интенсивной записью и томом в 10 или 20 ГБ двоичные журналы могут вырасти больше самих данных. Если вы не используете репликацию и восстановление на момент времени, сократите срок хранения:

SET PERSIST binlog_expire_logs_seconds = 259200;   -- 3 daysSHOW BINARY LOGS;PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

Полное отключение двоичного журналирования (skip-log-bin или disable-log-bin) требует опции запуска в файле конфигурации, а не SET PERSIST. expire_logs_days, старую настройку в днях, удалили в 8.4.

Применение настроек: SET PERSIST и откуда берутся значения#

В MySQL 8 для настройки сервера редко нужно править my.cnf. SET PERSIST меняет динамическую переменную сразу и записывает её в mysqld-auto.cnf в каталоге данных, который читается при запуске после обычных файлов опций, так что изменение переживает перезапуск. Для этого нужна SYSTEM_VARIABLES_ADMIN, которая есть у учётной записи root.

-- Where does each value come from?SELECT v.variable_name, v.variable_source, v.set_time, g.variable_valueFROM performance_schema.variables_info vJOIN performance_schema.global_variables g USING (variable_name)WHERE v.variable_name IN ('innodb_buffer_pool_size', 'max_connections',                          'innodb_redo_log_capacity', 'temptable_max_ram');-- Everything persisted so farSELECT * FROM performance_schema.persisted_variables;-- Undo a persisted setting (takes effect at next restart)RESET PERSIST temptable_max_ram;

variable_source принимает значения COMPILED (встроенное значение по умолчанию), GLOBAL или SERVER (файл опций), PERSISTED или DYNAMIC (задано во время работы). Переменные, которые нельзя менять на лету, например performance_schema или innodb_buffer_pool_chunk_size, принимают SET PERSIST_ONLY, который записывает значение для следующего запуска, не применяя его; для этого нужна дополнительная привилегия PERSIST_RO_VARIABLES_ADMIN.

На RE:NODE вы получаете пароль root от своего сервера MySQL, так что SET PERSIST доступен для всего, что описано выше. Меняйте по одной настройке за раз и делайте бэкап перед любым перезапуском - восстановление делается кнопкой, а слоты бэкапов есть на каждом тарифе MySQL.

Сначала измерить, потом настраивать#

Конфигурация возвращает проценты. Отсутствующий индекс стоит порядков. Прежде чем менять что-либо из этой статьи, включите журнал медленных запросов на сутки и посмотрите, что он поймает, а план худшего запроса прочитайте через EXPLAIN и EXPLAIN ANALYZE. Следите за графиком памяти, а не за средними значениями - статья чтение графика нагрузки сервера объясняет, почему убивает именно пик. А если измерения говорят, что рабочий набор просто не помещается, честный ответ - тариф побольше: когда переходить на другой тариф. Пользователи PostgreSQL найдут те же рассуждения применительно к своей СУБД в статье настройка PostgreSQL для небольших серверов.

FAQ#

Каким должен быть innodb_buffer_pool_size на сервере с 2 ГБ?

Около 1 ГБ, чтобы осталось место для соединений, временных таблиц, performance schema и операционной системы. Если ваших данных меньше, подгоните buffer pool под объём данных с запасом на рост и оставьте память свободной.

Нужен ли ещё innodb_log_file_size?

Нет. Начиная с MySQL 8.0.30 размер redo log задаётся через innodb_redo_log_capacity, который можно менять во время работы. innodb_log_file_size и innodb_log_files_in_group объявлены устаревшими и используются, только если новая переменная не задана.

Стоит ли отключить performance_schema, чтобы сэкономить память?

На сервере с 1 ГБ это может сэкономить ощутимый кусок, и это настройка запуска, так что нужен перезапуск. Цена - потеря представлений схемы sys, дайджестов операторов и учёта памяти, благодаря которым всё остальное в этой статье вообще можно измерить. Оставьте её включённой, если только память - не единственное, что отделяет вас от тарифа поменьше.

Почему MySQL использует больше памяти, чем buffer pool?

Потому что buffer pool - лишь самая крупная часть. К нему добавляются соединения, временные таблицы, буфер журнала, performance schema, кэш таблиц и собственный код сервера. sys.memory_global_total показывает итог по инструментам, а график памяти контейнера - реальный.

Хорошая ли идея innodb_dedicated_server на небольшом тарифе?

Не внутри контейнера. Он подбирает размер buffer pool по обнаруженной памяти, а это может быть не тот лимит, который накладывает контейнер. Задавайте buffer pool и ёмкость redo log явно.