Pada artikel ini, kami akan mempertimbangkan metode kompresi dan defragmentasi tabel dan database di MySQL / MariaDB, yang akan memungkinkan Anda untuk menghemat ruang disk dari database.
Dalam proyek-proyek besar, seiring berjalannya waktu, basis data tumbuh menjadi sangat besar dan pertanyaan selalu muncul tentang bagaimana menangani hal ini. Ada beberapa opsi untuk menyelesaikan masalah ini. Anda dapat mengurangi jumlah data dalam database itu sendiri dengan menghapus informasi lama, membagi database menjadi beberapa, menambah jumlah ruang disk di server, atau memampatkan tabel.
Aspek penting lain dari berfungsinya database adalah perlunya defragmentasi tabel dan database secara berkala, yang secara signifikan dapat mempercepat pekerjaan mereka..
Konten:
- Kompresi dan Optimasi Basis Data dengan Jenis Tabel InnoDB
- Kompres tabel MyISAM di MySQL
- Optimasi tabel dan database di MySQL / MariaDB
Kompresi dan Optimasi Basis Data dengan Jenis Tabel InnoDB
File Ibdata1 dan ib_log
Pada banyak proyek dengan tabel Innodb ada masalah dengan ukuran file yang sangat besar ibdata1 dan ib_log. Alasan dalam kebanyakan kasus adalah karena pengaturan server MySQL / MariaDB yang salah atau arsitektur database. Semua informasi dari tabel Innodb disimpan dalam file ibdata1, ruang yang tidak dibebaskan dengan sendirinya. Saya lebih suka menyimpan data tabel dalam file terpisah ibd *. Untuk melakukan ini, dalam file konfigurasi my.cnf tambahkan baris:
innodb_file_per_table
atau
innodb_file_per_table = 1
Jika server Anda sudah dikonfigurasi dan Anda memiliki beberapa database yang berfungsi dengan tabel Innodb, Anda perlu melakukan hal berikut:
- Buat cadangan semua database di server Anda (kecuali mysql dan performance_schema). Basis pembuangan dapat dihapus dengan perintah berikut:
# mysqldump -u [nama pengguna] -p [kata sandi] [database_name]> [dump_file.sql] - Setelah mencadangkan basis data, hentikan server mysql / mariadb;
- Ubah pengaturan dalam file my.cfg;
- Hapus file ibdata1 dan ib_log File
- Mulai server mysql / mariadb;
- Pulihkan semua basis data dari cadangan:
# mysql -u [nama pengguna] -p [kata sandi] [database_name] < [dump_file.sql]
Setelah menyelesaikan prosedur ini, semua tabel Innodb akan disimpan dalam file dan file terpisah ibdata1 tidak akan tumbuh secara eksponensial.
Kompresi tabel InnoDB
Anda bisa mengompres tabel dengan data tipe teks / BLOB. Jika Anda memiliki tabel serupa, Anda dapat menghemat cukup banyak ruang disk.
Saya memiliki database innodb_test dengan tabel yang berpotensi dikompresi dan membebaskan ruang disk. Sebelum semua pekerjaan, saya sangat menyarankan Anda membuat cadangan semua database Anda. Terhubung ke server mysql:
# mysql -u root -p
Di konsol mysql, masuk ke database yang diinginkan:
# gunakan innodb_test;
Untuk daftar tabel dan ukurannya, gunakan kueri:
SELECT table_name AS "Table",
ROUND (((data_length + index_length) / 1024/1024), 2) AS "Ukuran dalam (MB)"
DARI information_schema.TABLES
WHERE table_schema = "innodb_test"
ORDER BY (data_length + index_length) DESC;
Di mana innodb_test adalah nama basis data Anda.
Kemungkinan beberapa tabel dapat dikompresi. Ambil tabel b_crm_event_relations sebagai contoh. Jalankan permintaan:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT = COMPRESSED;
Kueri OK, 0 baris terpengaruh (3,27 dtk) Catatan: 0 Duplikat: 0 Peringatan: 0
Setelah eksekusi, Anda dapat melihat bahwa karena kompresi ukuran tabel menurun dari 26 menjadi 11 MB.
Berkat kompresi tabel, Anda dapat menghemat banyak ruang disk di server. Tetapi ketika bekerja dengan tabel terkompresi, beban prosesor akan meningkat. Kompresi tabel harus digunakan jika Anda tidak memiliki masalah dengan sumber daya prosesor, tetapi ada masalah dengan ruang disk.
Kompres tabel MyISAM di MySQL
Untuk memampatkan tabel format Myisam, Anda perlu menggunakan permintaan khusus dari konsol server, bukan di konsol mysql. Untuk mengompres tabel yang diinginkan, lakukan:
# myisampack -b / var / lib / mysql / test / modx_session
Di mana / var / lib / mysql / test / modx_session adalah path ke tabel Anda. Sayangnya, saya tidak memiliki database yang membengkak dan harus melakukan kompresi pada tabel kecil, tetapi hasilnya masih terlihat (file dikompresi dari 25 hingga 18 MB):
# du -sh modx_session.MYD
25M modx_session.MYD
# myisampack -b / var / lib / mysql / test / modx_session
Mengompresi /var/lib/mysql/test/modx_session.MYD: (4933 catatan) - Menghitung statistik - Mengompresi file 29,84% Ingatlah untuk menjalankan myisamchk -rq pada tabel terkompresi
# du -sh modx_session.MYD
18M modx_session.MYD
Dalam permintaan, kami menentukan saklar -b, ketika ditambahkan, cadangan tabel dibuat sebelum kompresi dan ditandai sebagai TUA:
# ls -la modx_session.OLD
-rw-r ----- 1 mysql mysql 25550000 17 Des 15.20 modx_session.OLD
# du -sh modx_session.OLD
25M modx_session.OLD
Optimasi tabel dan database di MySQL / MariaDB
Untuk mengoptimalkan tabel dan database, disarankan untuk melakukan defragmentasi. Periksa apakah ada tabel dalam database yang membutuhkan defragmentasi.
Mari masuk ke konsol MySQL, pilih database yang Anda butuhkan dan jalankan query:
pilih table_name, bulat (data_length / 1024/1024) sebagai data_length_mb, bulat (data_free / 1024/1024) sebagai data_free_mb dari information_schema.tables di mana bundar (data_free / 1024/1024)> 50 pesanan oleh data_free_mb;
Dengan demikian, kami akan menampilkan semua tabel yang memiliki ruang kosong minimal 50 MB:
+-------------------------------+----------------+--------------+ | 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 - total ukuran tabel
data_free_mb - ruang tabel yang tidak digunakan
Kami dapat mendefrag tabel ini. Periksa ruang disk sebelum:
# ls -lh / var / lib / mysql / innodb_test / | grep b_
-rw-r ----- 1 mysql mysql 402M 17 Des 15:43 b_disk_deleted_log_v2.MYD -rw-r ----- 1 mysql mysql 828M 17 Des 17 14:52 b_crm_timeline_bind.MYD -rw-r ----- 1 mysql mysql 981M 17 Des 15:45 b_disk_object_path.MYD
Untuk mengoptimalkan tabel ini, gunakan perintah berikut di konsol mysql:
# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
Setelah defragmentasi berhasil, Anda harus memiliki sesuatu seperti output ini:
+-------------------------------+----------------+--------------+ | 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 |
Seperti yang Anda lihat, data_free_mb sekarang 0, dan secara keseluruhan ukuran tabel telah menurun secara signifikan (3-4 kali).
Anda juga dapat mendefrag menggunakan utilitas mysqlcheck dari konsol server:
# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file
Di mana innodb_test adalah basis data Anda
Dan b_workflow_file adalah nama tabel yang diinginkan
Untuk mengoptimalkan semua tabel database yang Anda butuhkan, jalankan perintah di konsol server:
# mysqlcheck -o innodb_test -u root -p
Di mana innodb_test adalah nama database yang diinginkan.
Atau jalankan optimisasi semua database di server:
# mysqlcheck -o --all-database -u root -p
Jika Anda memeriksa ukuran database sebelum dan sesudah pengoptimalan, maka ukuran secara keseluruhan telah menurun:
# du -sh
2.5G
# mysqlcheck -o innodb_test -u root -p
Masukkan kata sandi: innodb_test.b_admin_notify note: Tabel tidak mendukung optimalkan, lakukan membuat ulang + menganalisis sebagai gantinya status: OK innodb_test.b_admin_notify_lang catatan: Tabel tidak mendukung optimisasi, melakukan membuat ulang + menganalisis sebagai gantinya status: OK innodb_test.b_adv_banner catatan: Tabel tidak mendukung optimalkan, lakukan buat ulang + analisis alih-alih status: OK ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~
# du -sh
1.7G
Dengan demikian, untuk menghemat ruang di server, Anda dapat secara berkala mengoptimalkan dan mengompresi tabel dan database Anda. Saya ulangi, sebelum melakukan pekerjaan optimasi, buat salinan cadangan dari basis data.
