MySQL ALTER TABLE bez dīkstāves: INSTANT, INPLACE, gh-ost vai pt-osc

MySQL ALTER TABLE bez dīkstāves lielai tabulai lielākoties nozīmē pareizi izvēlēties metodi, pirms nospiežat Enter. Dažas izmaiņas skar tikai metadatus un beidzas uzreiz pat uz terabaitiem. Citas pārbūvē visu tabulu, prasa tikpat daudz brīvas vietas, cik aizņem pati tabula, un uz stundām atstāj katru repliku iepakaļ. Un jebkura no tām, pat tūlītējā, var iesaldēt lietotni, ja viena aizmirsta transakcija tur tabulu atvērtu. Lūk, kā saprast, kurā situācijā esat, un ko darīt.

Trīs algoritmi ar ļoti atšķirīgām izmaksām

InnoDB ir trīs veidi, kā izpildīt ALTER TABLE. MySQL rokasgrāmatas online DDL operāciju tabula norāda, kurus atbalsta katra izmaiņa:

• INSTANT maina tikai datu vārdnīcu. Dati netiek skarti, tāpēc tabulas izmēram nav nozīmes. MySQL 8.4 tas ir noklusējums operācijām, kas to atbalsta.
• INPLACE strādā InnoDB iekšienē bez pilnas tabulas kopēšanas caur servera slāni. Atkarībā no operācijas tas vai nu tikai būvē ko jaunu (piemēram, sekundāro indeksu), vai pārbūvē tabulu, taču vairumā gadījumu ļauj lasīt un rakstīt, kamēr darbojas.
• COPY izveido jaunu tabulu un kopē katru rindu. Vienlaicīgs DML nav atļauts. Lielai tabulai tas nozīmē dīkstāvi.

Vienmēr norādiet algoritmu un bloķēšanas līmeni skaidri, piemēram, ALGORITHM=INSTANT vai ALGORITHM=INPLACE, LOCK=NONE. Ja izmaiņu tā nevar izpildīt, MySQL atgriež kļūdu, nevis klusi izvēlas ko smagāku. Kļūda testa serverī ir daudz labāka par negaidītu tabulas kopēšanu produkcijā.

Kas ir tūlītējs un kas nav

Labā ziņa ir tā, ka biežākās izmaiņas ir lētas. Saskaņā ar rokasgrāmatu INSTANT atbalsta:

• kolonnas pievienošanu jebkurā pozīcijā;
• kolonnas dzēšanu;
• kolonnas pārdēvēšanu;
• kolonnas noklusējuma vērtības iestatīšanu vai dzēšanu;
• ENUM vai SET kolonnas definīcijas maiņu (ar rokasgrāmatā aprakstītajiem nosacījumiem).

ALTER TABLE orders ADD COLUMN source VARCHAR(32) NULL, ALGORITHM=INSTANT;

-- cik tūlītējo rindu versiju tabula jau izmantojusi? (limits: 64 versijā 8.4)
SELECT NAME, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME = 'shop/orders';

Ilgi dzīvojošām tabulām svarīgi divi ierobežojumi. Katra tūlītēja pievienošana vai dzēšana izveido jaunu rindas versiju, un MySQL 8.4 atļauj ne vairāk kā 64 (no 9.1 versijas 255). Pēc tam saņemat ERROR 4092 (HY000): Maximum row versions reached, un nākamajai izmaiņai tabula jāpārbūvē. Pārbūve ar INPLACE, COPY vai OPTIMIZE TABLE atiestata skaitītāju. Tūlītēja kolonnas pievienošana nedarbojas arī tabulām ar ROW_FORMAT=COMPRESSED, tabulām ar FULLTEXT indeksu un pagaidu tabulām.

Šīs operācijas lielai tabulai sāp:

• Kolonnas datu tipa maiņa, piemēram, INT uz BIGINT, tiek atbalstīta tikai ar ALGORITHM=COPY. Bez vienlaicīga DML. Klasisks gadījums gh-ost vai pt-online-schema-change rīkam.
• VARCHAR paplašināšana notiek uz vietas un skar tikai metadatus, kamēr garuma baitu skaits nemainās. Līdz 255 baitiem izmanto vienu garuma baitu, no 256 baitiem — divus. Šīs robežas šķērsošanai vajag tabulas kopiju. Ievērojiet, ka robeža ir baitos, nevis rakstzīmēs: ar utf8mb4 (līdz 4 baitiem rakstzīmei) pāreja no VARCHAR(63) uz VARCHAR(64) to jau šķērso.
• Kolonnas maiņa uz NULL vai NOT NULL, kolonnu secības maiņa un primārās atslēgas pievienošana pārbūvē tabulu uz vietas. DML ir atļauts, bet tabula tiek pārrakstīta.
• Sekundārā indeksa pievienošana notiek uz vietas un tabulu nepārbūvē, taču tā tik un tā nolasa visu tabulu un kārto indeksu, tāpēc lielai tabulai tas aizņem ilgu laiku.

