Belajar Menyetel Database MySQL Server


Akhir tahun adalah saat yang paling tepat untuk beres-beres setelah setahun menulis kode program. Salah satu contoh bentuk upaya beres-beres tersebut misalnya adalah memeriksa apakah kinerja database yang dipakai oleh aplikasi sudah maksimal. Sama seperti kendaraan bermotor, database juga perlu dirawat secara berkala. Bila yang merawat sepeda motor adalah mekanik di bengkel, maka profesi yang bertugas merawat database dinamakan sebagai database administrator.

Untuk mengukur kinerja program, saya akan mencoba mensimulasikan operasi pengisian sejumlah data penjualan. Operasi ini tidak hanya terdiri atas operasi INSERT, tetapi juga operasi SELECT untuk mencari nama konsumen dan nama produk. Hasil yang saya peroleh adalah dibutuhkan waktu 30.053 ms untuk menyelesaikan query yang ada. Ini adalah perkiraan kasar karena masih belum memperhitungkan jeda waktu yang ditimbulkan oleh jaringan dan kesibukan server saat diakses secara bersamaan. Walaupun demikian, tanpa memperhitungkan hal lain tersebut, apakah nilai ‘dasar’ ini bisa ditingkatkan?

Konfiguras MySQL Server bisa dilakukan dengan mengubah file my.ini. Saya dapat menemukan file ini di lokasi C:\Program Files\MySQL\MySQL Server 5.6 atau di C:\ProgramData\MySQL\MySQL Server 5.6. Untuk lokasi yang lebih akurat, saya dapat menjalankan MySQL dari Command Prompt dan memberikan perintah mysqld --help --verbose | more. Disini akan ada daftar lokasi pada bagian Default options are read from the following files in the given order:. Saya juga dapat melakukan pengaturan secara sementara tanpa mengubah file my.ini dengan memberikan perintah SET GLOBAL. Akan tetapi, karena ingin melakukan perbandingan, saya memilih mengubah file my.ini dan me-restart database setiap kali pengujian dilakukan.

Salah satu pengaturan yang paling klasik adalah mengubah nilai innodb_buffer_pool_size. Ini adalah besarnya wilayah di memori yang dialokasikan khusus untuk menampung cache yang berisi informasi tabel dan index. Semakin besar ukuran buffer pool, maka semakin sedikit operasi disk yang dibutuhkan. Nilai default-nya yang berupa 128 MB merupakan sesuatu yang cukup kecil bila dipakai pada server dengan jumlah memori mencapai gigabyte. Dokumentasi MySQL Server merekomendasikan nilai 80% dari jumlah memori untuk database yang sibuk. Jangan lupa bahwa nilai innodb_buffer_pool_size yang terlalu besar malah bisa mengakibatkan perlambatan akibat paging.

Sebagai percobaan, saya mengubah konfigurasi menjadi seperti berikut ini:

innodb_buffer_pool_size=512M
innodb_log_file_size=512M

Saya juga meningkatkan ukuran innodb_log_file_size untuk mengurangi aktifitas flush. Hasil akhir percobaan setelah perubahan konfigurasi menunjukkan bahwa terdapat peningkatan waktu eksekusi sebesar 7%. Ini adalah peningkatan yang cukup besar. Sayangnya, saya tidak bisa menaikkan innodb_buffer_pool_size menjadi 1GB karena walaupun memiliki free memori lebih dari jumlah tersebut, Windows mensyaratkan lokasi memori bebas harus berlanjut dan tidak boleh tersebar! Batasan ini hanya terasa pada Windows 32-bit dan hampir tidak terjadi di Windows 64-bit (akibat ruang alokasi memori yang masih luas🙂 ).

Sejak versi 5.6.8, nilai query_cache_type secara default adalah 0 sehingga query cache tidak aktif. Query cache adalah fitur untuk menampung hasil query secara sementara di memori sehingga hasil yang sama bisa dikembalikan dengan cepat untuk SQL yang sama. Tentu saja query cache harus dihapus bila isi tabel yang dibaca sudah berubah sejak terakhir kali dibaca. Bila query cache terlalu sering kadaluarsa, ini bukannya mempercepat malah bisa memperlambat. Dengan demikian, query cache bisa bermanfaat dan juga bisa merugikan tergantung dari seberapa sering aplikasi mengubah isi tabelnya.

Sebagai contoh, saya akan menggunakan mysqlslap bawaan MySQL Server untuk mensimulasikan query SELECT yang dilakukan oleh beberapa klien secara bersamaan dengan memberikan perintah berikut ini:

C:\> mysqlslap --user=root --password --create-schema=inventory 
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 25.033 seconds
        Minimum number of seconds to run all queries: 22.435 seconds
        Maximum number of seconds to run all queries: 32.380 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

