V tomto článku se budeme zabývat metodami komprese a defragmentace tabulek a databází v MySQL / MariaDB, které vám umožní ušetřit místo na disku z databáze.
Ve velkých projektech časem databáze rostou do obrovské velikosti a vždy vyvstává otázka, jak se s tím vypořádat. Tento problém lze vyřešit několika způsoby. Množství dat v samotné databázi můžete snížit odstraněním starých informací, rozdělením databáze na několik, zvětšením místa na disku na serveru nebo komprimováním tabulek.
Dalším důležitým aspektem fungování databáze je potřeba periodické defragmentace tabulek a databází, což může výrazně urychlit jejich práci.
Obsah:
- Komprese a optimalizace databáze s typem tabulky InnoDB
- Komprimujte tabulky MyISAM v MySQL
- Optimalizace tabulek a databází v MySQL / MariaDB
Komprese a optimalizace databáze s typem tabulky InnoDB
Soubory Ibdata1 a ib_log
Na mnoha projektech s tabulkami Innodb je problém s obrovskými velikostmi souborů ibdata1 a ib_log. Důvodem je ve většině případů nesprávné nastavení serveru MySQL / MariaDB nebo architektura databáze. Všechny informace z tabulek Innodb uloženy v souboru ibdata1, jehož prostor není sám o sobě uvolněn. Raději ukládám data tabulky do samostatných souborů ibd *. To provedete v konfiguračním souboru my.cnf přidat řádek:
innodb_file_per_table
nebo
innodb_file_per_table = 1
Pokud je váš server již nakonfigurován a máte několik pracovních databází s tabulkami Innodb, musíte udělat následující:
- Vytvořte zálohu všech databází na serveru (kromě mysql a performance_schema). Základny výpisu lze odstranit pomocí následujícího příkazu:
# mysqldump -u [uživatelské jméno] -p [heslo] [jméno_databáze]> [dump_file.sql] - Po zálohování databáze zastavte server mysql / mariadb;
- Změňte nastavení v souboru my.cfg;
- Mazat soubory ibdata1 a ib_log Soubory
- Spusťte server mysql / mariadb;
- Obnovení všech databází ze zálohy:
# mysql -u [uživatelské jméno] -p [heslo] [jméno_databáze] < [dump_file.sql]
Po dokončení tohoto postupu jsou všechny tabulky Innodb budou uloženy v samostatných souborech a souborech ibdata1 nebude růst exponenciálně.
Komprese tabulky InnoDB
Tabulky můžete komprimovat daty typu text / BLOB. Pokud máte podobné tabulky, můžete ušetřit spoustu místa na disku.
Mám databázi innodb_test s tabulkami, které by mohly být komprimovány a uvolněny místo na disku. Před každou prací důrazně doporučujeme zálohovat všechny své databáze. Připojte se k serveru mysql:
# mysql -u root -p
V konzole mysql se přihlaste do požadované databáze:
# use innodb_test;
Chcete-li zobrazit tabulky a jejich velikost, použijte dotaz:
SELECT název_tabulky AS "Tabulka",
ROUND (((datová délka + indexová délka) / 1024/1024), 2) AS „Velikost v (MB)“
FROM information_schema.TABLES
WHERE table_schema = "innodb_test"
OBJEDNÁVKA (data_length + index_length) DESC;
Kde innodb_test je název vaší databáze.
Je pravděpodobné, že některé tabulky lze komprimovat. Jako příklad vezměte tabulku b_crm_event_relations. Spusťte požadavek:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT = COMPRESSED;
Dotaz je v pořádku, 0 ovlivněných řádků (3,27 s) Záznamy: 0 Duplikáty: 0 Varování: 0
Po spuštění můžete vidět, že v důsledku komprese se velikost tabulky snížila z 26 na 11 MB.
Díky komprimaci tabulky můžete na serveru ušetřit spoustu místa na disku. Při práci s komprimovanými tabulkami se však zatížení procesoru zvýší. Komprese tabulky by měla být použita, pokud nemáte problémy s prostředky procesoru, ale je zde problém s diskovým prostorem.
Komprimujte tabulky MyISAM v MySQL
Komprimovat formátovací tabulky Myisam, musíte použít speciální požadavek ze serverové konzole, nikoli z konzole mysql. Chcete-li komprimovat požadovanou tabulku, proveďte následující kroky:
# myisampack -b / var / lib / mysql / test / modx_session
Kde / var / lib / mysql / test / modx_session je cesta k vaší tabulce. Bohužel jsem neměl nafouklou databázi a musel jsem provádět komprimaci na malých tabulkách, ale výsledek je stále viditelný (soubor byl komprimován z 25 na 18 MB):
# du -sh modx_session.MYD
25M modx_session.MYD
# myisampack -b / var / lib / mysql / test / modx_session
Komprese /var/lib/mysql/test/modx_session.MYD: (4933 záznamů) - Výpočet statistiky - Komprimace souboru 29,84% Nezapomeňte spustit komprimovanou tabulku myisamchk -rq
# du -sh modx_session.MYD
18M modx_session.MYD
V požadavku jsme určili přepínač -b, když je přidán, před kompresí je vytvořena záloha tabulky a označena jako OLD:
# ls -la modx_session.OLD
-rw-r ----- 1 mysql mysql 25550000 17. prosince 15:20 modx_session.OLD
# du -sh modx_session.OLD
25M modx_session.OLD
Optimalizace tabulek a databází v MySQL / MariaDB
Pro optimalizaci tabulek a databází se doporučuje provést defragmentaci. Zkontrolujte, zda v databázi nejsou tabulky, které vyžadují defragmentaci.
Pojďme vstoupit do konzole MySQL, vyberte databázi, kterou potřebujete, a spusťte dotaz:
vyberte table_name, round (data_length / 1024/1024) jako data_length_mb, round (data_free / 1024/1024) jako data_free_mb z information_schema.tables kde round (data_free / 1024/1024)> 50 řádek podle data_free_mb;
Zobrazíme tedy všechny tabulky, které mají minimálně 50 MB nevyužitého prostoru:
+-------------------------------+----------------+--------------+ | TABLE_NAME | data_length_mb | data_free_mb | + ----------------------------------- + ---------------- + -------------- + | b_disk_deleted_log_v2 | 402 | 64 | | b_crm_timeline_bind | 827 | 150 | | b_disk_object_path | 980 | 72 |
data_length_mb - celková velikost tabulky
data_free_mb - nevyužitý tabulkový prostor
Můžeme defragmentovat tyto tabulky. Předtím zkontrolujte místo na disku:
# ls -lh / var / lib / mysql / innodb_test / | grep b_
-rw-r ----- 1 mysql mysql 402M 17. prosince 15:43 b_disk_deleted_log_v2.MYD -rw-r ----- 1 mysql mysql 828M 17.prosince 14:52 b_crm_timeline_bind.MYD -r-r ----- 1 mysql mysql 981M 17. prosince 15:45 b_disk_object_path.MYD
Chcete-li tyto tabulky optimalizovat, použijte v konzole mysql následující příkaz:
# OPTIMALIZOVAT TABULKU b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
Po úspěšné defragmentaci byste měli mít něco jako tento výstup:
+-------------------------------+----------------+--------------+ | TABLE_NAME | data_length_mb | data_free_mb | + ----------------------------------- + ---------------- + -------------- + | b_disk_deleted_log_v2 | 74 | 0 | | b_crm_timeline_bind | 115 | 0 | | b_disk_object_path | 201 | 0 |
Jak vidíte, data_free_mb je nyní 0 a celková velikost tabulky se výrazně snížila (3-4krát).
Můžete také defragmentovat pomocí obslužného programu mysqlcheck z konzoly serveru:
# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file
Kde je vaše databáze innodb_test
A b_workflow_file je název požadované tabulky
Chcete-li optimalizovat všechny potřebné tabulky databáze, spusťte příkaz v konzole serveru:
# mysqlcheck -o innodb_test -u root -p
Kde innodb_test je název požadované databáze.
Nebo spusťte optimalizaci všech databází na serveru:
# mysqlcheck -o --all-database -u root -p
Pokud zkontrolujete velikost databáze před a po optimalizaci, velikost jako celek se snížila:
# du -sh
2,5G
# mysqlcheck -o innodb_test -u root -p
Zadejte heslo: innodb_test.b_admin_notify poznámka: Tabulka nepodporuje optimalizaci, místo toho provádí recreate + analyzovat stav: OK innodb_test.b_admin_notify_lang poznámka: Tabulka nepodporuje optimalizaci, dělá znovu + analyzovat místo stavu: OK innodb_test.b_adv_banner poznámka: Tabulka nepodporuje optimalizovat, místo toho znovu vytvářet + analyzovat stav: OK ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~
# du -sh
1,7 G
Chcete-li tedy ušetřit místo na serveru, můžete pravidelně optimalizovat a komprimovat své tabulky a databáze. Před provedením jakékoli optimalizační práce opakuji vytvoření záložní kopie databáze.
