Memecahkan masalah lag replikasi log biner untuk Aurora MySQL

Bagian ini memberikan panduan tentang lag replikasi log biner untuk Aurora MySQL yang dikonfigurasi sebagai replika log biner. Ini mencakup arsitektur replikasi, replikasi paralel, pelacakan ketergantungan, pemantauan, dan praktik terbaik konfigurasi.

Replikasi log biner mereplikasi data antar MySQL-compatible database. Replikasi log biner yang tercakup dalam bagian ini tidak sinkron: sumber tidak menunggu replika untuk mengkonfirmasi penerapan perubahan sebelum melakukan transaksi. Ini tidak berlaku untuk replikasi semi-sinkron. Dalam replikasi semi-sinkron, sumber menunggu setidaknya satu replika untuk mengakui penerimaan transaksi sebelum melakukan. Misalnya, cluster Multi-AZ DB untuk Amazon RDS untuk MySQL menggunakan replikasi semi-sinkron, yang berada di luar cakupan untuk bagian ini. Replika mempertahankan koneksi persisten ke sumber melalui utas untuk I/O terus mengalirkan peristiwa log biner. Bagian ini berlaku untuk semua topologi replikasi binlog di mana Aurora MySQL adalah replika. Sumbernya bisa berupa cluster Aurora MySQL lainnya, Amazon RDS untuk MySQL, MySQL lokal, atau MySQL di Amazon EC2. Bagian ini juga berlaku ketika Aurora MySQL adalah sumber yang mereplikasi ke MySQL-compatible target apa pun.

Mengalami lonjakan lag sekarang? Lewati materi latar belakang dan mul Mengidentifikasi hambatan lag replikasi ailah dengan menentukan apakah I/O thread atau utas SQL adalah hambatan, lalu ikuti tautan untuk skenario itu: Memecahkan I/O masalah lag utas atau. Memecahkan masalah lag utas SQL

Arsitektur replikasi MySQL

Replikasi MySQL diimplementasikan melalui utas khusus:

Proses replikasi bekerja sebagai berikut:

Mengidentifikasi hambatan lag replikasi

Jeda replikasi dapat terjadi di dua area: I/O thread atau utas SQL. Langkah pertama adalah menentukan komponen mana yang tertinggal.

Source_Log_File:       mysql-bin.000045
Read_Source_Log_Pos:   524288000
Relay_Source_Log_File: mysql-bin.000045
Exec_Source_Log_Pos:   524200000

File:     mysql-bin.000045
Position: 1073741824

Memecahkan I/O masalah lag utas

Th I/O read bertanggung jawab untuk mengambil peristiwa log biner dari sumber dan menuliskannya ke log relay. Penyebab dan resolusi umum:

Strategi pengoptimalan untuk I/O lag utas

Memecahkan masalah lag utas SQL

Utas SQL adalah hambatan ketika utas ter I/O jebak dengan sumbernya tetapi berkembangSeconds_Behind_Source. Untuk mengukur lag, pantau lebih dari jendela 15 menit. Bandingkan tingkat pembuatan binlog pada sumber (Positiondelta dariSHOW MASTER STATUS) dengan tingkat penerapan utas SQL pada replika (Exec_Source_Log_Posdelta).

Penyebab dan resolusi umum:

SELECT t.table_schema, t.table_name
FROM information_schema.tables t
  LEFT JOIN information_schema.table_constraints tc
    ON t.table_schema = tc.table_schema
    AND t.table_name = tc.table_name
    AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_type IS NULL
  AND t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY 1;

SELECT trx_id, trx_started, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
WHERE trx_rows_modified > 10000
ORDER BY trx_started;

SELECT database_name, table_name,
    MIN(last_update) AS oldest_stat_update,
    DATEDIFF(NOW(), MIN(last_update)) AS stats_age_in_days
FROM mysql.innodb_index_stats
WHERE database_name NOT IN ('mysql', 'sys')
GROUP BY database_name, table_name
HAVING stats_age_in_days > 7
ORDER BY stats_age_in_days DESC;

