Share via

Memecahkan masalah latensi replikasi di Azure Database for MySQL - Server Fleksibel

  • Artikel

BERLAKU UNTUK: Azure Database for MySQL - Server Tunggal Database Azure untuk MySQL - Server Fleksibel

Penting

Server tunggal Azure Database for MySQL berada di jalur penghentian. Kami sangat menyarankan Agar Anda meningkatkan ke server fleksibel Azure Database for MySQL. Untuk informasi selengkapnya tentang migrasi ke server fleksibel Azure Database for MySQL, lihat Apa yang terjadi pada Server Tunggal Azure Database for MySQL?

Catatan

Artikel ini mereferensikan istilah yang tidak lagi digunakan Microsoft. Saat istilah dihapus dari perangkat lunak, kami akan menghapusnya dari artikel ini.

Fitur replika baca memungkinkan Anda mereplikasi data dari server Azure Database for MySQL ke server baca-saja. Anda dapat menskalakan beban kerja dengan merutekan kueri baca dan pelaporan dari aplikasi ke server replika. Penyiapan ini mengurangi tekanan pada server sumber dan meningkatkan performa keseluruhan dan latensi aplikasi saat diskalakan.

Replika diperbarui secara asinkron menggunakan teknologi replikasi berbasis posisi file log biner (binlog) asli mesin MySQL. Untuk informasi selengkapnya, lihat Ringkasan konfigurasi replikasi berbasis posisi file binlog MySQL.

Jeda replikasi replika baca sekunder bergantung pada beberapa faktor. Faktor-faktor ini termasuk, tetapi tidak terbatas pada:

  • Latensi jaringan.
  • Volume transaksi di server sumber.
  • Tingkat komputasi server sumber dan server replika baca sekunder.
  • Kueri yang berjalan di server sumber dan server sekunder.

Dalam artikel ini, Anda akan mempelajari cara memecahkan masalah latensi replikasi di Azure Database for MySQL. Anda juga akan mendapatkan gambaran yang lebih baik tentang beberapa penyebab umum peningkatan latensi replikasi pada server replika.

Catatan

Artikel ini berisi referensi ke istilah slave, istilah yang tidak lagi digunakan Microsoft. Saat istilah dihapus dari perangkat lunak, kami akan menghapusnya dari artikel ini.

Konsep replikasi

Ketika log biner diaktifkan, server sumber menulis transaksi yang diterapkan ke dalam log biner. Log biner digunakan untuk replikasi. Log biner diaktifkan secara default untuk semua server yang baru disediakan yang mendukung penyimpanan hingga 16 TB. Pada server replika, dua alur berjalan pada setiap server replika. Satu alur adalah alur IO, dan yang lainnya adalah alur SQL:

  • Alur IO terhubung ke server sumber dan meminta log biner yang diperbarui. Alur ini menerima pembaruan log biner. Pembaruan tersebut disimpan di server replika, dalam log lokal yang disebut log relai.
  • Alur SQL membaca log relai dan kemudian menerapkan perubahan data pada server replika.

Memantau latensi replikasi

Azure Database for MySQL menyediakan metrik untuk jeda replikasi dalam hitungan detik di Azure Monitor. Metrik ini hanya tersedia di server replika baca. Ini dihitung dengan metrik seconds_behind_master yang tersedia di MySQL.

Untuk memahami penyebab peningkatan latensi replikasi, sambungkan ke server replika dengan menggunakan MySQL Workbench atau Azure Cloud Shell. Kemudian jalankan perintah berikut.

Catatan

Di kode Anda, ganti nilai contoh dengan nama server replika dan nama pengguna admin Anda. Nama pengguna admin memerlukan @\<servername> untuk Azure Database for MySQL.

mysql --host=myreplicademoserver.mysql.database.azure.com --user=myadmin@mydemoserver -p

Berikut adalah tampilan pada terminal Cloud Shell:

Requesting a Cloud Shell.Succeeded.
Connecting terminal...

Welcome to Azure Cloud Shell

Type "az" to use Azure CLI
Type "help" to learn about Cloud Shell

user@Azure:~$mysql -h myreplicademoserver.mysql.database.azure.com -u myadmin@mydemoserver -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64796
Server version: 5.6.42.0 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Di terminal Cloud Shell yang sama, jalankan perintah berikut:

