Înainte de a face upgrade la MySQL: cifrele pe care clientul ți le cere și cum să le găsești cu adevărat

Mail-ul de la responsabilul de infrastructură a sosit într-o luni dimineață, trei rânduri seci. “Salut, până vineri am nevoie de patru cifre pentru a planifica fereastra de mentenanță pe MySQL-uri: cât de mari sunt astăzi, cât de mult cresc pe lună, cât durează un backup complet, cât ne ia să le reconstruim de la zero dacă ceva merge prost. Mulțumesc.”

Scenariu clasic într-o direcție IT a unei Administrații Publice italiene. Patru servere MySQL 8.0 care susțin aplicații interne și un portal pentru utilizatori, cu versiuni ușor diferite (8.0.32, 8.0.33, 8.0.34) pentru că au fost patch-uite în momente diferite. Upgrade de infrastructură planificat: noi host-uri, sistem de operare actualizat, aceeași versiune major de MySQL, cu fereastră de mentenanță nocturnă de șase ore.

PM-ul nu voia un assessment academic. Voia patru cifre reale de pus în planul de rollback. Iar tentația, când ai grabă, este să răspunzi după ureche: “Or fi vreo 300 GB, backup-ul durează vreo două ore, restore-ul poate trei.” Cifre plauzibile, poate chiar corecte, dar nemăsurate — iar dacă greșești estimarea restore-ului cu un factor de doi, fereastra nu mai este suficientă și cutover-ul pică.

Mi-am luat o jumătate de zi. Iată metoda pe care am folosit-o.

📏 1. Cât cântăresc cu adevărat — information_schema #

Prima cifră este cea mai simplă de găsit și cea mai înșelătoare de interpretat. În MySQL 8.0 `information_schema` expune tot ce este necesar, dar trebuie să știi ce să ceri [1].

-- Dimensiuni totale pe schemă (date + indecși)
SELECT
    table_schema                            AS schema_name,
    ROUND(SUM(data_length)  / 1024 / 1024 / 1024, 2) AS data_gb,
    ROUND(SUM(index_length) / 1024 / 1024 / 1024, 2) AS index_gb,
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gb,
    COUNT(*)                                AS num_tables
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema')
GROUP BY table_schema
ORDER BY total_gb DESC;

Rezultat tipic pe unul dintre cele patru servere:

Pare un rezultat închis, dar nu este. Două lucruri importante:

• data_length și index_length sunt estimări pe care InnoDB le actualizează periodic și care depind de ultimul ANALYZE TABLE. Pe tabele foarte volatile pot subestima cu 10-15%. Pentru date critice merită să verificăm cu dimensiunea fizică a fișierelor .ibd din datadir (du -sh /var/lib/mysql/portal_utilizatori/*.ibd).
• Totalul serverului nu este dimensiunea backup-ului. Fișierul de dump (logic) este mai compact pentru că nu replică fragmentarea InnoDB, dar conține INSERT-uri textuale care cântăresc mai mult decât datele binare. În practică, dump-ul necomprimat cântărește 70-90% din data_length + index_length. Cu gzip standard se coboară la 15-25%, cu zstd -3 în jur de 18-28% dar mult mai rapid.

Rulând interogarea pe cele patru servere, sizing-ul total pe care l-am prezentat PM-ului a fost:

Diferența dintre “data + index” și “fișiere fizice” este costul fragmentării și al tablespace-ului ibtmp1. Merită evidențiat pentru PM pentru că pe noul mediu se poate planifica un OPTIMIZE TABLE post-migrare care recuperează acel 5-6% de spațiu [5].

📈 2. Cât cresc — snapshot-uri periodice și citire din binary log #

Cifra creșterii este mai delicată. PM-ul întreabă “cât pe lună”, dar răspunsul util este: cât prevezi să crească în următoarele trei-șase luni, adică până la următorul assessment? Există două abordări, ambele valide, pe care le folosesc împreună.

Abordarea 1 — snapshot-uri periodice. Dacă ai istoricul monitorizării (Prometheus + mysqld_exporter, Zabbix sau chiar doar folderul cu backup-urile istorice), poți reconstrui curba dimensiunilor. Dacă nu ai nimic, începe acum: un cron săptămânal care execută interogarea de mai sus și scrie rezultatul într-un tabel ops.sizing_history — după 6-8 săptămâni ai un rezultat solid.

-- Tabel de istoricizare (de rulat o singură dată)
CREATE TABLE ops.sizing_history (
    captured_at   TIMESTAMP NOT NULL,
    server_name   VARCHAR(50) NOT NULL,
    schema_name   VARCHAR(64) NOT NULL,
    data_bytes    BIGINT,
    index_bytes   BIGINT,
    num_tables    INT,
    PRIMARY KEY (captured_at, server_name, schema_name)
);

-- Snapshot săptămânal via cron
INSERT INTO ops.sizing_history (captured_at, server_name, schema_name, data_bytes, index_bytes, num_tables)
SELECT
    NOW(),
    @@hostname,
    table_schema,
    SUM(data_length),
    SUM(index_length),
    COUNT(*)
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema', 'ops')
GROUP BY table_schema;

Abordarea 2 — estimare din binary log . Acesta este trucul pe care mulți nu îl folosesc. Binlog-ul înregistrează fiecare scriere, iar dimensiunea sa zilnică este un proxy excelent pentru rata de creștere a datelor (scăzând update-urile și delete-urile, care generează trafic dar nu creștere netă). Cu binlog_expire_logs_seconds=604800 (pe MySQL 8.0+; expire_logs_days=7 pe 5.7 / MariaDB) ai o săptămână de istoric gata de citit.

# Volum zilnic binlog (ultimele 7 zile)
ls -la /var/lib/mysql/binlog.* | awk '{print substr($6" "$7,1,6), $5}' | \
    sort | awk '{a[$1]+=$2} END {for (k in a) printf "%s  %.2f GB\n", k, a[k]/1024/1024/1024}'

Rezultat tipic pe unul dintre servere:

Apr 14   3,87 GB
Apr 15   4,12 GB
Apr 16   3,95 GB
Apr 17   4,44 GB
Apr 18   2,18 GB   # sâmbătă
Apr 19   1,02 GB   # duminică
Apr 20   3,78 GB

Media în zile lucrătoare ~4 GB/zi de trafic de scriere. Rata de creștere netă a tablespace-ului este tipic între 20% și 40% din volumul binlog, în funcție de mix-ul insert/update/delete. În cazul nostru, combinând cu puținele snapshot-uri disponibile, am ajuns la o estimare de +8-12 GB pe lună per server, cu vârfuri pe mysql-03 (cel al portalului utilizatori, mai dinamic).

💾 3. Cât durează backup-ul — mysqldump, mydumper, xtrabackup #

Aici PM-ul așteaptă o singură cifră. Răspunsul onest este: depinde de ce instrument folosești, iar timpii pot diferi cu un ordin de mărime.

Pe același server (mysql-03, 218 GB de date + indecși, tabele InnoDB cu câteva MyISAM reziduale pe care nimeni nu le-a atins din 2014), am măsurat empiric patru strategii.

mysqldump (logic, single-threaded) [2]:

time mysqldump --single-transaction --quick --routines --triggers --events \
    --default-character-set=utf8mb4 --hex-blob \
    --all-databases > /backup/mysql-03-full.sql

Rezultat: 2 ore și 47 minute. Fișier SQL necomprimat: 189 GB. Cu pipe în timp real pe gzip (| gzip -3 > ...gz): 3 ore și 22 minute, fișier comprimat 38 GB.

mysqldump + zstd (preferatul meu pentru serverele PA unde timpul de CPU contează mai puțin decât fereastra):

time mysqldump --single-transaction --quick --routines --triggers --events \
    --default-character-set=utf8mb4 --hex-blob --all-databases | \
    zstd -3 -T4 > /backup/mysql-03-full.sql.zst

Rezultat: 2 ore și 58 minute, fișier comprimat 42 GB. Puțin mai mare decât gzip dar aproximativ de două ori mai rapid la decompresie la restore — care este momentul când viteza contează cu adevărat.

mydumper (logic, paralel) [3]:

time mydumper --host=localhost --user=backup --socket=/var/lib/mysql/mysql.sock \
    --threads=8 --compress --rows=500000 \
    --outputdir=/backup/mysql-03-mydumper \
    --logfile=/backup/mysql-03-mydumper.log

Rezultat: 47 minute. Output: director cu 312 fișiere comprimate, total 41 GB. De aproape 4x mai rapid decât mysqldump datorită paralelismului la nivel de chunk de tabelă.

xtrabackup (fizic, hot backup) [4]:

time xtrabackup --backup --target-dir=/backup/mysql-03-xtra \
    --user=backup --password=*** --parallel=4 --compress --compress-threads=4

Rezultat: 22 minute. Output: 179 GB necomprimat / 48 GB comprimat. Este cel mai rapid pentru că copiază fișierele InnoDB la nivel fizic în loc să regenereze INSERT-uri, dar are o restricție importantă: tabelele MyISAM reziduale sunt blocate pe durata copiei lor. Din fericire pe mysql-03 erau reziduale și citite doar de un batch nocturn, deci nu impactează.

Rezumat prezentat PM-ului:

În assessment am propus standardizarea pe mydumper pentru backup-ul periodic (zilnic, ocupă puțin spațiu, restore flexibil per schemă) și xtrabackup pentru snapshot-ul pre-upgrade (foarte rapid, ideal pentru fereastra de mentenanță strânsă).

⏱️ 4. Cât durează restore-ul — cifra pe care PM-ul uită să o ceară #

Restore-ul este locul unde assessment-urile făcute prost eșuează. Un backup poate dura 47 minute, dar reconstrucția aceluiași dataset poate cere ore — iar în fereastra de mentenanță, asta contează.

Tot pe mysql-03, măsurare empirică a cât durează reconstruirea de la zero a bazei de date pornind de la backup-urile de mai sus, pe un host geamăn (același CPU, același storage NVMe):

Din mysqldump.sql.gz:

time gunzip -c /backup/mysql-03-full.sql.gz | \
    mysql --default-character-set=utf8mb4

Rezultat: 5 ore și 12 minute. Este lent pentru că restore-ul logic regenerează fiecare rând cu INSERT-uri individuale, actualizează indecșii tranzacțional și nu poate paraleliza pe o singură tabelă.

Din mysqldump.sql.zst:

time zstd -dc /backup/mysql-03-full.sql.zst | \
    mysql --default-character-set=utf8mb4

Rezultat: 4 ore și 38 minute. Aici se vede avantajul decompresiei zstd (aproximativ 2x mai rapidă decât gzip), care este singurul element ce diferă de testul anterior.

Din mydumper cu myloader:

time myloader --host=localhost --user=root --socket=/var/lib/mysql/mysql.sock \
    --threads=8 --directory=/backup/mysql-03-mydumper \
    --disable-redo-log --overwrite-tables

Rezultat: 1 oră și 52 minute. Flag-ul --disable-redo-log (MySQL 8.0.21+) este adevăratul game-changer: sare peste generarea redo log-ului în timpul încărcării inițiale, reducând overhead-ul de I/O. De folosit DOAR pe o instanță goală în faza de import, niciodată în producție.

Din xtrabackup:

time xtrabackup --decompress --target-dir=/backup/mysql-03-xtra --parallel=4
time xtrabackup --prepare --target-dir=/backup/mysql-03-xtra
# apoi rsync al fișierelor pe noul datadir + pornire mysqld

Rezultat: 34 minute (decompress) + 12 minute (prepare) + 6 minute (copiere + restart) = 52 minute total. Restore fizic: copie binară + crash recovery, fără SQL regenerat. Este singura opțiune care se apropie de timpul backup-ului însuși.

Rezumat restore:

📋 5. Template-ul de răspuns pentru PM #

După măsurătorile pe cele patru servere, am consolidat totul într-un singur tabel, pentru că PM-ul are nevoie de o pagină de atașat la planul de cutover, nu de treizeci de slide-uri.

Pe baza acestui tabel, fereastra de mentenanță de șase ore este compatibilă cu un rollback bazat pe xtrabackup (snapshot 57 minute + restore 2h 20m = 3h 17m, cu marjă de 2h 43m pentru debug și verificări), dar incompatibilă cu un rollback bazat pe mysqldump (peste 13 ore). Decizie operațională: xtrabackup ca strategie principală de rollback, mydumper ca fallback pentru restore-uri selective per schemă dacă apar probleme punctuale în timpul cutover-ului.

PM-ul mi-a cerut patru cifre. I-am dat douăzeci și patru. Dar sunt douăzeci și patru de cifre măsurate — nu estimări aproximative — iar diferența este toată acolo.

Ce am învățat #

Un pre-upgrade assessment nu este un document tehnic, este un instrument de guvernare a riscului. Clientul care întreabă “cât durează backup-ul” de fapt întreabă “dacă totul o ia razna în fereastra de mentenanță, reușim să repunem serviciile în funcțiune înainte de ora 6 dimineața?”. Dacă răspunsul tău este “vreo trei ore, cred”, întrebarea aceea rămâne fără răspuns și riscul nu a fost măsurat.

Partea tehnică — interogările, instrumentele, măsurătorile — este partea ușoară. Partea dificilă este să faci ca cifrele măsurate să ajungă în planul de cutover, ca PM-ul să le citească, ca echipa ops să le folosească pentru a calibra fereastra. În cazul nostru PM-ul a vrut să adauge un slide în plus la întâlnirea cu vendor-ul noului storage: “uite, astea sunt cifrele de referință; dacă array-ul vostru nu susține aceste throughput-uri de restore, planul nu funcționează”. Și este exact ce ar trebui să facă un PM bun.

În final upgrade-ul a trecut în patru ore, nu șase. Fără rollback. Clientul ne-a mulțumit nu pentru fereastra scurtă, ci pentru faptul că știuseseră mereu ce s-ar întâmpla dacă ceva mergea prost. Care este adevăratul obiectiv al unui pre-upgrade assessment bine făcut.

Surse oficiale #

1. MySQL 8.0 Reference Manual — The INFORMATION_SCHEMA TABLES Table
2. MySQL 8.0 Reference Manual — mysqldump — A Database Backup Program
3. mydumper — mydumper / myloader on GitHub
4. Percona — Percona XtraBackup 8.0 Documentation
5. MySQL 8.0 Reference Manual — OPTIMIZE TABLE Statement

Glosar #

information_schema — Schema de sistem MySQL (read-only) care expune metadate despre baze de date, tabele, indecși, utilizatori și starea serverului. Punct de plecare pentru orice assessment, sizing sau analiză structurală.

xtrabackup — Instrument de backup fizic hot pentru MySQL/MariaDB dezvoltat de Percona. Copiază direct fișierele InnoDB în timp ce baza de date rulează, gestionând tranzacțiile în curs prin redo log. Semnificativ mai rapid decât backup-urile logice pe dataset-uri mari.

Pre-upgrade assessment — Măsurare structurată a dimensiunilor, ratei de creștere, timpilor de backup și timpilor de restore ai unei baze de date înainte de un upgrade. Servește la dimensionarea ferestrei de mentenanță și la definirea unei strategii de rollback realiste.

mysqldump — Utilitar de backup logic inclus în orice instalație MySQL. Produce un fișier SQL secvențial cu toate instrucțiunile pentru a recrea schema și datele. Single-threaded, fiabil dar lent pe baze de date mari.

mydumper — Instrument open source de backup logic pentru MySQL/MariaDB cu paralelism real la nivel de chunk. Împarte tabelele mari în bucăți și le exportă cu thread-uri multiple, cu restore paralel prin myloader.