Změna MySQL tabulky bez odstávky

Potřebujete přidat nebo změnit sloupec v tabulce, ale ta má miliony řádek a změny by trvala několik hodin. Vy si ale nemůžete dovolit DB odstavit ani na pár minut, protože jde o kritický systém?

Teorie: Alter algoritmus

MySQL používá tzv. algoritmy pro změnu tabulky (příkaz ALTER TABLE). V MySQL a MariaDB existují 3 algoritmy:

  • COPY jednoduše vytvoří novou tabulku se změněnou strukturou, zkopíruje do ní data ze staré tabulky a následně starou tabulku smaže. Tuto metodu lze použít na jakoukoliv změnu, ale má za následek zamčení tabulky a pravděpodobnou odstávku projektu.
  • INPLACE se pokusí změnit existující tabulku, ale jen v případě, že změna nevyžaduje změnu velikosti sloupců (přejmenování sloupce nebo tabulky, změna výchozí hodnota, přidání hodnoty do ENUM, apod.).
  • INSTANT může zvětšit existující tabulky, ale jen za předpokladu, že pouze přidáváte data („na konec“) aniž byste měnili svávající data (přidání nového sloupce, přidání nebo odebrání výchozí hodnoty, přejmenování tabulky, apod.)
  • NOCOPY je 4. algoritmus dostupný v MariaDB a jde o rozšíření INSTANT algoritmu. Kromě přidávání nových položek dokáže v některých případech i měnit nebo odebírat data (sloupce, výchozí hodnoty, indexy, atd.) – pokud používáte MariaDB, jednoduše místo INSTANT používejte NOCOPY.

MySQL by se vždy měla pokusit provést změnu co nejšetrněji. Tedy nejrpve by se měla pokusit provést změnu pomocí INSTANT algoritmu a pokud se to nepovede, měla by zkusit INPLACE. Metoda COPY by měla přijít na řadu jen v případě, že se nepovede ani jedna z předchozích.

Pravidla pro co nejrychlejší změnu tabulky

  1. Nepoužívejte PhpMyAdmin ani žádné jiné „chytré“ nástroje. Připojte se přímo k DB konzolovým programem (např. mysql.exe) a zadávejte přímé SQL příkazy.
  2. Pokud přidáváte sloupec, vždy ho dávejte jako poslední (tzn. nepoužívejte AFTER). Pokud potřebujete mít sloupce v jiném pořadí, vytvořte si následně VIEW.
  3. Pokud měníte sloupec, nekombinujte několik akcí dohromady. Např. INSTANT algoritmus může sloupec přejmenovat a přidat nebo odebrat výchozí hodnotu, zatímco INPLACE může změnit existující výchozí hodnotu a změnit kódovou stránku (z kompatibilní UTF8 na UTF8MB4 nebo jakoukoliv na BINARY). Změna typu či velikost vyžaduje COPY. Pokud tedy chcete změnit jméno a výchozí hodnotu, zadejte to jako dva samostatné příkazy.
  4. Pro změnu indexu použijte akce ADD, DROP a RENAME (v tomto pořadí) – tyto akce lze provést pomocí INSTANT algoritmu
  5. Pokud měníte index z UNIQUE na INDEX (nebo obráceně), nekombinujte to s jinými akcemi s indexy. Změna lze (jako jediná) provést pomocí INPLACE algoritmu.
  6. Pokud měníte index použitý pro FOREIGN KEY, vypněte jejich kontrolu (viz níže) – jinak bude muset DB použít COPY algoritmus při kterém ověří správné provázání tabulek.
  7. Pokud přidáváte hodnoty do ENUM nebo SET, přidávejte je na konec a ujistěte se, že se jména a pořadí stávajících hodnot nezměnila.
  8. SET se ukládá po 8 bitech (a přidání 9. bitu znamená zvětšit sloupec o 1 byte). Pokud již máte SET s 8, 16, atd. hodnotami a potřebujete přidat další, přidejte další sloupec (na konec) místo změny stávajícího sloupce.
  9. V ENUM nebo SET nevytvářejte „reserved“ hodnoty za účelem vytvoření dostatečně velkého sloupce. Akce přejmenování hodnoty není podporována INPLACE a INSTANT algoritmy a jen tím vynutíte COPY metodu.
  10. Při definici sloupce uvažujte, zda se v budoucnu může zvětšit (např. pro sloupec typu bool bude stačit typ INT(1), ale pro id použijte vždy INT(10) i když zadavatel tvrdí, že víc jak 1000 položek v tabulce nebude).
  11. Nepoužívejte transakce pro změnu struktury databáze. I když provádíte změny s použitím INSTANT nebo INPLACE algoritmů, v případě selhání transakce bude muset databáze vrátit tabulky zpět, což si může vyžádat COPY algoritmus (např. mazání nových sloupců apod.). Můžete se tak nechtěně vystravit nečekané odstávce, i když jste celý proces připravili pro rychlou změnu.