mysql> SHOW SLAVE STATUS;

Berikut adalah output khas:

Monitoring replication latency

Output berisi banyak informasi. Biasanya, Anda hanya perlu fokus pada baris yang digambarkan tabel berikut ini.

Metrik Deskripsi
Slave_IO_State Mewakili status alur IO saat ini. Biasanya, statusnya adalah "Menunggu master untuk mengirim peristiwa" jika server sumber (master) disinkronkan. Status seperti "Menyambungkan ke master" menunjukkan bahwa replika kehilangan koneksi ke server sumber. Pastikan server sumber berjalan, atau periksa apakah firewall memblokir koneksi.
Master_Log_File Mewakili file log biner tempat server sumber menulis.
Read_Master_Log_Pos Menunjukkan di mana server sumber menulis dalam file log biner.
Relay_Master_Log_File Mewakili file log biner yang dibaca server replika dari server sumber.
Slave_IO_Running Menunjukkan apakah alur IO berjalan. Nilai ini harus Yes. Jika nilainya NO, maka replikasi kemungkinan rusak.
Slave_SQL_Running Menunjukkan apakah alur SQL berjalan. Nilai ini harus Yes. Jika nilainya NO, maka replikasi kemungkinan rusak.
Exec_Master_Log_Pos Menunjukkan posisi Relay_Master_Log_File yang diterapkan replika. Jika ada latensi, maka urutan posisi ini harus lebih kecil dari Read_Master_Log_Pos.
Relay_Log_Space Menunjukkan ukuran gabungan total dari semua file log relay yang ada. Anda dapat memeriksa ukuran batas atas dengan meminta SHOW GLOBAL VARIABLES seperti relay_log_space_limit.
Seconds_Behind_Master Menampilkan latensi replikasi dalam hitungan detik.
Last_IO_Errno Menampilkan kode kesalahan alur IO, jika ada. Untuk informasi selengkapnya tentang kode ini, lihat referensi pesan kesalahan server MySQL.
Last_IO_Error Menampilkan pesan kesalahan alur IO, jika ada.
Last_SQL_Errno Menampilkan kode kesalahan alur SQL, jika ada. Untuk informasi selengkapnya tentang kode ini, lihat referensi pesan kesalahan server MySQL.
Last_SQL_Error Menampilkan pesan kesalahan alur SQL, jika ada.
Slave_SQL_Running_State Menunjukkan status alur SQL saat ini. Dalam keadaan ini, System lock adalah normal. Hal ini juga normal untuk melihat status Waiting for dependent transaction to commit. Status ini menunjukkan bahwa replika sedang menunggu utas pekerja SQL lainnya untuk memperbarui transaksi yang diterapkan.

Jika Slave_IO_Running Yes dan Slave_SQL_Running Yes, maka replikasi berjalan dengan baik.

Selanjutnya, periksa Last_IO_Errno, Last_IO_Error, Last_SQL_Errno, dan Last_SQL_Error. Bidang-bidang ini menampilkan nomor kesalahan dan pesan kesalahan dari kesalahan terbaru yang menyebabkan alur SQL berhenti. Jumlah kesalahan 0 dan pesan kosong berarti tidak ada kesalahan. Selidiki setiap nilai kesalahan bukan nol dengan memeriksa kode kesalahan dalam referensi pesan kesalahan server MySQL.

Skenario umum untuk latensi replikasi tinggi

Bagian berikut membahas skenario di mana latensi replikasi tinggi adalah umum.

Latensi jaringan atau konsumsi CPU tinggi pada server sumber

Jika Anda melihat nilai berikut, latensi replikasi kemungkinan disebabkan oleh latensi jaringan tinggi atau konsumsi CPU yang tinggi di server sumber.

Slave_IO_State: Waiting for master to send event
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller than Master_Log_File, e.g. mysql-bin.00010

Dalam hal ini, alur IO berjalan dan sedang menunggu di server sumber. Server sumber telah menulis ke file log biner nomor 20. Replika hanya menerima hingga nomor file 10. Faktor utama untuk latensi replikasi tinggi dalam skenario ini adalah kecepatan jaringan atau pemanfaatan CPU yang tinggi pada server sumber.

Di Azure, latensi jaringan dalam suatu wilayah biasanya dapat diukur milidetik. Di seluruh wilayah, latensi berkisar dari milidetik hingga detik.