Kurangi beban kerja terapan dengan filter replikasi sisi replika

Aurora MySQL versi 3.01.0 dan yang lebih tinggi mendukung filter replikasi sisi replika. Gunakan ini untuk melewati database atau tabel yang tidak relevan selama penerapan, mengurangi beban kerja thread SQL. Replica-side filter diterapkan oleh utas SQL — utas masih I/O mengambil semua peristiwa log biner dari sumbernya, jadi filter ini mengurangi pekerjaan terapan saja, bukan transfer jaringan. Tidak seperti filter sisi sumber (yang beroperasi pada tingkat database saja), filter sisi replika mendukung granularitas tingkat tabel menggunakan dan. replicate-do-table replicate-ignore-table Untuk informasi selengkapnya, lihat Mengonfigurasi filter replikasi dengan Aurora MySQL.

Multi-threaded replikasi (MTR)

Dengan MTR, replika menerapkan transaksi independen secara paralel. Itu tidak dapat membagi satu transaksi besar menjadi bagian-bagian paralel. Tingkat paralelisme dibatasi oleh informasi ketergantungan yang ditulis oleh sumber.

Ketika MTR diaktifkan (replica_parallel_workers >= 2), aplikator SQL dibagi menjadi utas koordinator dan beberapa utas pekerja. Koordinator membaca transaksi dari log relay. Ini memeriksa stempel waktu logis (last_committeddansequence_number) yang disematkan sumber dalam setiap transaksi. Kemudian menetapkan transaksi independen ke utas pekerja yang tersedia untuk eksekusi paralel. Dua transaksi independen jika mereka tidak berbagi dependensi data - yaitu, jika mereka memodifikasi baris yang berbeda. Sumber menentukan dependensi ini pada waktu komit menggunakan metode pelacakan ketergantungan yang dikonfigurasi dan merekamnya dalam log biner. Replika tidak dapat meningkatkan paralelisme di luar apa yang diizinkan sumber.

MTR adalah default yang disarankan

Sebaiknya jalankan replika dengan MTR diaktifkan. MTR diaktifkan secara default (replica_parallel_workers=4) di MySQL 8.0.27 dan yang lebih tinggi, termasuk versi Aurora MySQL 3 berdasarkan rilis tersebut. Pada versi sebelumnya (Aurora MySQL 2.12.1 dan yang lebih tinggi, atau versi berdasarkan rilis MySQL sebelum 8.0.27), aktifkan secara eksplisit dengan menyetel replica_parallel_workers nilai 2 atau lebih. Menjaga MTR tetap aktif membutuhkan biaya sedikit ketika paralelisme tidak tersedia dan memungkinkan replika memanfaatkannya kapan pun beban kerja memungkinkan.

MTR tidak mengurangi lag dalam kasus berikut:

Pelacakan ketergantungan pada sumbernya

Sumber menentukan transaksi mana yang dapat diterapkan secara paralel menggunakan binlog_transaction_dependency_tracking parameter. Nilai yang disarankan:WRITESET.

Jenis paralel (replica_ parallel_type)

replica_parallel_typeParameter menentukan bagaimana transaksi didistribusikan di antara utas pekerja. Ada dua opsi:

Konfigurasi MTR

Setelah mengubah pengaturan pekerja paralel, mulai ulang replikasi:

CALL mysql.rds_stop_replication;
CALL mysql.rds_start_replication;

Aurora-specific optimasi replikasi

Aurora MySQL menyediakan fitur-fitur berikut untuk meningkatkan kinerja replikasi log biner. Tabel berikut merangkum ketersediaan, status default, dan cara mengaktifkan setiap fitur.

Memantau replikasi paralel

Gunakan metode berikut untuk memantau kinerja replikasi dan mengidentifikasi hambatan secara paralel berlaku:

Skema Performa

Tabel kunci untuk memantau MTR:

