Оптимизация MySQL на Ubuntu: innodb_buffer_pool_size и не только
Оптимизация MySQL начинается с одной настройки — innodb_buffer_pool_size: это память, в которой InnoDB держит таблицы и индексы. Из коробки там 128 МБ, как для маленькой машины. Дальше идут redo log, лимит соединений и журнал медленных запросов, который показывает, что исправить в самих запросах. Инструкция для MySQL 8.0 из пакета mysql-server в Ubuntu: Ubuntu 24.04 и 22.04 обе получают обновления 8.0, так что настройки для 8.0.30 и новее ниже подходят обеим. Каждое число — отправная точка, сверяйте его с SHOW GLOBAL STATUS.
Где лежат настройки MySQL и как их применить
В Ubuntu настройки сервера лежат в /etc/mysql/mysql.conf.d/mysqld.cnf, в разделе [mysqld]. Часть строк там — закомментированные примеры. Отредактируйте файл, проверьте его, перезапустите MySQL и посмотрите, какое значение сервер использует на самом деле:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
sudo mysqld --validate-config
sudo systemctl restart mysql
sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"Проверка настроек ничего не пишет, если всё в порядке, а иначе называет ошибочную строку. Если после перезапуска MySQL не поднялся, причина — в его журнале:
sudo tail -n 50 /var/log/mysql/error.logМногие настройки можно поменять и без перезапуска: SET GLOBAL (до следующего перезапуска) или SET PERSIST. SET PERSIST записывает значение в /var/lib/mysql/mysqld-auto.cnf, и оно сильнее mysqld.cnf, так что помните, где что задали.
Каким сделать innodb_buffer_pool_size
Чем больше данных и индексов помещается в buffer pool, тем реже MySQL читает с диска. В документации MySQL сказано, что на выделенном сервере базы ему часто отдают до 80% памяти. С innodb_dedicated_server MySQL сам берёт 75% на серверах больше 4 ГБ. С чего начать:
- Сервер только под MySQL — 70–75% памяти, например
12Gпри 16 ГБ. - MySQL рядом с сайтом (Nginx, PHP-FPM, Node.js) — то, что остаётся после приложения, часто 40–50% памяти, например
2Gна сервере с 4 ГБ. - Больше, чем нужно данным, не нужно: если вся база занимает 1 ГБ, хватит buffer pool на 1–2 ГБ.
Сколько весят ваши данные, покажет такой запрос:
sudo mysql -e "SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024) AS total_mb FROM information_schema.tables;"[mysqld]
innodb_buffer_pool_size = 2GMySQL округляет значение до числа, кратного 128 МБ, умноженным на число экземпляров buffer pool, так что итог может выйти чуть больше написанного. На работающем сервере его можно поменять и без перезапуска: sudo mysql -e "SET GLOBAL innodb_buffer_pool_size = 2147483648;" Через день обычной работы проверьте, как часто MySQL ходил на диск:
sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"Innodb_buffer_pool_reads — чтения с диска, Innodb_buffer_pool_read_requests — все чтения. Если первых больше примерно 1% от вторых, buffer pool, скорее всего, мал для ваших данных.
Размер redo log: innodb_redo_log_capacity
В redo log записываются изменения, прежде чем InnoDB перенесёт их в таблицы. С MySQL 8.0.30 его размер задаёт innodb_redo_log_capacity, по умолчанию 100 МБ. Он заменил innodb_log_file_size и innodb_log_files_in_group из старых инструкций. При большом числе записей маленький redo log заставляет InnoDB слишком часто сбрасывать изменения на диск. Большой сглаживает запись, но восстановление после сбоя идёт дольше. Для нагруженного сервера начните с 1–2 ГБ:
[mysqld]
innodb_redo_log_capacity = 2GЕго тоже можно поменять без перезапуска: sudo mysql -e "SET GLOBAL innodb_redo_log_capacity = 2147483648;" Текущий размер: sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_capacity_resized';" На то, чтобы увеличить или уменьшить файлы, MySQL нужно время.
max_connections и ошибка «Too many connections»
ERROR 1040 (HY000): Too many connections значит, что все соединения заняты. Предел — max_connections, по умолчанию 151. Одно соединение сверх него MySQL держит для учётной записи с CONNECTION_ADMIN, поэтому mysql, запущенный через sudo, обычно всё равно заходит. Сначала посмотрите на пик и текущее число:
sudo mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Max_used_connections', 'Threads_connected'); SHOW PROCESSLIST;"Часто дело не в пределе, а в приложении: соединения не закрываются или много процессов PHP-FPM, и каждый открывает своё. Нужный предел — примерно сумма всех, кто ходит в базу: pm.max_children всех пулов PHP-FPM, размеры пулов в Node.js, задачи cron, плюс запас. Каждое соединение занимает память под свои буферы, так что тысячи «на всякий случай» не ставьте.
[mysqld]
max_connections = 300Как включить журнал медленных запросов
Больше всего скорости дают не настройки, а исправленные медленные запросы, обычно нужным индексом. Журнал медленных запросов записывает каждый запрос дольше long_query_time секунд (по умолчанию 10). В mysqld.cnf Ubuntu эти строки уже есть, но закомментированы: уберите перед ними # и поставьте нужное время:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1sudo mysqld --validate-config
sudo systemctl restart mysql
sudo mysqldumpslow -s t /var/log/mysql/mysql-slow.log | head -n 40mysqldumpslow собирает одинаковые запросы вместе и сортирует их по общему времени, так что первые в списке обходятся дороже всего. Запустите каждый из них с EXPLAIN впереди и посмотрите, использует ли он индекс. Включить журнал без перезапуска, до следующего: sudo mysql -e "SET GLOBAL slow_query_log = 'ON';"
Временные таблицы и query cache, которого больше нет
Для GROUP BY, DISTINCT и части сортировок MySQL строит внутреннюю временную таблицу. В MySQL 8.0 она живёт в памяти, пока не больше tmp_table_size (по умолчанию 16 МБ), а крупные уходят на диск. Сравните счётчики:
sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"Если Created_tmp_disk_tables — заметная доля от Created_tmp_tables, поднимите tmp_table_size и max_heap_table_size вместе, например до 64M. Предел действует на каждую таблицу, а строить их могут несколько запросов сразу, так что поднимайте постепенно.
[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64MQuery cache убрали в MySQL 8.0. Старые инструкции советуют query_cache_size и query_cache_type. В 8.0 эти строки неизвестны, MySQL не запустится, а в error.log будет unknown variable. Уберите их. Кэшируйте результаты в приложении, например в Redis: как установить Redis на Ubuntu.
Подсказки mysqltuner
mysqltuner — скрипт из репозитория Ubuntu, который читает счётчики сервера и предлагает изменения. Запускайте его, когда сервер проработал под обычной нагрузкой хотя бы сутки, иначе счётчики мало что говорят:
sudo apt install -y mysqltuner
sudo mysqltunerЕго советы — подсказки, а не приказы. Сверяйте каждый с этой статьёй и документацией MySQL, меняйте по одной настройке и сравнивайте SHOW GLOBAL STATUS до и после.
Если вы работаете и на Mac
Сборка мусора в MacWashing находит кэш всех знакомых ей программ, в том числе спрятанный внутри их данных, а ещё журналы, мусор Xcode и остатки удалённых программ. Она показывает, сколько места занимает каждая находка, и очищает всё за один раз. Всё спорное остаётся без галочки, пока вы не решите.
Бесплатная пробная версия · macOS 14 и новее
Вопросы и ответы
Каким должен быть innodb_buffer_pool_size?
На сервере только под MySQL — около 70–75% памяти. Если сайт работает на том же сервере — то, что остаётся после приложения, часто 40–50%. Заметно больше объёма данных и индексов он не нужен.
Где my.cnf в Ubuntu?
/etc/mysql/my.cnf только подключает другие файлы. Настройки сервера — в /etc/mysql/mysql.conf.d/mysqld.cnf, в разделе [mysqld].
Какие настройки требуют перезапуска?
innodb_buffer_pool_size, innodb_redo_log_capacity, max_connections, long_query_time и многие другие можно менять командой SET GLOBAL на работающем сервере. Значения из mysqld.cnf применяются только после перезапуска: sudo systemctl restart mysql