Ubuntu 24.04: MySQL “too many connections” — виправити без уповільнення БД

Що насправді означає “too many connections” (і чого це не означає)

MySQL викидає “too many connections”, коли не може прийняти нове клієнтське з’єднання через досягнення внутрішнього обмеження. Найчастіше це стеля max_connections. Іноді це інша стіна: ліміти файлових дескрипторів ОС, обмеження systemd або виснаження ресурсів, через яке MySQL фактично не може створювати додаткові сесії.

Ось що люди пропускають: “too many connections” рідко викликано просто «занадто великою кількістю трафіку» у спрощеному сенсі. Зазвичай це спричинено тим, що з’єднання не звільняються достатньо швидко. Це можуть бути повільні запити, заблоковані транзакції, перевантажений CPU, насичене I/O, неправильно розмірений buffer pool, що викликає постійні читання з диска, або код додатка, що «тече» з’єднаннями. База даних каже вам, що вона тоне в паралельності. Ваше завдання — з’ясувати, чи вода йде з надто багатьох кранів, чи злив забитий.

Одна думка, яка заощадить вам час: підвищувати max_connections виправдано лише коли у вас є докази, що сервер має запас ресурсів (пам’ять, CPU, I/O) і додаток поводиться відповідально із з’єднаннями (пулінг, таймаути, адекватний обсяг транзакцій). Якщо піднімете його всліпу, часто перетворите гостру помилку на повільне страждання.

Жарт #1 (короткий, по темі): Витік з’єднань MySQL схожий на краплю з кухонного крану — ніхто не переймається, поки рахунок за воду не почне будити вас.

Швидкий план діагностики: перші/другі/треті перевірки

Це послідовність «я на чергуванні, кава охолола, дай сигнал». Робіть у цьому порядку. Кожен крок швидко звужує клас вузького місця.

Перший: витік з’єднань чи вузьке місце пропускної здатності?

• Перевірте поточні потоки і стани. Якщо більшість в Sleep, ймовірно проблеми з пулінгом/витоками на боці додатка або занадто довгі таймаути в стані простою. Якщо більшість в Query або Locked, це проблема пропускної здатності/блокувань.
• Перевірте, чи зростають підключення. Стійке зростання означає витік. Раптовий сплеск — піковий трафік або шторм повторних спроб.

Другий: що блокує прогрес — CPU, I/O, блокування чи пам’ять?

• CPU завантажений + багато виконуваних запитів: надто багато паралелізму, погані плани запитів або відсутні індекси.
• Диск насичений + велика латентність читання: buffer pool занадто малий, випадкові I/O через погані індекси, або повільне сховище.
• Домінують блокування/очікування: довгі транзакції, «гарячі» рядки або дизайн схеми, що викликає конфлікти.
• Тиск пам’яті/своп: MySQL дозволив занадто багато буферів на з’єднання, забагато з’єднань або ОС агресивно звільняє пам’ять.

Третій: ви досягли ліміту, який не max_connections?

• Файлові дескриптори занизькі: не можна відкрити більше сокетів або таблиць.
• systemd-ліміти: параметр LimitNOFILE сервісу може перевизначати налаштування, які ви вважали застосованими.
• Мережевий беклог: переповнення SYN-черги під сплесками (менш поширено, але виражається як таймаути підключення, а не 1040).

Після цього плану ви повинні вміти сказати одною фразою: «З’єднання накопичуються тому, що…» Якщо не можете — ви гадаєте. А гадання — це шлях, яким «швидкі фікси» перетворюються на архітектуру.

Цікаві факти та контекст (чому це повторюється)

• Факт 1: Історично MySQL використовував модель «один потік на з’єднання» за замовчуванням, що робило max_connections прямим показником кількості потоків і тиску на пам’ять.
• Факт 2: Багато буферів на з’єднання (sort_buffer_size, join_buffer_size, read_buffer_size) виділяються на сесію і можуть вибухнути пам’ять при стрибку паралельності.
• Факт 3: За замовчуванням wait_timeout традиційно досить довгий, щоб сесії в стані простою могли висіти годинами у погано налаштованих пулах.
• Факт 4: «Ефект гуркоту» — багато клієнтів, що одночасно повторно підключаються, перетворює невеликий сплеск у шторм з’єднань. Дуже часто з’являється через агресивні HTTP retry-методики.
• Факт 5: InnoDB buffer pool з’явився, щоб зменшити дискове I/O через кешування сторінок; занадто малий buffer pool часто виглядає як «too many connections», бо запити зависають, а сесії накопичуються.
• Факт 6: thread_cache_size важить більше, ніж здається: створення/знищення потоків при стрибкоподібному навантаженні додає латентності і навантаження на CPU.
• Факт 7: Ліміти Linux (ulimits, файлові дескриптори) такі ж древні, як і багатокористувацький Unix; вони досі кусають сучасні БД, бо значення за замовчуванням консервативні.
• Факт 8: На дистрибутивах на основі systemd (включно з Ubuntu 24.04) обмеження на рівні сервісу можуть мовчки перевизначати shell-уліміти, що плутає навіть досвідчених операторів.
• Факт 9: «Збільшити max_connections» — відомий антипатерн, бо це може збільшити конкуренцію за блокування і зменшити рівень кеш-хітів, через що кожен запит стає повільнішим.

