Cara mengurangi lag replikasi di mysql

Question

select unix_timestamp(now(6)) - unix_timestamp(ts) as replication_delay from heartbeat order by ts desc limit 1

6 & 100,0002 mempermudah aplikasi untuk menambahkan pelambatan ke operasi besar-besaran. Namun, insinyur masih perlu menyadari bahwa operasi mereka harus menggunakan pelambatan, dan dibutuhkan perubahan terprogram pada kode untuk memanggil pelambatan. Kami sedang mencari kueri yang mengidentifikasi secara cerdas. teknisi akan menjalankan kode kueri "normal", dan adaptor atau proxy akan mengidentifikasi apakah kueri tersebut memerlukan pembatasan, dan bagaimana caranya

Keterlambatan total diamati dengan metrik replica_lag. Perbedaan antara replica_lag dan network_lag dapat menunjukkan alasan ketiga saat replika tidak dapat menerapkan perubahan replikasi dengan cukup cepat. Metrik ini dijelaskan pada bagian di bawah ini

Table of Contents Show

Konfigurasi replika lebih cepat
Replikasi paralel
Pembilasan kinerja tinggi
Optimalkan kueri dan skema
Tingkat isolasi kueri di replika baca
Transaksi yang berjalan lama di database utama
Kunci utama tidak ada
Kunci eksklusif karena DDL
Replika yang kelebihan beban
Basis data primer monolitik
Pantau kelambatan replikasi
Verifikasi replikasi
Bagaimana cara mengatasi kelambatan replikasi di MySQL?
Bagaimana cara meningkatkan kinerja replikasi MySQL?
Bagaimana Anda menghentikan lag replikasi?
Bagaimana saya bisa membuat replikasi lebih cepat?

Konfigurasi replika lebih cepat

Kami memiliki dua cara untuk membuat replika MySQL menerapkan perubahan lebih cepat. Pengguna dapat mengonfigurasi replika mereka dengan opsi berikut

Replikasi paralel
Pembilasan kinerja tinggi

Replikasi paralel

Replikasi paralel dapat membantu kelambatan replikasi dengan mengonfigurasi replika untuk menggunakan beberapa utas yang bekerja secara paralel untuk menerapkan perubahan pada replika. Untuk informasi tentang penggunaan replikasi paralel, lihat

Pembilasan kinerja tinggi

Secara default, Cloud SQL untuk MySQL menghapus redo log ke disk setelah setiap transaksi. Pembilasan kinerja tinggi mengurangi frekuensi redo log disiram ke disk menjadi satu kali per detik, yang meningkatkan kinerja penulisan

Setel bendera pada replika baca ke 2. Anda juga harus menyetel bendera sync_binlog_ ke nilai yang lebih tinggi agar bendera innodb_flush_log_at_trx_commit menjadi efektif

Lihat untuk informasi lebih lanjut tentang bendera ini

Saat tanda ditetapkan pada replika baca dan Cloud SQL mendeteksi bahwa mungkin terjadi error, Cloud SQL akan otomatis membuat ulang replika tersebut

Optimalkan kueri dan skema

Bagian ini menyarankan beberapa pengoptimalan skema dan kueri umum yang dapat Anda buat untuk meningkatkan kinerja replikasi

Tingkat isolasi kueri di replika baca

Level isolasi transaksi REPEATABLE READ dan

SHOW MASTER STATUS;

0 memperoleh kunci yang mungkin memblokir perubahan replikasi. Pertimbangkan untuk mengurangi tingkat isolasi kueri Anda di replika. Tingkat isolasi transaksi

SHOW MASTER STATUS;

1 mungkin berkinerja lebih baik

Catatan. Level isolasi transaksi default MySQL adalah REPEATABLE READ karena alasan historis

Transaksi yang berjalan lama di database utama

Jika sejumlah besar baris diperbarui dalam satu transaksi, ini dapat menyebabkan lonjakan tiba-tiba dalam jumlah perubahan yang perlu diterapkan ke instance utama dan kemudian dikirim ke replika. Ini berlaku untuk pembaruan atau penghapusan pernyataan tunggal yang memengaruhi banyak baris sekaligus. Perubahan dikirim ke replika setelah dilakukan. Menerapkan lonjakan perubahan yang tiba-tiba pada replika dapat meningkatkan kemungkinan penguncian sanggahan pada replika jika beban kueri pada replika juga tinggi, yang menyebabkan kelambatan replikasi

