V tomto článku sa budeme zaoberať metódami kompresie a defragmentácie tabuliek a databáz v MySQL / MariaDB, ktoré vám umožnia ušetriť miesto na disku z databázy.
Vo veľkých projektoch časom databázy narastajú do obrovskej veľkosti a vždy vyvstáva otázka, ako sa s tým vysporiadať. Na vyriešenie tohto problému existuje niekoľko možností. Množstvo údajov v samotnej databáze môžete znížiť odstránením starých informácií, rozdelením databázy na niekoľko, zväčšením množstva miesta na disku na serveri alebo komprimovaním tabuliek..
Ďalším dôležitým aspektom fungovania databázy je potreba periodickej defragmentácie tabuliek a databáz, čo môže výrazne urýchliť ich prácu..
obsah:
- Kompresia a optimalizácia databázy s typom tabuľky InnoDB
- Komprimujte tabuľky MyISAM v MySQL
- Optimalizácia tabuliek a databáz v MySQL / MariaDB
Kompresia a optimalizácia databázy s typom tabuľky InnoDB
Ibdata1 a ib_log súbory
Na mnohých projektoch s tabuľkami InnoDB je problém s veľkými veľkosťami súborov ibdata1 a ib_log. Dôvodom je vo väčšine prípadov nesprávne nastavenie servera MySQL / MariaDB alebo architektúra databázy. Všetky informácie z tabuliek InnoDB uložené v súbore ibdata1, ktorého priestor nie je sám o sebe uvoľnený. Radšej ukladám údaje tabuľky do samostatných súborov ibd *. V konfiguračnom súbore to urobíte my.cnf pridať riadok:
innodb_file_per_table
alebo
innodb_file_per_table = 1
Ak je váš server už nakonfigurovaný a máte niekoľko pracovných databáz s tabuľkami InnoDB, musíte urobiť nasledovné:
- Vytvorte zálohu všetkých databáz na serveri (okrem mysql a performance_schema). Základne výpisu je možné odstrániť pomocou nasledujúceho príkazu:
# mysqldump -u [meno používateľa] -p [heslo] [meno_databázy]> [dump_file.sql] - Po zálohovaní databázy zastavte server mysql / mariadb;
- Zmeňte nastavenia v súbore my.cfg;
- Odstrániť súbory ibdata1 a ib_log súbory;
- Spustite server mysql / mariadb;
- Obnovte všetky databázy zo zálohy:
# mysql -u [meno používateľa] -p [heslo] [meno_databázy] < [dump_file.sql]
Po dokončení tohto postupu všetky tabuľky InnoDB budú uložené v samostatných súboroch a súboroch ibdata1 nebude rásť exponenciálne.
Kompresia tabuľky InnoDB
Tabuľky môžete komprimovať údajmi typu text / BLOB. Ak máte podobné tabuľky, môžete ušetriť dosť miesta na disku.
Mám databázu innodb_test s tabuľkami, ktoré by sa mohli komprimovať a uvoľniť miesto na disku. Pred všetkými prácami vám dôrazne odporúčame zálohovať všetky svoje databázy. Pripojte sa k serveru mysql:
# mysql -u root -p
V konzole mysql sa prihláste do požadovanej databázy:
# use innodb_test;
Ak chcete zobraziť tabuľky a ich veľkosť, použite dotaz:
SELECT table_name AS "Table",
ROUND (((dátová dĺžka + indexová dĺžka) / 1024/1024), 2) AS „Veľkosť v (MB)“
FROM information_schema.TABLES
WHERE table_schema = "innodb_test"
OBJEDNÁVKA BY (dátová dĺžka + indexová dĺžka) DESC;
Kde innodb_test je názov vašej databázy.
Je pravdepodobné, že niektoré tabuľky je možné komprimovať. Ako príklad si vezmite tabuľku b_crm_event_relations. Spustiť žiadosť:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT = COMPRESSED;
Dotaz je v poriadku, 0 ovplyvnených riadkov (3,27 s) Záznamy: 0 duplikáty: 0 upozornenia: 0
Po spustení môžete vidieť, že v dôsledku kompresie sa veľkosť tabuľky znížila z 26 na 11 MB.
Vďaka kompresii tabuľky môžete na serveri ušetriť veľa miesta na disku. Pri práci s komprimovanými tabuľkami sa však zaťaženie procesora zvýši. Kompresia tabuľky by sa mala použiť, ak nemáte problémy s prostriedkami procesora, ale je problém s diskovým priestorom.
Komprimujte tabuľky MyISAM v MySQL
Komprimovať tabuľky formátov MyISAM, musíte použiť špeciálnu požiadavku zo serverovej konzoly, nie z konzoly mysql. Požadovanú tabuľku skomprimujete takto:
# myisampack -b / var / lib / mysql / test / modx_session
Kde / var / lib / mysql / test / modx_session je cesta k vašej tabuľke. Bohužiaľ som nemal nafúknutú databázu a musel som vykonať komprimáciu na malých tabuľkách, ale výsledok je stále viditeľný (súbor bol komprimovaný z 25 na 18 MB):
# du -sh modx_session.MYD
25M modx_session.MYD
# myisampack -b / var / lib / mysql / test / modx_session
Komprimácia /var/lib/mysql/test/modx_session.MYD: (4933 záznamov) - Výpočet štatistík - Komprimácia súboru 29,84% Nezabudnite spustiť myisamchk -rq v komprimovaných tabuľkách
# du -sh modx_session.MYD
18M modx_session.MYD
V žiadosti sme špecifikovali prepínač -b, keď sa pridá, pred kompresiou sa vytvorí záloha tabuľky a označí sa ako OLD:
# ls -la modx_session.OLD
-rw-r ----- 1 mysql mysql 25550000 17 december 15:20 modx_session.OLD
# du -sh modx_session.OLD
25M modx_session.OLD
Optimalizácia tabuliek a databáz v MySQL / MariaDB
Na optimalizáciu tabuliek a databáz sa odporúča vykonať defragmentáciu. Skontrolujte, či v databáze nie sú tabuľky, ktoré vyžadujú defragmentáciu.
Vstúpte do konzoly MySQL, vyberte databázu, ktorú potrebujete, a vykonajte dotaz:
vyberte table_name, round (data_length / 1024/1024) ako data_length_mb, round (data_free / 1024/1024) ako data_free_mb z information_schema.tables, kde round (data_free / 1024/1024)> 50 zoradené podľa data_free_mb;
Zobrazíme teda všetky tabuľky, ktoré majú najmenej 50 MB nevyužitého priestoru:
+-------------------------------+----------------+--------------+ | 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á veľkosť tabuľky
data_free_mb - nevyužitý tabuľkový priestor
Môžeme defragmentovať tieto tabuľky. Skontrolujte miesto na disku skôr:
# ls -lh / var / lib / mysql / innodb_test / | grep b_
-rw-r ----- 1 mysql mysql 402M 17. 12. 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. december 15:45 b_disk_object_path.MYD
Na optimalizáciu týchto tabuliek použite v konzole mysql nasledujúci príkaz:
# OPTIMALIZOVAŤ TABUĽKU b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
Po úspešnej defragmentácii by ste mali mať niečo podobné tomuto výstupu:
+-------------------------------+----------------+--------------+ | 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 |
Ako vidíte, data_free_mb je teraz 0 a celková veľkosť tabuľky sa výrazne znížila (3-4 krát).
Defragmentáciu môžete vykonať aj pomocou nástroja mysqlcheck z konzoly servera:
# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file
Kde je vaša databáza innodb_test
A b_workflow_file je názov požadovanej tabuľky
Ak chcete optimalizovať všetky tabuľky databázy, ktoré potrebujete, spustite príkaz v serverovej konzole:
# mysqlcheck -o innodb_test -u root -p
Kde innodb_test je názov požadovanej databázy.
Alebo spustite optimalizáciu všetkých databáz na serveri:
# mysqlcheck -o --all-database -u root -p
Ak skontrolujete veľkosť databázy pred a po optimalizácii, veľkosť ako celok sa znížila:
# du -sh
2.5G
# mysqlcheck -o innodb_test -u root -p
Zadajte heslo: innodb_test.b_admin_notify poznámka: Tabuľka nepodporuje optimalizáciu, namiesto toho vykonáva status recreate + analyzovať: OK innodb_test.b_admin_notify_lang poznámka: Tabuľka nepodporuje optimalizáciu, robiť znovu + analyzuje namiesto toho status: OK innodb_test.b_adv_banner poznámka: Tabuľka nepodporuje optimalizovať, namiesto toho znova vytvárať + analyzovať stav: OK ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~
# du -sh
1,7 g
Preto, aby ste ušetrili miesto na serveri, môžete pravidelne optimalizovať a komprimovať svoje tabuľky a databázy. Pred vykonaním akejkoľvek optimalizácie opakujem vytvorenie záložnej kópie databázy.