Untuk mengevaluasi seberapa merata pekerjaan didistribusikan di seluruh utas pekerja, gunakan kueri berikut:

SELECT
  a.channel_name,
  rw1.thread_id AS replica_worker_thread_id,
  ts1.count_star AS number_of_trx_executed,
  ROUND((ts1.count_star / sum_count_star) * 100, 2) AS percent_of_trx_executed_by_worker
FROM (
  SELECT rw.channel_name, SUM(ts.count_star) AS sum_count_star
  FROM performance_schema.events_transactions_summary_by_thread_by_event_name AS ts
    JOIN performance_schema.replication_applier_status_by_worker AS rw
      ON ts.thread_id = rw.thread_id
  GROUP BY rw.channel_name
) AS a
JOIN performance_schema.replication_applier_status_by_worker AS rw1
  ON a.channel_name = rw1.channel_name
JOIN performance_schema.events_transactions_summary_by_thread_by_event_name AS ts1
  ON rw1.thread_id = ts1.thread_id;

Jika satu atau dua pekerja menangani sebagian besar transaksi, ini menunjukkan ketergantungan transaksi yang tinggi yang membatasi paralelisme. Pertimbangkan untuk beralih ke pelacakan ketergantungan WRITESET pada sumber.

Jika posisi I/O thread dan utas SQL dekat satu sama lain tetapi Seconds_Behind_Source masih berkembang, utas koordinator itu sendiri mungkin menjadi hambatan. Periksa performance_schema.replication_applier_status_by_coordinator penundaan buffering. Kemacetan koordinator biasanya menunjukkan dependensi transaksi yang berat atau utas koordinator tunggal yang kelebihan beban yang tidak dapat mendistribusikan peristiwa dengan cukup cepat.

Statistik replika multi-utas log kesalahan

Ketika log_error_verbosity=3 (default), Aurora MySQL menulis statistik replika multi-thread ke log kesalahan MySQL secara berkala. Anda dapat melihat entri ini dengan:

Untuk menemukan entri statistik replika multi-utas di log kesalahan, cari string yang ditunjukkan dalam contoh berikut. Log kesalahan MySQL menggunakan terminologi lama dalam string internal ini; dokumentasi ini menggunakan istilah yang disukai “replika” di seluruh.

Berikut ini adalah contoh entri log kesalahan:

2026-05-10 14:54:28.045308 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 7215200; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 1203644200 waited (count) when Workers occupied = 1022462 waited when Workers occupied = 62349203500

Bidang utama untuk dianalisis:

Contoh kerja: mendiagnosis hambatan penerapan paralel

Contoh berikut menunjukkan cara menggunakan sumber pemantauan sebelumnya untuk mendiagnosis hambatan penerapan paralel umum. Setiap contoh dimulai dari gejala yang sama - Seconds_Behind_Source terus berkembang dan Anda telah mengonfirmasi bahwa utas SQL adalah hambatan (lihatMengidentifikasi hambatan lag replikasi) - dan menggunakan statistik replika multi-thread dan Skema Kinerja untuk menentukan tindakan korektif.

Contoh 1: Pekerja kekurangan pasokan

Pada CloudWatch tahun, ReplicaLag metrik naik dengan mantap dari mendekati nol menjadi sekitar 400 detik selama 30 menit. Pemanfaatan CPU pada replika tetap sekitar 55%, jadi replika tidak CPU-bound.

Untuk mengonfirmasi di mana lag berada, jalankan SHOW REPLICA STATUS dua kali, terpisah 60 detik. Read_Source_Log_Posmeningkat sekitar 1,2 GB sementara Exec_Source_Log_Pos naik hanya sekitar 300 MB. U I/O tas mengikuti sumbernya, tetapi utas SQL tertinggal - utas SQL adalah hambatannya.

MTR diaktifkan denganreplica_parallel_workers=4. Ambil statistik replika multi-utas terbaru dari log kesalahan (lihatStatistik replika multi-utas log kesalahan):

2026-05-10 14:54:28.045308 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 123; events assigned = 9876544; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 8455000 waited (count) when Workers occupied = 2456789 waited when Workers occupied = 110293847000

Menafsirkan bidang utama:

Diagnosis: koordinator secara konsisten memiliki lebih banyak transaksi independen yang siap diterapkan daripada memiliki pekerja untuk menjalankannya. Karena menunggu konflik jam dapat diabaikan, paralelisme dibatasi oleh jumlah pekerja, bukan oleh dependensi.

Tind akan: replica_parallel_workers tingkatkan jumlah vCPU replika (misalnya, 16 pada adb.r6g.4xlarge) dan mulai ulang replikasi:

CALL mysql.rds_stop_replication;
CALL mysql.rds_start_replication;

Verifikasi: baris statistik selanjutnya menunjukkan penantian hampir hilang, dan ReplicaLag kembali ke bawah 5 detik:

2026-05-10 15:02:14.882201 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 31840552; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 9120000 waited (count) when Workers occupied = 41233 waited when Workers occupied = 5980411000

“menunggu ketika Pekerja diduduki” turun dari sekitar 110 detik menjadi sekitar 6 detik, dan throughput (peristiwa yang ditetapkan) lebih dari tiga kali lipat. Hentikan peningkatan pekerja setelah pemanfaatan CPU mendekati 80% atau penantian berhenti menurun.

Contoh 2: Pelacakan ketergantungan COMMIT_ORDER membuat serial beban kerja konkurensi rendah

Sumbernya adalah instans Amazon RDS untuk MySQL yang menjalankan beban kerja OLTP dengan hanya 4-8 utas aplikasi yang berkomitmen secara bersamaan. Replikanya adalah db.r6g.4xlarge dengan replica_parallel_workers=16 danreplica_parallel_type=LOGICAL_CLOCK. Meskipun 16 pekerja, ReplicaLag tahan stabil sekitar 600 detik dan pemanfaatan CPU replika hanya sekitar 20% - pekerja terlihat menganggur.

Pertama, periksa seberapa merata transaksi didistribusikan di seluruh pekerja menggunakan kueri distribusi pekerja dari: Skema Performa

+--------------+--------------------------+------------------------+-----------------------------------+
| channel_name | replica_worker_thread_id | number_of_trx_executed | percent_of_trx_executed_by_worker |
+--------------+--------------------------+------------------------+-----------------------------------+
|              |                       45 |                1903556 |                             96.41 |
|              |                       46 |                  23104 |                              1.17 |
|              |                       47 |                  18995 |                              0.96 |
|              |                       48 |                  16720 |                              0.85 |
.
.
+--------------+--------------------------+------------------------+-----------------------------------+

Output sebelumnya menunjukkan 4 pertama dari 16 utas pekerja. Satu pekerja menerapkan lebih dari 96% transaksi sementara 15 lainnya hampir menganggur (masing-masing pekerja yang tersisa dieksekusi kurang dari 0,05%). Terapkan secara efektif serial.

Selanjutnya, konfirmasikan ini dengan statistik replika multi-utas di log kesalahan:

2026-05-10 15:22:11.110432 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 2014500; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 98712340000 waited (count) when Workers occupied = 50122 waited when Workers occupied = 1209847000

Ini menunjukkan masalah ketergantungan, bukan masalah kapasitas. Periksa metode pelacakan ketergantungan pada sumber:

SELECT @@global.binlog_transaction_dependency_tracking;

+-------------------------------------------------+
| @@global.binlog_transaction_dependency_tracking |
+-------------------------------------------------+
| COMMIT_ORDER                                    |
+-------------------------------------------------+

Akar penyebab: denganCOMMIT_ORDER, sumber memutuskan transaksi mana yang dapat berjalan secara paralel berdasarkan transaksi mana yang dilakukan bersama dalam komit grup log biner yang sama. Dalam beban kerja konkurensi rendah ini, hanya segelintir thread yang melakukan satu waktu, jadi commit grup sangat kecil. Akibatnya, sumber mencap hampir setiap transaksi dengan last_committed nilai yang sama dengan transaksi sebelumnyasequence_number, menandainya sebagai tergantung pada transaksi sebelumnya. Replika kemudian harus menerapkannya satu demi satu - meskipun mereka memodifikasi baris yang sama sekali tidak terkait - itulah sebabnya 15 dari 16 pekerja duduk diam.

Tindakan: alihkan sumber ke pelacakan ketergantungan tingkat baris, yang tidak bergantung pada waktu komit. Atur secara dinamis dan pertahankan dalam grup parameter cluster (atau DB):

SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;

WRITESETmenghitung hash dari baris yang dimodifikasi setiap transaksi, sehingga transaksi yang menyentuh baris yang berbeda menerima cap waktu independen dan dapat diterapkan secara paralel terlepas dari kapan mereka melakukan. Pastikan semua tabel memiliki kunci utama, karena WRITESET menghasilkan writeset kosong untuk tabel tanpa kunci tersebut (lihat). Memecahkan masalah lag utas SQL Pada MySQL 8.4, WRITESET adalah default dan parameter ini tidak ada lagi.

Verifikasi: setelah perubahan, kueri distribusi pekerja menunjukkan pekerjaan tersebar merata di semua pekerja (menunjukkan 4 pertama dari 16; setiap pekerja sekarang menangani sekitar 6% transaksi):

+--------------+--------------------------+------------------------+-----------------------------------+
| channel_name | replica_worker_thread_id | number_of_trx_executed | percent_of_trx_executed_by_worker |
+--------------+--------------------------+------------------------+-----------------------------------+
|              |                       45 |                 132540 |                              6.30 |
|              |                       46 |                 125610 |                              5.97 |
|              |                       47 |                 129774 |                              6.17 |
|              |                       48 |                 122901 |                              5.84 |
.
.
+--------------+--------------------------+------------------------+-----------------------------------+

dan statistik log kesalahan menunjukkan konflik jam runtuh saat ReplicaLag jatuh mendekati nol:

2026-05-10 15:41:55.204713 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 120; events assigned = 19874100; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 412300000 waited (count) when Workers occupied = 88122 waited when Workers occupied = 7019847000

“menunggu saat konflik jam” turun dari sekitar 99 detik menjadi sekitar 0,4 detik, throughput naik kira-kira sepuluh kali lipat, dan hambatan bergeser dari dependensi ke kapasitas pekerja - di mana Anda dapat menyetel jumlah pekerja seperti pada Contoh 1.

Contoh 3: Kunci primer yang hilang memaksa pemindaian tabel penuh pada replika

ReplicaLagmelonjak selama pekerjaan batch malam hari yang menjalankan DELETE pernyataan besar UPDATE dan, lalu pulih sesudahnya. Selama lonjakan, CPU replika sedang tetapi satu pekerja tampak macet.

Pada replika, jalankan SHOW PROCESSLIST dan lihat thread pekerja replikasi. Satu pekerja tetap di Updating negara bagian dengan pernyataan yang sama selama beberapa detik pada satu waktu:

+----+-------------+------+---------+----------+------+
| Id | User        | db   | Command | State    | Time |
+----+-------------+------+---------+----------+------+
| 12 | system user | app  | Connect | Updating |   38 |
+----+-------------+------+---------+----------+------+

Periksa tabel yang direplikasi mana yang tidak memiliki kunci utama menggunakan kueri deteksi dari: Memecahkan masalah lag utas SQL

+--------------+----------------+
| table_schema | table_name     |
+--------------+----------------+
| app          | events_archive |
+--------------+----------------+

Akar penyebab: dengan logging ROW-based biner, setiap baris dalam DELETE acara UPDATE atau harus ditempatkan pada replika sebelum dapat diterapkan. Jika tabel tidak memiliki kunci primer atau kunci NOT NULL unik, replika melakukan pemindaian tabel lengkap untuk setiap baris yang terpengaruh. Pada tabel multi-juta baris, operasi batch berubah menjadi jutaan pemindaian penuh, dan pekerja yang menerapkannya terhenti — memblokir kemajuan aplikasi dan meningkatkan lag.

Tind akan: tambahkan kunci utama ke tabel. Jika Anda tidak dapat menentukan kunci eksplisit segera, aktifkan Generated Invisible Primary Keys (GIPK) dengan mengatur sql_generate_invisible_primary_key parameter ke ON (tersedia dalam versi Aurora MySQL 3 berdasarkan MySQL 8.0.30 dan yang lebih baru), sehingga tabel baru menerima kunci primer otomatis (lihat). Memecahkan masalah lag utas SQL

Verifikasi: setelah menambahkan kunci utama, pekerja tidak lagi berlama-lamaUpdating, tarif penerapan utas SQL pulih, dan ReplicaLag kembali ke baseline selama jendela batch berikutnya.

Contoh 4: Satu transaksi besar tidak dapat disejajarkan

ReplicaLagmelompat tajam pada waktu yang dapat diprediksi setiap hari, tahan selama beberapa menit, lalu turun kembali mendekati nol. Pelacakan ketergantungan WRITESET diaktifkanreplica_parallel_workers=16,, dan semua tabel memiliki kunci utama, sehingga contoh sebelumnya tidak berlaku.

Selama lonjakan, kueri distribusi pekerja menunjukkan satu pekerja sibuk dan sisanya menganggur, tetapi tidak seperti Contoh 2, log kesalahan tidak menunjukkan konflik jam tinggi atau penantian yang diduduki pekerja:

2026-05-10 02:13:40.551922 [Note] [MY-010559] [Repl] Multi-threaded slave statistics for channel '': seconds elapsed = 95; events assigned = 4120000; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 1530000 waited (count) when Workers occupied = 980 waited when Workers occupied = 210440000

Penantian ketergantungan dan penantian yang diduduki pekerja rendah, namun hanya satu pekerja yang aktif. Ini mengesampingkan kemacetan ketergantungan (Contoh 2) dan kemacetan kapasitas pekerja (Contoh 1).

Periksa pekerja yang sibukperformance_schema.replication_applier_status_by_worker, atau jalankanSHOW PROCESSLIST. Seorang pekerja tunggal menerapkan satu transaksi untuk seluruh durasi lonjakan. Pada sumber, pekerjaan aplikasi menjalankan satu pernyataan besar — misalnya, DELETE FROM orders WHERE created_at < '2025-01-01' mempengaruhi beberapa juta baris — sebagai satu transaksi.

Akar penyebab: MTR paralel di seluruh transaksi independen; itu tidak dapat membagi satu transaksi di antara pekerja. Transaksi besar diterapkan oleh tepat satu pekerja, jadi menambahkan pekerja, beralih ke WRITESET, atau menambahkan kunci utama tidak membantu. Untuk informasi selengkapnya, lihat Multi-threaded replikasi (MTR).

Tind akan: pecahkan operasi besar menjadi batch yang lebih kecil pada sumber (misalnya, hapus beberapa ribu baris per transaksi, dengan jeda singkat di antara batch). Transaksi yang lebih kecil dilakukan secara independen, sehingga replika dapat menyebarkannya ke seluruh pekerja. Jadwalkan pekerjaan massal selama jendela lalu lintas rendah jika memungkinkan.

Verifikasi: setelah melakukan batch pekerjaan, ReplicaLag lonjakan harian menjadi rata, dan kueri distribusi pekerja menunjukkan penyebaran batch di beberapa pekerja, bukan satu.

Memantau praktik terbaik

Praktik terbaik untuk meminimalkan jeda replikasi

Rekomendasi berikut merangkum tindakan utama untuk meminimalkan jeda replikasi. Untuk panduan terperinci tentang setiap topik, ikuti tautan referensi silang.