Pertimbangkan untuk memecah transaksi besar menjadi beberapa transaksi kecil

Kunci utama tidak ada

Replika baca Cloud SQL menggunakan replikasi berbasis baris, yang berperforma buruk jika tabel MySQL yang direplikasi tidak memiliki kunci utama. Kami merekomendasikan agar semua tabel yang direplikasi memiliki kunci primer

Kunci eksklusif karena DDL

Perintah bahasa definisi data (DDL), seperti

SHOW MASTER STATUS;

3 dan

SHOW MASTER STATUS;

4, dapat menyebabkan kelambatan replikasi dalam replika karena penguncian eksklusif. Untuk menghindari pertentangan kunci, pertimbangkan untuk menjadwalkan eksekusi DDL saat beban kueri lebih rendah pada replika

Replika yang kelebihan beban

Jika replika baca menerima terlalu banyak kueri, replikasi dapat diblokir. Pertimbangkan untuk membagi pembacaan di antara beberapa replika untuk mengurangi beban masing-masing replika

Untuk menghindari lonjakan kueri, pertimbangkan membatasi kueri baca replika di logika aplikasi Anda atau di lapisan proxy jika Anda menggunakannya

Jika ada lonjakan aktivitas pada instans utama, pertimbangkan untuk menyebarkan pembaruan

Basis data primer monolitik

Pertimbangkan sharding database utama secara vertikal (atau horizontal) untuk mencegah satu atau beberapa tabel lagging menahan semua tabel lainnya

Pantau kelambatan replikasi

Anda dapat menggunakan metrik replica_lag dan network_lag untuk memantau kelambatan replikasi dan mengidentifikasi apakah penyebab kelambatan ada di database utama, jaringan, atau replika

MetricDescriptionReplication lag
(

SHOW MASTER STATUS;

7)

Jumlah detik status replika tertinggal dari status instance utama. Ini adalah perbedaan antara waktu saat ini dan stempel waktu asli di mana database utama melakukan transaksi yang saat ini sedang diterapkan pada replika. Secara khusus, penulisan mungkin dihitung sebagai lagging meskipun telah diterima oleh replika, jika replika belum menerapkan penulisan ke database

Metrik ini melaporkan nilai

SHOW MASTER STATUS;

8 saat

SHOW MASTER STATUS;

9 dijalankan pada replika. Untuk informasi selengkapnya, lihat Memeriksa Status Replikasi di Manual Referensi MySQL

Nomor kesalahan utas I/O terakhir
(network_lag_0)

Menunjukkan kesalahan terakhir yang menyebabkan utas I/O gagal. Jika ini bukan nol, replikasi rusak. Ini jarang terjadi, tetapi mungkin saja terjadi. Periksa dokumentasi MySQL untuk memahami apa yang ditunjukkan oleh kode kesalahan. Misalnya, file binlog di instance utama mungkin telah dihapus sebelum replika menerimanya. Cloud SQL biasanya membuat ulang replika secara otomatis jika replikasi rusak. Metrik network_lag_1 ini mungkin memberi tahu Anda alasannya

Nomor error utas SQL terakhir
(network_lag_2)

Menunjukkan kesalahan terakhir yang menyebabkan utas SQL gagal. Jika ini bukan nol, replikasi rusak. Ini jarang terjadi, tetapi mungkin saja terjadi. Periksa dokumentasi MySQL untuk memahami apa yang ditunjukkan oleh kode kesalahan. Cloud SQL biasanya akan membuat ulang replika secara otomatis jika replikasi rusak. Metrik network_lag_3 ini dapat memberi tahu Anda alasannya

Keterlambatan jaringan
(network_lag_4)

Durasi waktu, dalam detik, yang diperlukan dari penulisan binlog di database utama hingga mencapai utas IO di replika

Jika network_lag nol, atau dapat diabaikan, tetapi replica_lag tinggi, ini menunjukkan bahwa utas SQL tidak dapat menerapkan perubahan replikasi dengan cukup cepat

Verifikasi replikasi

Untuk memverifikasi bahwa replikasi berfungsi, jalankan pernyataan berikut terhadap replika

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: xx.xxx.xxx.xxx
                  Master_User: cloudsqlreplica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.199927
          Read_Master_Log_Pos: 83711956
               Relay_Log_File: relay-log.000025
                Relay_Log_Pos: 24214376
        Relay_Master_Log_File: mysql-bin.199898
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 24214163
              Relay_Log_Space: 3128686571
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: master_server_ca.pem
           Master_SSL_CA_Path: /mysql/datadir
              Master_SSL_Cert: replica_cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: replica_pkey.pem
        Seconds_Behind_Master: 2627
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 321071839
                  Master_UUID: 437d04e9-8456-11e8-b13d-42010a80027b
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 437d04e9-8456-11e8-b13d-42010a80027b:52111095710-52120776390
            Executed_Gtid_Set: 437d04e9-8456-11e8-b13d-42010a80027b:1-52113039508
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

