MySQL Database Tuning

MySQL adalah sebuah perangkat lunak sistem manajemen basis data SQL (bahasa Inggris: database management system) atau DBMS yang multithread dan multi-user. MySQL AB membuat MySQL tersedia sebagai perangkat lunak gratis di bawah lisensi GNU General Public License (GPL).

Tetapi mereka juga menjual dibawah lisensi komersial untuk kasus-kasus dimana penggunaannya tidak cocok dengan penggunaan GPL.

MySQL dimiliki dan disponsori oleh sebuah perusahaan komersial Swedia yaitu MySQL AB. MySQL AB memegang penuh hak cipta hampir atas semua kode sumbernya. Kedua orang Swedia dan satu orang Finlandia yang mendirikan MySQL AB adalah: David Axmark, Allan Larsson, dan Michael "Monty" Widenius.

Permasalahan

Secara default, MySQL memiliki kinerja yang cukup baik. Namun untuk bisa menangani data yang berkapasitas lebih besar, MySQL dengan pengaturan default akan menghasilkan performa yang kurang maksimal.

Oleh karena itu, diperlukan langkah-langkah untuk memaksimalkan performa MySQL dengan cara melakukan tuning pada MySQL. Dengan adanya tuning pada MySQL, diharapkan dapat memaksimalkan kinerja dari MySQL.

Mengenal database tuning

Database tuning adalah sejumlah aktifitas yang dilakukan untuk mengoptimalkan kinerja dari basis data. Database tuning tidak hanya meliputi tuning query, tapi juga pada rancangan basis data, pemilihan DBMS, sistem operasi dan CPU yang menjalankan DBMS

Tujuan dari database tuning yaitu memaksimalkan penggunaan sumber daya sistem untuk melakukan kerja secara efisien dan secepat mungkin. Kebanyakan sistem telah dirancang untuk bekerja secara efisien.

Tapi bisa dilakukan peningkatan kinerja dengan cara MySQL database tuning secara signifikan. Misalnya seperti mengkostumisasi pengaturan, konfigurasi database dan meng-tuning DBMS. Hasilnya, kinerja database MySQL lebih cepat dan produktifitas semakin meningkat.

Beberapa hal yang harus diperhatikan saat melakukan database tuning :

• Kesepakatan dengan user. Apabila melaksanakan proses tuning pada database, tentunya harapannya adalah adanya peningkatan performance dari database tersebut. Untuk itulah maka perlu adanya satu batasan berupa kesepakatan dengan user database, jangan sampai suatu proses tuning database tidak bisa diterima oleh user karena user merasa performance database yang telah kita lakukan tuning tidak significant perubahannya.
• Identifikasi terlebih dahulu area-area mana saja pada database yang paling critical, apabila kita tidak segera melakukan tuning terhadap database tersebut, atau dengan kata lain prioritaskan proses tuning pada area-area yang dianggap paling critical.
• Identifikasi bottleneck yang ada dalam sebuah atau beberapa query. Biasanya hanya karena sebuah query yang jelek menyebabkan performance menjadi turun secara keseluruhan.
• Jika memungkinkan, review terlebih dahulu design dari database yang akan kita tuning, apakah sudah bagus, sebelum kita mempercepat query MySQL terhadap database tersebut. Karena apabila kita melakukan query tuning tetapi dari sisi design database kurang bagus, maka tuning pada query akan tidak optimal, bahkan mungkin tidak ada impact sama sekali.
• Pelajari strategi index yang sudah berjalan pada database yang akan kita tuning, dan lakukan improvement terlebih dulu pada area index ini.
• Jika kita rasakan index yang ada sudah optimal, lakukan identifikasi fragmentation level dari index yang ada, dan pastikan index statistic selalu up-to-date.
• Pelajari bagaimana cara kerja query optimizer, pelajari dan test beberapa bentuk tipe JOIN.
• Hindari penggunaan sub-query (select in select).
• Selalu gunakan UNION ALL daripada UNION, apabila memang ada operasi yang membutuhkan UNION
• Evaluasi penggunaan trigger yang berdampak pada performance.
• Hindari penggunaan SELECT ...... INTO sampai dengan kita yakin bahwa user yang terhubung ke database hanya kita sendiri atau proses yang kita lakukan hanya memerlukan waktu yang tidak lama. Jika sangat terpaksa gunakan INSERT .......SELECT.
• Gunakan SET NOCOUNT ON dalam semua modular code kita (Stored Procedure), untuk mengurangi informasi yang diberikan server ke client dan untuk mengurangi beban network.
• Jika memungkinkan, gantu semua query yang merupakan inline query menjadi stored procedure yang berparameter
• Jika memungkinkan, gunakan temporary table untuk mengurangi jumlah record pada saat query. Jika temporary table tersebut di join dengan permanent table, buat index di dalam temporary table tersebut.
• Optimalkan penggunaan loop, pindahkan semua proses yang tidak memerlukan pengulangan keluar loop
• Jangan gunakan cursor jika memang tidak sangat terpaksa, TSQL tidak dioptimalkan untuk memproses 1 record dalam satu waktu

Implementasi database tuning

Rancangan index yang tepat adalah salah satu teknik yang bisa meningkatkan kecepatan DBMS dan dapat mengurangi kebutuhan sumber daya database. Berikut ini beberapa cara untuk meningkatkan kinerja DBMS

1. Hati-hati memilih tipe dan panjang atribut. Bila memungkinkan, gunakan varian yang kecil seperti SMALLINT atau MEDIUMINT dari pada INT. Saat menggunakan atribut yang tetap seperti CHAR, tentukan panjang atribut sependek yang digunakan
2. Gunakan atribut dengan panjang tetap. Tetapi coba hindari tipe seperti VARCHAR atau BLOB. Saat menggunakan atribut teks dengan panjang tetap mungkin akan menghabiskan banyak ruang memori, membaca baris dengan panjang tetap dalam query lebih cepat dari pada membaca baris dengan panjang variabel
3. Menjaga index kunci primer sekecil mungkin, buat index yang dibutuhkan saja dan gunakan prefix atribut yang memungkinkan. Pastikan atribut dalam index sering digunakan dalam query. Bila semua atribut digunakan, pastikan atribut tersebut paling banyak duplikasinya.
4. Buat tabel statistik jika kumpulan fungsi seperti COUNT() dan SUM() sering digunakan dalam query pada tabel besar. Tabel statistik menyimpan hanya 1 baris yang secara manual di update dengan kujmpulan nilai dari tabel lain.
5. Jika banyak baris yang dihapus dari tabel atau tabel yang berisi atribut variabel sering dimodifikasi, akan menghabiskan banyak ruang. MySql biasanya tidak menghapus atau memodifikasi data. MySql menyediakan perintah OPTIMIZE TABLE yang harus dijalankan saat DBMS sedang offline.
6. Dimungkinkan untuk membuat tipe tabel untuk tujuan yang spesifik. Secara default MySql menggunakan tipe MyISAM.

Tuning yang dapat dilakukan meliputi :

• Tuning pada hardware
• Tuning pada skema
• Tuning pada query
• Tuning dengan index
• Tuning pada materialized view
• Tuning pada transaksi
• Tuning pada parameter MySQL Server