Dalam kebanyakan kasus, keterlambatan koneksi antara alur IO dan server sumber disebabkan oleh pemanfaatan CPU yang tinggi pada server sumber. Alur IO diproses secara perlahan. Anda dapat mendeteksi masalah ini dengan menggunakan Azure Monitor untuk memeriksa pemanfaatan CPU dan jumlah koneksi bersamaan di server sumber.

Jika Anda tidak melihat pemanfaatan CPU yang tinggi di server sumber, masalahnya mungkin latensi jaringan. Jika latensi jaringan tiba-tiba tinggi secara abnormal, periksa halaman status Azure untuk masalah atau pemadaman yang diketahui.

Ledakan transaksi yang berat di server sumber

Jika Anda melihat nilai-nilai berikut, maka ledakan transaksi yang berat di server sumber kemungkinan menyebabkan latensi replikasi.

Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller then Master_Log_File, e.g. mysql-bin.00010

Output menunjukkan bahwa replika dapat mengambil log biner di belakang server sumber. Tetapi alur replika IO menunjukkan bahwa ruang log relai sudah penuh.

Kecepatan jaringan tidak menyebabkan penundaan. Replika mencoba untuk mengejar keterlambatan. Tetapi ukuran log biner yang diperbarui melebihi batas atas ruang log relai.

Untuk memecahkan masalah ini, aktifkan log kueri lambat di server sumber. Gunakan log kueri yang lambat untuk mengidentifikasi transaksi yang berjalan lama di server sumber. Kemudian sesuaikan kueri yang diidentifikasi untuk mengurangi latensi pada server.

Latensi replikasi semacam ini umumnya disebabkan oleh beban data pada server sumber. Ketika server sumber memiliki beban data mingguan atau bulanan, latensi replikasi sayangnya tidak dapat dihindari. Server replika akhirnya mengejar keterlambatan setelah beban data di server sumber selesai.

Kelambatan pada server replika

Jika Anda mengamati nilai-nilai berikut, maka masalahnya mungkin ada di server replika.

Slave_IO_State: Waiting for master to send event
Master_Log_File: The binary log file sequence equals to Relay_Master_Log_File, e.g. mysql-bin.000191
Read_Master_Log_Pos: The position of master server written to the above file is larger than Relay_Log_Pos, e.g. 103978138
Relay_Master_Log_File: mysql-bin.000191
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: The position of slave reads from master binary log file is smaller than Read_Master_Log_Pos, e.g. 13468882
Seconds_Behind_Master: There is latency and the value here is greater than 0

Dalam skenario ini, output menunjukkan bahwa alur IO dan alur SQL berjalan dengan baik. Replika membaca file log biner yang sama dengan yang ditulis server sumber. Namun, beberapa latensi pada server replika mencerminkan transaksi yang sama dari server sumber.

Bagian-bagian berikut menjelaskan penyebab umum latensi semacam ini.

Tidak ada kunci primer atau kunci unik pada tabel

Azure Database for MySQL menggunakan replikasi berbasis baris. Server sumber menulis peristiwa ke log biner, merekam perubahan dalam baris tabel individual. Alur SQL kemudian mereplikasi perubahan tersebut ke baris tabel yang sesuai di server replika. Saat tabel tidak memiliki kunci primer atau kunci unik, alur SQL memindai semua baris dalam tabel target untuk menerapkan perubahan. Pemindaian ini dapat menyebabkan latensi replikasi.

Di MySQL, kunci primer adalah indeks terkait yang memastikan performa kueri cepat karena tidak dapat menyertakan nilai NULL. Jika Anda menggunakan mesin penyimpanan InnoDB, data tabel diatur secara fisik untuk melakukan pencarian dan pengurutan yang sangat cepat berdasarkan kunci primer.

Kami menyarankan agar Anda menambahkan kunci primer pada tabel di server sumber sebelum Anda membuat server replika. Tambahkan kunci primer pada server sumber lalu buat ulang replika baca untuk membantu meningkatkan latensi replikasi.

Gunakan kueri berikut untuk mengetahui tabel mana yang kehilangan kunci primer di server sumber:

