Sharing pengalaman – Cara import file .sql berukuran besar (541 GB) ke dalam database MySQL 8.0

Kali ini saya mau share pengalaman saya mengimport file .sql berukuran 541GB ke database MySQL versi 8.0. Wow? 541GB ? Yes !

Jadi sebelumnya, saya sudah ada 2x percobaan import:

1. Percobaan pertama, import berhasil dalam waktu 13 hari (super lama)
2. Percobaan kedua, import berhasil hanya dalam waktu 3 hari ..!

Dan disini saya mau share cara nomor 2 tersebut, supaya bisa import .sql besar dalam waktu singkat.

Caranya adalah:

• Naikkan RAM pada server
• Naikkan konfigurasi innodb_buffer, dll (Saya menggunakan Engine InnoDB pada hampir semua table di database)

Sebenarnya cara diatas saya dapat dari stackoverflow dan saya coba praktekkan.. dan berhasil. Saya cukup nunggu waktu 3 hari saja dan import pun kelar.

https://dba.stackexchange.com/questions/83125/mysql-any-way-to-import-a-huge-32-gb-sql-dump-faster

Yuk mari kita mulai pembahasan intinya…

Naikkan RAM pada server

Pertanyaannya, kenapa spek server (RAM) perlu dinaikkan? Karena nanti akan berhubungan dengan konfigurasi innodb_buffer yang akan kita naikkan juga. Karena Engine InnoDB perlu menggunakan RAM, tujuannya agar proses INSERT, SELECT, UPDATE, DELETE menjadi lebih cepat.

Untuk server, saya menggunakan EC2 di cloud AWS. Jadi bisa dinaikkan spek servernya dengan mudah hanya dengan beberapa klik saja.

• Waktu itu spek server saya adalah t3.medium dengan vCPU 2 RAM 4GB
• Saya naikkan menjadi r5n.large dengan vCPU 2 dan RAM 16GB
• Untuk storage tidak ada perubahan, tetap menggunakan EBS tipe gp2.

Naikkan konfigurasi innodb_buffer

Kita harus naikkan nilai dari innodb_buffernya.

Disini saya menggunakan OS ubuntu, jadi default konfigurasinya ada di folder /etc/mysql.

Buka file mysqld.cnf

vi /etc/mysql/mysql.conf.d/mysqld.cnf

Tambahkan parameter berikut diakhir baris:

innodb_buffer_pool_size = 8G
innodb_log_buffer_size = 2G
innodb_log_file_size = 2G
innodb_write_io_threads = 20
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_doublewrite = 0

Penjelasan:

• innodb_buffer_pool_size, untuk mempercepat insert dengan meng-cache data ke memory. Saat insert dilakukan, maka data akan disimpan sementara ke memory sebelum dipindahkan ke disk. Saya set RAM = 8GB dari total 16GB (rekomendasinya 70-80% dari total RAM, tapi saya hanya set 50%)
• innodb_log_buffer_size, untuk mengurangi write I/O ke transaction logs. Saat insert, selain data, log akan disimpan juga ke memory sebelum dipindahkan ke disk. Nilai semakin besar semakin baik
• innodb_log_file_size, buat mengurangi checkpointing dan write I/O
• innodb_write_io_threads, Jumlah thread write I/O ke .ibd files
• innodb_flush_log_at_trx_commit, seberapa sering flush untuk log dijalankan. Diisi 0 artinya log akan diflush/dipindahkan ke disk dari memory sekali perdetik
• innodb_flush_method, O_DIRECT artinya langsung data & log diflush langsung ke disk tanpa ada cache di OS
• innodb_doublewrite, 0 artinya mendisablekan double write buffer untuk mempercepat flush ke disk (hanya sekali). FYI, jika diset 1 maka akan ditulis dua kali (double) ke disk, tujuannya untuk backup jika terjadi corrupt

Setelah itu restart service mysql nya dengan command berikut:

service mysql restart

Setelah itu kita import file .sql nya ke database:

mysql -u root -p'passwordsaya' online_store < dbdump.sql &

• root, Saya menggunakan akun root disini saat mengimport data
• passwordsaya, ganti dengan password database kalian
• online_store, nama databasenya
• dbdump.sql, nama file .sql nya
• &, jalankan dalam background process

Saya run command diatas, tunggu dan importnya kelar dalam waktu 3 hari.

Hasilnya di DB menjadi 685.1 GB ..

Setelah selesai mengimport, nonaktifkan parameter berikut agar kembali ke settingan default:

#innodb_flush_log_at_trx_commit=0
#innodb_flush_method=O_DIRECT
#innodb_doublewrite=0

Tujuan dikembalikan ke default, supaya jika terjadi crash lebih aman untuk direcovery.

Lalu restart mysqlnya

service mysql restart

Database sudah ready untuk digunakan 🙂

—

Semoga bermanfaat!