Лимиты соединений и пулы в MySQL: Too many connections

ERROR 1040 (HY000): Too many connections почти никогда не означает, что базе данных нужен больший max_connections. Это значит, что что-то открыло больше соединений, чем требовала текущая работа, и обычно это что-то - пул с настройками по умолчанию, на которые никто не смотрел, умноженный на каждый процесс и каждый воркер, который такой пул держит. MySQL 8.4 по умолчанию допускает 151 клиентское соединение. Небольшому приложению под реальной нагрузкой редко нужно больше 20 одновременно. Исправление почти всегда на стороне клиента: меньше соединений, общих и переиспользуемых, - и серверный лимит, выставленный по тому, что реально выдержит память.

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

Как MySQL считает соединения#

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

Настройки, которые определяют, сколько их может быть:

Две детали из этой таблицы объясняют большинство сюрпризов. Во-первых, MySQL держит одно дополнительное соединение сверх max_connections для аккаунта с привилегией CONNECTION_ADMIN (или устаревшей SUPER). Поэтому обычно можно войти под root и разобраться с сервером, который отказывает вашему приложению, - если, конечно, root не входит в число аккаунтов, которые его заваливают. Пользуйтесь этим: у пользователя приложения такой привилегии быть не должно никогда.

Во-вторых, wait_timeout равен восьми часам. Приложение, которое открывает соединения и забывает о них, не получит уборки за собой в течение рабочего дня. На сервере с лимитом 151 именно так скрипт с утечкой, запускаемый из cron каждую минуту, заполняет таблицу к середине утра.

Начиная с MySQL 8.0.14 есть ещё отдельный административный интерфейс (admin_address и admin_port, порт по умолчанию 33062), который принимает соединения привилегированных аккаунтов независимо от max_connections. Он выключен, пока не задан admin_address, а на хостинговом тарифе этот порт обычно недоступен, так что считайте своим аварийным выходом зарезервированное дополнительное соединение.

Во что обходится соединение#

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

Сессионные буферы, которые имеют значение:

Простаивающее соединение стоит мегабайт-два. Соединение, выполняющее плохо проиндексированный отчёт с сортировкой, двумя join без индексов и временной таблицей, может ненадолго обойтись в десятки мегабайт. Умножьте худший случай на max_connections - и получите число, которое нужно сравнивать с RAM, оставшейся после innodb_buffer_pool_size.

Вот настоящее правило подбора. На сервере с 1 GB буферный пул занимает примерно половину, накладные расходы самого сервера - несколько сотен мегабайт, а остатка хватает примерно на 50-100 активных соединений с обычной работой. На 4 GB есть место для нескольких сотен, а на 8 GB лимитом скорее станет CPU, а не память: 300 соединений, одновременно выполняющих запросы на четырёх ядрах, просто стоят в очереди к ядрам. Сторону буферного пула в этой сумме разбирает статья настройка InnoDB в MySQL для небольших серверов.

Как узнать, кто держит соединения#

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

SHOW GLOBAL STATUS WHERE Variable_name IN  ('Threads_connected', 'Threads_running', 'Max_used_connections',   'Max_used_connections_time', 'Aborted_connects', 'Aborted_clients',   'Connection_errors_max_connections');

Что говорит каждый показатель:

• Threads_connected - сколько соединений существует сейчас. Threads_running - сколько из них реально выполняют запрос. У здорового приложения между ними большой разрыв: 40 подключено, 2 выполняются.
• Max_used_connections - пиковое значение с последнего перезапуска, а Max_used_connections_time говорит, когда оно было. Если пик равен 151 и время совпадает со сбоем, инцидент найден.
• Connection_errors_max_connections считает отказы из-за лимита.
• Aborted_clients растёт, когда клиенты исчезают, не закрыв соединение корректно, - обычно это процессы, убитые посреди запроса, или соединения, закрытые по wait_timeout.

Затем разбейте соединения по тому, кто и откуда:

SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client, db, command,       COUNT(*) AS conns, MAX(time) AS longest_sFROM information_schema.processlistGROUP BY user, client, db, commandORDER BY conns DESC;

Сотня строк с command = 'Sleep' с одного клиентского адреса - это слишком большой пул или процесс с утечкой. Дюжина строк с command = 'Query' и большим time - это медленные запросы, которые держат соединения, и это другая проблема: пул заполняется, потому что каждое соединение застряло за блокировкой или полным сканированием таблицы. Инструмент для этого случая - журнал медленных запросов MySQL.

sys.session даёт более подробную картину, если нужны текущий запрос, память и блокировки для каждой сессии, а performance_schema.threads содержит те же данные на более низком уровне. В экстренной ситуации KILL <id> завершает соединение, а KILL QUERY <id> останавливает только выполняющийся в нём запрос.

Как изменить лимиты сервера#