Одна перефразована думка, яку варто мати на стіні, приписують John Ousterhout: скоротити складність — це податок, який ви платите пізніше; найкращі системи тримають швидкий шлях простим.

Модель паралелізму: з’єднання, потоки і чому «більше» може бути повільніше

Уявіть сервер MySQL як ресторан, що посаджує клієнтів (з’єднання) і призначає офіціанта (потік). Якщо ви додаєте більше столів, але не збільшуєте потужність кухні, їжі не стане більше — просто більше людей стоятиме в черзі, більше шуму і гірший сервіс.

Коли ви підвищуєте max_connections, ви дозволяєте не просто більше клієнтів заходити. Ви дозволяєте більше одночасної роботи. Це змінює:

• Пам’ять: кожна сесія споживає базову пам’ять, плюс буфери на з’єднання, тимчасові таблиці, структури блокувань метаданих.
• Планування CPU: більше потоків — більше перемикань контексту. На насиченому CPU це може зменшити корисну роботу.
• Блокування: більше одночасних транзакцій збільшує час очікування блокувань, особливо на «гарячих» таблицях/рядках.
• Глибина черги I/O: більше відкритих операцій читання/запису може перевищити можливості сховища й підвищити латентність для всіх.

Мета не в «допускати нескінченні з’єднання». Мета — «утримувати час відповіді стабільним під піковим навантаженням». Це часто означає контроль паралелізму: пулінг з боку додатка, обмеження максимального розміру пулу, короткі транзакції і налаштування MySQL так, щоб він витримував реальне навантаження.

Жарт #2 (короткий, по темі): Найпростіший спосіб впоратися з 10 000 MySQL-з’єднань — не мати 10 000 MySQL-з’єднань.

Практичні завдання (команди, очікуваний вихід та рішення)

Це перевірки для production. Виконуйте їх під час інциденту або в спокійному вікні техобслуговування. Кожне завдання містить: команду, що означає типовий вихід, і рішення, яке ви приймаєте.

Завдання 1: Підтвердити точний рівень помилок і звідки вони йдуть

cr0x@server:~$ sudo journalctl -u mysql --since "30 min ago" | tail -n 30
Dec 29 02:08:11 db1 mysqld[1327]: [Warning] Aborted connection 18933 to db: 'app' user: 'appuser' host: '10.0.2.41' (Got an error reading communication packets)
Dec 29 02:08:13 db1 mysqld[1327]: [Note] Too many connections
Dec 29 02:08:14 db1 mysqld[1327]: [Note] Too many connections

Що це означає: MySQL логуює тиск на з’єднання, плюс перервані з’єднання, що можуть вказувати на проблеми з мережею, таймаутами клієнтів або перевантаженням сервера.

Рішення: Якщо бачите багато рядків «Too many connections», переходьте до вимірювання поточних сесій і їхніх станів. Якщо здебільшого бачите «Aborted connection», перевірте мережу, таймаути клієнтів та завантаження CPU/I/O сервера.

Завдання 2: Перевірити налаштовані та ефективні ліміти підключень

cr0x@server:~$ sudo mysql -e "SHOW VARIABLES LIKE 'max_connections'; SHOW STATUS LIKE 'Max_used_connections';"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
+-----------------+-------+
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 298   |
+----------------------+-------+

Що це означає: Ви справді досягли стелі. Це не теоретично; ви вдаряєтесь у неї.

Рішення: Не підвищуйте стелю поки не знайдете, чому сесії не завершаються. Якщо Max_used_connections значно нижче max_connections, помилка може бути в іншому ліміті (FDs/systemd) або в проксі-шарі.

Завдання 3: Побачити, що роблять підключення зараз

cr0x@server:~$ sudo mysql -e "SHOW FULL PROCESSLIST;" | head -n 25
Id	User	Host	db	Command	Time	State	Info
19401	appuser	10.0.2.41:53312	app	Sleep	412		NULL
19408	appuser	10.0.2.45:58921	app	Query	18	Sending data	SELECT ...
19412	appuser	10.0.2.44:51220	app	Query	18	Waiting for table metadata lock	ALTER TABLE ...
19420	appuser	10.0.2.47:60011	app	Sleep	399		NULL

Що це означає: У вас суміш: багато довго сплячих сесій (пул або витоки) і деякі активні сесії, застряглі на metadata locks.