Pro změnu indexu, který je použít pro FOREIGN KEY použijte:

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `table` RENAME INDEX index_name TO FK_index_name;
SET FOREIGN_KEY_CHECKS = 1;

Pro spuštění připraveného SQL skriptu na konkrétním MySQL serveru použijte:

> mysql -h192.168.0.1 -uroot -p123456 < script.sql

Kde mysql je jméno klientského programu (např. mysql.exe), h je IP adresa MySQL serveru, u je jméno uživatele a p je jeho heslo. Program se připojí k serveru a postupně na něj odešle všechny příkazy nalezené v souboru script.sql. Tímto způsobem můžete rychle provádět sekvence příkazů např. na přejmenování tabulek, sloupců, indexů, atd. aniž by to na delší dobu odstavilo projekt.

Praxe: Proč COPY?

Jak bylo uvedeno výše, databáze by se měla pokusit použít INSTANT a INPLACE algoritmy před tím, než sáhne k dlouhotrvajícímu COPY.

V praxi se ale často setkáte s tím, že i jednoduše vypadající příkaz bude trvat desítky minut nebo hodiny, protože DB prostě použije COPY aniž by se zdánlivě pokusila udělat INSTANT nebo INPLACE nahrazení.

Důvodů může být celá řada: např. tabulka používá engine, který daný algoritmus nepodporuje nebo nevědomky měníte index, který je svázán s FOREIGN KEY. Cílem tohoto článku není vyjmenovat a vysvětlit všechny případy, kdy k tomu dojde.

Pokud si chcete být jisti, že daná změna proběhne pomocí konkrétního algoritmu a nesklouzne k nechtěnému COPY, můžete přidat příkazy pro vynucení algoritmu:

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE `table` 
  RENAME COLUMN `value` TO `old_value`; //MySQL 8+

SET SESSION alter_algorithm='INSTANT';
ALTER TABLE `table` 
  ADD COLUMN `new_value` VARCHAR(255) 
     CHARACTER SET utf8 
     COLLATE utf8_general_ci 
     NULL DEFAULT NULL;

SET SESSION alter_algorithm='DEFAULT';

Tímto vynutíte na databázi, aby použila pro příkazy daný algoritmus. Pokud by daný algoritmus na tabulku použít nešel, příkaz selže a zastaví se vykonávání ostatních. Následně můžete problém analyzovat a příkazy restrukturalizovat pro jiný algoritmus.

Ruční COPY změna

Pokud se vám nedaří změnu provést INPLACE ani INSTANT algoritmem, ale nechcete obětovat čas odstávky na použití COPY metody, můžete použít připravený nástroj: 

> sudo apt-get install percona-toolkit
> pt-online-schema-change --alter

nebo si ji naprogramovat sami:

1. Nová tabulka

Nejprve budete potřebovat novou tabulku (např. user_new) coby kopii té staré (user) a následně na ní provést potřebné změny.

mysql> CREATE TABLE `user_new` LIKE `user`;

Jelikož CREATE TABLE ... LIKE kopíruje pouze strukturu, musíte následně vytvořit všechny potřebné indexy a FOREIGN KEY:

mysql> ALTER TABLE `user_new` 
           ADD PRIMARY KEY (`id`) 
           ADD KEY `FK_group` (`group_id`) 
           ADD CONSTRAINT FK_user_group_id 
               FOREIGN KEY (`group_id`) 
               REFERENCES `group` (`id`) 
               ON DELETE CASCADE 
               ON UPDATE CASCADE
      ;

Teď můžete provést potřebné změny – jelikož je tabulka prázdná, změna proběhne okamžitě, i když použije COPY algoritmus. Pro příklad zvětšíme velikost sloupce username:

mysql> ALTER TABLE `user_new`
           MODIFY COLUMN `username` CHAR(64);

2. Kopírování dat do nové tabulky

Dalším krokem je zkopírovat všechna existující data do nové tabulky. Jelikož celý tenhle proces podstupujete proto, že jde o hodně velkou tabulku, bude asi potřeba provádět kopírování postupně po malých blocích, aby nedošlo k zablokování staré tabulky, do které se stále zapisují nová data z programu. K tomu bude potřeba použít nějaký skript:

$max = $table->query('SELECT MAX(`id`) FROM `user`;');
$from = 0; //počátek kopírování (viz níže)
$batch = 1000; //zkopíruje 1000 řádek najednou a pak chvíli počká
for (; $from < $max; $from += $batch) {
    $table->query("INSERT INTO `user_new` SELECT * FROM `user` LIMIT $from, $batch;");
    echo 'Zpracováno ', floor($from*100/$max), "% ($from/$max)\r";
    sleep(1); //čeká 1 sekundu aby ostatní procesy mohly do tabulky zapsat data
}
echo 'Dokončeno na ', $from; //pro opětovné spuštění

Kopírování může trvat několik hodin v závislosti na množství dat a zvolené rychlosti (při 1000 řádek za sekundu bude 10 milionů řádek trvat minimálně 3 hodiny).

