Затримка реплікації MySQL та PostgreSQL: чому виникає і як її зменшити

Що таке затримка реплікації насправді (і чим вона не є)

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

• Транспортна затримка: первинний вузол згенерував зміни, але репліка їх ще не отримала (мережа, обмеження пропускної здатності, сплески).
• Затримка застосування/відтворення: репліка отримала зміни, але ще не застосувала їх (CPU, I/O, конкуренція, одно-потокове застосування, конфлікти).
• Затримка видимості: зміни застосовано, але вони не видимі для конкретного запиту/сесії через правила знімків стану (часто в Postgres при довгих транзакціях).

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

Дві визначення, які варто ввести внутрішньо

Операційна затримка: «Скільки часу проходить до того, як підтверджений запис на первинному вузлі стає доступним для запитів на репліці?» Це те, що цікавить додатки.

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

Цікаві факти та історичний контекст (бо історія допомагає не повторювати помилок)

1. Реплікація MySQL починалася як statement-based, що робило «детермінізм» молитвою, а не гарантією. Пізніше рядкове логування стало розумним дефолтом.
2. Вбудована потокова реплікація PostgreSQL (фізичний WAL) з’явилася в еру 9.x і перетворила «standby» з «відновлення з архіву» на «майже в реальному часі».
3. Півсинхронна реплікація MySQL була введена, щоб зменшити ризик втрати даних, але вона може обміняти затримку на надійність — ваші SLO це відчують.
4. GTID у MySQL спростив відновлення і зміни топології, але також полегшив створення надмірно самовпевнених автоматизацій.
5. Postgres hot standby дозволив виконувати запити тільки для читання на репліках, але також запровадив поведінку конфліктів запитів, що виглядає як «випадкові скасування», якщо ви не плануєте.
6. Логічна реплікація в Postgres з’явилася пізніше за фізичну і не є «фізичною, але кращою»; це інструмент з іншими режимами відмов.
7. Паралельна реплікація MySQL розвивалась з версіями: ранні реалізації були обмежені, пізніші краще паралелізують застосування. Ваша версія має значення.
8. Метрики затримки реплік брешуть через упущення: «seconds behind master» у MySQL не є універсальною істиною; це оцінка на основі міток часу з сліпими плямами.

Цитата, яку варто повісити на стіну, бо вона завадить героїчній маячні під час інцидентів:

«Надія — не стратегія.» — Вінс Ломбарді

Затримка реплікації — це місце, куди йде надія, щоб померти. Добре. Тепер можна інженерити.

Як реплікація MySQL створює затримку (і як вона її долає)

Класична реплікація MySQL здається обманливо простою: первинний записує binlog, репліки їх забирають і застосовують. Диявол у тому, як вони їх застосовують.

Пайплайн реплікації MySQL (практичний вигляд)

• Первинний вузол генерує події binlog (statement або row based; в продакшені зазвичай бажано row-based).
• I/O потік на репліці читає події binlog з первинного вузла і пише їх у relay logs.
• SQL потік(и) застосовують події relay log до даних репліки.

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

Типові форми затримки в MySQL

• Одна велика транзакція: репліка виглядає «завмерлою», потім раптово надолужує. Binlog у порядку; застосування активно «жує».
• Багато дрібних транзакцій: репліка повільно відстає під час піку і ніколи не наздоганяє. Потрібна більша пропускна здатність застосування або менше записів.
• Конкуренція за блокування на репліці: запити на читання на репліці блокують застосування (або навпаки), створюючи пилообразний графік затримки.

Сухий факт: у MySQL репліка — це не пасивне дзеркало. Це другий сервер бази даних, що виконує реальну роботу, змагаючись за CPU, I/O та buffer pool.

Як реплікація PostgreSQL створює затримку (і як вона її долає)

Потокова реплікація PostgreSQL — це відправка WAL по постійному з’єднанню. Репліки (standby) отримують WAL-записи і відтворюють їх. З hot standby вони також обслуговують запити лише для читання.

Пайплайн реплікації Postgres (фізичне стрімінгове відтворення)

• Первинний вузол записує WAL-записи для змін.
• WAL sender транслює WAL на репліки.
• WAL receiver на репліці записує отриманий WAL на диск.
• Startup/replay процес відтворює WAL, щоб зробити файли даних консистентними з таймлайном первинного вузла.

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

Специфічні реалії затримки в Postgres