Ar skaidri norādītu algoritmu izmaiņa, ko nevar veikt uz vietas, uzreiz beidzas ar kļūdu. Rokasgrāmatas piemērs vienbaita rakstzīmju kopai, pārejot no VARCHAR(255) uz VARCHAR(256):

ALTER TABLE t ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
column type INPLACE. Try ALGORITHM=COPY.

Metadatu bloķēšanas rinda: kā "online" ALTER iesaldē produkciju

Tas ir viens no biežākajiem shēmas maiņas incidentiem, un tas notiek pat ar tūlītējām izmaiņām. Katram ALTER TABLE vajag ekskluzīvu metadatu bloķējumu uz tabulas, vismaz uz īsu brīdi. Rokasgrāmata apraksta secību precīzi:

1. Kāda transakcija ir lasījusi no tabulas un vēl nav apstiprināta. Tā tur koplietojamu metadatu bloķējumu. Tā var būt atskaite, pakešuzdevums vai savienojums, ko lietotne atstājusi dīkstāvē transakcijas vidū.
2. Jūsu ALTER TABLE pieprasa ekskluzīvo bloķējumu un gaida šo transakciju.
3. Katrs jauns vaicājums uz tabulu tagad gaida aiz gaidošā ALTER, arī vienkārši SELECT.

No malas izskatās, ka tabula nestrādā. Savienojumi krājas, pūls izsīkst, un lietotne sāk kļūdīties, un tas viss vienas transakcijas dēļ, kas neko nedarīja. SHOW FULL PROCESSLIST parāda gaidošās sesijas.

Noklusējuma lock_wait_timeout ir 31536000 sekundes, tas ir, viens gads. Tātad pēc noklusējuma ALTER gaidīs un liks gaidīt visiem citiem praktiski bezgalīgi. Pirms jebkura DDL uz noslogotas tabulas samaziniet to savā sesijā un vispirms pārbaudiet ilgas transakcijas:

-- padoties pēc 5 sekundēm, nevis likt visiem gaidīt aiz mums
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

Ja bloķējums netiek piešķirts 5 sekundēs, ALTER beidzas ar kļūdu, rinda iztīrās, un varat mēģināt vēlāk. Neizdevies mēģinājums neko nemaksā, bet bloķēta tabula maksā incidentu. Šī metadatu bloķējuma gaidīšana atšķiras no InnoDB rindu bloķējumu gaidīšanas, ko aprakstām mūsu rakstā par lock wait timeout.

Lielas tabulas: disks, žurnāli un replikas

Dažu gigabaitu tabulai pārbūve uz vietas ir kafijas pauze. Pie simtiem gigabaitu pirms sākšanas pārbaudiet šo.

• Diska vieta. Saskaņā ar vietas prasību lapu pārbūve var izveidot starptabulu tajā pašā direktorijā, kur oriģināls, līdz oriģinālās tabulas izmēram. Pagaidu kārtošanas faili tiek rakstīti MySQL pagaidu direktorijā un var prasīt tikpat vietas, cik tabula kopā ar indeksiem. Ja tmpdir ir mazs, novirziet innodb_tmpdir uz lielāku sējumu. Diska vietas beigšanās pārbūves vidū produkcijā ir slikta diena.
• Online alter žurnāls. Rakstīšana pārbūves laikā tiek ierakstīta pagaidu žurnālā, ko ierobežo innodb_online_alter_log_max_size (noklusējums 128 MB). Ja intensīva rakstīšana to piepilda, operācija beidzas ar DB_ONLINE_LOG_TOO_BIG, un neapstiprinātais vienlaicīgais DML tiek atcelts. Lielāks limits ļauj vairāk rakstīšanas, bet arī pagarina bloķēto fāzi beigās, jo ierakstītās izmaiņas jāpiemēro.
• Nav slodzes ierobežošanas. Rokasgrāmata uzskaita ierobežojumus lielām tabulām: online DDL operāciju nevar apturēt vai ierobežot, un tās atcelšana pēc kļūmes var būt dārga.
• Replikas. Replika sāk ALTER tikai tad, kad tas beidzies avotā, un rakstīšana, kas avotā notikusi pa to laiku, replikā tiek piemērota tikai pēc tam, kad ALTER tur ir pabeigts. Sešu stundu pārbūve nozīmē aptuveni sešas stundas repliku nobīdes. Ja lasīšana, rezerves kopijas vai failover ir atkarīgi no replikām, tas bieži ir izšķirošais faktors. Mūsu MySQL replikācijas nobīdes ceļvedis apraksta, kā to uzraudzīt.

