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?
Rychlá navigace
Teorie: Alter algoritmus
MySQL engine InnoDB 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, změna výchozí hodnoty, přidání hodnoty do ENUM, přidání nového indexu, 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 stá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 nejprve 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.
Jak už jsem zmínil, algoritmy ale podporuje pouze InnoDB. Pokud máte tabulku MyISAM nebo jinou, budete muset zkusit něco jiného.
No lock
Od verze MySQL 5.6 můžete ALTER algoritmus ještě rozšířit o parametr LOCK, který upřesní, jak se má databáze postarat o zámky během změny. Výhoda LOCK parametru je v tom, že by měla fungovat na všechny MySQL enginy vč. MyISAM.
-- InnoDB
ALTER TABLE t ADD INDEX i(id), ALGORITHM=INPLACE, LOCK=NONE;
-- MyISAM
ALTER TABLE t ADD INDEX i(id), LOCK=NONE;
Hodnota LOCK=NONE zakáže používání zámků během operace. Pokud operaci není možno bez zámku provést, operace selže, místo aby tabulku zamkla. Hodnota LOCK=SHARED umožní čtení dat, ale zakáže jejich změnu (INSERT, UPDATE, DELETE). Hodnota LOCK=EXCLUSIVE zakáže veškerý přístup k tabulce. Ve většině případů tedy chcete nejprve zkusit LOCK=NONE a pouze pokud takový příkaz selže, můžete přemýšlet, jak jinak provést změnu za běhu (nebo naplánovat odstávku).
Pravidla pro co nejrychlejší změnu tabulky
- 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. Tím zabráníte tomu, aby nástroj příkaz nějak upravil a znemožnil použití rychlejší metody nebo použil další příkazy (např. PhpMyAdmin často přidává příkaz pro spočtení řádek v tabulce; některé nástroje mohou po změně tabulky spustit její optimalizaci, což u InnoDB znamená zkopírování dat). - 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. - Pokud měníte jeden 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.
- Pro přidání nové indexu to nekombinujte s jinými změnami a použijte algoritmus
INPLACE(pro InnoDB) aLOCK=NONE. Nový index lze vytvořit bez zamčení tabulky, ale jen pokud nedojde k jiné změně. Stále ale myslete na to, že vytvořit nový index na velké tabulce zabere čas a zatíží server! - Pro změnu indexu použijte 3 oddělené akce
ADD,DROPaRENAME(v tomto pořadí) – tyto akce lze provést pomocíINSTANTalgoritmu sLOCK=NONE. Myslete ale na to, že první příkaz musí vytvořit nový index, což zabere čas a zatíží server! - Pokud měníte index z
UNIQUEnaINDEX(nebo obráceně), nekombinujte to s jinými akcemi s indexy. Změna lze (jako jediná) provést pomocí INPLACE algoritmu. - 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.
- 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.
- 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.
- V ENUM nebo SET nevytvářejte „reserved“ hodnoty za účelem vytvoření dostatečně velkého sloupce nebo pozdější změnu jména. Akce přejmenování hodnoty není podporována INPLACE ani INSTANT algoritmy a jen tím vynutíte COPY metodu.
- Při definici sloupce uvažujte, zda se v budoucnu může zvětšit (např. pro sloupec typu
boolbude stačit typINT(1), ale proidpoužijte vždyINT(10)i když zadavatel tvrdí, že víc jak 1000 položek v tabulce nebude; naopak pro tabulky, kde očekáváte miliony řádek, rovnou použijteBIGINT). - 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. (Pozn.: V MySQL je vše transakce; zde je tím myšleno nedávat více různých příkazů do jedné transakce, ale naopak nechat každý příkaz jako samostatnou transakci.)
Vypnutí FOREIGN KEY
Pro změnu indexu, který je použit 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;
Změna přes příkazový řádek
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ý algoritmus nepodporuje nebo nevědomky měníte index, který je svázán s FOREIGN KEY. (Další důvody můžete odvodit z předchozí kapitoly o rychlé změní tabulky.)
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 ho 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";
usleep(100_000); //čeká 100 milisekund 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 (čekání usleep()). Mezi každým příkazem byste měli provést nějaké čekání – pro člověka se může zdát 100ms velmi krátkých, ale pro databázi je to spousta času pro získání zámků a spuštění ostatních čekajících příkazů.
V závislosti na tom, jak tabulka mění data byste měli použít správnou transakci – viz Jak správně používat zámky a transakce v MySQL a InnoDB.
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. Příprava na dokončení
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í poslední id na 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.
Poznámka: změna autoinkrement ID je obecně dobrá při větší změně. Pokud později budete potřebovat analyzovat nějaký problém, můžete snadno zjistit, jestli byl záznam (např. uživatel) vytvořen před změnou (ID menší než 5M) nebo až po změně (ID větší než 5M dle příkladu výše).
4. Přejmenování tabulky
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; resp. odstávka bude trvat jen pár milisekund – po dobu co si příkaz vytvoří zámek na obou tabulkách a změní jejich jména.
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.
- Přepněte projekt, aby běžel se SLAVE serveru.
- Vypněte synchronizaci mezi MASTER a SLAVE databázemi.
- Na MASTER serveru proveďte potřebné změny (klidně použijte COPY ALTER, protože vám odstávka tabulky nevadí).
- 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ě).
- Přepněte projekt zpět na MASTER server.
- Dokopírujte data (vytvořená během kroku 5) ze SLAVE na MASTER.
- Zapněte synchronizaci mezi MASTER -> SLAVE databázemi.
- Nechte SLAVA server, by provedl změny (krok 3)
- 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.