• Відтворення може блокуватися через конфлікти під час recovery (hot standby): довгі запити можуть заважати vacuum на первинному, а на репліці їх можуть скасовувати або вони спричиняють затримку відтворення залежно від налаштувань.
• Сплески обсягу WAL можуть бути спричинені масовими оновленнями, перестроюванням індексів або повними переписами таблиць; це більше «фізика», ніж «тюнінг».
• Replication slots не дозволяють видаляти WAL; якщо репліка недоступна або повільна, WAL накопичується на первинному, поки диск не почне панікувати.

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

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

Це порядок, який найшвидше виявляє вузьке місце в реальних системах. Не імпровізуйте. Ваш живіт — не телескоп.

Спочатку: класифікуйте тип затримки

1. Транспортна затримка? Первинний генерує зміни, репліка їх не отримує досить швидко.
2. Затримка застосування/відтворення? Репліка має дані (relay/WAL отримано), але відтворення відстає.
3. Затримка видимості? Застосовано, але запити все ще бачать старі знімки (довгі транзакції, семантика repeatable read тощо).

По-друге: вирішіть, де росте черга

• MySQL: розмір relay log, стан SQL-потоку, стани аплікаційних воркерів реплікації.
• Postgres: отриманий LSN vs replay LSN, затримка відтворення, конфлікти, статус WAL receiver.

По-третє: знайдіть ресурсне обмеження

• Обмеження I/O: високий await, мало IOPS у запасі, очищення брудних сторінок, тиск WAL/fsync.
• Обмеження CPU: високий CPU, насичення одного ядра (часто потік застосування), витрати на стиснення/декомпресію.
• Блокування/конкуренція: застосування чекає на блокування, DDL або конфлікти hot standby.
• Мережеве обмеження: втрата пакетів, невисока пропускна здатність, неправильно підібрані TCP буфери, крос-регіональна реальність.

По-четверте: оберіть найменш небезпечний важіль

• Віддавайте перевагу змінам у навантаженні (розмір пакетів, розмір транзакцій, індекси, патерни запитів) над «магічними перемикачами».
• Віддавайте перевагу додаванню паралелізму там, де це безпечно (паралельне apply у MySQL, більше I/O), а не «відключенню надійності».
• Віддавайте перевагу перенесенню читань з репліки під час нагонів (feature flags, circuit breakers), ніж дозволяти реплікам тонути.

Практичні завдання: команди, виходи, рішення

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

Завдання 1 (MySQL): Перевірте базовий стан репліки та визначте вузький потік

cr0x@server:~$ mysql -e "SHOW SLAVE STATUS\G" | egrep "Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running|Slave_SQL_Running_State|Last_SQL_Error|Last_IO_Error|Relay_Log_Space"
Seconds_Behind_Master: 187
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Waiting for dependent transaction to commit
Relay_Log_Space: 4294967296
Last_SQL_Error:
Last_IO_Error:

Значення: I/O потік здоровий (ймовірно, транспорт у порядку). SQL потік працює, але заблокований на очікуванні залежного коміту; relay логи величезні. Черга — у застосуванні.

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

Завдання 2 (MySQL): Інспектуйте воркери реплікації (здоров’я паралельного застосування)

cr0x@server:~$ mysql -e "SELECT WORKER_ID, THREAD_ID, SERVICE_STATE, LAST_APPLIED_TRANSACTION, LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP, APPLYING_TRANSACTION FROM performance_schema.replication_applier_status_by_worker\G"
*************************** 1. row ***************************
WORKER_ID: 1
THREAD_ID: 52
SERVICE_STATE: ON
LAST_APPLIED_TRANSACTION: 0f3a2b1c-11aa-11ee-9d5b-0800272b3f7a:912334
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2025-12-31 11:58:01.123456
APPLYING_TRANSACTION: 0f3a2b1c-11aa-11ee-9d5b-0800272b3f7a:912390
*************************** 2. row ***************************
WORKER_ID: 2
THREAD_ID: 53
SERVICE_STATE: ON
LAST_APPLIED_TRANSACTION: 0f3a2b1c-11aa-11ee-9d5b-0800272b3f7a:912333
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2025-12-31 11:58:01.120000
APPLYING_TRANSACTION:

Значення: Деякі воркери простоюють, тоді як один застосовує. Паралелізм є, але він неефективний (навантаження не паралелізується або заблоковано порядком комітів).

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

Завдання 3 (MySQL): Визначте нинішнє очікування SQL-потоку (блокування проти I/O проти коміту)

cr0x@server:~$ mysql -e "SHOW PROCESSLIST" | awk 'NR==1 || $4 ~ /Connect|Binlog Dump|SQL/ {print}'
Id	User	Host	db	Command	Time	State	Info
14	system user		replication	Connect	187	Waiting for relay log		NULL
15	system user		replication	Connect	187	Waiting for dependent transaction to commit	NULL
102	app_ro	10.0.2.55:61234	prod	Query	32	Sending data	SELECT ...

Значення: SQL-аплайн застосування репліки чекає на залежні коміти; один запит на читання триває 32с і може спричиняти зміну кешу або тиск блокувань.

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

Завдання 4 (MySQL): Підтвердіть формат binlog та row image (контроль обсягу binlog/WAL)

cr0x@server:~$ mysql -e "SHOW VARIABLES WHERE Variable_name IN ('binlog_format','binlog_row_image','sync_binlog','innodb_flush_log_at_trx_commit')"
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| binlog_format                | ROW       |
| binlog_row_image             | FULL      |
| innodb_flush_log_at_trx_commit | 1       |
| sync_binlog                  | 1         |
+------------------------------+-----------+

Значення: Налаштування надійності (добре), але потенційно великий обсяг binlog, якщо FULL row image на широких рядках.

Рішення: Розгляньте binlog_row_image=MINIMAL лише якщо ви перевірили сумісність інструментів і безпеку реплікації для вашого навантаження; інакше оптимізуйте схему і розмір транзакцій.

Завдання 5 (MySQL): Перевірте тиск flush InnoDB на репліці

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty'; SHOW GLOBAL STATUS LIKE 'Innodb_data_pending_fsyncs'; SHOW GLOBAL STATUS LIKE 'Innodb_os_log_pending_fsyncs';"
+--------------------------------+--------+
| Variable_name                  | Value  |
+--------------------------------+--------+
| Innodb_buffer_pool_pages_dirty | 184223 |
+--------------------------------+--------+
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Innodb_data_pending_fsyncs| 67    |
+---------------------------+-------+
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Innodb_os_log_pending_fsyncs | 29 |
+---------------------------+-------+

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

Рішення: Додайте IOPS (швидші диски, краще профілюване сховище), налаштуйте flush-логіку або зменшіть write amplification (індекси, батчинг транзакцій). Не «виправляйте» це відключенням fsync, якщо ви не любите вправи з втрати даних.

Завдання 6 (Postgres): Перевірте отриманий vs відтворений LSN (транспорт проти відтворення)

cr0x@server:~$ psql -x -c "SELECT now() AS ts, pg_last_wal_receive_lsn() AS receive_lsn, pg_last_wal_replay_lsn() AS replay_lsn, pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS bytes_pending;"
-[ RECORD 1 ]--+------------------------------
ts             | 2025-12-31 12:00:12.1122+00
receive_lsn    | 3A/9F1200A0
replay_lsn     | 3A/9E8803D8
bytes_pending  | 58982400

Значення: Репліка отримує WAL, але відтворення відстає на ~56MB. Транспорт у порядку; черга — у відтворенні.

Рішення: Перевірте CPU/I/O на репліці та конфлікти hot standby. Якщо bytes_pending росте в пікові години, потрібна більша пропускна здатність відтворення або менше WAL-інтенсивних записів.

Завдання 7 (Postgres): Виміряйте затримку за часом, яку бачить система

cr0x@server:~$ psql -x -c "SELECT now() AS ts, pg_last_xact_replay_timestamp() AS last_replay_ts, now() - pg_last_xact_replay_timestamp() AS replay_delay;"
-[ RECORD 1 ]---+------------------------------
ts              | 2025-12-31 12:00:20.004+00
last_replay_ts  | 2025-12-31 11:56:59.771+00
replay_delay    | 00:03:20.233

Значення: Близько 3м20с затримки відтворення. Це ближче до того, що відчувають додатки, ніж байти.

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

Завдання 8 (Postgres): Перевірте статус WAL receiver та мережеві симптоми

cr0x@server:~$ psql -x -c "SELECT status, receive_start_lsn, received_lsn, latest_end_lsn, latest_end_time, conninfo FROM pg_stat_wal_receiver;"
-[ RECORD 1 ]---+---------------------------------------------
status          | streaming
receive_start_lsn | 3A/9B000000
received_lsn    | 3A/9F1200A0
latest_end_lsn  | 3A/9F1200A0
latest_end_time | 2025-12-31 12:00:18.882+00
conninfo        | host=10.0.1.10 port=5432 user=replicator ...

Значення: Streaming здоровий; latest_end_time актуальний. Транспорт не є вузьким місцем.

Рішення: Припиніть звинувачувати «мережу». Дивіться обмеження відтворення: сховище, CPU, конфлікти і чекпоінти.

Завдання 9 (Postgres): Виявлення конфліктів hot standby і скасувань

cr0x@server:~$ psql -c "SELECT datname, confl_snapshot, confl_lock, confl_bufferpin, confl_deadlock FROM pg_stat_database_conflicts;"
  datname  | confl_snapshot | confl_lock | confl_bufferpin | confl_deadlock
-----------+----------------+------------+-----------------+----------------
 postgres  |              0 |          0 |               0 |              0
 prod      |            144 |          3 |              12 |              0

Значення: Конфлікти реальні на prod; конфлікти знімків поширені, коли запити на читання довго виконуються, а відтворення має видалити старі версії рядків.

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

Завдання 10 (Postgres): Визначте довгі транзакції на репліці, що спричиняють конфлікти чи роздування

cr0x@server:~$ psql -x -c "SELECT pid, usename, state, now() - xact_start AS xact_age, wait_event_type, wait_event, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start ASC LIMIT 5;"
-[ RECORD 1 ]----+---------------------------------------------
pid             | 21844
usename         | app_ro
state           | active
xact_age        | 00:18:41.772
wait_event_type | Client
wait_event      | ClientRead
query           | SELECT ... FROM big_table JOIN ...

Значення: 18-хвилинна транзакція на hot standby — магніт для конфліктів і ризик уповільнення відтворення.

Рішення: Убивайте її під час інциденту, потім виправляйте патерн: застосуйте statement timeouts, перемістіть аналітичні запити з hot standby або використовуйте сховище для аналітики.

Завдання 11 (Система): Перевірте латентність диска, що змушує застосування/відтворення повзти

cr0x@server:~$ iostat -x 1 3
Linux 6.1.0 (db-replica-1) 	12/31/2025 	_x86_64_	(8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.11    0.00    7.88   28.42    0.00   51.59

Device            r/s     w/s   rkB/s   wkB/s  avgrq-sz avgqu-sz await  svctm  %util
nvme0n1         120.0  980.0  6400.0 51200.0     97.4     22.8  21.8   0.9   99.2

Значення: 99% завантаження і 20ms await — це не «добре». Ваша репліка в зв’язці зі сховищем; відтворення/застосування відставатиме.

Рішення: Розширюйте сховище (кращі диски, більше IOPS), зменшуйте write amplification (індекси, батчинг) або переносіть репліку на окремі томи для WAL/relay логів і даних, де це можливо.

Завдання 12 (Система): Перевірте здоров’я мережі (бо іноді це саме мережа)

cr0x@server:~$ mtr -r -c 20 10.0.1.10
Start: 2025-12-31T12:02:00+0000
HOST: db-replica-1               Loss%   Snt   Last   Avg  Best  Wrst StDev
  1.|-- 10.0.2.1                  0.0%    20    0.4   0.5   0.3   1.2   0.2
  2.|-- 10.0.10.5                 0.0%    20    1.1   1.3   1.0   2.4   0.3
  3.|-- 10.0.1.10                 2.0%    20    2.2   2.4   2.0   6.8   1.1

Значення: 2% втрат і сплески джиттера можуть абсолютно створити транспортну затримку та ретрансмісії, особливо під час тривалих потоків.

Рішення: Якщо Postgres/MySQL показують прогалини в отриманні, виправляйте шлях (помилки NIC, перевантажений канал, «шумний сусід»). Не налаштовуйте базу даних, щоб компенсувати втрату пакетів — це як знижувати вимоги при наймі, бо крісла скриплять.

Завдання 13 (Postgres): Перевірте replication slots і ризик утримання WAL

cr0x@server:~$ psql -x -c "SELECT slot_name, slot_type, active, restart_lsn, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes FROM pg_replication_slots;"
-[ RECORD 1 ]---+------------------------------
slot_name      | replica_1
slot_type      | physical
active         | f
restart_lsn    | 3A/12000000
retained_bytes | 107374182400

Значення: Слот неактивний і утримує ~100GB WAL. Диск первинного вузла тепер — таймер зворотного відліку.

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

Завдання 14 (MySQL): Перевірте темп зростання relay log та використання диска (щоб уникнути «репліка померла» як побічного квесту)

cr0x@server:~$ du -sh /var/lib/mysql/*relay* 2>/dev/null; df -h /var/lib/mysql
4.2G	/var/lib/mysql/mysql-relay-bin.000123
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1p1  400G  362G   18G  96% /var/lib/mysql

Значення: Relay логи великі і диск майже повний. Якщо він досягне 100%, MySQL матиме дуже поганий день і забере з собою інцидент.

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

Завдання 15 (Postgres): Перевірте, чи чекпоінти не занадто часті (тиск WAL і I/O-сплески)

cr0x@server:~$ psql -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
------------------+-----------------+-----------------------+----------------------+--------------------
              112 |             389 |               9823412 |              4412231 |           18442201

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

Рішення: Налаштуйте параметри чекпоінтів і сховище; зменшіть джерела сплесків WAL (масові оновлення, частота перестроювань індексів). Підтверджуйте зміни виміряним I/O і поведінкою відтворення, а не відчуттями.

Це понад десяток завдань. Використовуйте їх як чекліст, а не як буфет.

Причини затримки, які справді мають значення (за підсистемами)

1) Форма транзакцій: великі коміти, багато комітів і тиранія «одного гарячого рядка»

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

• Великі транзакції створюють сплески затримки: репліка має застосувати великий блок, перш ніж затримка покращиться.
• Багато дрібних транзакцій створюють постійний оверхед: fsync, отримання блокувань, оновлення btree і облік комітів домінують.
• Гарячі рядки (лічильники, last_seen, один акаунт) створюють ланцюги залежностей, які паралельне застосування не може розірвати.

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

2) Write amplification: індекси, зовнішні ключі та «корисні» обмеження

Кожен запис — це не один запис. Це маленька процесія: оновлення хіпу/сторінки таблиці, оновлення індексів, WAL/binlog, скидання сторінок, метадані і інколи додаткові читання для перевірки обмежень.

Якщо репліки відстають під час інтенсивних записів, ваш перший підозрюваний — write amplification, а не налаштування реплікації.

3) Фізика сховища: латентність перемагає пропускну здатність

Застосування/відтворення реплікації — це навантаження з великою кількістю записів і неприємними синхронізаціями. Низька латентність важливіша за вражаючі послідовні показники пропускної здатності.

• Високий await у iostat часто прямо корелює з ростом затримки.
• Хмарні томи з кредитами на сплески можуть «виглядати добре», поки не закінчаться, після чого затримка зростає і не відновлюється.
• Змішані навантаження (реплікація + аналітичні читання) можуть руйнувати кеші і спричиняти випадкове I/O-ускладнення.

4) Трафік читань на репліках: фальшива «безкоштовна їжа»

Обслуговування читань з реплік звучить як безкоштовна ємність. На практиці це проблема конкуренції за ресурси:

• Інтенсивні читання спричиняють зміну кешу, змушуючи більше дискових операцій для відтворення/застосування.
• Довгі читання в Postgres hot standby можуть викликати конфлікти з відтворенням.
• У MySQL читання конкурують за buffer pool і I/O; також можуть виникати метадані блокування і DDL-інтеракції.

Правило: репліка, призначена для свіжості, не повинна бути одночасно вашою ad-hoc аналітичною майданчиком.

5) Мережа: латентність, втрата пакетів і крос-регіональні фантазії

Реплікація між регіонами — це не питання тюнінгу; це фізика й імовірності. Втрата викликає ретрансмісії; затримка розтягує зворотні петлі; джиттер створює мікросплески.

Якщо ви реплікуєте через публічний інтернет (або завантажений VPN), ви не керуєте базою даних; ви керуєте мережевим експериментом.

Специфічні виправлення для MySQL і їх компроміси

Паралельна реплікація: допомагає, але не чарівна

Сучасний MySQL підтримує паралельне застосування, але ефективність залежить від версії та налаштувань. Також навантаження має дозволяти паралелізм (різні схеми, незалежні транзакції).

Робіть: увімкніть і перевірте паралельні воркери; моніторьте їхнє завантаження.

Уникайте: «просто підняти кількість воркерів до 64» без вимірювання блокувань і порядку комітів. Ви отримаєте оверхед і ту ж затримку.

Формат binlog і row image: контролюйте обсяг

Рядкове логування безпечніше для більшості продакшен-навантажень, але воно може генерувати багато binlog-даних — особливо для широких рядків і FULL row image.

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

Розгляньте: binlog_row_image=MINIMAL лише після ретельних перевірок сумісності (downstream tooling, CDC та власні потреби з відладки).

Схема і патерни запитів, дружні до реплік

• Тримайте вторинні індекси навмисними. Кожен додатковий індекс — це додаткова робота apply на кожній репліці.
• Віддавайте перевагу ідемпотентним записам і уникайте read-modify-write циклів, що створюють гарячі точки.
• Пакетуйте фоні задачі і уникайте «дрібних комітів назавжди».

Налаштування надійності — не план продуктивності

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

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

Специфічні виправлення для PostgreSQL і їх компроміси

Конфлікти hot standby: оберіть сторону

На hot standby відтворення має застосувати зміни, що можуть видаляти версії рядків або блокувати ресурси. Довгі запити можуть конфліктувати з цим відтворенням.

Postgres дає вам вибір, а не чудо:

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

Що робити: чітко визначте роль кожної репліки. «Ця репліка для дашбордів, до 5 хвилин застаріла» — цілком валідне продуктовет рішення. Претензія на реальний час — шлях до інцидентів.

Управління обсягом WAL: непоказна перемога

WAL — це рахунок за вашу поведінку запису. Зменшуйте платіж:

• Уникайте повних переписів таблиць у пікові години.
• Будьте обережні з масовими оновленнями; часто INSERT-into-new-table + swap працює краще операційно, хоча змінює патерн WAL також.
• Розгляньте налаштування autovacuum і обслуговування таблиць, щоб зменшити роздування (яке інакше збільшує I/O під час відтворення).

Replication slots: запобіжники з зубами

Слоти не дозволяють видаляти WAL, поки споживач його потребує. Чудово для логічної реплікації і надійних стендбаїв. Також чудово для заповнення дисків, коли споживач зникає.

Операційне правило: якщо ви використовуєте слоти, ви мусите мати алерти на розмір утримуваного WAL і процедуру «чи безпечно видалити цей слот?»

Синхронна реплікація: коли потрібно, і що ви платите

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

Поширені помилки: симптом → корінна причина → фікс

1) «Seconds behind master» низький, але користувачі все ще бачать застарілі дані

Симптом: MySQL показує низьку затримку, але додаток читає старі дані.

Корінна причина: Ви вимірюєте не те. Оцінка на основі міток часу може вводити в оману під час періодів простою, або ваш додаток робить read-after-write без стікінесу.

Фікс: Реалізуйте read-your-writes: направляйте до первинного після запису, або використовуйте сесійну стікінес, або відстежуйте GTID/LSN і чекайте, поки репліка наздожене.

2) Затримка зростає щодня, потім «випадково» скидається після обслуговування

Симптом: Репліки з часом стають повільніші; рестарти або відпустки «вирішують» це.

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

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

3) Postgres репліка скасовує запити, і дашборди кричать

Симптом: Поява «canceling statement due to conflict with recovery»; користувачі незадоволені.

Корінна причина: Конфлікт hot standby: довгі знімкові читання конфліктують з очищенням/блокуваннями відтворення.

Фікс: Перемістіть довгі запити, додайте statement_timeout на репліці; налаштуйте параметри hot standby відповідно до ролі. Не ставте BI-навантаження на стендбай, орієнтований на свіжість.

4) Диск первинного Postgres заповнюється WAL несподівано

Симптом: Каталог WAL швидко росте; тиск на диск; replication slot утримує величезні байти.

Корінна причина: Неактивний або повільний споживач із replication slot; WAL не можна переробити.

Фікс: Відновіть споживача або видаліть слот після перевірки. Додайте моніторинг і політику життєвого циклу слотів.

5) Репліка MySQL «працює», але застосування ніколи не наздоганяє після піку

Симптом: I/O потік у порядку, SQL потік у порядку, затримка росте і залишається високою.

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

Фікс: Збільшіть ресурси репліки (IOPS, CPU), налаштуйте паралельну реплікацію там, де має сенс, зменшіть write amplification і розділіть навантаження між репліками з чіткими ролями.

6) Ви масштабуєте репліки і затримка стає гіршою

Симптом: Додавання реплік збільшує навантаження на первинний і затримку реплік.

Корінна причина: Кожна репліка додає оверхед відправника реплікації (мережа, WAL sender, binlog dump потоки) і може збільшити тиск на fsync/лог залежно від конфігурації.

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

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

Міні-історія #1: Інцидент через неправильне припущення (репліки для читання — «безпечні»)

Середня SaaS-компанія мала акуратний архітектурний слайд: запис на первинний, читання з реплік. У них також була функція «миттєвий експорт», яку користувачі могли запускати одразу після зміни налаштувань.

Хтось припустив, що налаштування можна читати з репліки, бо «це просто конфігурація». Процес експорту створював запис «started» на первинному, потім читав налаштування з репліки і генерував результат на основі того, що знайшов.

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

Виправлення не було юзер-френдлі. Вони реалізували read-your-writes: після зміни налаштувань сесію користувача прив’язували до первинного на короткий час. Також маркували репліки за ролями: «свіжість» vs «аналітика». Функція експорту перестала марити про «дешеві» читання.

Міні-історія #2: Оптимізація, що обернулася проти команди (більші батчі — більший біль)

Платформа платежів працювала на MySQL з репліками. Нічна задача оновлювала статуси батчами. Затримка була образливою, але керованою. Хтось помітив, що робота витрачає багато часу на коміти, і «оптимізував» збільшивши розмір батчів радикально.

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

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

Нудна правда: вони оптимізували оверхед комітів первинного і проігнорували механіку apply реплік. Виправлення — підібрати розмір батчу до виміряного «золото-середнього»: досить малий, щоб apply був плавним, і досить великий, щоб уникнути смерті від великої кількості комітів. Додали обмеження максимальної транзакції і «аварійний вимикач», що призупиняв завдання, коли затримка реплік перевищувала поріг.

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

Рітейл-компанія працювала на Postgres з двома стендбай. Один був «свіжим стендбай» з агресивним скасуванням конфліктуючих запитів; інший — «репортинг стендбай», де довгі читання дозволені і затримка очікувана.

Під час пікового сезону розробник запустив важкий звіт. Він потрапив на репортинг стендбай, як і планувалось. Той стендбай відстав на хвилини, але ніхто не панікував, бо його SLO це дозволяв. Свіжий стендбай залишався близьким до реального часу, бо не обслуговував звіт.

Потім розгортання внесло масову міграцію записів. Відтворення на свіжому стендбаї почало відставати. На виклику інженери дотрималися рунбуку: перевірити receive vs replay LSN, перевірити iostat, підтвердити відсутність конфліктів запитів, тимчасово перенаправити «чутливі до застарілості» читання на первинний. Вони не дискутували в каналі 45 хвилин. Вони діяли.

Бізнес-імпакт був мінімальним. Не тому що у них ідеальна реплікація, а тому що вони мали визначені ролі реплік, вимірювані очікування і навчений план. Нудна практика врятувала день. Знову.

Чеклісти / покроковий план

Чекліст A: Коли затримка зростає просто зараз (реагування на інцидент)

1. Підтвердіть охоплення: одна репліка чи всі? MySQL чи Postgres? один регіон чи крос-регіон?
2. Класифікуйте затримку: транспорт, застосування чи видимість (використовуйте завдання вище).
3. Захистіть коректність: направляйте критичні читання на первинний; увімкніть read-your-writes.
4. Зменшіть навантаження на репліку: зупиніть аналітику, призупиніть фоні задачі, обмежте пакетні записи.
5. Перевірте латентність сховища: якщо await високий і %util майже повний, виправляйте інфраструктуру або зменшуйте записи.
6. Шукайте одного великого порушника: велика транзакція, перестроювання індексу, міграція або конфлікт vacuum.
7. Зробіть найменшу безпечну зміну: тимчасове перенаправлення, пауза задачі, вбивство запиту, додавання ресурсу.
8. Документуйте хронологію: коли почалася затримка, що корелювало (деплой, задача, трафік, події сховища).

Чекліст B: Щоб затримка не стала вашим брендом (інженерний план)

1. Визначте ролі реплік: репліка для свіжості, репліка для звітності, репліка DR.
2. Встановіть бюджети затримки: прийнятні секунди/хвилини для кожної ролі; повідомте про це продукт.
3. Інструментуйте канал реплікації:
   • MySQL: зростання relay log, стани воркерів, швидкість застосування.
   • Postgres: отриманий vs replay LSN, затримка за timestamp, лічильники конфліктів, утримання слотів.
4. Побудуйте безпеку додатка: read-your-writes, токени консистентності (GTID/LSN), fallback на первинний.
5. Контролюйте write amplification: гігієна індексів, уникайте широких рядків, уникайте марних оновлень.
6. Плануйте вікна обслуговування: масові операції не в піку; обмежуйте міграції і backfill.
7. Ємність з запасом: IOPS і CPU мають бути розраховані для піку + наздоганяння, а не для середнього.
8. Практикуйте відновлення: затримка взаємодіє з failover неприємними способами; репетируйте це.

Чекліст C: Схеми і патерни навантаження, що зменшують затримку за дизайном

• Замініть «постійне оновлення одного рядка» на append-only події + періодичну компактацію.
• Пакетуйте записи до стабільного розміру; уникайте обох крайнощів (дрібні коміти і монстр-коміти).
• Тримайте вторинні індекси навмисними; видаляйте ті, що «можливо знадобляться колись».
• У Postgres уникайте довгих транзакцій на hot standby; вони коштують свіжості або надійності.

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

1) Чи надійне вимірювання затримки реплікації в MySQL?

Воно корисне, але не є євангелієм. Seconds_Behind_Master — це оцінка на основі міток часу і може вводити в оману під час періодів простою або при певних навантаженнях. Дивіться також зростання relay log і стани apply.

2) Чи легше розуміти затримку в Postgres?

Зазвичай так. Метрики на основі LSN дозволяють відокремити отримання від відтворення. Лаг за часом через pg_last_xact_replay_timestamp() також практичний, хоча повертає NULL, якщо ще не відтворено жодної транзакції.

3) Чи варто запускати аналітику на репліках?

Так, але не на тій самій репліці, від якої ви очікуєте свіжість. Виділіть аналітичну репліку (або систему). Інакше ви обміняєте «менше запитів на первинному» на «репліка відстає і коректність стає дивною».

4) Чи зменшить додавання більше реплік затримку?

Ні. Репліки зменшують навантаження на первинний (іноді). Затримка залежить від здатності репліки отримувати й застосовувати. Більше реплік може збільшити навантаження первинного і мережі.

5) Який найшвидший безпечний спосіб зменшити вплив на користувача під час затримки?

Направляйте чутливі до застарілості читання на первинний і зберігайте репліки для некритичних читань. Реалізуйте read-your-writes після записів. Це рівень безпеки додатку, що працює незалежно від бренду БД.

6) Чи може паралельна реплікація «вирішити» затримку в MySQL?

Вона може значно допомогти, коли транзакції незалежні. Вона мало допоможе з ланцюгами залежностей (гарячі рядки, конкуренція в одній таблиці, суворий порядок комітів). Розглядайте її як множник для навантаження, яке вже паралелізується.

7) Чому Postgres іноді скасовує запити на репліці?

Через конфлікти hot standby: відтворення має застосувати зміни, що конфліктують зі знімком запиту або потрібними блокуваннями. Залежно від налаштувань Postgres або затримує відтворення (більше лагу), або скасовує запити (більше збоїв запитів). Обирайте усвідомлено.

8) Чи обов’язкові replication slots в Postgres?

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

9) Чи варто використовувати синхронну реплікацію, щоб усунути затримку?

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

10) Яка найпоширеніша корінна причина в продакшені?

Латентність сховища під навантаженням записів, підсилена вибором схеми (занадто багато індексів) і змішаними навантаженнями на репліках (аналітика + свіжість). Бази даних зазвичай отримують звинувачення; графіки I/O тихо кажуть правду.

Висновок: наступні кроки, що не поставлять вас у незручне становище

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

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

1. Реалізуйте read-your-writes для коректності користувача. Припиніть вдавати, що репліки миттєво консистентні.
2. Розділіть ролі реплік: одна для свіжості, одна для звітності, одна для DR. Дайте кожній бюджет затримки і дотримуйтеся його.
3. Інструментуйте канал реплікації з метриками отримання vs застосування/відтворення, а не тільки одним числом затримки.
4. Виправте резерви сховища: якщо диски репліки вичавлені, інші речі не мають значення.
5. Аудитуйте write amplification: індекси, пакетні задачі і гарячі рядки. Більшість «проблем реплікації» починаються у схемі та навантаженні, а не в механіці реплікації.
6. Напишіть рунбук і відрепетируйте його. Найгірший час дізнатися, яка метрика брешуть — о 3:00 на свято.

MySQL і PostgreSQL обидва добре реплікують — коли ви ставите реплікацію в ряд важливих виробничих систем, а не як чекбокс. Затримка, яку ви терпите — це продуктове рішення. Затримка, яку ви не вимірюєте — це кадрове рішення.