select tab.table_schema as database_name, tab.table_name 
from information_schema.tables tab left join 
information_schema.table_constraints tco 
on tab.table_schema = tco.table_schema 
and tab.table_name = tco.table_name 
and tco.constraint_type = 'PRIMARY KEY' 
where tco.constraint_type is null 
and tab.table_schema not in('mysql', 'information_schema', 'performance_schema', 'sys') 
and tab.table_type = 'BASE TABLE' 
order by tab.table_schema, tab.table_name;

Kueri yang berjalan lama di server replika

Beban kerja pada server replika dapat membuat alur SQL tertinggal dari alur IO. Kueri yang berjalan lama di server replika adalah salah satu penyebab umum latensi replikasi tinggi. Untuk memecahkan masalah ini, aktifkan log kueri lambat pada server replika.

Kueri yang lambat dapat meningkatkan konsumsi sumber daya atau memperlambat server sehingga replika tidak dapat mengejar ketinggalan dengan server sumber. Dalam skenario ini, sesuaikan kueri yang lambat. Kueri yang lebih cepat mencegah penyumbatan alur SQL dan meningkatkan latensi replikasi secara signifikan.

Kueri DDL pada server sumber

Pada server sumber, perintah bahasa definisi data (DDL) seperti ALTER TABLE dapat memakan waktu lama. Saat perintah DDL berjalan, ribuan kueri lain mungkin berjalan secara paralel di server sumber.

Ketika DDL direplikasi, untuk memastikan konsistensi database, mesin MySQL menjalankan DDL dalam satu alur replikasi. Selama tugas ini, semua kueri lain yang direplikasi diblokir dan harus menunggu sampai operasi DDL selesai di server replika. Bahkan operasi DDL online menyebabkan penundaan ini. Operasi DDL meningkatkan latensi replikasi.

Jika Anda mengaktifkan log kueri lambat di server sumber, Anda dapat mendeteksi masalah latensi ini dengan memeriksa perintah DDL yang berjalan di server sumber. Melalui penurunan indeks, penggantian nama, dan pembuatan, Anda dapat menggunakan algoritma INPLACE untuk ALTER TABLE. Anda mungkin perlu menyalin data tabel dan membangun ulang tabel.

Biasanya, DML bersamaan didukung untuk algoritma INPLACE. Tetapi Anda dapat secara singkat mengambil kunci metadata eksklusif pada tabel ketika Anda mempersiapkan dan menjalankan operasi. Jadi untuk pernyataan CREATE INDEX, Anda dapat menggunakan klausul ALGORITMA dan LOCK untuk mempengaruhi metode untuk penyalinan tabel dan tingkat konkurensi untuk membaca dan menulis. Anda masih dapat mencegah operasi DML dengan menambahkan indeks FULLTEXT atau indeks SPATIAL.

Contoh berikut membuat indeks dengan menggunakan klausul ALGORITMA dan LOCK.

ALTER TABLE table_name ADD INDEX index_name (column), ALGORITHM=INPLACE, LOCK=NONE;

Sayangnya, untuk pernyataan DDL yang membutuhkan kunci, Anda tidak dapat menghindari latensi replikasi. Untuk mengurangi efek potensial, lakukan jenis operasi DDL ini di luar jam sibuk, misalnya pada malam hari.

Server replika yang diturunkan

Di Azure Database for MySQL, replika baca menggunakan konfigurasi server yang sama dengan server sumber. Anda bisa mengubah konfigurasi server replika setelah dibuat.

Jika server replika diturunkan, beban kerja dapat mengonsumsi lebih banyak sumber daya, yang pada gilirannya dapat menyebabkan latensi replikasi. Untuk mendeteksi masalah ini, gunakan Azure Monitor untuk memeriksa konsumsi CPU dan memori server replika.

Dalam skenario ini, kami menyarankan agar Anda menjaga konfigurasi server replika pada nilai yang sama dengan atau lebih besar dari nilai server sumber. Konfigurasi ini memungkinkan replika untuk mengikuti server sumber.

Meningkatkan latensi replikasi dengan menyetel parameter server sumber

Di Azure Database for MySQL, secara default, replikasi dioptimalkan untuk berjalan dengan alur paralel pada replika. Ketika beban kerja konkurensi tinggi di server sumber menyebabkan server replika tertinggal, Anda dapat meningkatkan latensi replikasi dengan mengonfigurasi parameter binlog_group_commit_sync_delay pada server sumber.