Рішення: Якщо більшість у Sleep з великим часом, зменшіть таймаути простою і виправте пулінг. Якщо багато чекають на блокування метаданих — є зміна схеми або довга транзакція, що блокує інших — вирішіть проблему блокувальника.

Завдання 4: Порахуйте сесії за станом (швидкий сигнал)

cr0x@server:~$ sudo mysql -NBe "SELECT COMMAND, STATE, COUNT(*) c FROM information_schema.PROCESSLIST GROUP BY COMMAND, STATE ORDER BY c DESC LIMIT 15;"
Sleep		221
Query	Sending data	36
Query	Waiting for table metadata lock	18
Query	Sorting result	6

Що це означає: Ваша «проблема з підключеннями» — здебільшого ідл-сесії плюс реальний випадок блокувань.

Рішення: Атакуйте накопичення ідл-сесій (таймаути + розмір пулу) і розблокуйте metadata lock (завершити/вбити DDL, уникайте DDL у піку).

Завдання 5: Визначити блокувальника metadata lock

cr0x@server:~$ sudo mysql -e "SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID FROM performance_schema.metadata_locks WHERE LOCK_STATUS='PENDING' LIMIT 10;"
+---------------+-------------+-----------+-------------+----------+
| OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID|
+---------------+-------------+-----------+-------------+----------+
| app           | orders      | EXCLUSIVE | PENDING     | 8421     |
+---------------+-------------+-----------+-------------+----------+

cr0x@server:~$ sudo mysql -e "SELECT * FROM performance_schema.threads WHERE THREAD_ID=8421\G" | head -n 20
*************************** 1. row ***************************
THREAD_ID: 8421
NAME: thread/sql/one_connection
PROCESSLIST_ID: 19412
PROCESSLIST_USER: appuser
PROCESSLIST_HOST: 10.0.2.44:51220
PROCESSLIST_DB: app
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 18

Що це означає: Маєте DDL, який потребує ексклюзивного metadata lock, і інші сесії можуть тримати конфліктні блокування.

Рішення: Знайдіть, хто тримає grant-блокування (granted locks) на тому ж об’єкті, і вирішіть: чекати, вбити блокувальника або відкласти DDL. В продакшні «відкласти DDL» зазвичай дорослий хід.

Завдання 6: Перевірити довгі транзакції, що тримають блокування

cr0x@server:~$ sudo mysql -e "SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 5\G" | sed -n '1,40p'
*************************** 1. row ***************************
trx_id: 42119291
trx_started: 2025-12-29 01:58:03
trx_mysql_thread_id: 19321
trx_query: UPDATE orders SET status='paid' WHERE ...

Що це означає: Транзакція відкрита близько 10 хвилин. Це вічність в OLTP. Вона може тримати рядкові блокування і перешкоджати purge, викликаючи каскадні затримки.

Рішення: Якщо це випадково (застряглий воркер, поганий шлях коду) — вбийте її і виправте область транзакції в додатку. Якщо це навмисно (батч-джоб), перенесіть у непіковий час або розбийте на частини з частими commit.

Завдання 7: Перевірити, чи є ви CPU-bound

cr0x@server:~$ mpstat -P ALL 1 5
Linux 6.8.0-xx-generic (db1) 	12/29/2025 	_x86_64_	(16 CPU)

02:12:10 PM  CPU  %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
02:12:11 PM  all  78.2  0.0   9.1   1.3    0.0  0.8   0.0    0.0    0.0   10.6

Що це означає: CPU сильно завантажений. Якщо це тримається, додаткові з’єднання зазвичай підвищать латентність, а не пропускну здатність.

Рішення: Якщо ви CPU-bound, пріоритет — оптимізація запитів і обмеження паралелізму (пул-кепи), а не підвищення max_connections.

Завдання 8: Перевірити тиск на диск і латентність

cr0x@server:~$ iostat -xz 1 3
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          62.11    0.00    7.40    7.55    0.00   22.94

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   w_await aqu-sz  %util
nvme0n1         920.0  48200.0     12.0   1.3    9.80    52.4     240.0  19320.0   14.20   6.12  96.3

Що це означає: Сховище майже насичене (%util ~96%) з помітним часом очікування. Запити будуть зависати, і підключення накопичуватимуться.

Рішення: Якщо I/O-bound, підвищуйте buffer pool (якщо є пам’ять), зменшуйте випадкові читання через індекси і уникайте підвищення паралелізму, доки латентність не покращиться.

Завдання 9: Перевірити своп (мовчазний вбивця продуктивності)

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:            64Gi        58Gi       1.2Gi       612Mi       4.8Gi       2.1Gi
Swap:           8.0Gi       2.9Gi       5.1Gi

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
12  3 3024896 128992  98112 3621120  64  120   980   410 9200 18000 72  8 10 10

Що це означає: У вас відбувається свопінг (si/so), що підвищить латентність запитів і спричинить накопичення підключень.