С паролем root лимиты можно менять на лету. SET GLOBAL действует до следующего перезапуска; SET PERSIST дополнительно записывает значение в mysqld-auto.cnf в каталоге данных, поэтому оно переживает перезапуски.

-- Look firstSELECT @@max_connections, @@wait_timeout, @@max_user_connections;-- A realistic limit for a 2 GB server, kept across restartsSET PERSIST max_connections = 200;-- Close idle connections after ten minutes instead of eight hoursSET PERSIST wait_timeout = 600;-- Cap one application account so it cannot starve the othersALTER USER 'app'@'%' WITH MAX_USER_CONNECTIONS 120;

Более короткий wait_timeout - самый полезный из трёх на сервере, который делят несколько приложений, потому что он подбирает соединения, брошенные упавшими или небрежными клиентами. У него есть одно условие: ваши пулы должны обновлять соединения раньше, чем их убьёт сервер, иначе приложение время от времени берёт мёртвое соединение, и первый запрос на нём падает с MySQL server has gone away (ошибка 2006) или Lost connection to MySQL server during query (ошибка 2013). В каждом рантайме ниже для этого есть настройка, обычно она называется максимальным временем жизни или временем переиспользования. Ставьте её на минуту или больше ниже wait_timeout.

Лимит на аккаунт - второй недооценённый инструмент. Если отчётная задача, скрипт из cron и веб-приложение делят один сервер баз данных, дайте каждому собственного пользователя с MAX_USER_CONNECTIONS. Когда задача cron начнёт чудить, она упрётся в собственный потолок (ошибка 1203, User already has more than 'max_user_connections' active connections), а сайт продолжит работать. Как создать такие отдельные аккаунты, описано в статье пользователи и привилегии в MySQL.

Арифметика пулов#

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

Ловушка в том, что у каждого процесса свой пул. Общее число соединений, которое может открыть ваше приложение:

total = pool size  x  processes per instance  x  instances (+ cron, workers, shells)

Приложение на Node.js с умолчанием mysql2 в 10 соединений, запущенное в режиме cluster с 4 воркерами и развёрнутое на 3 серверах, может открыть 120 соединений ещё до того, как кто-то запустит миграцию. Добавьте воркер очереди с собственным пулом на 10 и staging-окружение, смотрящее в ту же базу данных, - и вы на 151.

Полезное число - не максимум, а та конкурентность, которая вам действительно нужна. Пулу нужно столько соединений, сколько запросов выполняется в один и тот же момент. Если каждый запрос проводит в базе данных 5 мс, а экземпляр обслуживает 200 запросов в секунду, это одна секунда времени базы данных в секунду - примерно одно занятое соединение. Десять - с запасом. Широко цитируемая отправная точка от проекта HikariCP - connections = (cores x 2) + effective spindle count для сервера баз данных, и для базы на 2 vCPU это даёт сумму по всем клиентам от единиц до пары десятков - меньше, чем ожидает большинство, и быстрее, потому что база данных с меньшим числом одновременных запросов тратит меньше времени на переключение между ними.

Запишите эту сумму где-нибудь рядом с max_connections и оставьте 20% запаса на миграции, консоль и периодический бэкап.

Настройки пула в каждом рантайме#

Умолчания каждого рантайма и что в них поменять.

PHP

У PHP-FPM нет общего пула. Каждый воркер FPM - отдельный процесс, который открывает соединение на запрос и закрывает его в конце, поэтому число соединений ограничено pm.max_children. Пул с pm.max_children = 50 может открыть 50 соединений. Обычно это нормально: по короткому сетевому пути соединения открываются дёшево. PDO::ATTR_PERSISTENT => true держит по одному соединению на воркер открытым между запросами - это экономит рукопожатие, но оставляет состояние сессии (временные таблицы, переменные SET, открытую транзакцию после фатальной ошибки) следующему запросу и превращает простаивающие воркеры в простаивающие соединения. Используйте это, только если вы измерили стоимость рукопожатия. Параметры соединения подробно разобраны в статье PHP PDO и MySQL.

Node.js