Parameter binlog_group_commit_sync_delay mengontrol berapa mikrodetik penerapan log biner menunggu sebelum menyinkronkan file log biner. Manfaat dari parameter ini adalah bahwa alih-alih segera menerapkan setiap transaksi yang diterapkan, server sumber mengirim pembaruan log biner secara massal. Penundaan ini mengurangi IO pada replika dan membantu meningkatkan performa.

Hal tersebut mungkin berguna untuk mengatur parameter binlog_group_commit_sync_delay ke 1000 atau lebih. Kemudian pantau latensi replikasi. Atur parameter ini dengan hati-hati, dan gunakan hanya untuk beban kerja konkurensi tinggi.

Penting

Di server replika, binlog_group_commit_sync_delay parameter disarankan menjadi 0. Hal ini direkomendasikan karena tidak seperti server sumber, server replika tidak akan memiliki konkurensi tinggi dan meningkatkan nilai untuk binlog_group_commit_sync_delay pada server replika secara tidak sengaja dapat menyebabkan jeda replikasi meningkat.

Untuk beban kerja konkurensi rendah yang mencakup banyak transaksi database tunggal, pengaturan binlog_group_commit_sync_delay dapat meningkatkan latensi. Latensi dapat meningkat karena alur IO menunggu pembaruan log biner massal bahkan jika hanya beberapa transaksi yang diterapkan.

Opsi Pemecahan Masalah Tingkat Lanjut

Jika menggunakan perintah tampilkan status budak tidak memberikan informasi yang cukup untuk memecahkan masalah latensi replikasi, coba lihat opsi tambahan ini untuk mempelajari proses mana yang aktif atau menunggu.

Menampilkan tabel utas

Tabel performance_schema.threads memperlihatkan status proses. Proses dengan status Menunggu kunci lock_type menunjukkan bahwa ada kunci pada salah satu tabel, mencegah utas replikasi memperbarui tabel.

SELECT name, processlist_state, processlist_time FROM performance_schema.threads WHERE name LIKE '%slave%';

Untuk informasi selengkapnya, lihat Status Utas Umum.

Menampilkan tabel replication_connection_status

Tabel performance_schema.replication_connection_status menunjukkan status utas I/O replikasi saat ini yang menangani koneksi replika ke sumber, dan lebih sering berubah. Tabel berisi nilai yang bervariasi selama koneksi.

SELECT * FROM performance_schema.replication_connection_status;

Menampilkan tabel replication_applier_status_by_worker

Tabel performance_schema.replication_applier_status_by_worker memperlihatkan status utas pekerja, Transaksi terakhir dilihat bersama dengan nomor kesalahan dan pesan terakhir, yang membantu Anda menemukan transaksi yang mengalami masalah dan mengidentifikasi akar penyebabnya.

Anda dapat menjalankan perintah di bawah ini dalam replikasi Data-in untuk melewati kesalahan atau transaksi:

az_replication_skip_counter

or

az_replication_skip_gtid_transaction

SELECT * FROM performance_schema.replication_applier_status_by_worker;

Menampilkan pernyataan ACARA SHOW RELAYLOG

Pernyataan menunjukkan show relaylog events peristiwa dalam log relai replika.

· Untuk replikasi berbasis GITD (Replika baca), pernyataan menunjukkan transaksi GTID dan file binlog dan posisinya, Anda dapat menggunakan mysqlbinlog untuk mendapatkan konten dan pernyataan yang dijalankan. · Untuk replikasi posisi binlog MySQL (digunakan untuk replikasi Data-in), ini menunjukkan pernyataan yang dijalankan, yang akan membantu mengetahui transaksi tabel mana yang sedang dijalankan

Periksa Monitor Standar InnoDB dan Kunci Output Monitor

Anda juga dapat mencoba memeriksa Monitor Standar InnoDB dan Kunci Output Monitor untuk membantu mengatasi kunci dan kebuntuan dan meminimalkan jeda replikasi. Monitor Kunci sama dengan Monitor Standar kecuali menyertakan informasi kunci tambahan. Untuk melihat informasi kunci dan kebuntuan tambahan ini, jalankan perintah show engine innodb status\G.

Langkah berikutnya

Lihat ringkasan replikasi binlog MySQL.