Рішення: Зменшіть використання пам’яті (часто шляхом зменшення буферів на з’єднання і/або max_connections), і розмірно встановіть buffer pool. Свопінг у поєднанні з «підвищити max_connections» — це самопідрив.

Завдання 10: Перевірити ліміти файлових дескрипторів для MySQL (systemd має значення)

cr0x@server:~$ sudo systemctl show mysql -p LimitNOFILE -p LimitNPROC
LimitNOFILE=1048576
LimitNPROC=15238

cr0x@server:~$ sudo cat /proc/$(pidof mysqld)/limits | egrep "Max open files|Max processes"
Max open files            1048576              1048576              files
Max processes             15238                15238                processes

Що це означає: У вашого сервісу високі FD-ліміти. Якщо б це було низько (наприклад 1024/4096), воно могло б маскуватись як проблема з підключеннями або викликати дивні помилки під навантаженням.

Рішення: Якщо низько — встановіть systemd override для mysql, щоб підняти їх. Якщо вже високо — не звинувачуйте ulimit; рухайтеся далі.

Завдання 11: Перевірити тиск на кеш відкритих таблиць (може викликати затримки і зависання)

cr0x@server:~$ sudo mysql -e "SHOW VARIABLES LIKE 'table_open_cache'; SHOW STATUS LIKE 'Opened_tables'; SHOW STATUS LIKE 'Open_tables';"
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| table_open_cache | 4000   |
+------------------+--------+
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Opened_tables | 812349 |
+---------------+--------+
+-------------+------+
| Variable_name | Value |
+-------------+------+
| Open_tables | 3998 |
+-------------+------+

Що це означає: Opened_tables величезне і все ще росте, тоді як Open_tables практично досяг розміру кешу. MySQL постійно відкриває таблиці — додаткова витрата під навантаженням.

Рішення: Збільште table_open_cache, якщо дозволяє пам’ять, і переконайтесь, що у вас адекватні файлові дескриптори. Це питання «дрібні витрати стають кровотечею».

Завдання 12: Знайти найгірші шаблони запитів, що тримають підключення відкритими

cr0x@server:~$ sudo mysql -e "SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000000 AS total_s, AVG_TIMER_WAIT/1000000000000 AS avg_s FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 5\G" | sed -n '1,80p'
*************************** 1. row ***************************
DIGEST_TEXT: SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT ?
COUNT_STAR: 1203912
total_s: 84231.1134
avg_s: 0.0699

Що це означає: У вас великий сумарний час, прив’язаний до кількох патернів запитів. Навіть якщо середня латентність «нормальна», сумарний час показує велике навантаження і зайнятість підключень.

Рішення: Оптимізуйте найгарячіші digests (індекси, покривні індекси, зменшення вибираних колонок, краща пагінація). Це зменшить час на запит і вирішить симптом у джерелі.

Завдання 13: Перевірити ефективність кешу потоків (легкий виграш при стрибках)

cr0x@server:~$ sudo mysql -e "SHOW VARIABLES LIKE 'thread_cache_size'; SHOW STATUS LIKE 'Threads_created'; SHOW STATUS LIKE 'Connections';"
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 8     |
+-------------------+-------+
+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| Threads_created | 238912 |
+-----------------+--------+
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Connections   | 9823812 |
+---------------+---------+

Що це означає: Малий розмір кешу і багато створених потоків свідчать про накладні витрати від churn потоків.

Рішення: Збільшіть thread_cache_size, поки Threads_created не перестане активно зростати щодо Connections. Це не робить запити повільнішими; це зменшує накладні витрати навколо них.

Завдання 14: Підтвердити розмір buffer pool та сигнали хіт-рейту

cr0x@server:~$ sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW STATUS LIKE 'Innodb_buffer_pool_reads'; SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';"
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 8589934592 |
+-------------------------+------------+
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Innodb_buffer_pool_reads| 832912311 |
+-------------------------+-----------+
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
| Innodb_buffer_pool_read_requests | 3412981123 |
+----------------------------------+------------+

Що це означає: Багато фізичних читань відносно запитів вказує, що кеш не справляється (грубий сигнал; не робіть ідола з одного співвідношення).

Рішення: Якщо у вас є пам’ять — і ви I/O-bound — збільшіть buffer pool. Якщо ви свопите — робіть навпаки: зменште інші споживачі пам’яті спочатку.

Виправлення, що не уповільнюють БД

Ось практична ієрархія: виправте поведінку додатка зі з’єднаннями, виправте час виконання запитів, виправте поведінку блокувань, потім підберіть розміри лімітів MySQL під реальність. Якщо робити навпаки — ви просто маскуєте проблему, поки вона не повернеться голосніше.

1) Виправити накопичення з’єднань: пулінг, обмеження і таймаути

Робіть: Використовуйте пул підключень на кожному інстансі додатка, встановіть жорсткий ліміт і адекватні таймаути.

• Розмір пулу: починайте менше, ніж думаєте. Якщо у вас 50 інстансів додатка і кожен має пул 50 — це 2500 потенційних підключень перш ніж база отримає голос.
• Таймаут отримання з’єднання: змушуйте швидко фейлити в додатку замість безкінечного очікування. Безкінечне очікування тримає більше сокетів відкритими.
• Таймаут простою: закривайте неактивні з’єднання, щоб не витрачати ліміт на сплячі сесії.

Не робіть: «одне з’єднання на запит» без пулінгу. Це машина churn потоків і перетворює сплески в шторми.

З боку MySQL два ручки допомагають зменшити сплячі зомбі:

• wait_timeout (неінтерактивні сесії)
• interactive_timeout (інтерактивні сесії)

Орієнтовні рекомендації: Для типових веб-додатків з пулінгом wait_timeout 60–300 секунд — розумна відправна точка. Якщо ваш додаток не може цього витримати — проблема в додатку, а не в БД.

2) Розблокувати накопичення блокувань: тримайте транзакції короткими і плануйте DDL

Помилки підключень часто слідують за блокувальними заторами. Сесії чекають; прибувають нові сесії; врешті-решт ви досягаєте стелі. Вирішення блокувальних заторів — один із небагатьох способів позбутися «too many connections» без додавання ресурсів.

• Тримайте транзакції короткими: ніякого «мислиння користувача» всередині транзакції, ніяких довгих циклів, жодного «прочитати 10k рядків і потім вирішити».
• Дисципліна DDL: не виконувати зміни схеми під час піку, особливо ті, що довго тримають metadata locks.
• Розбивайте батч-джоби: комітьте частіше, використовуйте обмежені партії і відступайте коли система гаряча.

3) Зменшити час запиту: бо час утримання з’єднання — ключовий

Кількість підключень ≈ коефіцієнт прибуття × час у системі. Ви не завжди контролюєте коефіцієнт прибуття. Ви можете зменшити час у системі.

• Індексувати фактичні предикати та сортування, які використовують гарячі digest-ї.
• Віддавати перевагу покривним індексам для типової пагінації списків, щоб уникнути зайвих читань сторінок.
• Припинити вибирати колонки, які не використовуються. Так — навіть якщо «це просто JSON». Це пам’ять, CPU і мережа.
• Припинити OFFSET-пагінацію у масштабі. Вона сповільнюється зі збільшенням offset. Використовуйте keyset-пагінацію за можливості.

4) Розглядати max_connections як розраховане рішення, а не бажання

Якщо ви перевірили, що не маєте блокувань, не свопите і не насичені I/O, підняття max_connections може бути доречним. Але розраховуйте його математично і з захисними механізмами.

Правило великого пальця (з розумінням):

• Базова пам’ять: InnoDB buffer pool + глобальні буфери MySQL + накладні витрати ОС.
• Пам’ять на підключення: може сильно варіювати залежно від налаштувань і навантаження. Небезпека в тому, що багато буферів виділяються під запит; найгірший випадок неприємний.

Практичний підхід:

1. Виміряйте використання пам’яті при поточних пікових підключеннях (RSS mysqld).
2. Оцініть додаткову пам’ять на кожні додаткові 50–100 підключень по зміні спостережуваної статистики, а не здогадками.
3. Збільшуйте невеликими кроками (наприклад, +20%); спостерігайте своп, латентність і lock waits.

5) Консервативно налаштуйте буфери на підключення

Ось як уникнути «ми підняли max_connections і машина почала свопити». Багато шаблонів my.cnf з величезними буферами копіюють без розуму, забуваючи, що вони множаться на активні сесії.

Параметри, до яких варто ставитися скептично при високій паралельності:

• sort_buffer_size
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size
• tmp_table_size / max_heap_table_size (впливає на тимчасові таблиці в пам’яті)

Орієнтовна порада: Тримайте ці параметри відносно малими, поки не матимете доказу, що конкретне навантаження отримує від них користь. Більшість OLTP отримують більше від індексів і buffer pool, а не від гігантських буферів на потік.

6) Кеш потоків: зменшити накладні витрати при churn

Стрибкоподібні навантаження створюють churn з’єднань. Churn породжує витрати на створення/знищення потоків. Розумний thread_cache_size допомагає поглинути піки без марної витрати CPU.

Що робити: Збільшуйте thread_cache_size, поки Threads_created не перестане рости швидко відносно Connections. Не ставте 10000 «на всякий випадок». Кешуйте достатньо для пік.

7) Використовуйте пулінг на правильному шарі (спочатку додаток, потім проксі)

Пулінг на боці додатка зазвичай кращий, бо зберігає контекст запиту і backpressure. Але є випадки, коли потрібен dedicated pooling proxy:

• Багато короткоживучих клієнтів (serverless, cron, CI-джоби).
• Устарілі додатки, що відкривають занадто багато з’єднань і які неможливо швидко виправити.
• Мульти-tenant середовища, де потрібна жорстка політика управління.

Увага: пулінг на рівні транзакції може зламати припущення сесії (тимчасові таблиці, змінні сесії). Якщо додаток їх використовує — потрібен сесіонний пул або зміни в коді.

8) Особливості Ubuntu 24.04: systemd override і як зафіксувати їх

На Ubuntu 24.04 найпоширеніша історія «ми встановили ulimit, але він не спрацював» — це systemd. Unit сервісу має власні ліміти.

Щоб встановити постійний FD-ліміт для MySQL:

cr0x@server:~$ sudo systemctl edit mysql
# (opens an editor)

cr0x@server:~$ sudo cat /etc/systemd/system/mysql.service.d/override.conf
[Service]
LimitNOFILE=1048576

cr0x@server:~$ sudo systemctl daemon-reload
cr0x@server:~$ sudo systemctl restart mysql

Що це означає: MySQL тепер буде успадковувати FD-ліміт при старті сервісу.

Рішення: Робіть це лише якщо підтвердили, що FD-ліміти дійсно є частиною проблеми. Підняття лімітів не виправить повільні запити; воно лише дасть помилці з’явитись пізніше.

9) Додайте backpressure: фейліть швидко вгорі замість плавлення внизу

Якщо БД — спільний критичний ресурс, ваш додаток має його захищати. Стратегії backpressure, що підтримують відзивчивість:

• Кеп пулу на інстанс додатку.
• Короткий таймаут отримання з’єднання (наприклад 100–500ms залежно від SLO).
• Черги з обмеженнями у додатку; відхилення запитів понад ліміт.
• Вимкнути агресивні повтори при помилках з БД; використовуйте експоненційний бекоф з джитером.

10) Масштабуйте правильно: read replicas, шардинг або більший інстанс

Якщо ви провели гігієну і все ще досягаєте стелі підключень через реальне навантаження, масштабуйтесь свідомо:

• Read replicas для навантаження читання (перемістіть звітні/листингові кінцеві точки, не критичні записи).
• Більший хост якщо ви пам’ять/I/O-обмежені і можете швидко вертикально масштабуватись.
• Шардинг якщо проблема записів і модель даних дозволяє — це не справа на один вівторок.

Типові помилки: симптом → корінь → рішення

1) Симптом: багато сесій у “Sleep”, max_connections вичерпано, але CPU не високий

Корінь: пул додатка занадто великий, витоки з’єднань або довгий wait_timeout, що тримає ідл-сесії.

Рішення: обмежте розміри пулів, переконайтесь, що з’єднання повертаються, зменшіть wait_timeout до кількох хвилин і встановіть клієнтські таймаути простою.

2) Симптом: багато сесій «Waiting for table metadata lock»

Корінь: онлайн зміна схеми/DDL або довга транзакція, що блокує metadata locks.

Рішення: плануйте DDL поза піком, використовуйте інструменти для online schema change, і вбивайте/уникайте довгих транзакцій, що тримають блокування. Ідентифікуйте блокувальника через performance_schema і дійте.

3) Симптом: “too many connections” з’являється разом зі свопом

Корінь: передоз пам’яті, часто через буфери на підключення і надмірну паралельність.

Рішення: зменшіть буфери на підключення, зменшіть ліміти підключень, правильно підберіть buffer pool і зупиніть своп перш ніж піднімати max_connections.

4) Симптом: підключення пікують під час збою, потім ніколи не відновлюються гладко

Корінь: шторм повторних спроб, клієнти агресивно перепідключаються, або health checks балансувальника відкривають сесії.

Рішення: експоненційний бекоф з джитером, circuit breakers, обмеження частоти перепідключень, і переконайтесь, що health checks не аутентифікуються в MySQL постійно.

5) Симптом: “Aborted connection… error reading communication packets” росте під час піку

Корінь: клієнти таймаутять через повільний сервер, втрата пакетів або перевантаження мережевого стека.

Рішення: виправте базову латентність (CPU/I/O/блокування), перевірте стабільність мережі і узгодьте таймаути клієнта/сервера, щоб клієнти не створювали додатковий churn.

6) Симптом: підняття max_connections «вирішує» помилку але латентність подвоюється

Корінь: ви збільшили конкуренцію і тиск на пам’ять; не підвищили пропускну здатність.

Рішення: відкотіть або зменште, введіть кепи пулів, оптимізуйте гарячі запити і масштабуйтесь по потужності (CPU/I/O) якщо потрібно.

7) Симптом: помилки залишаються, хоча max_connections виглядає високим

Корінь: ви досягаєте лімітів ОС/сервісу (FDs) або проксі має власний ліміт підключень.

Рішення: перевірте systemd LimitNOFILE, підтвердіть ліміти mysqld через /proc і перевірте проміжні шари на наявність лімітів.

Три міні-історії з практики

Міні-історія 1: Інцидент через хибне припущення

У них була охайна ментальна модель: «У нас 20 подів додатка, отже 20 підключень». Приємно. Заспокійливо. Але неправильно.

Реальна конфігурація включала бекграунд-воркери, збирач метрик, адмін UI і міграційний джоб, що «іноді запускається». Кожен компонент мав свої дефолтні значення пулу. Веб-поди самі по собі були в порядку. Воркери ж масштабувалися під час заторів — саме коли база вже була зайнята.

Перший інцидент «too many connections» стався під час маркетингової кампанії. Вони підняли max_connections, перезапустили MySQL і дивились, як помилка зникла. Приблизно на годину. Потім БД сповільнилась, таймаути зросли, і додаток почав частіше повторювати. Підключення знову зросли, тепер з більшими lock waits і більше свопу.

У звіті після інциденту висновок був не «підняти max_connections». А «порахувати підключення від усього флоту, включно з тим, про що ніхто не думає». Вони виправили це, обмеживши кожен пул, додавши таймаут отримання і навчивши воркери відступати, коли латентність БД зростає.

Міні-історія 2: Оптимізація, що відбилася бумерангом

Команда хотіла зменшити латентність запитів. Хтось агресивно збільшив join_buffer_size і sort_buffer_size, бо блог сказав, що це допомагає. На одному staging-боксі, де виконувалося по одному запиту, це допомогло.

У продакшні паралельність — все. На піку сотні сесій були активні. Ці більші буфери не завжди виділялись, але коли певні ендпоінти запускали складні запити, використання пам’яті зросло швидко. ОС почала свопити під стрибками. Коли розпочався своп — запити уповільнились. Коли запити уповільнились — з’єднання залишалися відкритими довше. Коли вони трималися довше — сервер вичерпав з’єднання. Першопочатковий симптом? «Too many connections».

Здавалось, ніби це проблема ліміту підключень, але насправді це було посилення пам’яті на підключення. Вирішення було нудне: відкотити надмірні буфери на потік, додати правильні індекси і безпечно збільшити buffer pool. Латентність стала кращою, і ліміт підключень перестав бути щоденною драмою.

Міні-історія 3: Нудна, але правильна практика, що врятувала ситуацію

Інша організація мала непомітну, але корисну практику: у кожного сервісу був документований «DB-бюджет». Максимальний розмір пулу на інстанс. Максимальна кількість інстансів перед рев’ю скейлінгу. Стандартний таймаут при отриманні та політика повторів.

Під час регіонального відключення трафік різко змінився. Навантаження подвоїлося. База нагрілася, але не впала. Замість створення безмежної кількості підключень додатки трохи підчергували, швидко відкидали надлишкові запити і відновились після спаду піку.

Вони досі бачили підвищений CPU бази. Довелося підправити кілька індексів. Але вони не отримали катастрофічного «too many connections», що зазвичай викликає хаотичні зміни вночі.

Найкраща частина: звіт по інциденту був короткий. Нудні системи — надійні системи, а надійні системи — ті, поруч з якими можна спокійно спати.

Контрольні списки / покроковий план

Покроково: зупинити кровотечу під час інциденту (15–30 хвилин)

1. Підтвердьте, що насичене: виконайте Завдання 7 (CPU), Завдання 8 (I/O), Завдання 9 (swap).
2. Ідентифікуйте стани з’єднань: Завдання 4 щоб побачити Sleep vs Query vs Locked.
3. Якщо домінують lock waits: Завдання 5 і Завдання 6, щоб знайти блокувальників. Вирішіть: чекати, вбити або відкласти DDL/пакети.
4. Якщо домінує Sleep: зменшіть розмір пулу додатку (найшвидший важіль), потім подумайте над зниженням wait_timeout після оцінки впливу.
5. Якщо I/O bound: припиніть збільшення паралелізму. Зменшіть навантаження (rate limit, вимкніть важкі ендпоінти), потім налаштуйте buffer pool/індекси.
6. Якщо своп: негайно зменшіть використання пам’яті (обмеження підключень/пулів, відкат надмірних буферів). Своп — сигнал «киньте все».
7. Лише потім, якщо є запас ресурсів і це потрібно, помірно підвищуйте max_connections як тимчасове пом’якшення.

Покроково: постійне виправлення (один спринт, а не паніка)

1. Перелічіть усіх клієнтів: веб, воркери, cron, admin tools, метрики, ETL. Задокументуйте очікувані макс-підключення для кожного компоненту.
2. Реалізуйте пулінг правильно: капи пулів, таймаути отримання і переконайтесь, що з’єднання повертаються.
3. Виправте гарячі запити: використовуйте performance_schema digest (Завдання 12), додавайте/налаштовуйте індекси і зменшуйте обсяг вибірки.
4. Зменшіть блокування: скоротіть транзакції, розбийте батч-джоби, плануйте DDL.
5. Підберіть ліміти MySQL: встановіть max_connections на основі виміряного запасу; налаштуйте thread_cache_size, table_open_cache.
6. Перевірте ліміти ОС/сервісу: переконайтесь, що systemd-ліміти відповідають вашому дизайну (Завдання 10).
7. Тестуйте навантаження з паралельністю: не лише QPS — перевіряйте латентність і помилки під патернами сплесків.
8. Налаштуйте алерти, що запобігають сюрпризам: алерт на зростання Threads_connected, lock waits, swap і зростання aborted connections.

Чого уникати (бо ви пожалкуєте)

• Встановлювати max_connections у величезне число «щоб цього ніколи не сталося». Воно повториться — просто повільніше і дорожче.
• Копіювати «high performance» my.cnf з масивними буферами на потік.
• Запускати зміни схеми під час піку і потім дивуватись на metadata locks.
• Дозволяти клієнтам миттєво і нескінченно повторювати з’єднання. Це не стійкість; це функція denial-of-service.

FAQ

1) Чи просто підняти max_connections?

Тільки якщо ви підтвердили, що маєте запас пам’яті (немає свопу), і сервер не вже насичений CPU/I/O. Інакше ви поміняєте жорсткі помилки на повільний крах.

2) Чому база стає повільнішою, коли я дозволяю більше з’єднань?

Більше сесій означає більше конкуренції, більше перемикань контексту, більше churn у буферах і потенційно більше дискового I/O. Пропускна здатність не масштабується лінійно з кількістю з’єднань.

3) Яке хороше значення для wait_timeout?

Для типових веб-додатків з пулінгом 60–300 секунд — розумна відправна точка. Якщо вам потрібні години — ваш додаток використовує MySQL як сховище сесій, і це інша розмова.

4) Як визначити, що це витік підключень у додатку?

Якщо Threads_connected повільно росте і більшість сесій у Sleep з великим часом — це класичний витік/накопичення. Підтвердьте, корелюючи з кількістю інстансів додатка і налаштуваннями пулу.

5) Що якщо багато сесій «Waiting for table metadata lock»?

Припиніть виконувати DDL під час піку, знайдіть блокувальника (часто довга транзакція) і вирішіть, чи вбивати її. Потім поставте DDL у вікно змін і процес.

6) Чи вирішують read replicas проблему «too many connections»?

Можуть, якщо тиск викликаний читанням і ви реально маршрутизували читання на репліки. Вони не вирішать конфлікти записів, довгі транзакції або поганий пулінг.

7) Чи можуть ліміти файлових дескрипторів ОС викликати помилки підключення?

Так. Якщо mysqld не може відкрити більше сокетів/файлів — ви побачите дивні помилки під навантаженням. На Ubuntu 24.04 перевірте systemd LimitNOFILE і ефективні ліміти mysqld.

8) Чи завжди безпечно знижувати буфери на підключення?

Зазвичай це безпечніше, ніж їх піднімати всліпу. Нижчі буфери можуть збільшити використання тимчасового диску або уповільнити певні запити, тому перевіряйте на реальному навантаженні. Але надмірні буфери при високій паралельності часто викликають інциденти.

9) Як обрати між оптимізацією запитів і додаванням заліза?

Якщо ви CPU/I/O-сатуровані з відомими поганими запитами — оптимізуйте запити перш за все. Якщо ви вже добре оптимізовані і все ще насичені — додайте залізо або масштабуйтесь. Міряйте перед витратами.

10) На які метрики ставити алерти, щоб помітити це раніше?

Threads_connected, Max_used_connections, індикатори lock waits, rate aborted connections, використання swap, disk await/utilization і p95/p99 латентність запитів (не лише середнє).

Наступні кроки, які не зашкодять

Якщо ви бачите “too many connections” на Ubuntu 24.04 — ставтесь до цього як до симптома, а не як до налаштування. Найшвидше і найстабільніше рішення майже завжди зменшити зайнятість підключень: скоротити час запитів, укоротити транзакції і припинити накопичення сесій додатком.

Зробіть ці кроки у порядку:

1. Запустіть швидкий план діагностики і зберіть докази (стани процесів, CPU/I/O/swap).
2. Виправте найбільшого драйвера: сплячі натовпи або блокувальні затори. Не сперечайтесь — міряйте.
3. Обмежте і налаштуйте пули додатка. Додайте таймаути отримання і адекватний retry backoff.
4. Оптимізуйте найгарячіші statement digests. Це зменшить час у системі і полегшить все інше.
5. Тільки потім — підкоригуйте max_connections і кеші MySQL на основі виміряного запасу.

Коли ви виправите це правильно, база стане швидшою під навантаженням, а не повільнішою. Ось у чому весь сенс. Помилка зникне не тому, що ви навчили її терпіти біль, а тому, що система стала здоровішою.