Meningkatkan Kinerja Database MySQL Lama
Seorang relasi senior meminta saya untuk melakukan perubahan pada sebuah aplikasi yang telah berusia lima tahun. Aplikasi tersebut adalah aplikasi PHP berbasis web dengan database MySQL. Bukan sebuah aplikasi yang dipisahkan oleh layer, tapi apa yang saya sebut sebagai aplikasi maze dimana kode program harus ditelusuri satu per satu (bila buntu, kembali jelajahi lagi dari awal). Biasanya saya akan segera menolak permintaan seperti ini, tetapi rasa segan membuat saya untuk bertindak lain. Developer yang membuat program tersebut sudah kabur ke Bali dan kini ia harus sendirian. Saya pun menghampiri pengguna aplikasi tersebut dan berdialog bersamanya. Salah satu keluhan yang disampaikan adalah aplikasi tersebut menjadi sangat lambat setelah menampung data selama lima tahun. Saya pernah menemukan database dengan jumlah data yang lebih besar tapi tidak separah ini. Apa yang dapat saya lakukan dari sisi database untuk meningkatkan kinerja aplikasinya?
1. Membuat Index Untuk Field Yang Sering Diakses
Membuat index adalah opsi pertama yang sering dipakai dalam meningkatkan kinerja database. Index adalahdaftar isi yang dapat dipakai oleh query tanpa perlu membaca isi tabel secara langsung. Jangan lupa bahwa walaupun index akan mempercepat query baca (SELECT), ia memberikan penalti kinerja pada query yang mengubah data (INSERT, UPDATE, atau DELETE). Hal ini karena setiap terjadi perubahan record, index harus diperbaharui. Sebaiknya saya memberikan index pada field yang sering diakses saja.
Sebagai contoh, pengguna aplikasi tersebut mengeluh karena pada saat menampilkan sebuah laporan dimana ia harus menunggu hingga sepuluh menit atau lebih (itupun bila ia beruntung aplikasi tidak crash). Kenapa demikian? Berikut ini adalah struktur tabel yang dibuat oleh developer lama:
Struktur Tabel Yang Dibuat Developer Lama
Relasi antara tabel faktur dan tabel penjualan adalah one-to-many. Developer lama tidak memberikan primary key di penjualan (sebagai informasi, sebuah primary key juga berlaku sebagai index). Karena tidak memiliki primary key dan index, maka operasi pencarian pada tabel penjualan tersebut akan mengakses tabel secara langsung!
Ini adalah salah contoh hasil EXPLAIN untuk sebuah query lambat di laporan tersebut:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
| mysql> EXPLAIN SELECT faktur.faktur_id, faktur.tgl, faktur.kon_id, penjualan.brg_id, penjualan.qty, penjualan.harga, barang.jenis_id, barang.nm_brg, penjualan.disc, penjualan.discrp FROM faktur LEFT JOIN penjualan ON (faktur.faktur_id = penjualan.faktur_id) LEFT JOIN barang ON (penjualan.brg_id = barang.brg_id) ORDER BY faktur.faktur_id\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: faktur type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: xxx Extra: Using temporary; Using filesort*************************** 2. row *************************** id: 1 select_type: SIMPLE table: penjualan type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: xxx Extra: Using where; Using join buffer (Block Nested Loop)*************************** 3. row *************************** id: 1 select_type: SIMPLE table: barang type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 20 ref: toko2.penjualan.brg_id rows: xxx Extra: NULL |
Perhatikan bahwa nilai type untuk tabel
penjualan adalah ALL. Hal ini menunjukkan bahwa tidak ada index yang dipakai sehingga akan terjadi full table scan. Bayangkan apa yang terjadi bila full table scan dilakukan pada database dengan jumlah data lima tahun! Hal ini yang membuat pengguna frustasi. Saya sendiri tidak bisa sabar menunggu hingga hasil query ini selesai ditampilkan. Oleh sebab itu, saya segera menambahkan index pada fieldfaktur_id milik tabel penjualan dengan perintah berikut ini:
1
2
| ALTER TABLE `penjualan`ADD INDEX `faktur_id_idx` (`faktur_id` ASC) ; |
Berikut ini adalah hasil EXPLAIN setelah penambahan index:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
| mysql> EXPLAIN SELECT faktur.faktur_id, faktur.tgl, faktur.kon_id, penjualan.brg_id, penjualan.qty, penjualan.harga, barang.jenis_id, barang.nm_brg, penjualan.disc, penjualan.discrp FROM faktur LEFT JOIN penjualan ON (faktur.faktur_id = penjualan.faktur_id) LEFT JOIN barang ON (penjualan.brg_id = barang.brg_id) ORDER BY faktur.faktur_id\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: faktur type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: xxx Extra: Using filesort*************************** 2. row *************************** id: 1 select_type: SIMPLE table: penjualan type: refpossible_keys: faktur_id_idx key: faktur_id_idx key_len: 20 ref: toko2.faktur.faktur_id rows: xxx Extra: Using where*************************** 3. row *************************** id: 1 select_type: SIMPLE table: barang type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 20 ref: toko2.penjualan.brg_id rows: xxx Extra: NULL3 rows in set (0.01 sec) |
Sekarang type pada penjualan untuk query tersebut adalah ref yang menunjukkan index akan dipakai. Query yang tadinya tidak pernah selesai kini dapat ditampilkan dalam waktu 7.535 sec. Perbedaan kinerja yang sangat penting!
2. Pastikan Semua Tabel Memiliki Primary Key
Satu lagi yang kurang pada tabel
penjualan adalah tidak adanya primary key. Tabel yang baik sebaiknya memiliki primary key, sehingga mempermudah developer dalam mengakses sebuah baris (record). Lalu apa kandidat yang tepat untuk primary key di tabel tersebut? Saya dapat memakai compound primary key yang merupakan kombinasi dari faktur_id dan brg_id. Tapi saya lebih senang memilih memakai surrogate primary key dimana setiap baris (record) benar-benar memiliki pengenal unik (misalnya dengan angka auto-increment). Sebagai contoh, ini adalah hasil profile sebuah query UPDATE untuk tabel penjualan:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| mysql> set profiling = 1;mysql> UPDATE penjualan SET qty = 100 WHERE faktur_id = 'abc' AND brg_id = 'xyz';Query OK, 0 rows affected (0.00 sec)Rows matched: 2 Changed: 0 Warnings: 0mysql> show profile;+----------------------+----------+| Status | Duration |+----------------------+----------+| starting | 0.000101 || checking permissions | 0.000012 || Opening tables | 0.001019 || init | 0.000062 || System lock | 0.000185 || updating | 0.001277 || end | 0.000010 || query end | 0.000005 || closing tables | 0.000014 || freeing items | 0.000085 || cleaning up | 0.000145 |+----------------------+----------+ |
Bukankah sebuah faktur dan barang harus unik, kenapa bisa terdapat 2 record yang di-update? Hal ini mungkin saja bisa timbul akibat kesalahan tidak terduga. Tanpa sebuah primary key yang unik untuk setiap baris, saya tidak akan pernah bisa memilih baris yang salah. Oleh sebab itu, saya menambahkan sebuah surrogate primary keypada tabel penjualan:
1
2
| ALTER TABLE `toko2`.`penjualan` ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (`id`) ; |
Sekarang, saya bisa menghapus baris berdasarkan id-nya yang dijamin unik, seperti pada query berikut ini:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| mysql> set profiling = 1;mysql> UPDATE penjualan SET qty = 100 WHERE id = 10;mysql> show profile;+----------------------+----------+| Status | Duration |+----------------------+----------+| starting | 0.000093 || checking permissions | 0.000012 || Opening tables | 0.001072 || init | 0.000050 || System lock | 0.000098 || updating | 0.000121 || end | 0.000009 || query end | 0.000005 || closing tables | 0.000013 || freeing items | 0.000086 || cleaning up | 0.000023 |+----------------------+----------+ |
3. MyISAM atau InnoDB?
MySQL memberikan pilihan beberapa table engine untuk setiap tabel yang ada. Sebelum versi 5.5, table yang dibuat dengan CREATE TABLE tanpa menyertakan table engine yang akan dipakai secara otomatis akan menggunakan engine MyISAM. Pada versi 5.5, default table engine diganti dari MyISAM menjadi InnoDB.
Banyak yang bilang bahwa MyISAM bukan ‘database’ dalam arti sesungguhnya: tidak ada dukungan transaksi,referential integrity, crash recovery, dsb. MyISAM juga melakukan table-locking; bandingkan dengan InnoDB yang menggunakan row-level locking. Apa dampaknya bila memakai table-locking? Sebuah koneksi yang ingin memodifikasi sebuah tabel wajib menunggu bila ada koneksi lain yang sedang memakai tabel tersebut (termasuk membaca dengan SELECT). Bila koneksi tersebut berhasil mendapatkan giliran untuk memodifikasi tabel, maka koneksi lainnya tidak dapat membaca dan mengubah tabel tersebut.
Sejak MySQL jatuh ke dalam tangan Oracle, fasilitas InnoDB terus diperbaharui dan ditingkatkan. InnoDB menawarkan fasilitas yang umumnya dijumpai pada sebuah database. Tapi MyISAM juga masih bisa dipakai, misalnya untuk database yang lebih sering dibaca (mendekati read-only) seperti database blog. Seseorang mungkin butuh beberapa jam atau beberapa hari untuk mengubah konten blog-nya, bukan? Kasus dimana InnoDB lebih unggul misalnya pada aplikasi e-commerce dimana transaksi baca-tulis sering terjadi dan harus akurat (tidak boleh salah sedikitpun).
Developer lama memakai engine MyISAM untuk seluruh tabel yang ada, tapi saya merasa InnoDB lebih tepat untuk aplikasi ini. Untuk itu, saya mengganti database engine dari MyISAM menjadi InnoDB dengan perintah berikut ini:
1
2
3
4
5
| ALTER TABLE faktur ENGINE = InnoDB;ALTER TABLE penjualan ENGINE = InnoDB;ALTER TABLE barang ENGINE = InnoDB; |
Setelah itu, saya mencoba memberikan query yang tadinya memakan waktu 7 detik:
1
2
3
4
5
| SELECT faktur.faktur_id, faktur.tgl, faktur.kon_id, penjualan.brg_id, penjualan.qty,penjualan.harga, barang.jenis_id, barang.nm_brg, penjualan.disc, penjualan.discrpFROM faktur LEFT JOIN penjualan ON (faktur.faktur_id = penjualan.faktur_id)LEFT JOIN barang ON (penjualan.brg_id = barang.brg_id)ORDER BY faktur.faktur_id |
Saya cukup terkejut saat melihat hasil bahwa query tersebut kini hanya membutuhkan waktu 3.5 detik di MySQL 5.6. Mungkin sudah banyak peningkatan pada InnoDB sehingga ia bisa lebih cepat dibandingkan MyISAM; atau konfigurasi di database saya lebih optimal untuk InnoDB. Tapi saya tidak bisa senang karena ketika mencobanya di server produksi dengan MySQL 5.1, query tersebut membutuhkan waktu 14.76 detik sehingga jauh lebih lambat dibanding MyISAM. Hal ini membuktikan bahwa InnoDB masih belum cukup dewasa di MySQL versi lama.
4. Sebaiknya Jangan Memakai XAMPP untuk Produksi!
Saya cukup terkejut karena menemukan banyak yang memakai XAMPP (baik WAMPP untuk Windows atau LAMPP untuk Linux) untuk keperluan produksi. XAMPP adalah sebuah paket instalasi yang memiliki tujuan mulia untuk memudahkan developer dalam mempelajari pemograman PHP dan MySQL tanpa perlu diribetkan dengan konfigurasi. MySQL Server di XAMPP telah dilengkapi dengan pengaturan standar sehingga developer bisa langsung mulai menulis kode program. Tapi pada banyak kasus, tidak ada yang disebut ‘pengaturan standar‘ karena database harus selalu di-tuning (disetel) sesuai dengan hardware (misalnya memori yang tersedia) dan perilaku aplikasi (jumlah koneksi/session dan intensitas baca-tulis).
Alasan lain menghindari XAMPP adalah proses upgrade MySQL. Untuk mendapatkan kinerja InnoDB yang lebih baik, saya perlu men-upgrade versi MySQL dari 5.1 menjadi 5.6. Saya ingin men-upgrade hanya server MySQL di XAMPP tanpa perlu men-upgrade software lainnya (seperti PHP dan Apache HTTP Server). Bila memakai XAMPP, saya harus men-install MySQL di lokasinya XAMPP, memastikan file konfigurasi MySQL tetap terbaca, dan tidak ada yang break dengan komponen XAMPP lainnya. Hal ini malah jadi lebih rumit ketimbang meng-install masing-masing paket tersendiri secara terpisah.
5. Tidak Semuanya Salah Database
Tidak semua letak kesalahan ada di database, bisa jadi kesalahan ada di kode program PHP atau Javascript. Sebagai contoh, pengguna mengeluh bahwa apa yang diketiknya sering hilang saat halaman diperbaharui. Solusi yang tepat baginya adalah memakai Ajax sehingga tidak perlu sering memperbaharui halaman secara keseluruhan. Salah satu contohnya adalah menampilkan daftar barang berdasarkan jenis barang yang dipilih, hal ini seharusnya tidak perlu me-request halaman baru, saya dapat menggunakan
jQuery.post() untuk itu. Berikut ini adalah kode program jQuery yang saya pakai:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| $("#jenis").change(refreshNamaBarang);function refreshNamaBarang() { var barang = $('select#barang'); barang.children().remove(); barang.hide(); $('#barangLoading').show(); var jenisId = $('select#jenis option:selected').val(); $.post('jenis_barang_ajax.php', {jenisId: jenisId}, function(data) { barang.children().remove(); $.each(data, function(index, value) { $('<option />', { value: value.harga, text: value.namaBarang }).appendTo(barang); }); if (data[0] != undefined) { $('#harga').text(outputComma(data[0].harga)); } $('#barangLoading').hide(); barang.show(); }, 'json');} |
Kode program jenis_barang_ajax.php hanya sebuah halaman yang mengembalikan JSON berisi seluruh barang untuk jenis tertentu. Tapi ternyata terjadi hal diluar dugaan, loading isi combobox terasa lebih lambat dibandingkan dengan yang dibuat oleh programmer lama. Mengapa demikian? Saya berusaha melakukan tuning pada query dan tabel yang terlibat, tetapi tidak mencapai hasil seperti yang diharapkan. Pada akhirnya saya menyadari bahwa membuat elemen DOM secara dinamis melalui objek jQuery memberikan penalti yang cukup berat bila jumlahnya banyak. Cara yang lebih ‘ringan’ adalah dengan membuat HTML secara langsung melalui Javascript (tidak ada validasi disini!) seperti berikut ini:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| function refreshNamaBarang() { var barang = $('select#barang'); barang.children().remove(); barang.hide(); $('#barangLoading').show(); var jenisId = $('select#jenis option:selected').val(); $.post('jenis_barang_ajax.php', {jenisId: jenisId}, function(data) { barang.children().remove(); var output = []; for (var i=0; i<data.length; i++) { output.push('<option value="' + data[i].harga + '">' + data[i].namaBarang + '</option>'); } barang.html(output.join('')); if (data[0] != undefined) { $('#harga').text(outputComma(data[0].harga)); } $('#barangLoading').hide(); barang.show(); }, 'json');} |
No comments:
Post a Comment