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_tmp) 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 ookamž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 bude potřeba použít nějaký skript:

$max = $table->query('SELECT MAX(`id`) FROM `user`;');
$from = 0;
$batch = 1000; //zkopíruje 1000 řádek najednou a pak chvíli počká
for ($from = 0; $from < $max; $from += 1000) {
    $table->query("INSERT INTO `user_new` SELECT * FROM `user` LIMIT $from, $batch;");
    sleep(1); //čeká 1 sekundu aby ostatní procesy mohly do tabulky zapsat data
}

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).

Mezi tím si můžete připravit další krok…

Napsat komentář

Vaše emailová 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..