MySQL InnoDB History List Length: kāpēc tas aug un kā to samazināt

InnoDB history list length ir viens skaitlis izvadē SHOW ENGINE INNODB STATUS, kas parāda, vai MySQL tiek līdzi veco rindu versiju tīrīšanai. Kad tas no dažiem tūkstošiem kāpj līdz miljoniem, vaicājumi palēninās, undo tabulu telpas aug, un serveris turpina darīt papildu darbu ar datiem, kuriem sen vajadzēja pazust. Vairumā gadījumu pamatcēlonis nav pats purge, bet transakcija, kas atvērta pārāk ilgi.

Ko history list length patiesībā skaita

InnoDB glabā mainīto rindu vecās versijas undo žurnālos, lai citas transakcijas joprojām varētu lasīt konsekventu momentuzņēmumu. Saskaņā ar MySQL purge dokumentāciju transakciju sistēma uztur sarakstu ar transakcijām, kuru indeksu ieraksti atzīmēti kā dzēsti ar UPDATE vai DELETE. Šī saraksta garums ir purge nobīde, kas sadaļā TRANSACTIONS redzama kā History list length.

Purge ir fona process, kas noņem dzēšanai atzīmētos ierakstus un atbrīvo undo, kad neviena transakcija tos vairs nevar vajadzēt. Dokumentācija saka, ka vērtība parasti ir zema, parasti zem dažiem tūkstošiem, un ka rakstīšanas noslogota slodze vai ilgas transakcijas, pat tikai lasīšanas, var likt tai augt.

Kā to pārbaudīt

-- klasiskā vieta
SHOW ENGINE INNODB STATUS\G
--   ------------
--   TRANSACTIONS
--   ------------
--   Trx id counter 48211973
--   Purge done for trx's n:o < 48211950 undo n:o < 0 state: running but idle
--   History list length 1843

-- tas pats skaitlis, ērti grafikam
SELECT name, count
FROM information_schema.INNODB_METRICS
WHERE name = 'trx_rseg_history_len';

Skaitītājs trx_rseg_history_len tabulā INNODB_METRICS pēc noklusējuma ir ieslēgts, tāpēc to var vākt ar jebkuru monitoringa sistēmu un brīdināt par tendenci. Viens augsts mērījums pēc pakešdarba ir normāls; līnija, kas tikai kāpj, nav.

Kāpēc tas aug

1. Ilga transakcija tur vecu lasīšanas skatu

Tas ir biežākais iemesls. Ar REPEATABLE READ, noklusējuma izolācijas līmeni, transakcijai jāturpina redzēt dati tādi, kādi tie bija, kad tika izveidots tās lasīšanas skats. Dokumentācija skaidro, ka tāpēc InnoDB jāglabā šim skatam vajadzīgais undo, līdz visas no tā atkarīgās transakcijas ir beigušās. Viena sesija, kas atvēra transakciju pirms stundas, bloķē purge visam, kas kopš tā laika mainīts, visās tabulās.

Dokumentācija min divus tipiskus piemērus:

• mysqldump --single-transaction, kas darbojas laikā, kad notiek daudz vienlaicīga DML.
• SELECT pēc autocommit izslēgšanas bez sekojoša COMMIT vai ROLLBACK. Savienojumu pūli un ORM, kas atstāj sesijas atvērtā transakcijā, rada to pašu efektu.

Citi tipiski aizdomās turamie ir atskaišu vaicājumi, kas darbojas stundām, SQL klientā atstātas interaktīvas sesijas un lietotņu kļūdas, kas sāk transakciju un tad gaida ārēju API.

2. Purge vienkārši netiek līdzi

Ar ilgstošu intensīvu UPDATE un DELETE trafiku purge var atpalikt arī bez ilgām transakcijām. Daudzversiju nodaļa brīdina, ka tādā gadījumā tabulas var turpināt augt ar mirušām rindām, līdz viss kļūst diska ierobežots un lēns.

Atrodiet transakciju, kas aiztur purge

SELECT t.trx_id, t.trx_state, t.trx_started,
       TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS age_s,
       t.trx_mysql_thread_id, t.trx_rows_modified,
       p.user, p.host, p.command, p.time, LEFT(p.info, 80) AS current_sql
FROM information_schema.INNODB_TRX t
LEFT JOIN information_schema.PROCESSLIST p ON p.id = t.trx_mysql_thread_id
ORDER BY t.trx_started
LIMIT 10;

Skatieties uz vecākajām rindām. Transakcija stāvoklī RUNNING tūkstošiem sekunžu, kamēr savienojums rāda Sleep, ir klasiskais vaininieks: lietotne to atvēra un aizmirsa. Transakcija ar lielu trx_rows_modified var arī turēt slēdzenes, un tas ir stāsts aiz Lock wait timeout exceeded.

Risinājumi no svarīgākā līdz mazāk svarīgajam