Ilgu ALTER var sekot līdzi caur Performance Schema posmu notikumiem. WORK_ESTIMATED darbības laikā var mainīties, tāpēc uztveriet to kā aptuvenu rādītāju:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
 WHERE NAME LIKE 'stage/innodb/alter%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
 WHERE NAME LIKE '%stages%';

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
FROM performance_schema.events_stages_current;

gh-ost un pt-online-schema-change

Ja izmaiņai vajag pārbūvi vai kopiju un tabula ir liela un noslogota, online shēmas maiņas rīki parasti ir labāka izvēle. Abi izveido jaunu tabulu ar jauno struktūru, kopē rindas mazās daļās, sinhronizē to ar notiekošo rakstīšanu un beigās samaina tabulas. Atšķirība ir sinhronizācijas veidā.

pt-online-schema-change no Percona Toolkit izmanto trigerus uz oriģinālās tabulas un samaina tabulas ar atomāru RENAME TABLE. Kas jāzina:

• Vairumā gadījumu tam vajag PRIMARY KEY vai UNIQUE indeksu, jo tas nepieciešams DELETE trigerim.
• Esoši trigeri uz tabulas ir problēma, ja vien neizmantojat --preserve-triggers.
• Ārējām atslēgām, kas atsaucas uz tabulu, vajag lēmumu ar --alter-foreign-keys-method. Metode drop_swap ir ātrāka, bet atstāj brīdi, kad tabula neeksistē.
• Tas apstājas, ja pārsniegts --max-load (noklusējums Threads_running=25), un pārtrauc darbu pie --critical-load (noklusējums Threads_running=50). Bez --execute tas tikai veic pārbaudes.

gh-ost no GitHub neizmanto trigerus. Tā vietā tas lasa izmaiņas no binārā žurnāla, tāpēc rakstīšana oriģinālajā tabulā neiedarbina papildu trigerus. Tā prasības:

• rindu formāta binārie žurnāli ar FULL rindas attēlu;
• uz tabulas nav ārējo atslēgu un trigeru;
• vecajai un jaunajai tabulai kopīga PRIMARY KEY vai UNIQUE atslēga.

Tā stiprā puse lielām tabulām ir kontrole: tas ierobežo darbu pēc repliku nobīdes ar --max-lag-millis, pēc slodzes ar --max-load, pārtrauc darbu pie --critical-load un var atlikt galīgo pārslēgšanu, līdz izdzēšat karoga failu, tāpēc maiņa notiek tad, kad jūs izvēlaties:

gh-ost --host=replica-1 --database=shop --table=orders \
  --alter="MODIFY amount DECIMAL(14,2) NOT NULL" \
  --max-lag-millis=1500 --max-load=Threads_running=30 \
  --critical-load=Threads_running=80 \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone \
  --execute

Abi rīki kopē tabulu, tāpēc tiem vajag brīvu vietu pilnai otrai kopijai. Kopēšana sastāv no parastas rakstīšanas, tāpēc tā nonāk arī binārajā žurnālā un katrā replikā. Tas ir lēnāk nekā iebūvēta pārbūve, taču ierobežojams, apturams un tur replikas gandrīz aktuālas, un produkcijā parasti tieši to vajag.

Pārbaudes saraksts pirms ALTER lielai tabulai

1. Noskaidrojiet algoritmu: izpildiet komandu ar skaidri norādītu ALGORITHM kopijā ar to pašu shēmu un skatieties, vai MySQL to pieņem.
2. Tūlītējām izmaiņām pārbaudiet TOTAL_ROW_VERSIONS.
3. Pārbūvēm pārbaudiet brīvo vietu datu direktorijā un tmpdir/innodb_tmpdir.
4. Pārbaudiet ilgas un dīkstāvē esošas transakcijas uz tabulas un iestatiet savā sesijā īsu lock_wait_timeout.
5. Novērtējiet ilgumu un repliku nobīdi uz pilna izmēra kopijas un izlemiet, vai der iebūvēts DDL vai rīks.
6. Sagatavojiet svaigu, pārbaudītu rezerves kopiju un iepriekš izlemiet, kā apturēt vai atcelt.