Konfiguras MySQL Server bisa dilakukan dengan mengubah file my.ini. file ini berada di lokasi C:\Program Files\MySQL\MySQL Server 5.6 atau C:\ProgramData\MySQL\MySQL Server 5.6. Untuk mengetahui lokasi folder mysql berada, menjalankan MySQL dari Command Prompt : mysqld --help --verbose | more. Disini akan ada daftar lokasi pada bagian Default options are read from the following files in the given order:. Disini dapat melakukan pengaturan secara sementara tanpa mengubah file my.ini dengan memberikan perintah SET GLOBAL. Jika ingin permanen maka 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. value ini merupakan besarnya 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.
Contoh :
innodb_buffer_pool_size=512M
innodb_log_file_size=512M
Disini 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, 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
Hasil
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 mensimulasikan akses dari 20 client secara bersamaan yang mengerjakan isi query di file select.sql selama 10 kali.
Kemudian mengaktifkan query cache dengan menambahkan baris berikut ini di my.ini:
query_cache_type=1
query_cache_size=5MB
Jalankan lagi
c:\> mysqlslap --user=root --password --create-schema=inventory
--concurrency=20 --iterations=10 --no-drop --query=select.sql
Hasil
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. Untuk melihat status query cache, 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, coba 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
Hasil
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, tapi 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.
Masalah jaringan, MySQL Server memiliki kebiasaan melakukan request DNS untuk melakukan validasi host pada saat pengguna login. Bila DNS server yang dipakai lambat ( bawaan ISP), maka hal ini bisa menimbulkan kesan bahwa koneksi database sangat lambat pada aplikasi pertama kali dijalankan. Untuk mengatasinya, 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. Tambahkan 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, perlu memastikan bahwa nilai innodb_file_per_table adalah ON (nilai default sejak versi 5.6.6).