_

Jika terjadi replikasi, network_lag_7 (kolom pertama) menunjukkan network_lag8 atau pesan serupa. Juga, bidang network_lag_9 kosong

Jika replikasi tidak terjadi, kolom network_lag_7 menunjukkan status replica_lag1 dan kolom network_lag9 menunjukkan status replica_lag3

Menurut dokumentasi MySQL, beberapa bidang menarik lainnya yang berhubungan dengan kelambatan replikasi adalah

KeteranganBidangreplica_lag_4
Nama file log biner sumber yang sedang dibaca oleh utas I/O. replica_lag_5
Posisi dalam file log biner sumber saat ini yang telah dibaca oleh utas I/O. replica_lag_6
Nama file log relai yang sedang dibaca dan dijalankan oleh utas SQL. replica_lag_7
Posisi dalam file log relai saat ini yang telah dibaca dan dieksekusi oleh utas SQL. replica_lag_8
Nama file log biner sumber yang berisi peristiwa terbaru yang dieksekusi oleh utas SQL

Dalam contoh di atas replica_lag_8 memiliki nilai replica_lag0. replica_lag_4 memiliki nilai replica_lag2. Akhiran numerik 199898 kurang dari 199927. Ini berarti bahwa meskipun replika telah menerima file log replica_lag2 yang lebih baru, replika tersebut masih menerapkan file log replica_lag0 yang lebih lama

Dalam hal ini, utas SQL tertinggal di replika

Anda juga dapat terhubung ke database utama dan mengeksekusi

SHOW MASTER STATUS;

_

Perintah ini menunjukkan kepada Anda file binlog mana yang sedang ditulis di database utama

Jika file log biner basis data utama lebih baru dari replica_lag4 dalam replika, itu berarti utas I/O tertinggal. Replika masih membaca file log biner yang lebih lama dari database utama

Saat utas I/O tertinggal, metrik network_lag juga tinggi. Ketika utas SQL tertinggal, tetapi utas I/O tidak, maka metrik network_lag tidak setinggi, tetapi replica_lag tinggi

Perintah sebelumnya memungkinkan Anda mengamati detail kelambatan saat kelambatan terjadi, tetapi metrik network_lag dan replica_lag memberi Anda cara untuk melihat kejadian kelambatan sebelumnya

Bagaimana cara mengatasi kelambatan replikasi di MySQL?

Mengaktifkan pencadangan otomatis pada replika dapat mengakibatkan overhead untuk menyinkronkan log biner ke disk pada replika. Nilai default parameter sync_binlog diatur ke 1. Jika Anda mengubah nilai ini menjadi 0, maka Anda juga mematikan sinkronisasi log biner ke disk oleh server MySQL

Bagaimana cara meningkatkan kinerja replikasi MySQL?

Salah satu cara untuk meningkatkan performa proses replikasi adalah dengan membuat struktur replikasi yang lebih dalam yang memungkinkan sumber untuk mereplikasi hanya ke satu replika, dan agar replika yang tersisa terhubung ke replika utama ini untuk kebutuhan replikasi masing-masing.

Bagaimana Anda menghentikan lag replikasi?

Untuk mengurangi kelambatan replikasi untuk operasi besar, kami menggunakan pengelompokan . Kami tidak pernah menerapkan perubahan pada 100.000 baris sekaligus. Setiap pembaruan besar dipecah menjadi segmen kecil, subtugas, masing-masing sekitar 50 atau 100 baris. Sebagai contoh, katakanlah aplikasi kita perlu membersihkan beberapa baris yang memenuhi syarat dari tabel yang sangat besar.

Bagaimana saya bisa membuat replikasi lebih cepat?

Pastikan alokasi file data database dan file log yang tepat . Gunakan drive disk terpisah untuk log transaksi untuk semua database yang terlibat dalam replikasi. Anda dapat mengurangi waktu yang dibutuhkan untuk menulis transaksi dengan menyimpan file log pada disk drive yang berbeda dari yang digunakan untuk menyimpan database.

kode mysql