Poznámka: uvedení "\r" na konci echo výpisu uvnitř FOR zajistí, že se průběh kopírování bude zobrazovat na jedné řádce.

Důležitý fakt k tomuto postupu je ten, že nová data nesmí do tabulky přibývat rychleji než jste schopni je kopírovat. V opačném případě by kopírování nikdy neskončilo! (Můžete zkusit větší $batch nebo kratší čekání pomocí usleep() pro zrychlení kopírování).

V závislosti na tom, jak je skript napsán, může být potřeba ho spustit několikrát, aby se zkopírovala i data, která vznikla v době kopírování (k tomu slouží vypsání $from na konci a možnost na začátku $from nastavit na tuto hodnotu). Alternativně můžete rovnou použít $from = $table->query('SELECT MAX(id) FROM user_new');

3. Dokončení a přejmenování

Pokud má tabulka nastavený autoinkrement na primárním sloupci (tzn. ID se generují automaticky pro nové řádky), bude potřeba si vytvořit rezervu (viz dále). Koukněte, jaké je poslední ID v nové tabulce, a odhadnětě, kolik nových řádek může přibýt během pár minut. Následně nastavte autoincrement nové tabulky tak, aby vám zbylo alespoň 2x tolik volných míst (než kolik jich přibude za předpokládanou dobu).

ALTER TABLE user_new AUTO_INCREMENT=5000000000;

Například, pokud máte v tabulce user aktuálně 4 725 236, nastavte autoincrement na 4 730 000, čímž získáte asi 5000 volných míst pro dokončení změn (tzn. pokud čekáte, že na dokončení potřebujete 5 minut, neměli byste mít víc než cca 500 registrací za minutu.

V případě tabulek, které rostou výrazně rychleji (např. log nebo statistiky), bych v uvedeném případě doporučil nastavit autoincrement na 4 800 000 nebo rovnou 5 000 000, abyste měli dostatečně velkou rezervu.

Dalším krokem je pak přejmenovat stávající tabulky a nahradit jí novou tabulkou.

RENAME TABLE user TO user_old
             user_new TO user;

Jelikož jde udělat obě přejmenování jedním příkazem, neměla by odstávka být potřeba. Po přejmenování se nová data automaticky začnou zapisovat do nové tabulky.

Následně ještě ověřte, jestli nová tabulka skutečně obsahuje všechny řádky té staré a pokud ne (před nebo během přejmenování stihla DB zapsat ještě nějaká data do staré tabulky), zkopírujte je ručně (nebo upravte jména tabulky ve skriptu). To je možné díky tomu, že jste si připravili větší autoincrement, takže data, která se začnou zapisovat do nové tabulky nebudou mít stejná ID jako data zapsaná ještě do staré tabulky!

Posledním krokem je pak logicky smazání staré tabulky:

DROP TABLE user_old;

Změna pomocí MASTER-SLAVE

Pokud máte na databázi zapnutou synchronizaci MASTER -> SLAVE a jste schopni v případě potřeby přepnout projekt na SLAVE server (jinak synchronizace nedává moc smysl), můžete to využít k bez-odstávkové změně struktury.

  1. Přepněte projekt, aby běžel se SLAVE serveru.
  2. Vypněte synchronizaci mezi MASTER a SLAVE databázemi.
  3. Na MASTER serveru proveďte potřebné změny (klidně použijte COPY ALTER, protože vám odstávka tabulky nevadí).
  4. Zkopírujte nová data (vytvořená během kroku 3) ze SLAVE serveru zpět na MASTER (využijte zpětnou synchronizaci SLAVE -> MASTER nebo je zkopírujte ručně).
  5. Přepněte projekt zpět na MASTER server.
  6. Dokopírujte data (vytvořená během kroku 5) ze SLAVE na MASTER.
  7. Zapněte synchronizaci mezi MASTER -> SLAVE databázemi.
  8. Pokud je potřeba, přeskočte synchronizační příkazy dat, která jste kopírovali ručně.

Pokud máte MASTER-SLAVE synchronizaci, budu předpokládat, že se v MySQL databázích vyznáte podrobněji a bude vám tento zkrácený seznam stačit.

V případě MASTER-SLAVE synchronizace byste měli mít nastaven autoincrement tak, aby data z MASTER a SLAVE serverů nekolidovala (např. lichá a sudá ID), takže není potřeba provádět výše uvedený krok s navýšením autoinrementu.

Při přepínání mezi databázemi si jen dejte pozor, aby na serveru nezůstali běžet nějaké dlouhotrvající skripty (crony nebo uživatelské dotazy). Pokud takové riziko hrozí, doporučuji mezi kroky 1 a 2 nechat nějakou dobu (minuty až hodiny) na doběhnutí všech skriptů, které ještě čtou (a hlavně zapisují) do staré (MASTER) databáze. Stejný postup platí u kroků 5 a 6.

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *

Tato stránka používá Akismet k omezení spamu. Podívejte se, jak vaše data z komentářů zpracováváme..