1. Novērsiet ilgas transakcijas. Apstipriniet regulāri, arī transakcijās, kas tikai lasa; MySQL dokumentācija iesaka tieši to. Salabojiet savienojumu pūla un ORM iestatījumus, lai sesijas atgrieztos ar ieslēgtu autocommit un bez atvērtas transakcijas.
2. Pārceliet ilgas lasīšanas prom no primārā. Ilgi atskaišu vaicājumi un loģiskās rezerves kopijas uz noslogota primārā tur vecus lasīšanas skatus. Palaidiet tos uz replikas vai lielām datu kopām izmantojiet fizisku rezerves kopēšanas rīku.
3. Sadaliet lielu DML. Dzēsiet vai atjauniniet pa tūkstošiem rindu ar apstiprināšanu pēc katras daļas, nevis ar vienu priekšrakstu, kas skar desmitiem miljonu.
4. Dodiet purge vairāk pavedienu, ja tas tiešām ir šaurā vieta. MySQL 8.4 innodb_purge_threads noklusējums ir 1 serveros ar 16 vai mazāk loģiskajiem procesoriem un 4 pārējos (MySQL 8.0 bija 4). Diapazons ir no 1 līdz 32, un tas nav dinamisks, tāpēc izmaiņai vajag restartēšanu. Vairāk pavedienu palīdz galvenokārt tad, ja DML ir izkliedēts pa daudzām tabulām. innodb_purge_batch_size noklusējums ir 300.
5. Kā pēdējo līdzekli ierobežojiet rakstītājus. innodb_max_purge_lag (noklusējums 0, bez ierobežojuma) aizkavē DML, kad vēstures saraksts pārsniedz slieksni, ar augšējo robežu innodb_max_purge_lag_delay (mikrosekundes, noklusējums 0). Abi ir dinamiski. Tas aizsargā serveri, bet palēnina lietotni, tāpēc izmantojiet to uzmanīgi.

Undo tabulu telpas un diska vieta

Garš vēstures saraksts nozīmē, ka undo tabulu telpas turpina augt. MySQL izveido divas noklusējuma undo tabulu telpas, un automātiskai saīsināšanai vajag vismaz divas. Ar innodb_undo_log_truncate (noklusējums ON) undo tabulu telpa, kas lielāka par innodb_max_undo_log_size (noklusējums 1 GiB), tiek atzīmēta saīsināšanai, bet tikai pēc tam, kad purge ir atbrīvojis tās atcelšanas segmentus. innodb_purge_rseg_truncate_frequency (noklusējums 128) nosaka, cik bieži tas notiek. Pārbaudiet pašreizējo izmēru:

SELECT name, file_size, allocated_size, state
FROM information_schema.INNODB_TABLESPACES
WHERE space_type = 'Undo';

Būtība: kamēr vecā transakcija ir atvērta, saīsināšana nepalīdz. Undo, kas vēl vajadzīgs, nevar noņemt, un, ja disks piepildās, jūs saskaraties ar tādām problēmām kā MySQL kļūda 1114: the table is full. Par undo tabulu telpu pievienošanu, deaktivizēšanu un dzēšanu skatiet undo tabulu telpu dokumentāciju.

Kas mainās lielos serveros

• Kaitējums aug līdz ar rakstīšanas ātrumu, nevis tikai ar transakcijas vecumu. Serverī ar desmitiem tūkstošu ierakstu sekundē viena stunda bloķēta purge var nozīmēt desmitiem gigabaitu undo un miljoniem dzēšanai atzīmētu indeksa ierakstu.
• Lasīšana kļūst lēnāka visiem. Vaicājumiem, kas skar nesen mainītas rindas, var nākties iet cauri garām versiju ķēdēm undo, un vēl neiztīrītie dzēšanai atzīmētie ieraksti indeksu skenēšanā joprojām jāizlaiž. Lielās tabulās tas izpaužas kā lielāks CPU un I/O bez acīmredzamām izmaiņām vaicājumos.
• Atkopšanās prasa laiku arī pēc bloķētāja aiziešanas. Kad ilgā transakcija beidzas, purge jāapstrādā viss uzkrājums. Lielā serverī tas var aizņemt stundas un tā laikā pievieno I/O. Plānojiet to, nevis gaidiet, ka skaitlis kritīs uzreiz.
• Lielu datubāzu loģiskās rezerves kopijas ir biežs cēlonis. Vairāku terabaitu mysqldump --single-transaction uz primārā var darboties daudzas stundas un visu laiku turēt vienu lasīšanas skatu. Izmantojiet repliku vai fiziskas rezerves kopijas.
• Replikām ir savs purge. Katrs serveris pārvalda savu undo. Ilgas atskaites uz replikas var palielināt vēstures sarakstu tur, kamēr primārais izskatās vesels, un replika tādā stāvoklī var arī atpalikt replikācijā. Vērojiet šo metriku katrā serverī.
• Undo vieta var būt liela un lēni atgūstama. Lielos serveros atstājiet diska rezervi undo vajadzībām un nerēķinieties, ka saīsināšana ātri atbrīvos vietu, kamēr purge vēl panāk nokavēto.