V tem članku bomo razmislili o metodah stiskanja in defragmentacije tabel in baz podatkov v MySQL / MariaDB, kar vam bo omogočilo prihranek prostora na disku iz baze podatkov.
V velikih projektih sčasoma baze podatkov naraščajo v ogromno velikost in vedno se postavlja vprašanje, kako se tega lotiti. Obstaja več možnosti za rešitev te težave. Količino podatkov v sami bazi podatkov lahko zmanjšate tako, da izbrišete stare podatke, razdelite bazo na več, povečate količino prostora na disku na strežniku ali stisnete tabele.
Drug pomemben vidik delovanja baze podatkov je potreba po občasni defragmentaciji tabel in baz podatkov, kar lahko znatno pospeši njihovo delo.
Vsebina:
- Stiskanje in optimizacija zbirke podatkov s tipom InnoDB
- Stisnite tabele MyISAM v MySQL
- Optimizacija tabel in baz podatkov v MySQL / MariaDB
Stiskanje in optimizacija zbirke podatkov s tipom InnoDB
Datoteke Ibdata1 in ib_log
Pri mnogih projektih s tabelami Innodb obstaja težava z ogromnimi velikostmi datotek ibdata1 in ib_log. Razlog je v večini primerov posledica napačnih nastavitev strežnika MySQL / MariaDB ali arhitekture baze podatkov. Vse informacije iz tabel Innodb shranjeno v datoteki ibdata1, čigar prostor se ne sprosti sam. Raje shranjujem podatke tabel v ločene datoteke ibd *. To naredite v konfiguracijski datoteki moj.cnf dodaj vrstico:
innodb_file_per_table
ali
innodb_file_per_table = 1
Če je vaš strežnik že konfiguriran in imate več delovnih baz podatkov s tabelami Innodb, morate storiti naslednje:
- Naredite varnostno kopijo vseh baz podatkov na vašem strežniku (razen mysql in performance_schema). Odlagalne baze lahko odstranite z naslednjim ukazom:
# mysqldump -u [uporabniško ime] -p [geslo] [ime_ zbirke podatkov]> [dump_file.sql] - Po varnostni kopiji baze podatkov ustavite strežnik mysql / mariadb;
- Spremenite nastavitve v datoteki my.cfg;
- Izbrišite datoteke ibdata1 in ib_log Datoteke
- Zaženite strežnik mysql / mariadb;
- Obnovite vse baze podatkov iz varnostne kopije:
# mysql -u [uporabniško ime] -p [geslo] [ime_podatkov] < [dump_file.sql]
Po zaključku tega postopka so vse tabele Innodb bodo shranjene v ločenih datotekah in datoteki ibdata1 ne bo eksponentno zrasel.
Stiskanje tabele InnoDB
Tabele lahko stisnete s podatki tipa besedila / BLOB. Če imate podobne tabele, lahko prihranite precej prostora na disku.
Imam bazo podatkov innodb_test s tabelami, ki bi jih lahko stisnili in sprostili prostor na disku. Pred vsakim delom toplo priporočam, da varnostno kopirate vse svoje baze podatkov. Povezava s mysql strežnikom:
# mysql -u root -p
V konzoli mysql se prijavite v želeno bazo podatkov:
# uporaba innodb_test;
Če želite prikazati tabele in njihovo velikost, uporabite poizvedbo:
IZBERI ime_na tabele AS "Tabela",
ROUND (((data_length + index_length) / 1024/1024), 2) AS "Velikost v (MB)"
IZ informacije_schema.TABLES
WHERE table_schema = "innodb_test"
NAROČI PO (data_length + index_length) DESC;
Kjer je innodb_test ime vaše baze podatkov.
Verjetno je mogoče nekaj tabel stisniti. Kot primer vzemite tabelo b_crm_event_relations. Izvedite zahtevo:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT = COMPRESSED;
Poizvedovanje v redu, prizadetih 0 vrstic (3,27 sek) Zapisi: 0 Podvojeni: 0 Opozorila: 0
Po izvedbi lahko vidite, da se je zaradi stiskanja velikost tabele zmanjšala s 26 na 11 MB.
Zahvaljujoč stiskanju tabel lahko prihranite veliko prostora na disku na strežniku. Toda pri delu s stisnjenimi tabelami se bo obremenitev procesorja povečala. Stiskanje tabel je treba uporabiti, če nimate težav z viri procesorja, vendar je težava s prostorom na disku.
Stisnite tabele MyISAM v MySQL
Za stiskanje tabel oblikovanja Myisam, morate uporabiti posebno zahtevo s konzole strežnika, ne v ukazni mizi mysql. Če želite stisniti želeno tabelo, naredite:
# myisampack -b / var / lib / mysql / test / modx_session
Kjer / var / lib / mysql / test / modx_session je pot do vaše tabele. Na žalost nisem imel napihnjene baze podatkov in sem moral izvajati stiskanje na majhnih mizah, vendar je rezultat še vedno viden (datoteka je bila stisnjena od 25 do 18 MB):
# du -sh modx_session.MYD
25M modx_session.MYD
# myisampack -b / var / lib / mysql / test / modx_session
Stiskanje /var/lib/mysql/test/modx_session.MYD: (4933 zapisov) - Izračun statistike - Stiskanje datoteke 29,84% Ne pozabite zagnati myisamchk -rq na stisnjenih tabelah
# du -sh modx_session.MYD
18M modx_session.MYD
V zahtevi smo določili stikalo -b, ko je dodano, se ustvari varnostno kopiranje tabele pred stiskanjem in označi kot STARO:
# ls -la modx_session.OLD
-rw-r ----- 1 mysql mysql 25550000 17. dec 15. 15:20 modx_session.OLD
# du -sh modx_session.OLD
25M modx_session.OLD
Optimizacija tabel in baz podatkov v MySQL / MariaDB
Za optimizacijo tabel in baz podatkov je priporočljivo izvesti defragmentacijo. Preverite, ali so v bazi tabel, ki zahtevajo defragmentacijo.
Vstopimo v konzolo MySQL, izberemo potrebno bazo podatkov in izvedemo poizvedbo:
izberite ime_nabele, okroglo (data_length / 1024/1024) kot data_length_mb, okroglo (data_free / 1024/1024) kot data_free_mb iz informacije_schema.tables, kjer je okroglo (data_free / 1024/1024)> 50 vrstni red po data_free_mb;
Tako bomo prikazali vse tabele, ki imajo najmanj 50 MB neizkoriščenega prostora:
+-------------------------------+----------------+--------------+ | 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 - skupna velikost tabele
data_free_mb - neuporabljeni prostor tabel
Te tabele lahko defragmentiramo. Preverite prostor na disku pred:
# ls -lh / var / lib / mysql / innodb_test / | grep b_
-rw-r ----- 1 mysql mysql 402M 17. december 15:43 b_disk_deleted_log_v2.MYD -rw-r ----- 1 mysql mysql 828M 17. dec 14 14:52 b_crm_timeline_bind.MYD -rw-r ----- 1 mysql mysql 981M 17. dec 15:45 b_disk_object_path.MYD
Za optimizacijo teh tabel uporabite ukaz v ukazni konzoli mysql:
# OPTIMIZIRAJ TABELO b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
Po uspešni defragmentaciji morate imeti nekaj takega:
+-------------------------------+----------------+--------------+ | 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 |
Kot vidite, je data_free_mb zdaj 0, na splošno pa se je velikost tabele znatno zmanjšala (3-4 krat).
Defragmentirate lahko tudi s pripomočkom mysqlcheck s konzole strežnika:
# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file
Kjer je innodb_test vaša baza podatkov
In b_workflow_file je ime želene tabele
Če želite optimizirati vse tabele baze podatkov, ki jo potrebujete, zaženite ukaz v konzoli strežnika:
# mysqlcheck -o innodb_test -u root -p
Kjer je innodb_test ime želene baze podatkov.
Ali pa izvedite optimizacijo vseh baz podatkov na strežniku:
# mysqlcheck -o - vse baze podatkov -u root -p
Če preverite velikost baze podatkov pred in po optimizaciji, se je velikost kot celota zmanjšala:
# du-š
2.5G
# mysqlcheck -o innodb_test -u root -p
Vnesite geslo: innodb_test.b_admin_notify note: Tabela ne podpira optimiziranja, naredi ponovno ustvari + analizira namesto statusa: OK innodb_test.b_admin_notify_lang opomba: Tabela ne podpira optimiziranja, znova ustvari + analizira namesto stanja: OK innodb_test.b_adv_banner note: Tabela ne podpira optimizirati, narediti rekreirati + analizirati namesto stanja: OK ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ ~~~~~~~~~~~~~~~~
# du-š
1.7G
Tako prihranite prostor na strežniku, lahko občasno optimizirate in stisnete svoje tabele in baze podatkov. Pred ponovnim optimizacijskim delom ponovim, da ustvarim varnostno kopijo baze podatkov.