Perintah di atas akan mensimulasikan akses dari 20 client secara bersamaan yang mengerjakan isi query di file select.sql selama 10 kali.

Sebagai perbandingan, saya kemudian mengaktifkan query cache dengan menambahkan baris berikut ini di my.ini:

query_cache_type=1
query_cache_size=5MB

Kali ini, bila saya memberikan perintah yang sama, saya akan memperoleh hasil seperti berikut ini:

C:\> mysqlslap --user=root --password --create-schema=inventory
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 36.043 seconds
        Minimum number of seconds to run all queries: 31.943 seconds
        Maximum number of seconds to run all queries: 41.467 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

Terlihat bahwa mengaktifkan query cache hanya membuat query menjadi lambat. Mengapa bisa demikian? Untuk melihat status query cache, saya dapat memberikan perintah SQL berikut ini:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 327     |
| Qcache_free_memory      | 988680  |
| Qcache_hits             | 1196351 |
| Qcache_inserts          | 65103   |
| Qcache_lowmem_prunes    | 64153   |
| Qcache_not_cached       | 240682  |
| Qcache_queries_in_cache | 950     |
| Qcache_total_blocks     | 2351    |
+-------------------------+---------+

Nilai Qcache_lowmem_prunes menunjukkan jumlah query yang terpaksa dihapus karena ukuran query cache sudah tidak cukup lagi. Pada hasil di atas, nilainya cukup tinggi. Walaupun demikian, nilai Qcache_hits yang tinggi menunjukkan bahwa query cache seharusnya bisa membantu. Oleh sebab itu, saya akan mencoba meningkatkan ukuran query cache dengan mengubah konfigurasi menjadi seperti berikut ini:

query_cache_type=1
query_cache_size=10MB

Hasil ketika menjalankan mysqlslap adalah:

C:\> mysqlslap --user=root --password --create-schema=inventory
--concurrency=20 --iterations=10 --no-drop --query=select.sql

Benchmark
        Average number of seconds to run all queries: 24.233 seconds
        Minimum number of seconds to run all queries: 22.326 seconds
        Maximum number of seconds to run all queries: 31.803 seconds
        Number of clients running queries: 20
        Average number of queries per client: 7513

Walaupun sudah tidak lambat lagi, saya tidak menjumpai peningkatkan kinerja yang cukup berarti saat mengaktifkan query cache. Hal ini cukup masuk akal karena saat ini belum ada tabel raksasa yang membutuhkan waktu pencarian lama sehingga query cache belum dapat menunjukkan taringnya.

Bicara soal jaringan, MySQL Server memiliki kebiasaan melakukan request DNS untuk melakukan validasi host pada saat pengguna login. Bila DNS server yang dipakai lambat (misalnya bawaan ISP), maka hal ini bisa menimbulkan kesan bahwa koneksi database sangat lambat pada aplikasi pertama kali dijalankan. Untuk mengatasinya, saya bisa mematikan request DNS dan hanya melakukan validasi berdasarkan IP dengan menambahkan konfigurasi berikut ini:

skip_name_resolve=ON

Fitur lainnya yang berguna untuk tabel berukuran besar adalah kompresi tabel. MySQL Server mendukung 2 jenis file format untuk InnoDB: Antelope (versi original) dan Barracuda (versi terbaru). File format yang dipakai secara default adalah Antelope untuk memaksimalkan kompatibilitas dengan fitur lama. Karena suka ber-eksperimen, saya akan beralih ke Barracuda dengan menambahkan baris berikut ini di file konfigurasi:

innodb_file_format=Barracuda

Salah satu fitur baru yang ditawarkan oleh format Barracuda adalah kompresi tabel. Kompresi dapat diakses dengan menambahkan ROW_FORMAT=COMPRESSED pada saat memberikan perintah CREATE TABLE atau ALTER TABLE seperti pada:

ALTER TABLE `produk`
ROW_FORMAT = COMPRESSED ;

Untuk bisa memakai kompresi, saya perlu memastikan bahwa nilai innodb_file_per_table adalah ON (nilai default sejak versi 5.6.6). Lagi-lagi saya tidak memiliki tabel yang berukuran sangat besar sehingga saya tidak menjumpai peningkatan kinerja yang cukup berarti dengan mengaktifkan kompresi tabel.

Perihal Solid Snake
I'm nothing...

2 Responses to Belajar Menyetel Database MySQL Server

  1. marialauraveronika mengatakan:

    wow, you do it like an expert.

  2. Ping-balik: Memakai Full-Text Search Di MySQL Server | Programming Logic And Technology

Apa komentar Anda?

Please log in using one of these methods to post your comment:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: