PostgreSQL проти Percona Server: хто потребує більше налаштувань для швидкості

Груба теза: хто потребує більше налаштувань

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

Percona Server (удосконалений дистрибутив MySQL, переважно на базі InnoDB) може бути надзвичайно швидким, але він частіше змушує вас
робити явні вибори раніше: розмір buffer pool, розмір redo логів, компроміс щодо надійності, поведінка скидання, налаштування реплікації,
накладні витрати інструментів спостереження. Деякі з цих значень за замовчуванням безпечні; деякі — консервативні; деякі — чудові,
поки навантаження не зміниться, і тоді вони тихо перетворюються на податок.

Отже, хто потребує більше ручок? На практиці:

• Percona Server частіше потребує більш явного тюнінгу, щоб досягти «гарної швидкості» для write-heavy OLTP, високої конкуренції
  та топологій з інтенсивною реплікацією — особливо коли затримка має значення і набір даних вже не вміщується в пам’ять.
• PostgreSQL зазвичай потребує менше ручок для «гарної швидкості» на змішаних навантаженнях і складних запитах — але він вимагає
  постійної гігієни (autovacuum, контроль bloat, статистика), інакше з часом «зіпсується».

Прихований поворот: питання не в «хто має більше ручок», а в «які ручки неминучі і наскільки дорого помилитися з ними».
Неправильні налаштування InnoDB flushing можуть перетворити швидку систему на заїкаючу. Неправильна поведінка autovacuum може зробити PostgreSQL
«випадково повільним», хоча насправді він тоне в мертвих кортежах.

Перша жартівлива ремарка (коротко і заслужено): Налаштування баз даних схоже на налаштування гітари — більшість людей крутять колки, поки звук зміниться, а не стане кращим.

Кілька цікавих фактів (і чому вони важливі)

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

1. MVCC у PostgreSQL зберігає старі версії рядків у heap, поки vacuum їх не звільнить. Саме тому
   налаштування autovacuum — це налаштування продуктивності, а не просто прибирання.
2. Buffer pool InnoDB — центр гравітації продуктивності для MySQL/Percona. Якщо він занадто малий, все стає I/O-зв’язним;
   якщо його зробити занадто великим без запасу, кеш ОС і файлові системи починають конфліктувати.
3. Percona Server виник через те, що «стоковий MySQL» був недостатнім для багатьох операторів — він історично підкреслював спостережуваність,
   додаткову діагностику та поліпшення продуктивності при збереженні сумісності. Ця культура помітна в тому, скільки налаштувань і метрик доступно.
4. PostgreSQL отримав паралельні запити великими кроками починаючи з 9.6+, і це досі область, де кілька ручок
   (наприклад, max_parallel_workers_per_gather) можуть перетворити звіт із «перерва на каву» на «мить».
5. Лінія реплікації MySQL спричинила багато операційного тюнінгу: row vs statement, формат binlog, налаштування sync, варіанти групової реплікації.
   Топологія — частина продуктивності.
6. WAL і контрольні точки PostgreSQL працюють інакше, ніж redo/undo InnoDB. Налаштування контрольних точок у Postgres часто спрямовані на згладжування піків записів;
   налаштування redo InnoDB — на підтримку пропускної здатності записів без трясіння.
7. Percona додав/покращив функції, як-от розширений slow query log і додаткові статус-змінні, що робить підхід «спочатку заміряй» практичнішим.
   З іншого боку: ви можете вимірювати настільки багато, що створите додаткове навантаження.
8. Обидві екосистеми вивчили на власному досвіді, що «більші кеші» не завжди швидші. Тиск пам’яті, ефекти NUMA і поведінка алокаторів
   можуть перетворити «більше ОЗП для БД» на «більша затримка для всіх».

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

Що означає «гарна швидкість» в операціях

У продакшні швидкість — це не одне число. Це:

• p95 і p99 латентності при реальній конкуренції
• стабільність пропускної здатності під час компактування/vacuum/checkpoints
• передбачувана поведінка хвоста під час бекапів, догонів реплікації та перемикання
• швидкість діагностики, коли щось повільне (спостережуваність як частина продуктивності)

Параметри PostgreSQL за замовчуванням: ввічливі, консервативні й іноді наївні

Значення за замовчуванням у PostgreSQL розраховані на «працює на ноутбуку» і «не зламає машину». Вони не захоплять автоматично більшість вашої ОЗП.
Це хороші манери. Саме тому нові інсталяції можуть виглядати не вражаючими, поки ви не встановите кілька ключових параметрів.

Плюс у тому, що після налаштування пам’яті та поведінки контрольних точок PostgreSQL часто поводиться передбачувано. Вам все одно доведеться налаштовувати
під конкретні шаблони запитів і навантаження на обслуговування, але система меншою мірою вимагатиме довгого списку «або інакше» перемикачів.

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

Percona Server успадковує багато значень від MySQL, і багато з них безпечні, але не найшвидші. Але аудиторія Percona часто запускає серйозний OLTP,
тому операційне припущення інше: ви налаштуєте buffer pool, redo логи, поведінку flush і реплікацію. Якщо не налаштуєте,
база все одно працюватиме — але ви залишите швидкодію на столі, а іноді таблиця загориться під навантаженням.

PostgreSQL: налаштування, що реально впливають

1) Пам’ять: shared_buffers, work_mem, maintenance_work_mem

PostgreSQL має кілька доменів пам’яті. Це збиває з пантелику тих, хто звик до «встановити buffer pool і забути».
Ваше завдання — уникнути загибелі від тисячі алокацій work_mem, одночасно зберігаючи достатній кеш і резерв для обслуговування.

• shared_buffers: основний спільний кеш PostgreSQL. Звичайна відправна точка: 20–30% ОЗП на Linux.
  Більше не завжди краще; кеш ОС все ще важливий.
• work_mem: на сортування/хеш-нод, на запит, на воркер. Надто високе значення перетворює конкуренцію на рулетку свопу.
  Надто низьке — і ви спровокуєте виливання на диск. Встановлюйте обачливо.
• maintenance_work_mem: для vacuum і побудови індексів. Надто мало — обслуговування повільне; надто багато — може відняти ресурси в інших робіт.

2) WAL і контрольні точки: припиніть «обрив записів»

Часті скарги на продуктивність PostgreSQL виглядають так: «Швидко, потім повільно, потім знову швидко.»
Зазвичай це контрольні точки, поведінка background writer або насичення сховища.

• checkpoint_timeout і max_wal_size: збільшіть, щоб скоротити частоту контрольних точок.
• checkpoint_completion_target: прагніть розподілити I/O контрольної точки у часі.
• wal_compression: корисне, коли обсяг WAL великий і CPU доступний.

3) Autovacuum: або ви його налаштовуєте, або він налаштовує вас

Autovacuum — це фонова система обслуговування, яка запобігає bloat і переповненню transaction ID. Якщо вона відстає, ви отримаєте:
роздуті таблиці, повільні запити, неефективні індекси та «таємничий» I/O.

Ключові важелі:

• autovacuum_max_workers: більше воркерів допомагає на багатьох таблицях; занадто багато може викликати спайки I/O.
• autovacuum_vacuum_scale_factor і autovacuum_analyze_scale_factor: зменшуйте для «гарячих» таблиць.
• autovacuum_vacuum_cost_limit і …_delay: контролюють, наскільки агресивний процес.

4) Реалістичність планувальника: статистика і effective_cache_size

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

• effective_cache_size: скажіть планувальнику, скільки кешу він може припускати (shared buffers + кеш ОС).
• default_statistics_target і ціль статистики на рівні стовпця: підвищуйте для некоректно розподілених даних.

5) Обробка підключень: класика «занадто багато бекенд-ів»

PostgreSQL використовує модель процес-на-з’єднання (з варіаціями та покращеннями з часом). Висока кількість підключень збільшує споживання пам’яті
і контекстне перемикання. Це не виправляється бажанням; це вирішується пулінгом.

• max_connections: не виставляйте 5000 лише тому, що так можна.
• Використовуйте пулер (наприклад, pgBouncer), коли ваш застосунок відкриває багато короткоживучих з’єднань.

Percona Server: налаштування, які ігнорувати не можна

1) InnoDB buffer pool: велика опція

Якщо ви налаштовуєте тільки одну річ у Percona Server/MySQL, налаштуйте це. Buffer pool кешує дані та індекси; сюди йдуть ваші операції читання.
Типова порада: 60–75% ОЗП на виділеному хості БД, лишаючи місце для ОС, підключень, реплікації та файлового кешу (особливо якщо використовується O_DIRECT, вибір відрізняється).

• innodb_buffer_pool_size: встановіть свідомо.
• innodb_buffer_pool_instances: допомагає при великих пулах; занадто багато екземплярів додає накладні витрати.

2) Редо-логи і скидання: надійність проти пропускної здатності — це не моральне питання

Записова продуктивність в InnoDB сильно залежить від розміру redo логів і поведінки скидання.
Ви можете зробити її швидкою, взявши ризики щодо надійності. Іноді це прийнятно; часто — ні.

• innodb_redo_log_capacity (або старіший підхід зі зміною розміру файлу логів): занадто мало — часті контрольні точки і чати.
• innodb_flush_log_at_trx_commit: 1 — найбезпечніше; 2 — часто дає кращу продуктивність з певним ризиком; 0 — гостріше.
• sync_binlog: надійність binlog теж важлива, якщо ви покладаєтесь на реплікацію або відновлення за часом.

3) Пропускна здатність I/O і «брудні» сторінки: навчіть рушій вашого сховища

InnoDB намагається адаптуватись, але йому все одно потрібні підказки. Сучасні NVMe відрізняються від мережевих SSD або блокових пристроїв у хмарі з кредитами на піки.

• innodb_io_capacity і innodb_io_capacity_max: виставте реалістично під IOPS вашого пристрою.
• innodb_max_dirty_pages_pct і пов’язані налаштування: контролюють, скільки брудних даних може накопичитись до початку скидання.

4) Мультипоточність і конкуренція: менше «магії», більше вибору

MySQL має давню історію тюнінгу конкуренції. Багатьом навантаженням підходять значення за замовчуванням, поки не перестають підходити.
Тоді ви виявляєте, що CPU завантажений через mutex-и або страждає від накладних витрат планувальника потоків.

• thread_cache_size: зменшує витрати на створення потоків.
• max_connections: надто велике значення створює тиск на пам’ять і конкуренцію блокувань.

5) Реплікація і binlog: продуктивність залежить від топології

Налаштування реплікації існують і в PostgreSQL, але в операційному житті MySQL/Percona часто все обертається навколо реплікації.
Формат binlog, порядок комітів і паралелізм застосування на репліках впливають і на швидкість, і на очікування коректності.

• binlog_format: ROW поширений для коректності; STATEMENT може бути компактнішим, але ризикованішим; MIXED — компроміс.
• replica_parallel_workers (або старі slave_*): допомагає реплікам надолужувати, але стежте за конкуренцією за ресурси.

Шаблони навантаження: де кожен рушій «просто працює» (і де ні)

OLTP з високою конкуренцією та простими запитами

Percona Server (InnoDB) у своїй природній ніші тут. З правильно розміреним buffer pool і адекватними налаштуваннями скидання він може витримувати великі швидкості запису.
PostgreSQL також добре підходить для OLTP, але вам слід звернути увагу на autovacuum і дизайн індексів раніше, бо під update-heavy навантаженням накопичуються мертві кортежі.

Якщо ваше навантаження — «постійні оновлення усюди», PostgreSQL потребує обслуговування як первинну турботу. Якщо ставитимете його у режим set-and-forget,
продуктивність погіршиться протягом місяців.

Складні запити, аналітика, JOIN-и і «чому цей звіт повільний?»

PostgreSQL зазвичай має перевагу в складності планувальника запитів, розширюваності та зручності вираження складного SQL.
Це не означає, що MySQL не може виконувати JOIN-и; швидше за все ви зіткнетесь з гострими краями раніше у виборі планів та індексації.

Саме тут тюнінг PostgreSQL менше про «більше ручок» і більше про якість статистики, правильні індекси і
запобігання регресіям планів після оновлень або змін у схемі.

Сплески записів і пакетні задачі

Поведінка контрольних точок/WAL у PostgreSQL може створювати періодичні сплески I/O при поганому налаштуванні.
Percona також може страждати від скидів, коли управління брудними сторінками налаштоване неправильно або коли сховище неправдиво повідомляє про свою продуктивність.

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

Операційна простота проти операційного контролю

PostgreSQL може здаватися простішим, бо менше налаштувань є «обов’язковими». Але його коректність в операціях залежить від vacuum і розумного управління підключеннями.
Percona відчувається більш «ручковим», бо надає багато явних контрольних точок, особливо навколо надійності та I/O.

Вибирайте, виходячи з того, хто буде керувати цим о 02:13. Якщо у вас є команда, яка любить явні контролі і спостережуваність, екосистема Percona буде зручною.
Якщо ви цінуєте розумні значення за замовчуванням і потужний SQL з меншою площею налаштувань для досягнення прийнятної продуктивності, PostgreSQL зазвичай дружніший — за умови, що ви зобов’язуєтесь до гігієни vacuum.

Сховище та ОС: та частина, яку ви хотіли проігнорувати

Ви не зможете переоблаштувати погане сховище налаштуваннями. Ви лише зробите його виходити з ладу більш цікавими способами.
І PostgreSQL, і Percona чесні: якщо латентність сховища непередбачувана, ваш p99 виглядатиме як фільм жахів.

Латентність важливіша за пропускну здатність для більшості OLTP

Більшість OLTP-навантажень більше дбають про передбачувану підмілісекундну або низькомілісекундну латентність, ніж про сирі МБ/с.
Пристрій, що робить 3 GB/s послідовно, але іноді зависає на 200 ms — не диск для бази даних, а шаліка.

Файлова система та параметри монтування мають значення (але менше, ніж ви боїтеся)

Сучасні файлові системи Linux підходять. Важливо узгодити I/O-патерн вашої БД зі стеком збереження:

• Остерігайтеся подвійного кешування (кеш БД + кеш ОС), коли пам’ять обмежена.
• Будьте обережні з агресивними параметрами writeback, що створюють періодичні зависання.
• Розумійте, чи ви на локальному NVMe, мережевому блочному сховищі або «хмарному SSD» з піковою поведінкою.

Одна операційна цитата, щоб тримати вас чесними

Надія — не стратегія. — генерал Гордон Р. Салліван

Це цитата для менеджменту, але SRE її підхопили, бо вона боляче корисна. Не «надійтеся», що значення за замовчуванням годяться. Міряйте й ухвалюйте рішення.

Практичні завдання: команди, виводи та рішення (12+)

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

Завдання 1: Перевірте, чи ви завантажені CPU або I/O (Linux)

cr0x@server:~$ iostat -xz 1 3
Linux 6.5.0 (db1)  12/30/2025  _x86_64_  (16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          32.10    0.00    7.90   18.50    0.00   41.50

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   wrqm/s  %wrqm w_await wareq-sz  aqu-sz  %util
nvme0n1         420.0  28500.0     10.0   2.3    3.10    67.9    780.0  112000.0    50.0   6.0   18.20   143.6   15.2   98.0

Значення: Високий %iowait і близький до 100% відсоток завантаження пристрою з високим w_await вказує на проблему з латентністю записів.
Це не момент для «більше індексів».

Рішення: Пріоритезуйте налаштування шляху запису (контрольні точки/WAL у Postgres, InnoDB flushing/redo) і розслідування сховища перед оптимізацією запитів.

Завдання 2: Перевірте тиск пам’яті і свопінг (Linux)

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 st
 3  0      0 120432  98200 8123456  0    0   120  2400  920 2100 35  8 40 17  0
 6  1   8192  20480  11000 7012000  0  256  1024  8900 1200 4800 42 10 25 23  0

Значення: Активність свопінгу (so) і падіння вільної пам’яті під навантаженням вказують на перенасичення пам’яті.
Для PostgreSQL це часто означає множення work_mem на конкуренцію. Для MySQL — надто великий buffer pool плюс велика кількість підключень.

Рішення: Зменшіть ризик пам’яті на підключення (пулінг, зниження work_mem, зниження max_connections) перед тим, як «рефлекторно додавати ОЗП».

Завдання 3: PostgreSQL — подивіться на основні очікування (на чому бекенди застрягли?)

cr0x@server:~$ psql -X -c "SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE state='active' GROUP BY 1,2 ORDER BY 3 DESC LIMIT 10;"
 wait_event_type |     wait_event      | count
-----------------+---------------------+-------
 IO              | DataFileRead         |    18
 Lock            | transactionid        |     7
 LWLock          | WALWrite             |     5
                 |                     |     0

Значення: Багато DataFileRead означає промахи кешу / I/O-зв’язані операції читання. WALWrite вказує на тиск на WAL.
transactionid блокування можуть сигналізувати про контенцію або довгі транзакції.

Рішення: Якщо домінує I/O — перевірте показники попадань у кеш і латентність сховища. Якщо домінує WALWrite — налаштуйте WAL/контрольні точки і підтвердьте fsync-латентність.

Завдання 4: PostgreSQL — перевірте відсоток попадань в буферний кеш (орієнтовно)

cr0x@server:~$ psql -X -c "SELECT datname, blks_hit, blks_read, round(100.0*blks_hit/nullif(blks_hit+blks_read,0),2) AS hit_pct FROM pg_stat_database ORDER BY hit_pct ASC;"
  datname  | blks_hit | blks_read | hit_pct
-----------+----------+-----------+---------
 appdb     | 93210012 |  8200455  | 91.93
 postgres  |  1023012 |    12045  | 98.84

Значення: 92% може бути прийнятним або поганим залежно від навантаження. Важливі тренд і кореляція з латентністю.
Раптове падіння вказує на ріст робочого набору, погані шаблони запитів або недостатню пам’ять.

Рішення: Якщо відсоток попадань падає й читання зростають, розгляньте поліпшення індексів або додаткову пам’ять (shared_buffers + кеш ОС), але спочатку перевірте плани запитів.

Завдання 5: PostgreSQL — виявіть відставання autovacuum і ризик bloat

cr0x@server:~$ psql -X -c "SELECT relname, n_dead_tup, n_live_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"
    relname     | n_dead_tup | n_live_tup |     last_autovacuum     |     last_autoanalyze
----------------+------------+------------+-------------------------+-------------------------
 orders         |    8923012 |   40211234 |                         | 2025-12-30 00:41:13+00
 order_items    |    6112001 |   88012210 | 2025-12-29 22:10:02+00  | 2025-12-30 00:40:59+00
 sessions       |    4100122 |    1802210 |                         | 2025-12-30 00:40:15+00

Значення: Висока кількість мертвих кортежів і відсутність last_autovacuum на гарячих таблицях — червоний прапорець.
Запити сповільнюються, бо індекси та heap ростуть, а перевірки видимості стають дорожчими.

Рішення: Налаштуйте autovacuum на рівні таблиць (scale factors, cost limits) і виправте довгі транзакції, що заважають прибиранню.

Завдання 6: PostgreSQL — знайдіть довгі транзакції, що блокують vacuum

cr0x@server:~$ psql -X -c "SELECT pid, usename, now()-xact_start AS xact_age, state, left(query,80) AS q FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_age DESC LIMIT 5;"
 pid  | usename |  xact_age   | state  | q
------+---------+-------------+--------+--------------------------------------------------------------------------------
 8421 | app     | 03:12:44    | idle   | BEGIN; SELECT * FROM customers WHERE id=...
 9122 | app     | 00:18:02    | active | UPDATE orders SET status='paid' WHERE id=...

Значення: Сесія «idle in transaction», що тримає снапшот, може заважати vacuum видаляти мертві кортежі,
призводячи до bloat і погіршення продуктивності.

Рішення: Виправте область транзакцій у застосунку. Вбивати сесії варто лише у нагальних випадках, а потім виправляти код.

Завдання 7: PostgreSQL — перевірте поведінку контрольних точок

cr0x@server:~$ psql -X -c "SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint FROM pg_stat_bgwriter;"
 checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint
-------------------+-----------------+-----------------------+----------------------+--------------------
               120 |              95 |              18234012 |              3100221 |           90211234

Значення: Високий показник checkpoints_req означає, що ви досягаєте лімітів WAL і змушуєте контрольні точки.
Великі часи запису/синхронізації контрольних точок корелюють з піками латентності.

Рішення: Збільшіть max_wal_size, налаштуйте checkpoint_timeout і розгладьте за допомогою checkpoint_completion_target.
Якщо сховище не витягує — полагодьте сховище або зменшіть write amplification (пакетування, менше індексів, зміни схеми).

Завдання 8: MySQL/Percona — подивіться, на що чекають потоки (швидкий огляд)

cr0x@server:~$ mysql -e "SHOW PROCESSLIST\G" | head -n 40
*************************** 1. row ***************************
     Id: 12091
   User: app
   Host: 10.0.1.25:52144
     db: appdb
Command: Query
   Time: 12
  State: Waiting for table metadata lock
   Info: ALTER TABLE orders ADD COLUMN note TEXT
*************************** 2. row ***************************
     Id: 12102
   User: app
   Host: 10.0.1.18:51902
     db: appdb
Command: Query
   Time: 10
  State: update
   Info: UPDATE orders SET status='paid' WHERE id=...

Значення: Очікування метаданихних блокувань може заморозити трафік під час змін схеми. Це може виглядати як «повільна база», але насправді — DDL блокує.

Рішення: Використовуйте online-підходи до змін схеми де потрібно; плануйте DDL; зменшуйте довгі транзакції, що тримають блокування.

Завдання 9: MySQL/Percona — перевірте стан InnoDB buffer pool

cr0x@server:~$ mysql -e "SHOW ENGINE INNODB STATUS\G" | sed -n '1,120p'
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2025-12-30 01:12:09 INNODB MONITOR OUTPUT
=====================================
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 274877906944
Buffer pool size   16777216
Free buffers       1024
Database pages     16776190
Old database pages 6192000
Modified db pages  210400
Pages read 98200123, created 2210021, written 81230111
...

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

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

Завдання 10: MySQL/Percona — перевірте тиск на redo лог і скидання

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_os_log_fsyncs'; SHOW GLOBAL STATUS LIKE 'Innodb_log_waits'; SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';"
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| Innodb_os_log_fsyncs| 18220031 |
+---------------------+----------+
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 12044 |
+------------------+-------+
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

Значення: Innodb_log_waits показує сесії, які чекають на простір у redo логах / поведінку скидання.
При innodb_flush_log_at_trx_commit=1 латентність fsync потрапляє безпосередньо в шлях транзакції.

Рішення: Якщо очікування значні, збільште ємність redo і перевірте fsync-латентність сховища. Розглядайте компроміси надійності лише з чітким RPO/RTO.

Завдання 11: MySQL/Percona — швидко знайти топ-запити (резюме Performance Schema)

cr0x@server:~$ mysql -e "SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1e12,2) AS total_s FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 3\G"
*************************** 1. row ***************************
DIGEST_TEXT: SELECT * FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT ?
 COUNT_STAR: 1822011
   total_s: 9120.55
*************************** 2. row ***************************
DIGEST_TEXT: UPDATE order_items SET status = ? WHERE order_id = ?
 COUNT_STAR: 801122
   total_s: 6122.17

Значення: Великий загальний час може означати «викликається часто» або «кожен виклик повільний». Зведення за digest допомагає обрати, з чого почати.

Рішення: Візьміть найгірші digest-и і виконайте EXPLAIN, перевірте індекси і виміряйте співвідношення рядків, що оглядаються, до повернутих.

Завдання 12: PostgreSQL — знайти запити з найбільшим сумарним часом (pg_stat_statements)

cr0x@server:~$ psql -X -c "SELECT query, calls, round(total_exec_time::numeric,1) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
                           query                           | calls  | total_ms | mean_ms
-----------------------------------------------------------+--------+----------+---------
 SELECT * FROM orders WHERE customer_id=$1 ORDER BY ...     | 1822011| 812034.2 |   0.45
 UPDATE order_items SET status=$1 WHERE order_id=$2         |  801122| 610221.7 |   0.76

Значення: Та сама історія, що й у MySQL digest: знайдіть реальні поглиначі часу. PostgreSQL дає середній і загальний час; обидва мають значення.

Рішення: Для частих запитів шукайте підтримку індексами і уникайте непотрібного витягання рядків. Для великих середніх часів — аналізуйте плани і I/O.

Завдання 13: PostgreSQL — проінспектуйте план запиту на предмет «брехні» і неправильних оцінок

cr0x@server:~$ psql -X -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id=123 ORDER BY created_at DESC LIMIT 50;"
 Limit  (cost=0.43..12.86 rows=50 width=128) (actual time=0.210..4.882 rows=50 loops=1)
   Buffers: shared hit=120 read=320
   ->  Index Scan Backward using idx_orders_customer_created on orders  (cost=0.43..9012.12 rows=35210 width=128) (actual time=0.207..4.865 rows=50 loops=1)
         Index Cond: (customer_id = 123)
         Buffers: shared hit=120 read=320
 Planning Time: 0.220 ms
 Execution Time: 4.910 ms

Значення: Параметр read=320 вказує на фізичні читання під час виконання. Якщо цей запит «гарячий» і це число зростає, ви I/O-зв’язані.
Неправильні оцінки видно, коли «rows=» суттєво відрізняється від «actual rows=».

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

Завдання 14: MySQL/Percona — перевірте відставання реплікації та тиск застосування

cr0x@server:~$ mysql -e "SHOW REPLICA STATUS\G" | egrep 'Seconds_Behind_Source|Replica_SQL_Running|Replica_IO_Running|Last_SQL_Error'
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 87
Last_SQL_Error:

Значення: Лаг без помилок означає, що репліка не встигає застосовувати. Причини — I/O, CPU або однопоточне застосування.

Рішення: Збільшіть паралельність застосування на репліці там, де це безпечно, зменшіть write amplification і переконайтесь, що сховище репліки не повільніше за primary.

Завдання 15: Перевірте симптоми латентності файлової системи (Linux dmesg)

cr0x@server:~$ dmesg -T | tail -n 8
[Mon Dec 30 01:11:44 2025] nvme nvme0: I/O 123 QID 6 timeout, completion polled
[Mon Dec 30 01:11:44 2025] blk_update_request: I/O error, dev nvme0n1, sector 182291234 op 0x1:(WRITE) flags 0x800 phys_seg 32 prio class 0

Значення: Якщо в логах ядра є timeouts або помилки — перестаньте звинувачувати базу даних.

Рішення: Кваліфікуйте це як інцидент інфраструктури: замініть пристрій, виправте контролер/прошивку, перевірте стан хмарного тому і лише потім повертайтесь до налаштувань БД.

Швидкий план діагностики

Коли все повільно, ви не починаєте з редагування конфігів. Починаєте з доказу, куди йде час.
Цей план налаштований на «продакшн горить, але ми все ще дорослі».

Перший крок: це сама база чи вона чекає на щось інше?

1. Перевірте латентність і насичення сховища (iostat -xz, метрики хмарного тому). Якщо %util завалений і очікування високі,
   ваш «повільний запит» може бути історією «повільного диска».
2. Перевірте тиск пам’яті (vmstat, свопінг). Своп перетворює обидва рушії на трагічне перформанс-мистецтво.
3. Перевірте насичення CPU (load average проти кількості CPU, top, pidstat).
   Якщо CPU завалений, дізнайтесь: це виконання запитів, фонове обслуговування чи накладні витрати компресії/шифрування?

Другий крок: сесії чекають на блокування чи на I/O?

• PostgreSQL: очікування в pg_stat_activity (IO vs Lock vs LWLock). Багато Lock waits — контенція; багато IO waits — кеш/сховище.
• Percona: стан SHOW PROCESSLIST + стан InnoDB. Metadata locks, row locks і log waits розповідають різні історії.

Третій крок: визначте топ-запити за загальним часом, а не за відчуттями

• PostgreSQL: pg_stat_statements, потім EXPLAIN (ANALYZE, BUFFERS) для найгірших.
• Percona: зведення Performance Schema digest, потім EXPLAIN і перегляд індексів.

Четвертий крок: перевірте «мовчазних вбивць» специфічних для рушія

• PostgreSQL: відставання autovacuum, мертві кортежі, довгі транзакції, частота контрольних точок.
• Percona: очікування redo логів, промахи buffer pool, скиди, відставання репліки, що навантажує primary.

Друга жартівлива ремарка (і ми закінчили): Якщо ваша база повільна і ви не перевірили латентність диска — ви по суті дебагуєте за допомогою інтерпретативного танцю.

Три міні-історії з корпоративного світу (анонімізовано, технічно реальні)

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

Середнього розміру SaaS-компанія використовувала PostgreSQL для транзакційного трафіку й нещодавно додала функцію «таймлайн активності клієнта».
Це була класична таблиця з частими додаваннями й періодичними оновленнями статусів. У staging все було гаразд.

У продакшні p99 латентності поступово зростав протягом тижнів. Команда припустила, що це «просто ріст», і планувала вертикальне масштабування.
Вони додали CPU і RAM, похвалилися і чекали на кращі графіки.

Але нічого не змінилося. Додаткова пам’ять допомагала трохи, потім система знову сповзала. Під час наступного інциденту хтось запустив
pg_stat_user_tables і побачив гору мертвих кортежів у кількох гарячих таблицях. Autovacuum не встигав.
Гірше: фоновий воркер тримав довгі транзакції зі снапшотами відкритими годинами, обробляючи чергу.

Хибне припущення було просте: «Autovacuum автоматичний, тож усе під контролем». Він автоматичний так само, як пральна машина — поки ви не поклали туди одяг.
Треба запускати й контролювати процеси.

Виправлення було нудним і точковим: скоротили область транзакцій у воркері, знизили scale factors для гарячих таблиць, підняли кількість autovacuum workers,
і запланували контрольоване VACUUM (FULL) лише там, де це потрібно. Продуктивність стабілізувалася. Героїчні покупки заліза не знадобились.

Міні-історія 2: Оптимізація, що відскочила назад

E-commerce платформа використовувала Percona Server з інтенсивними записами: замовлення, платежі, резерви складу. Вони гналися за більшою пропускною здатністю,
і хтось запропонував «безпечну» зміну: збільшити buffer pool до «майже всієї RAM». Ідея — зменшити читання з диска.

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

Причина: тиск пам’яті. Віддавши InnoDB майже всю RAM, вони позбавили ОС та іншим компонентам запасу пам’яті: на підключення, реплікаційні буфери та поведінку файлової системи.
Хост почав інколи свопити, що перетворило fsync і скидання сторінок на непередбачувані піки латентності. База не «повільніла»; вона іноді зависала.

Повернення розміру buffer pool вирішило негайні симптоми. Довгострокові виправлення були комплекснішими:
налаштували buffer pool з реальним запасом, обмежили max connections і ввели пулінг з боку застосунку.
Вони також перестали робити DDL опівдні, що, хоч і не пов’язане з buffer pool, підвищило загальний спокій.

Урок: «більше кешу» не завжди добре. Це компроміс. ОС — частина системи, а не дрібниця, яку можна вигнати.

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

Фінансова команда вела обидва рушії: PostgreSQL для звітності і MySQL/Percona для легасі OLTP.
Вони не були найфлешовіші. Але були тихо ефективні.

Їхня секретна зброя — строгий, нудний рутин: щотижневий огляд топ-SQL за загальним часом, щомісячна перевірка bloat і стану vacuum у Postgres,
і щоквартальна валідація часу відновлення на обох системах. Вони тримали конфіги під контролем версій і вимагали нотатку з описом мети і плану відкату для кожної зміни тюнінгу.

Одного дня проблема з прошивкою сховища підвищила латентність записів. Обидві бази сповільнились, але команда швидко ідентифікувала причину,
бо мала базові виміри і знала, як виглядають нормальні fsync і контрольні точки. Вони не витратили години на підгонку налаштувань БД, намагаючись компенсувати померлий диск.

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

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

1) PostgreSQL p99 піки кожні кілька хвилин

Симптом: Піки латентності корелюють зі сплесками write I/O.

Корінь проблеми: Контрольні точки занадто часті або занадто «сплескові» через малий max_wal_size або погане згладжування контрольних точок.

Виправлення: Збільште max_wal_size, збільште checkpoint_timeout в межах розумного, встановіть checkpoint_completion_target (часто 0.7–0.9) і перевірте fsync-латентність сховища.

2) PostgreSQL поступово гіршає протягом тижнів

Симптом: Запити, що були швидкими, стають повільнішими; використання диска росте без очікування.

Корінь проблеми: Autovacuum не встигає; накопичуються мертві кортежі; довгі транзакції перешкоджають прибиранню; статистика застаріла.

Виправлення: Налаштуйте autovacuum для гарячих таблиць (знизьте scale factors), виправте довгі транзакції, обережно підвищте кількість autovacuum workers і перезапустіть аналіз критичних таблиць.

3) Percona/MySQL зависає під навантаженням записів через «log waits»

Симптом: Пропускна здатність падає під максимальними записами; статус показує збільшення log waits.

Корінь проблеми: Redo логи занадто малі або fsync сховища повільний; налаштування flush ставлять fsync у шлях комітів.

Виправлення: Збільште ємність redo, перевірте латентність NVMe/хмарного тому і перегляньте налаштування надійності лише з явним бізнес-рішенням.

4) MySQL трафік призупиняється під час зміни схеми

Симптом: Багато потоків показують «Waiting for table metadata lock».

Корінь проблеми: DDL блокує або блокується довгими транзакціями; metadata locks серіалізують доступ.

Виправлення: Використовуйте online-методи зміни схеми, вбивайте/уникати довгих транзакцій, плануйте DDL у вікна з низьким трафіком і проектуйте міграції з урахуванням блокувань.

5) Обидва рушії: «ми підняли max_connections і стало гірше»

Симптом: Більше таймаутів, вища латентність, збільшене контекстне перемикання, можливий свопінг.

Корінь проблеми: Перенавантаження конкуренції: занадто багато активних сесій насичують CPU, пам’ять або менеджер блокувань; черги перемістились із застосунку до БД.

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

6) Запити стали повільнішими після «оптимізації індексів»

Симптом: Зростає латентність записів; реплікація відстає; CPU і I/O ростуть.

Корінь проблеми: Надто багато індексів збільшують write amplification; «оптимізація читання» не виправдовує глобальні витрати.

Виправлення: Видаліть невикористовувані індекси, консолідуйте багатоколонні індекси і перевірте ефект на реальному навантаженні (не на припущеннях).

Перевірочні списки / покроковий план

Покроково: як привести PostgreSQL до «гарної швидкості» мінімальною кількістю змін

1. Встановіть shared_buffers як розумну частку (почніть з ~20–30% ОЗП на виділеному хості).
2. Встановіть effective_cache_size так, щоб відображати shared buffers + кеш ОС (звично 50–75% ОЗП).
3. Встановіть work_mem консервативно, потім підвищуйте для ролей/сесій з важкими запитами; не ставте глобально велике значення.
4. Увімкніть і використовуйте pg_stat_statements для ранжування запитів за загальним часом.
5. Налаштуйте контрольні точки (max_wal_size, checkpoint_timeout, checkpoint_completion_target), щоб уникнути I/O-обривів.
6. Щотижня перевіряйте здоров’я autovacuum; застосовуйте налаштування на рівні таблиць для гарячих таблиць.
7. Використовуйте пулінг підключень, якщо маєте багато короткоживучих з’єднань або великий фан-аут сервісів.
8. Заміряйте латентність сховища і спостерігайте її як частину застосунку (бо це так і є).

Покроково: як привести Percona Server до «гарної швидкості» без магії

1. Свідомо підберіть innodb_buffer_pool_size; лиште запас для ОС і конкуренції.
2. Налаштуйте ємність redo, щоб не робити постійні контрольні точки; перевірте log waits.
3. Явно визначте надійність: innodb_flush_log_at_trx_commit і sync_binlog у відповідності з бізнес-RPO.
4. Встановіть innodb_io_capacity згідно з реальністю сховища, а не маркетинговими характеристиками.
5. Увімкніть зведення за digest і використовуйте його регулярно.
6. Тримайте max_connections у межах розумного; виправляйте поведінку застосунку, замість збільшення ліміту.
7. Плануйте тюнінг реплікації (паралельне застосування, формат binlog) як частину продуктивності, а не потім.
8. Практикуйтесь у зміні схеми з урахуванням блокувань; metadata locks — не сюрпризний бонус.

Контрольний список рішень: «Мені потрібно більше ручок чи менше проблем?»

• Якщо ви не можете виміряти «гарячі» місця запитів, тюнінг — це азартна гра. Спочатку увімкніть правильну статистику.
• Якщо ви не можете описати вимоги до надійності, не чіпайте flush-ручки.
• Якщо ви не можете обмежити конкуренцію, база зробить це за вас — з латентністю.
• Якщо латентність сховища непередбачувана, результати тюнінгу теж будуть непередбачувані.

Поширені питання

1) Хто швидший з коробки?

Залежить від навантаження, але PostgreSQL часто «відчувається прийнятним» швидше, бо потрібно менше налаштувань, щоб уникнути очевидних пасток.
Percona/MySQL частіше вимагає вибору розміру buffer pool та параметрів redo/flush, щоб досягнути оптимальної поведінки при важкому OLTP.

2) Чи PostgreSQL «самоналаштовується» завдяки autovacuum?

Ні. Autovacuum автоматичний, але не всезнаючий. Гарячі таблиці часто потребують налаштувань на рівні таблиці, а довгі транзакції можуть його саботувати.
Ставте здоров’я vacuum у частину управління продуктивністю.

3) Чи Percona Server — це просто MySQL з додатковими ручками?

Це сумісний з MySQL продукт, що історично фокусувався на продуктивності і спостережуваності. «Додаткові ручки» часто з’явилися тому, що оператори просили контролю.
Більше контролю — це добре, поки не перетвориться на некеровану складність.

4) Який найважливіший важіль тюнінгу PostgreSQL?

Якби треба було вибрати один: підтримка обслуговування — ефективність autovacuum плюс запобігання контрольним точкам-сплескам. Пам’ять важлива, але bloat і «write cliffs» зруйнують усе.

5) Який найважливіший важіль тюнінгу Percona/MySQL?

innodb_buffer_pool_size. Якщо він неправильний, ви або будете шукати диск, або позбавите ОС ресурсів і спричините зависання. На другому місці: конфігурація redo/flush у відповідності зі сховищем.

6) Чи ставити PostgreSQL shared_buffers на 80% ОЗП як buffer pool?

Зазвичай ні. PostgreSQL виграє від кешу ОС і потребує запасу для work_mem, обслуговування й інших речей.
Почніть з меншого значення і міряйте; «вся RAM у shared_buffers» — часта самонанесена рана.

7) Чи можна виправити повільні запити, додавши індекси поки не стане швидко?

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

8) Чому мої PostgreSQL запити гальмують, хоча CPU простий?

Часто через I/O очікування, блокування або bloat. Перевірте очікування в pg_stat_activity, подивіться на мертві кортежі і підтвердіть латентність сховища.
Простий CPU не завжди означає здоров’я; іноді це ознака очікування.

9) Чому мої MySQL запити «застряють» під час міграцій?

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

10) Хто потребує більше ручок для гарної швидкості на хмарному блоковому сховищі?

Percona/MySQL частіше вимагає більш явного тюнінгу I/O та надійності, бо поведінка fsync критична для латентності комітів.
PostgreSQL теж чутливий до fsync-латентності (WAL), але часто менше ручок потрібно, щоби досягти стабільної поведінки — за умови налаштування контрольних точок.

Наступні кроки, яких не буде соромно

Якщо ви обираєте між PostgreSQL і Percona Server виключно за «хто потребує більше налаштувань», ви ставите правильне питання не в тому напрямку.
Запитайте замість цього: які ручки обов’язкові для мого навантаження і наскільки швидко моя команда зможе діагностувати проблеми, коли навантаження зміниться.

Практичні наступні кроки:

1. Виберіть одне репрезентативне навантаження (read-heavy OLTP, write-heavy OLTP, змішане, звітність) і бенчмаркніть з продакшн-подібною конкуренцією.
2. Увімкніть статистику запитів (pg_stat_statements або Performance Schema digests) і створіть щотижневий ритуал «топ SQL».
3. Встановіть базові значення для латентності сховища, поведінки контрольних точок/скидання та відставання реплікації. Запишіть їх.
4. Свідомо обмежте конкуренцію пулінгом і адекватними лімітами підключень. Не дозволяйте застосунку культурно DDoSити базу.
5. Змінюйте по одному параметру, вимірюйте і майте план відкату. Тримайте конфіги під контролем версій, бо це важливо.

PostgreSQL винагороджує стабільне обслуговування і гарну SQL-гігієну. Percona Server винагороджує явні вибори і щільний операційний контроль.
Жоден не «швидший за своєю природою». Швидшим буде той, кого ви вмієте експлуатувати без вгадувань.