MySQL replikācijas nobīde: kāpēc replika atpaliek un kā to novērst

MySQL replikācijas nobīde reti piesaka sevi skaļi. Vispirms atskaite rāda vakardienas skaitļus. Tad lietotājs atjaunina savu profilu un nākamajā lapā redz veco. Tad kāds jautā, vai uz repliku var droši pārslēgties, un neviens negrib atbildēt. Atpalikusi replika nav tikai veiktspējas problēma: šodien tie ir novecojuši dati, bet dienā, kad repliku nāksies paaugstināt, tās ir pazaudētas transakcijas. Lūk, kā atrast nobīdes avotu un kas to patiešām novērš.

Pirmais solis: kura replikācijas puse ir lēna?

MySQL replikai ir divi uzdevumi. Saņēmēja (I/O) pavediens lejupielādē bināro žurnālu notikumus no avota un ieraksta tos relay žurnālā. Izpildītāja (SQL) pavediens jeb darbinieku pavedienu kopa šos notikumus izpilda. Nobīde var rasties jebkurā pusē, un risinājumi ir pilnīgi atšķirīgi. Sāciet ar:

SHOW REPLICA STATUS\G

Lauki, kurus vērts izlasīt vispirms:

• Replica_IO_Running un Replica_SQL_Running: abiem jārāda Yes. Ja kāds rāda No, jums nav nobīdes, jums ir salauzta replikācija. Izlasiet Last_IO_Error un Last_SQL_Error.
• Retrieved_Gtid_Set pret Executed_Gtid_Set (ja izmanto GTID): ja replika ir saņēmusi krietni vairāk, nekā izpildījusi, vājā vieta ir izpildītājs.
• Relay_Log_Space: relay žurnāls, kas turpina augt, arī nozīmē, ka notikumi pienāk ātrāk, nekā tiek izpildīti.

Ja saņemtā pozīcija ir tuvu avotam un relay žurnāls ir mazs, bet replika joprojām atpaliek, skatieties uz saņēmēja pusi: tīkla joslas platums, latentums starp reģioniem vai avots, kuram grūti rakstīt un sūtīt savu bināro žurnālu.

Neuzticieties Seconds_Behind_Source akli

Seconds_Behind_Source ir skaitlis, ko attēlo visi, un tas bieži maldina. Pati MySQL dokumentācija brīdina, ka vērtība 0 parasti nozīmē, ka replika ir panākusi avotu, bet ne vienmēr. Pārtrūkušu tīkla savienojumu var nepamanīt, līdz paiet replica_net_timeout, tāpēc replika mierīgi rāda 0, neko nesaņemot. Un, kad saņēmējs ievieto rindā notikumus ar veciem laikspiedoliem, vērtība var lēkāt starp 0 un lielu skaitli.

Brīdinājumiem mēriet nobīdi tieši. Klasiskā pieeja ir sirdspukstu tabula: uzdevums avotā katru sekundi atjaunina laikspiedolu nelielā tabulā, un jūs to salīdzināt ar pulksteni replikā. Percona Toolkit rīks pt-heartbeat dara tieši to. MySQL 8 var nolasīt arī katra darbinieka laikspiedolus no Performance Schema:

SELECT WORKER_ID,
       LAST_APPLIED_TRANSACTION,
       TIMEDIFF(LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP,
                LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) AS apply_delay
FROM performance_schema.replication_applier_status_by_worker;

Brīdiniet par abām lietām: nobīdi virs pieļaujamās un replikācijas pavedieniem, kas nedarbojas. Nobīdei jāatrodas blakus citiem agrīnajiem signāliem, ko aprakstām rakstā datubāzu rādītāji, kas paredz incidentus.

Ierastie aizdomās turētie

1. Viena milzīga transakcija

DELETE, kas dzēš 20 miljonus vecu rindu, avotā aizņem, teiksim, 15 minūtes. Replika visu transakciju saņem tikai pēc tam, kad avots to apstiprina, un tad tērē savas 15 minūtes vai vairāk, lai to izpildītu. Viss, kas aiz tās, gaida. Nobīdes grafiki, kas pēc nakts uzdevumiem izskatās pēc zāģa zobiem, gandrīz vienmēr ir šī iemesla dēļ.

Risinājums: sadaliet masveida izmaiņas daļās pa dažiem tūkstošiem rindu ar īsu pauzi starp tām. Tieši tā jāraksta tīrīšanas, datu aizpildīšanas un datu labošanas skripti. Uz papīra tas ir lēnāk, bet visiem pārējiem daudz ātrāk.

