0

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 berekstensi .sql berukuran 541GB ke database MySQL versi 8.0. Wow? 541GB ? Yes !

cara import file sql berukuran besar

Jadi waktu itu total 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 dari percobaan kedua tersebut, supaya kita bisa import file .sql berukuran 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

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.

Kenapa spek server perlu dinaikkan, RAM? 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.

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
cara import file sql

Penjelasan:

  • innodb_buffer_pool_size, buat cache untuk read data, index. Misal ada statement SELECT * FROM, maka statement itu akan di cache ke RAM. Sebenarnya ini tidak berpengaruh saat import file .sql (CMIIW), tapi berdasarkan dokumentasi mysql harus diset 70-80% dari RAM, maka saya set 8GB.
  • innodb_log_buffer_size, buat mengurangi write I/O ke transaction logs
  • 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 banyak flush dilakukan pada commit. Diisi 0 artinya flush dijalankan sekali perdetik.

Setelah itu restart service mysql nya dengan command berikut:

service mysql restart --innodb-doublewrite=0

Parameter –innodb-doublewrite=0 artinya kita disablekan InnoDB Double Write Buffer

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

Setelah menjalankan command diatas, saya tunggu dan importnya kelar dalam waktu 3 hari.

Hasilnya di DB menjadi 685.1 GB ..

Setelah selesai mengimport, nonaktifkan parameter:

innodb_flush_log_at_trx_commit

Agar parameternya menjadi 1 (default), supaya lebih aman log diflush setiap transaksi commit.

Lalu restart mysqlnya tanpa parameter –innodb-doublewrite=0

service mysql restart

Database sudah ready untuk digunakan 🙂

Semoga bermanfaat!

Ambar Hasbiyatmoko

Hello, I'm web developer. Passionate about programming, web server, and networking.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.