import mysql from "mysql2/promise";export const pool = mysql.createPool({  host: process.env.DB_HOST,  port: Number(process.env.DB_PORT),  user: process.env.DB_USER,  password: process.env.DB_PASSWORD,  database: process.env.DB_NAME,  connectionLimit: 10,     // default 10  waitForConnections: true, // queue instead of failing  queueLimit: 0,           // 0 = unbounded queue  maxIdle: 5,              // idle connections kept open  idleTimeout: 60000,      // ms before an idle connection is closed  enableKeepAlive: true,});

Создавайте пул один раз на процесс, на уровне модуля. Классическая утечка - вызов createPool внутри обработчика запроса, который на каждый запрос создаёт новый пул на десять соединений. В serverless-развёртываниях каждый экземпляр функции получает свой пул, поэтому там ставьте connectionLimit низким (1-2).

Python

Пул держит движок SQLAlchemy: по умолчанию pool_size=5 и max_overflow=10, то есть до 15 на процесс. Добавьте pool_recycle ниже wait_timeout и pool_pre_ping=True, который проверяет соединение, прежде чем его выдать.

from sqlalchemy import create_engineengine = create_engine(    "mysql+pymysql://app:secret@db.example.com:3306/app?charset=utf8mb4",    pool_size=5, max_overflow=5, pool_recycle=540, pool_pre_ping=True,)

Gunicorn с 4 воркерами и таким движком может открыть 40. Django вообще не использует пул: CONN_MAX_AGE по умолчанию равен 0, то есть новое соединение на каждый запрос; задайте значение ниже wait_timeout (и CONN_HEALTH_CHECKS = True), чтобы переиспользовать соединения в каждом рабочем потоке.

Java и .NET

У HikariCP по умолчанию maximumPoolSize = 10 и maxLifetime = 1800000 мс (30 минут) - это нормально при стандартном восьмичасовом wait_timeout, но значение нужно понизить, если вы его сокращаете. MySqlConnector для .NET по умолчанию использует пул с Maximum Pool Size=100, а это много; для небольшого сервера впишите в строку подключения Maximum Pool Size=20;Connection Lifetime=540. database/sql в Go не ограничивает число открытых соединений, пока вы не вызовете SetMaxOpenConns, и по умолчанию держит два простаивающих; всегда задавайте SetMaxOpenConns, SetMaxIdleConns и SetConnMaxLifetime.

Те же идеи со стороны PostgreSQL, где соединения - это процессы и арифметика ещё строже, разбирает статья пулы соединений и лимиты.

Разбор ошибок, которые вы действительно увидите#

`ERROR 1040: Too many connections`. Достигнут глобальный лимит. Войдите под root, выполните запрос к processlist выше и найдите клиента, который держит большую часть соединений. Убейте спящие, если приложение нужно вернуть немедленно, а затем исправьте размер пула или утечку. Поднимайте max_connections, только если сумма законных пулов действительно его превышает и память это позволяет.

`ERROR 1203: User already has more than 'max_user_connections' active connections`. Лимит на аккаунт. Либо лимит слишком низок для пулов, использующих этот аккаунт, либо какой-то процесс течёт.

`MySQL server has gone away` (2006) или `Lost connection to MySQL server during query` (2013) после периодов простоя. Сервер закрыл простаивающее соединение по wait_timeout, а пул всё равно его выдал. Поставьте максимальное время жизни в пуле ниже wait_timeout или включите pre-ping. Если это происходит во время большой вставки, другая классическая причина - пакет больше max_allowed_packet (в 8.4 по умолчанию 64 MB).

`Host 'x' is blocked because of many connection errors`. После max_connect_errors (по умолчанию 100) неудачных рукопожатий подряд с одного хоста MySQL его блокирует. Обычно виновата проверка доступности, которая открывает TCP-соединение и закрывает его, не залогинившись. FLUSH HOSTS удалили в пользу TRUNCATE TABLE performance_schema.host_cache, который снимает блокировку; после этого исправьте проверку.

Пул не дожидается соединения по таймауту, а MySQL показывает мало соединений. Пул слишком мал для такой конкурентности, или соединения удерживаются во время медленной работы - например, HTTP-вызова при открытой транзакции. Освобождайте соединения, прежде чем делать что-то, что не является запросом.

Соединения медленно растут весь день. Утечка: код, который берёт соединение и на ветке обработки ошибки выходит раньше, не освободив его. В Node используйте pool.query() вместо getConnection() везде, где можно, потому что он освобождает соединение автоматически.

FAQ#

Каким должен быть max_connections?

Достаточно высоким для суммы ваших пулов плюс 20% запаса и достаточно низким, чтобы все соединения, одновременно выполняющие умеренно тяжёлый запрос, всё равно поместились в память. Для большинства приложений на сервере MySQL с 1-4 GB это где-то между 100 и 300. Значение по умолчанию 151 разумно для одного приложения.

Пул соединений всегда быстрее?

Для всего, что выполняет больше нескольких запросов в секунду, - да: он убирает из каждого запроса несколько сетевых обменов туда и обратно. Скрипту cron, который запускается раз в час, пул ничего не даёт; откройте одно соединение, сделайте работу, закройте его.

Нужен ли мне ProxySQL или другой прокси соединений?

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

Почему в моей базе данных сотни спящих соединений?

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

Влияет ли wait_timeout на долгие запросы?

Нет. Он относится только к простаивающим соединениям. Запрос, который выполняется двадцать минут, не простаивает. Долгие запросы ограничиваются max_execution_time для операторов SELECT (в миллисекундах, по умолчанию 0, то есть без лимита) и собственным таймаутом чтения клиента.