U ovom ćemo članku razmotriti metode kompresije i defragmentacije tablica i baza podataka u MySQL / MariaDB, što će vam omogućiti uštedu prostora na disku iz baze podataka.
U velikim projektima s vremenom baze podataka postaju ogromne veličine i uvijek se postavlja pitanje kako se s tim nositi. Postoji nekoliko mogućnosti za rješavanje ovog problema. Možete smanjiti količinu podataka u samoj bazi podataka brisanjem starih podataka, podijeliti bazu podataka na nekoliko, povećati količinu prostora na disku na poslužitelju ili komprimirati tablice.
Drugi važan aspekt funkcioniranja baze podataka je potreba za periodičnom defragmentacijom tablica i baza podataka, što može značajno ubrzati njihov rad.
sadržaj:
- Kompresija i optimizacija baze podataka s tipom InnoDB tablice
- Stisnite MyISAM tablice u MySQL
- Optimizacija tablica i baza podataka u MySQL / MariaDB
Kompresija i optimizacija baze podataka s tipom InnoDB tablice
Datoteke Ibdata1 i ib_log
Na mnogim projektima s tablicama InnoDB postoji problem s ogromnim veličinama datoteka ibdata1 i ib_log. Razlog u većini slučajeva je zbog pogrešnih postavki MySQL / MariaDB poslužitelja ili arhitekture baze podataka. Sve informacije iz tablica InnoDB pohranjena u datoteci ibdata1, čiji se prostor ne oslobađa sam od sebe. Radije čuvam podatke tablice u zasebne datoteke ibd *. Da biste to učinili, u konfiguracijskoj datoteci my.cnf dodaj liniju:
innodb_file_per_table
ili
innodb_file_per_table = 1
Ako je vaš poslužitelj već konfiguriran i imate nekoliko radnih baza podataka s tablicama InnoDB, trebate učiniti sljedeće:
- Napravite sigurnosnu kopiju svih baza podataka na vašem poslužitelju (osim mysql i performance_schema). Baze dump mogu se ukloniti sljedećom naredbom:
# mysqldump -u [korisničko ime] -p [lozinka] [naziv baze podataka]> [dump_file.sql] - Nakon izrade sigurnosne kopije baze podataka zaustavite mysql / mariadb poslužitelj;
- Promijenite postavke u datoteci my.cfg;
- Izbriši datoteke ibdata1 i ib_log datoteke;
- Pokrenite mysql / mariadb poslužitelj;
- Vrati sve baze podataka iz sigurnosne kopije:
# mysql -u [korisničko ime] -p [lozinka] [ime_ baze podataka] < [dump_file.sql]
Nakon dovršetka ovog postupka, sve tablice InnoDB bit će pohranjeni u zasebne datoteke i datoteke ibdata1 neće rasti eksponencijalno.
Kompresija InnoDB tablice
Možete komprimirati tablice s podacima tipa teksta / BLOB. Ako imate slične tablice, možete uštedjeti mnogo prostora na disku.
Imam bazu podataka innodb_test sa tablicama koje bi se potencijalno moglo komprimirati i osloboditi prostor na disku. Prije svih radova toplo preporučujem da napravite sigurnosnu kopiju svih svojih baza podataka. Spajanje na mysql poslužitelj:
# mysql -u korijen -p
Na konzoli mysql prijavite se na željenu bazu podataka:
# use innodb_test;
Za popis tablica i njihove veličine koristite upit:
SELECT_ime_tabele AS "Table",
ROUND (((data_length + index_length) / 1024/1024), 2) AS "Veličina u (MB)"
OD informacije_schema.TABLES
GDJE table_schema = "innodb_test"
NAROČITE PO (data_length + index_length) DESC;
Gdje je innodb_test naziv vaše baze podataka.
Vjerojatno se neke tablice mogu komprimirati. Pogledajte primjer b_crm_event_relations. Pokrenite zahtjev:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT = COMPRESSED;
Upit je u redu, pogođeno je 0 redaka (3,27 sek) Zapisi: 0 duplikata: 0 Upozorenja: 0
Nakon izvršenja možete vidjeti da se zbog kompresije veličina tablice smanjila sa 26 na 11 MB.
Zahvaljujući kompresiji tablice možete uštedjeti puno prostora na disku na poslužitelju. No, pri radu s komprimiranim tablicama, opterećenje procesora će se povećati. Kompresija tablice treba koristiti ako nemate problema s resursima procesora, ali postoji problem s diskovnim prostorom.
Stisnite MyISAM tablice u MySQL
Za komprimiranje tablica formata MyISAM, trebate koristiti poseban zahtjev s konzole poslužitelja, a ne u mysql konzoli. Da biste komprimirali željenu tablicu:
# myisampack -b / var / lib / mysql / test / modx_session
Gdje je / var / lib / mysql / test / modx_session put do vaše tablice. Nažalost, nisam imao napuhanu bazu podataka i morao sam izvršiti kompresiju na malim tablicama, ali rezultat je i dalje vidljiv (datoteka je komprimirana od 25 do 18 MB):
# du -sh modx_session.MYD
25M modx_session.MYD
# myisampack -b / var / lib / mysql / test / modx_session
Komprimiranje /var/lib/mysql/test/modx_session.MYD: (4933 zapisa) - Proračunska statistika - Kompresija datoteke 29,84% Ne zaboravite pokrenuti myisamchk -rq na komprimiranim tablicama
# du -sh modx_session.MYD
18M modx_session.MYD
U zahtjevu smo odredili prekidač -b, kad se doda, izrađena je sigurnosna kopija tablice prije kompresije i označena kao OLD:
# ls -la modx_session.OLD
-rw-r ----- 1 mysql mysql 25550000 17. prosinca 15:20 modx_session.OLD
# du -sh modx_session.OLD
25M modx_session.OLD
Optimizacija tablica i baza podataka u MySQL / MariaDB
Da biste optimizirali tablice i baze podataka, preporučuje se izvođenje defragmentacije. Provjerite postoje li tablice u bazi podataka za koje je potrebna defragmentacija.
Ulazimo u MySQL konzolu, odaberite bazu podataka koja vam je potrebna i izvršimo upit:
izaberite tablicu_ime, okrugla (data_length / 1024/1024) kao data_length_mb, okrugla (data_free / 1024/1024) kao data_free_mb iz informacije_schema.tables gdje je okrugla (data_free / 1024/1024)> 50 poredak po data_free_mb;
Tako ćemo prikazati sve tablice sa najmanje 50 MB neiskorištenog 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 - ukupna veličina tablice
data_free_mb - neiskorišteni prostor tablice
Te tablice možemo defragmentirati. Provjerite prostor na disku prije:
# ls -lh / var / lib / mysql / innodb_test / | grep b_
-rw-r ----- 1 mysql mysql 402M 17. prosinca 15:43 b_disk_deleted_log_v2.MYD -rw-r ----- 1 mysql mysql 828M 17. prosinca 14:52 b_crm_timeline_bind.MYD -rw-r ----- 1 mysql mysql 981M 17. prosinca 15:45 b_disk_object_path.MYD
Da biste optimizirali ove tablice, koristite sljedeću naredbu u mysql konzoli:
# OPTIMIZIRAJTE TABELU b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
Nakon uspješne defragmentacije, trebao bi imati nešto poput ovog rezultata:
+-------------------------------+----------------+--------------+ | 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 |
Kao što vidite, data_free_mb je sada 0, a ukupna veličina tablice značajno se smanjila (3-4 puta).
Možete i defragmentirati pomoću uslužnog programa mysqlcheck s konzole poslužitelja:
# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file
Gdje je innodb_test vaša baza podataka
A b_workflow_file je naziv željene tablice
Da biste optimizirali sve potrebne tablice baze podataka, pokrenite naredbu u konzoli poslužitelja:
# mysqlcheck -o innodb_test -u korijen -p
Gdje je innodb_test naziv željene baze podataka.
Ili pokrenite optimizaciju svih baza podataka na poslužitelju:
# mysqlcheck -o - sve baze podataka -u root -p
Ako provjerite veličinu baze podataka prije i nakon optimizacije, tada se veličina u cjelini smanjila:
# du -sh
2.5G
# mysqlcheck -o innodb_test -u korijen -p
Unesite lozinku: innodb_test.b_admin_notify napomena: Tablica ne podržava optimizaciju, izvodi ponovno kreiranje + analiziranje umjesto statusa: OK innodb_test.b_admin_notify_lang napomena: Tablica ne podržava optimizaciju, izradite ponovo + analizirajte umjesto toga status: U redu innodb_test.b_adv_banner napomena: Tabela ne podržava optimizirati, radeći rekreirajte + analizirajte umjesto statusa: OK ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~
# du -sh
1.7 g
Tako, za uštedu prostora na poslužitelju, možete povremeno optimizirati i komprimirati tablice i baze podataka. Ponavljam, prije izvođenja bilo kakvih poslova optimizacije, napravite sigurnosnu kopiju baze podataka.