2. Tabulas bez primārās atslēgas

Rindu bāzes replikācijā replikai jāatrod katra mainītā rinda. Bez primārās atslēgas vai piemērota indeksa tā var skenēt tabulu, un sliktākajā gadījumā skenēt to vienreiz par katru mainīto rindu. Viens UPDATE, kas avotā skar 10 000 rindu, replikā var pārvērsties par 10 000 tabulas skenēšanām. Tā ir sen zināma problēma (MySQL kļūda #53375).

Risinājums: piešķiriet katrai InnoDB tabulai primāro atslēgu. Atrodiet vainīgās tabulas ar vaicājumu information_schema un ieslēdziet sql_require_primary_key, lai jaunas neparādītos. Ja lietotni mainīt nevarat, MySQL var pievienot ģenerētu neredzamu primāro atslēgu: sql_generate_invisible_primary_key avotā jaunām tabulām vai REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE replikas kanālā (MySQL dokumentācija par GIPK; Percona neredzamās kolonnas pieeju apraksta šeit).

3. Izpildītājs nevar strādāt paralēli

MySQL 8.4 replikas pēc noklusējuma ir daudzpavedienu: replica_parallel_workers ir 4 un replica_preserve_commit_order ir ON (replikas parametri). Vecākās konfigurācijās bieži joprojām ir viens izpildītāja pavediens, tāpēc pārbaudiet, kas jums patiesībā darbojas. Vairāk darbinieku palīdz tikai tad, ja transakcijas ir neatkarīgas. Ja katra transakcija atjaunina vienu un to pašu skaitītāja rindu vai vienu karsto tabulu, tās jāizpilda pēc kārtas neatkarīgi no darbinieku skaita. Pirms skaita palielināšanas pārbaudiet, vai darbinieki strādā vai lielākoties gaida.

4. Replika ir vājāka par avotu

"Tā taču ir tikai replika" ir veids, kā replikas nonāk uz pusi mazāka CPU un lēnākiem diskiem. Taču replikai jāizpilda visi tie paši ieraksti, ko avotam, plus visi lasījumi, ko jūs uz to sūtāt. Dodiet tai līdzvērtīgu krātuvi, tādu pašu buffer pool izmēru un tos pašus svarīgos InnoDB iestatījumus. Dažas komandas replikās mīkstina drošuma iestatījumus (innodb_flush_log_at_trx_commit, sync_binlog), lai paātrinātu izpildi. Tas var palīdzēt, bet saprotiet kompromisu: pēc avārijas šo repliku var nākties pārbūvēt, un tā ir slikta kandidāte pārslēgšanai.

5. Tas, kas darbojas replikā

• Atskaišu vaicājumi un rezerves kopēšana konkurē ar izpildītāju par I/O un CPU, un garas transakcijas replikā var aizturēt purge.
• Liels DDL. ALTER TABLE, kas avotā darbojās tiešsaistē, replikā tiek izpildīts kā viens priekšraksts, un transakcijas aiz tā gaida, līdz tas beidzas. Tiešsaistes shēmas maiņas rīki, kas kopē pa daļām, šo garo apstāšanos novērš.

6. Tīkls

Starpreģionu replikas pa šauru kanālu ierakstu pīķu laikā atpaliek. Bināro žurnālu transakciju saspiešana (binlog_transaction_compression, pieejama kopš MySQL 8.0.20) rindu bāzes slodzēm var ievērojami samazināt apjomu uz nelielu CPU rēķina abās pusēs.

Ātra diagnostikas secība

1. Vai abi pavedieni darbojas? Ja nē, vispirms izlasiet kļūdas.
2. Saņēmējs vai izpildītājs? Salīdziniet saņemtās un izpildītās GTID kopas un vērojiet relay žurnālu.
3. Ko izpildītājs dara tieši tagad? Skatieties SHOW PROCESSLIST un Performance Schema izpildītāja tabulas. Viens ilgi izpildāms notikums norāda uz lielu transakciju vai tabulas skenēšanu.
4. Pārbaudiet, vai tabulai, pie kuras tas iestrēdzis, ir primārā atslēga.
5. Salīdziniet replikas aparatūru un konfigurāciju ar avotu.
6. Tikai tad pielāgojiet paralēlos darbiniekus.