Memakai Full-Text Search Di MySQL Server

Hari ini adalah hari pertama di tahun 2015. Sama seperti hari-hari sebelumnya, awan gelap dan angin kencang tidak kunjung hilang. Tidak ada yang lebih indah daripada mengawali hari pertama di tahun baru dengan segelas teh hangat sambil menulis blog. Pada artikel Memahami Eksekusi SQL di MySQL Server, saya menunjukkan bahwa query dengan pencarian floating seperti LIKE '%abc%' tidak dapat memanfaatkan index. Untuk mengatasi permasalahan tersebut, MySQL Server sejak versi 5.6.4 telah menambahkan dukungan Full-Text Index pada engine InnoDB. Full-Text Search memiliki kemampuan yang jauh lebih canggih dibandingkan dengan floating LIKE: ia akan mengurutkan record berdasarkan hasil yang paling relevan (misalnya record yang mengandung banyak jumlah kata yang dicari akan muncul di urutan awal).

Sebagai contoh, saya dapat menambahkan Full-Text Index pada kolom nama di tabel barang dengan memberikan SQL seperti berikut ini:

ALTER TABLE barang
ADD FULLTEXT INDEX idx_nama(nama ASC);

Setelah ini, saya dapat memberikan SQL yang menggunakan Full-Text Search pada kolom nama seperti:

SELECT nama FROM barang WHERE MATCH(nama) AGAINST('seal');

Query di atas akan mengembalikan seluruh nama produk yang mengandung kata 'seal'. Untuk menunjukkan bahwa index dipakai, saya dapat menggunakan EXPLAIN yang hasilnya terlihat seperti pada gambar berikut ini:

Full-Text Index Dipakai Pada Full-Text Search

Full-Text Index Dipakai Pada Full-Text Search

Agar lebih jelas, saya juga akan membandingkan perubahan kinerja yang dicapai. Saya akan mulai dengan memantau kinerja versi query yang memakai pencarian floating LIKE:

mysql> SET PROFILING=1;

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%';

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000108 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000715 |
| init                 | 0.000056 |
| System lock          | 0.000009 |
| optimizing           | 0.000014 |
| statistics           | 0.000037 |
| preparing            | 0.000022 |
| executing            | 0.000003 |
| Sending data         | 0.154063 |
| end                  | 0.000014 |
| query end            | 0.000010 |
| closing tables       | 0.000017 |
| freeing items        | 0.000131 |
| cleaning up          | 0.000031 |
+----------------------+----------+

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SHOW PROFILE FOR QUERY 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000069 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000024 |
| init                 | 0.000032 |
| System lock          | 0.000010 |
| optimizing           | 0.000009 |
| statistics           | 0.000018 |
| preparing            | 0.000014 |
| executing            | 0.000004 |
| Sending data         | 0.009502 |
| end                  | 0.000013 |
| query end            | 0.000009 |
| closing tables       | 0.000014 |
| freeing items        | 0.000122 |
| cleaning up          | 0.000017 |
+----------------------+----------+

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SHOW PROFILES;
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration   | Query                                                      |
+----------+------------+------------------------------------------------------------+
|        1 | 0.15523725 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        2 | 0.00986225 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        3 | 0.01032150 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        4 | 0.00977775 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        5 | 0.00983650 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
+----------+------------+------------------------------------------------------------+

Sekarang, saya akan membandingkannya dengan query yang memakai Full-Text Search:

mysql> SET PROFILING=1;

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SHOW PROFILE FOR QUERY 1;
+-------------------------+----------+
| Status                  | Duration |
+-------------------------+----------+
| starting                | 0.000115 |
| checking permissions    | 0.000008 |
| Opening tables          | 0.000708 |
| init                    | 0.000046 |
| System lock             | 0.000010 |
| optimizing              | 0.000013 |
| statistics              | 0.000037 |
| preparing               | 0.000013 |
| FULLTEXT initialization | 0.172336 |
| executing               | 0.000012 |
| Sending data            | 0.093124 |
| end                     | 0.000013 |
| query end               | 0.000013 |
| closing tables          | 0.000017 |
| freeing items           | 0.000150 |
| cleaning up             | 0.000022 |
+-------------------------+----------+

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SHOW PROFILE FOR QUERY 2;
+-------------------------+----------+
| Status                  | Duration |
+-------------------------+----------+
| starting                | 0.000090 |
| checking permissions    | 0.000014 |
| Opening tables          | 0.000027 |
| init                    | 0.000031 |
| System lock             | 0.000012 |
| optimizing              | 0.000009 |
| statistics              | 0.000022 |
| preparing               | 0.000010 |
| FULLTEXT initialization | 0.000320 |
| executing               | 0.000006 |
| Sending data            | 0.001038 |
| end                     | 0.000009 |
| query end               | 0.000009 |
| closing tables          | 0.000013 |
| freeing items           | 0.000141 |
| cleaning up             | 0.000018 |
+-------------------------+----------+

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                               |
+----------+------------+---------------------------------------------------------------------+
|        1 | 0.26663350 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        2 | 0.00176875 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        3 | 0.00201450 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        4 | 0.00165800 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        5 | 0.00160050 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
+----------+------------+---------------------------------------------------------------------+

Pada hasil percobaan sederhana tersebut, terlihat bahwa bila saya mengabaikan eksekusi pertama yang lambat, maka untuk setiap query berikutnya, versi yang memakai Full-Text Search rata-rata lebih cepat 82% dibanding versi yang memakai float LIKE.

Selain melakukan pencarian pada modus Natural Language, Full-Text Search juga menyediakan modus Boolean. Sebagai contoh, saya bisa mencari kolom nama yang mengandung kata 'Seal' dan 'Set' tetapi tidak mengandung kata 'Shock' dengan query seperti berikut ini:

SELECT 
    nama
FROM
    barang
WHERE
    MATCH (nama) AGAINST ('+Seal +Set -Shock' IN BOOLEAN MODE);

Pada saat melakukan pencarian pada modus boolean, saya dapat memakai operator seperti + untuk menyertakan sebuah kata, - untuk memastikan sebuah kata tidak muncul, ( dan ) untuk pengelompokkan, dan sebagainya. Saya juga dapat menggunakan operator > dan < untuk memberikan prioritas kata yang dicari sehingga mempengaruhi urutan record yang dikembalikan. Sebagai contoh, query berikut ini:

SELECT 
    nama
FROM
    barang
WHERE
    MATCH (nama) AGAINST ('+Seal >Water' IN BOOLEAN MODE);

akan menghasilkan record yang mengandung kata 'Water' pada urutan yang lebih awal.

Full-Text Search juga memiliki modus Query Expansion yang akan melakukan pencarian dua kali. Pencarian kedua akan menghasilkan record yang kira-kira berhubungan dengan hasil pencarian pertama. Modus pencarian ini biasanya menghasilkan banyak record yang tidak relevan, tetapi bisa berguna untuk pencarian fuzzy dimana pengguna tidak tahu persis apa yang hendak dicari. Sebagai contoh, berikut adalah SQL yang memakai Query Expansion:

SELECT 
    nama
FROM
    barang
WHERE
    MATCH (nama) AGAINST ('seal water' WITH QUERY EXPANSION);

Bila saya mengerjakan query di atas, saya tidak hanya memperoleh nama yang mengandung 'water seal', tetapi juga yang berkaitan dengannya seperti 'water pump'. Pada modus Natural Language dan Boolean, saya hanya memperoleh 1 record yang benar-benar mengandung nama 'water seal'. Akan tetapi pada modus Query Expansion, saya bisa memperoleh hingga 92 record.

Full-Text Search akan mengabaikan stopword (kata yang dianggap tidak penting) pada kolom yang dicari. Secara default, stopword bawaan MySQL Server adalah:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
| be    |
| by    |
| com   |
| de    |
| en    |
| for   |
| from  |
| how   |
| i     |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the   |
| this  |
| to    |
| was   |
| what  |
| when  |
| where |
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+

Terlihat bahwa stopword terdiri atas kata-kata dalam bahasa Inggris. Bila ingin memakai tabel lain sebagai daftar stopword, saya dapat mengubah nilai variabel innodb_ft_server_stopword_table untuk merujuk pada tabel lain tersebut.

Iklan

Memahami eksekusi SQL di MySQL Server

Tulisan ini akan menjadi tulisan paling akhir di tahun 2014. Sudah tiba saatnya untuk mengucapkan selamat tinggal pada 2014 dan bersiap-siap menyongsong 2015. Happy new year! Sembari menunggu tahun baru tiba, saya akan memeriksa slow query log di MySQL Server Server.

MySQL Server selalu mencatat query lambat yang pernah dikerjakannya pada slow query log yang memiliki nama dengan format nama_komputer-slow.log. Sebagai contoh, bila nama komputer saya adalah PC-Snake, maka saya dapat menemukan slow query log di lokasi C:\ProgramData\MySQL\MySQL Server 5.6\data\PC-Snake-slow.log. Lalu, apa kriteria untuk sebuah query yang dianggap lambat sehingga perlu dicatat di slow query log? Hal ini tergantung pada nilai variabel long_query_time yang default-nya adalah 10 detik dan nilai min_examined_row_limit yang default-nya adalah 0. Dengan demikian, secara default, query yang eksekusinya memakan waktu lebih dari 10 detik akan dicatat di slow query log.

Sebagai contoh, saya menemukan sebuah query lambat yang sering terjadi:

SELECT DISTINCT
    produk0_.id AS id1_39_0_,
    daftarstok1_.id AS id1_52_1_,
    listperiod2_.id AS id1_33_2_,
    produk0_.createdBy AS createdB2_39_0_,
    produk0_.createdDate AS createdD3_39_0_,
    produk0_.deleted AS deleted4_39_0_,
    produk0_.hargaDalamKota AS hargaDal5_39_0_,
    produk0_.hargaLuarKota AS hargaLua6_39_0_,
    produk0_.jumlah AS jumlah7_39_0_,
    produk0_.jumlahAkanDikirim AS jumlahAk8_39_0_,
    produk0_.jumlahRetur AS jumlahRe9_39_0_,
    produk0_.jumlahTukar AS jumlahT10_39_0_,
    produk0_.keterangan AS keteran11_39_0_,
    produk0_.levelMinimum AS levelMi12_39_0_,
    produk0_.modifiedBy AS modifie13_39_0_,
    produk0_.modifiedDate AS modifie14_39_0_,
    produk0_.nama AS nama15_39_0_,
    produk0_.poin AS poin16_39_0_,
    produk0_.satuan_id AS satuan_17_39_0_,
    produk0_.supplier_id AS supplie18_39_0_,
    daftarstok1_.jumlah AS jumlah2_52_1_,
    daftarstok1_.createdBy AS createdB3_52_1_,
    daftarstok1_.createdDate AS createdD4_52_1_,
    daftarstok1_.deleted AS deleted5_52_1_,
    daftarstok1_.gudang_id AS gudang_i8_52_1_,
    daftarstok1_.modifiedBy AS modified6_52_1_,
    daftarstok1_.modifiedDate AS modified7_52_1_,
    daftarstok1_.produk_id AS produk_i9_52_1_,
    daftarstok1_.produk_id AS produk_i9_39_0__,
    daftarstok1_.id AS id1_52_0__,
    daftarstok1_.gudang_id AS formula0_0__,
    listperiod2_.arsip AS arsip2_33_2_,
    listperiod2_.jumlah AS jumlah3_33_2_,
    listperiod2_.saldo AS saldo4_33_2_,
    listperiod2_.tanggalMulai AS tanggalM5_33_2_,
    listperiod2_.tanggalSelesai AS tanggalS6_33_2_,
    listperiod2_.createdBy AS createdB7_33_2_,
    listperiod2_.createdDate AS createdD8_33_2_,
    listperiod2_.deleted AS deleted9_33_2_,
    listperiod2_.modifiedBy AS modifie10_33_2_,
    listperiod2_.modifiedDate AS modifie11_33_2_,
    listperiod2_.riwayat_id AS riwayat12_52_1__,
    listperiod2_.id AS id1_33_1__,
    listperiod2_.listPeriodeRiwayat_ORDER AS listPer13_1__
FROM
    Produk produk0_
        LEFT OUTER JOIN
    StokProduk daftarstok1_ ON produk0_.id = daftarstok1_.produk_id
        LEFT OUTER JOIN
    PeriodeItemStok listperiod2_ ON daftarstok1_.id = listperiod2_.riwayat_id
WHERE
    1 = 1
ORDER BY produk0_.nama ASC;

Terlihat kompleks? Tenang saja, saya tidak menulis SQL tersebut secara manual melainkan memakai Hibernate JPA untuk menghasilkan query secara otomatis. Pada domain class, saya memiliki hierarki composition (whole-part relationship) berupa Produk memiliki StokProduk yang selanjutnya memiliki PeriodeItemStok. Karena MySQL Server adalah database relasional yang tidak mendukung composition, maka ia akan diterjemahkan menjadi LEFT JOIN. Ini adalah apa yang disebut sebagai memakai OOP sebagai paradigma tetapi menerapkan murni dalam bentuk relasional (Taniar, Pardede & Rahayu (2005), Composition in Object-Relational Database, http://www.irma-international.org/viewtitle/14285/).

Untuk mendapatkan bayangan bagaimana MySQL Server akan mengerjakan query diatas, saya dapat menggunakan EXPLAIN untuk melihat bagaimana ‘pola pikir’ MySQL dalam menjalankan query. Untuk itu, saya perlu menambahkan EXPLAIN sebelum SELECT seperti pada query berikut ini:

mysql> EXPLAIN SELECT ... /G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: produk0_
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 443
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: daftarstok1_
         type: ref
possible_keys: FK_qdv4fq1uprbpkool88p9y6h44
          key: FK_qdv4fq1uprbpkool88p9y6h44
      key_len: 8
          ref: inventory.produk0_.id
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: listperiod2_
         type: ref
possible_keys: FK_ls70le3nhwwxfw0gudwcpv4l3
          key: FK_ls70le3nhwwxfw0gudwcpv4l3
      key_len: 9
          ref: inventory.daftarstok1_.id
         rows: 1
        Extra: NULL
3 rows in set (0.00 sec)

Bagi yang tidak suka membaca informasi dalam bentuk tabel, MySQL Workbench dapat menyajikan hasil EXPLAIN dalam bentuk visual (secara default) seperti pada gambar berikut ini:

Hasil EXPLAIN dalam bentuk visual

Hasil EXPLAIN dalam bentuk visual

Pada tampilan visual di atas, saya perlu menghindari kotak merah yang disebut Full Table Scan. Ini adalah operasi yang paling berat karena harus mencari isi tabel satu per satu tanpa memakai index sama sekali.

Bila saya membiarkan pointer mouse agak lama di kotak tersebut, saya akan memperoleh informasi lebih lanjut seperti pada gambar berikut ini:

Tooltip yang berisi informasi

Tooltip yang berisi informasi

Full table scan akan semakin sia-sia bila saya melakukan pencarian pada tabel yang memiliki banyak record tetapi tidak membutuhkan seluruh baris yang ada. Pada contoh yang saya peroleh, seluruh 443 record yang dicari di tabel produk akan dikembalikan sehingga kerja keras full table scan tidak akan sia-sia.

Operasi LEFT JOIN sudah memakai index. Hal ini terlihat pada dua kotak hijau bertuliskan Non-Unique Key Lookup. Ini adalah foreign key index yang dihasilkan secara otomatis oleh Hibernate (melalui klausa FOREIGN KEY pada saat CREATE TABLE).

Berikutnya, saya menemukan bahwa SELECT DISTINCT pada dasarnya adalah sebuah operasi yang tidak jauh berbeda dengan GROUP BY. Karena SQL ini melibatkan operasi LEFT JOIN, maka MySQL Server tidak bisa begitu saja memakai index. Untuk itu, ia terpaksa harus membuat internal temporary table di memori. Hal ini terlihat dari tulisan tmp_table di bawah kotak DISTINCT.

Internal temporary table adalah tabel di memori yang berisi hasil proses sementara sebelum hasil akhir diperoleh. Saya bisa melihat berapa banyak jumlah internal temporary table yang sudah dibuat oleh MySQL Server (sejak ia dinyalakan) dengan memberikan perintah SQL berikut ini:

SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

Bila seandainya ukuran internal temporary table mencapai batas tertentu, maka ia akan disimpan ke dalam harddisk dalam bentuk tabel MyISAM. Tentu saja akibatnya adalah query akan menjadi lebih lambat. Untuk melihat jumlah temporay table di memori yang akhirnya disimpan ke harddisk, saya dapat memberikan perintah SQL berikut ini:

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

Kapan MySQL Server menyimpan temporary table ke disk? Hal ini tergantung pada nilai tmp_table_size dan max_heap_table_size. Pada sistem dengan jumlah memori yang berlimpah, meningkatkan kedua variabel tersebut akan mencegah MySQL Server untuk menyimpan temporary table di disk sehingga bisa meningkatkan kinerja bila terdapat banyak query yang mengandung DISTINCT dan GROUP BY.

Pada bagian ORDER, saya menemukan tulisan filesort. Ini adalah algoritma yang akan dipakai oleh MySQL Server bila pengurutan tidak dapat dilakukan melalui index. Seperti yang bisa ditebak, filesort akan lebih lambat dibandingkan dengan pengurutan yang dilakukan dengan menggunakan index.

Lalu apa yang harus saya lakukan untuk mengoptimalkan query ini? Cara yang paling realistis adalah dengan melakukan perubahan pada sisi aplikasi, bukan pada sisi database. Apakah pengguna perlu melihat seluruh produk yang ada setiap kali menampilkan screen produk? Pada banyak kasus, jawabannya adalah ‘tidak’.

Seandainya saya menambahkan kriteria pencarian berdasarkan nama pada query di atas, maka saya akan memperoleh hasil seperti berikut ini:

EXPLAIN SELECT ... 
WHERE produk0_.nama = 'namaprodukdicari' 
ORDER BY produk0_.nama ASC;
Hasil EXPLAIN setelah menambahkan kondisi WHERE

Hasil EXPLAIN setelah menambahkan kondisi WHERE

Terlihat bahwa full table scan masih dilakukan! Hanya saja kini tidak dibutuhkan lagi filesort pada ORDER. Mengapa demikian? Hal ini terjadi karena saya tidak memiliki index untuk kolom nama di tabel produk. Memberikan index pada kolom nama untuk produk adalah sesuatu yang lumrah karena biasanya tabel produk lebih sering dibaca daripada ditulis. Oleh sebab itu, saya segera menambahkan index dengan memberikan perintah berikut ini:

ALTER TABLE produk ADD INDEX idx_nama(nama ASC);

Sekarang, hasil visualisasi EXPLAIN untuk query sebelumnya akan terlihat seperti:

Hasil visualisasi EXPLAIN setelah penambahan index pada kolom nama

Hasil visualisasi EXPLAIN setelah penambahan index pada kolom nama

Ini adalah hasil yang paling optimal karena semuanya berwarna ‘hijau’ 🙂

Walaupun sempurna dari sisi kinerja, mucul permasalahan baru yang berkaitan dengan produktifitas. Kondisi pencarian yang saya gunakan pada WHERE adalah pencarian sama dengan seperti WHERE produk0_.nama = 'namaprodukdicari'. Ini berarti pengguna harus memasukkan nama produk secara lengkap dan sama persis seperti yang tersimpan di database! Program yang lebih user-friendly seharusnya memungkinkan pencarian berdasarkan bagian dari nama. Oleh sebab itu, saya perlu mengubah kriteria pada query menjadi WHERE produk0_.nama LIKE '%bagian_nama_produk%' seperti:

EXPLAIN SELECT ... 
WHERE produk0_.nama LIKE '%bagian_nama_produk%' 
ORDER BY produk0_.nama ASC;

Hasil EXPLAIN kini tiba-tiba menjadi tidak se-‘hijau’ sebelumnya! Full table scan kembali terjadi lagi. Mengapa demikian? Kriteria seperti LIKE '%abc%' tidak dapat memanfaatkan index biasa dengan baik karena abc bisa ada dimana saja di nama produk.

Sebagai gantinya, saya bisa melakukan pengorbanan dengan menghilangkan tanda persen (%) di awal. Dengan demikian, query akan mencari nama produk yang diawali dengan apa yang diketik oleh pengguna:

EXPLAIN SELECT ... 
WHERE produk0_.nama LIKE 'namadepan_produk%' 
ORDER BY produk0_.nama ASC;

Hasil visualisasi akan terlihat seperti pada gambar berikut ini:

Index yang membantu kondisi LIKE

Index yang membantu kondisi LIKE

Index Range Scan menunjukkan bahwa index dipakai untuk membantu mencari nama produk yang diawali oleh kriteria pencarian. Ini jauh lebih baik daripada full table scan. Bila seandainya saya tetap ingin mengoptimalkan kriteria pencarian seperti nama LIKE '%abc%', maka saya perlu menggunakan index khusus yang disebut sebagai Full-Text Index. Saya juga perlu mengganti kondisi LIKE menjadi seperti MATCH(nama) AGAINST('abc'). Fasilitas yang disebut sebagai Full-Text Searching (FTS) ini tersedia di InnoDB sejak versi 5.6.4.

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.

Membuat Plugin MySQL Yang Menulis Hasil Audit Ke Windows Event Log

Kode program untuk artikel ini juga bisa dibaca di https://github.com/JockiHendry/mysql_windows_audit_plugin.

Pada artikel Memahami Authentication dan Auditing Di MySQL Server, saya menggunakan MariaDB Audit Plugin di MySQL Server untuk melakukan auditing dan menuliskan hasilnya dalam bentuk file. Bila saya memberikan perintah seperti SET GLOBAL server_audit_output_type = 'SYSLOG' maka MariaDB Audit Plugin akan menulis hasil audit ke dalam syslog. Sayangnya ini hanya berlaku khusus untuk sistem operasi yang mendukung syslog seperti Linux. Fitur ini tidak akan menulis ke Windows Event Log!

Menampilkan hasil audit di Event Viewer akan memberikan lebih banyak flekibilitas dibandingkan dengan melihat log dalam bentuk file. Selain itu, dari sisi keamanan, file bisa di-edit secara leluasa oleh penyerang (misalnya menghilangkan baris yang mencurigakan). Hal ini tidak terjadi di Event Viewer karena log bersifat read-only dan hanya bisa dihapus secara global (log yang tiba-tiba hilang semua pasti mencurigakan!).

Apakah mungkin membuat MySQL Server menulis hasil audit ke Event Viewer? Yup, ini bisa dilakukan karena MySQL Server dapat di-extend dengan mekanisme plugin. Saya hanya perlu membuat sebuah auditing plugin untuk MySQL Server. Saya sudah menuliskan contoh kode program yang memakai Windows Event Log di artikel Membuat Program Visual C++ Yang Menulis Ke Windows Event Log.

Saya akan mulai dengan membuka Visual C++ 2010 dan membuat sebuah Win32 Project baru yang saya beri nama sebagai mysql_windows_audit. Pada kotak dialog yang muncul, saya memilih jenis aplikasi DLL dan Empty Project seperti yang terlihat pada gambar berikut ini:

Membuat proyek baru.

Membuat proyek baru.

Saya perlu menambahkan sebuah file kode program C dengan men-klik kanan pada Source Files dan memilih menu Add, New Item…. Pada kotak dialog yang muncul, saya memilih C++ File (.cpp). Agar jelas bahwa saya ingin memakai bahasa pemograman C, saya mengisi nama file dengan windows_audit.c sebelum men-klik tombol Add.

Sebelum mulai membuat kode program, saya perlu melakukan beberapa pengaturan. Untuk itu, saya men-klik kanan nama proyek dan memilih Properties. Setelah itu, saya beralih ke konfigurasi Release. Pada bagian C/C++, General, saya perlu menyertakan header file dari MySQL. Secara default, lokasi ini adalah C:\Program Files\MySQL\MySQL Server 5.6\include:

Melakukan pengaturan proyek.

Melakukan pengaturan proyek.

Selanjutnya, pada bagian C/C++, Preprocessor, saya menambahkan MYSQL_DYNAMIC_PLUGIN di bagian Preprocessor Definitions. Saya juga memastikan bahwa saya pada C/C++, Precompiled Headers, nilai untuk Precompiled Header adalah Not Using Precompiled Headers. Setelah itu, saya men-klik tombol OK untuk menutup dialog.

Karena memakai Windows Event Log, saya perlu membuat instrumentation manifest, misalnya sebuah file XML dengan nama message.man yang isinya seperti berikut ini:

<?xml version="1.0" encoding="UTF-16"?>
<instrumentationManifest xsi:schemaLocation="http://schemas.microsoft.com/win/2004/08/events eventman.xsd" xmlns="http://schemas.microsoft.com/win/2004/08/events" xmlns:win="http://manifests.microsoft.com/win/2004/08/windows/events" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:trace="http://schemas.microsoft.com/win/2004/08/events/trace">
    <instrumentation>
        <events>
            <provider name="MySQLWindowsAuditProvider" guid="{566E77CB-DC32-4B2D-A0CA-01CDF9BEC031}" symbol="MYSQL_WINDOWS_AUDIT_PROVIDER" resourceFileName="C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin\mysql_windows_audit.dll" messageFileName="C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin\mysql_windows_audit.dll">
                <events>
                    <event symbol="Connect" value="1" version="1" channel="MySqlWindowsAudit" level="win:Informational" template="ConnectionTemplate" message="$(string.MySQLWindowsAuditProvider.event.1.message)">
                    </event>
                    <event symbol="Disconnect" value="2" version="1" channel="MySqlWindowsAudit" level="win:Informational" template="ResultTemplate" message="$(string.MySQLWindowsAuditProvider.event.2.message)">
                    </event>
                    <event symbol="Change" value="3" version="1" channel="MySqlWindowsAudit" level="win:Informational" template="ConnectionTemplate" message="$(string.MySQLWindowsAuditProvider.event.3.message)">
                    </event>
                    <event symbol="Error" value="4" version="1" channel="MySqlWindowsAudit" level="win:Error" template="ConnectionTemplate" message="$(string.MySQLWindowsAuditProvider.event.4.message)">
                    </event>
                    <event symbol="ActiveChanged" value="5" version="1" channel="MySqlWindowsAudit" level="win:Warning" template="BooleanUpdateTemplate" message="$(string.MySQLWindowsAuditProvider.event.5.message)">
                    </event>
                </events>
                <levels>
                </levels>
                <channels>
                    <channel name="MySqlWindowsAudit" chid="MySqlWindowsAudit" symbol="MYSQL_WINDOWS_AUDIT" type="Operational" enabled="true" message="$(string.MySQLWindowsAudit.channel.MYSQL_WINDOWS_AUDIT.message)">
                    </channel>
                </channels>
                <templates>
                    <template tid="ConnectionTemplate">
                        <data name="status" inType="win:Int32" outType="xs:int">
                        </data>
                        <data name="user" inType="win:AnsiString" outType="xs:string">
                        </data>
                        <data name="host" inType="win:AnsiString" outType="xs:string">
                        </data>
                        <data name="ip" inType="win:AnsiString" outType="xs:string">
                        </data>
                        <data name="database" inType="win:AnsiString" outType="xs:string">
                        </data>
                    </template>
                    <template tid="ResultTemplate">
                        <data name="status" inType="win:Int32" outType="xs:int">
                        </data>
                    </template>
                    <template tid="BooleanUpdateTemplate">
                        <data name="var" inType="win:AnsiString" outType="xs:string">
                        </data>
                        <data name="active" inType="win:Boolean" outType="xs:boolean">
                        </data>
                    </template>
                </templates>
            </provider>
        </events>
    </instrumentation>
    <localization>
        <resources culture="en-US">
            <stringTable>
                <string id="level.Warning" value="Warning">
                </string>
                <string id="level.Informational" value="Information">
                </string>
                <string id="level.Error" value="Error">
                </string>
                <string id="MySQLWindowsAuditProvider.event.5.message" value="%1 has been changed to %2.">
                </string>
                <string id="MySQLWindowsAuditProvider.event.4.message" value="Connection error for %2 (%4) at database %5 at %3.  Status: %1.">
                </string>
                <string id="MySQLWindowsAuditProvider.event.3.message" value="Change user for %2 (%4) at database %5 at %3.  Status: %1.">
                </string>
                <string id="MySQLWindowsAuditProvider.event.2.message" value="User disconnect with the following status: %1.">
                </string>
                <string id="MySQLWindowsAuditProvider.event.1.message" value="Connection from %2 (%4) to database %5 at %3.  Status: %1.">
                </string>
                <string id="MySQLWindowsAudit.channel.MYSQL_WINDOWS_AUDIT.message" value="MySQL Connection Audit">
                </string>
            </stringTable>
        </resources>
    </localization>
</instrumentationManifest>

Saya mengisi nilai resourceFileName dan messageFileName dengan "C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin\mysql_windows_audit.dll". Ini adalah file DLL yang akan dihasilkan oleh proyek yang sedang saya buat. Bila seandainya plugin di-install di tempat yang terpisah, saya perlu mengubah nilai tersebut.

File di atas mendefinisikan 5 jenis event yang bisa dihasilkan oleh program. Event Connect, Disconnect dan Change mewakili aktifitas koneksi, diskoneksi dan perubahan user. Ketiga event ini memiliki level Information. Event Error akan terjadi terdapat kesalahan yang berhubungan dengan koneksi (misalnya pengguna login dengan password yang salah atau mengakses database yang salah). Dan terakhir, event ActiveChanged yang memiliki level Warning akan terjadi bila pengguna mematikan atau mengaktifkan fasilitas logging dari plugin ini.

Setelah memberikan perintah MC.exe, saya segera menambahkan referensi ke file yang dihasilkan sehingga struktur proyek terlihat seperti pada gambar berikut ini:

Struktur proyek

Struktur proyek

Sekarang, saatnya untuk menulis kode program! Karena tutorial menulis plugin MySQL tidak begitu banyak, saya akan memakai kode program MariaDB Audit Plugin sebagai sumber referensi. Kode program MariaDB Audit Plugin bisa dilihat di https://github.com/MariaDB/server/blob/10.1/plugin/server_audit/server_audit.c. Selain itu, saya memakai proyek audit_null yang bisa dilihat di https://github.com/mysql/mysql-server/tree/5.7/plugin/audit_null sebagai skeleton awal. Tentu saja karena saya membuat fitur yang hanya jalan di Windows, saya bisa memakai API khusus Windows dan tidak perlu memikirkan kode program yang cross-platform.

Sebagai contoh, saya membuat file windows_audit.c yang isinya seperti berikut ini:

#include <windows.h>
#include <tchar.h>
#include <stdio.h>
#include "message.h"
#include <mysql/plugin.h>
#include <mysql/plugin_audit.h>

static char active = 1;
static long connection_errors;
static int internal_stop_logging = 0;
static CRITICAL_SECTION cs;
static const char* nullString = "null";

static struct st_mysql_show_var audit_status[] = {  
    {"windows_audit_connection_errors", (char*) &connection_errors, SHOW_LONG},
    {0,0,0}
};

static void update_active(MYSQL_THD thd, struct st_mysql_sys_var *var, void *var_ptr, const void *save) {   
    char new_is_active = *(char*) save;
    if (new_is_active == active) return;
    EnterCriticalSection(&cs);
    internal_stop_logging = 1;
    active = new_is_active; 
    EventWriteActiveChanged("windows_audit_active", active);
    internal_stop_logging = 0;
    LeaveCriticalSection(&cs);
}

static MYSQL_SYSVAR_BOOL(active, active, PLUGIN_VAR_OPCMDARG, "Turn on/off the logging.", NULL, update_active, 1);

static struct st_mysql_sys_var* vars[] = {
    MYSQL_SYSVAR(active),
    NULL
};

static int windows_audit_plugin_init(void *arg)
{   
    if (EventRegisterMySQLWindowsAuditProvider() != ERROR_SUCCESS) {
        fwprintf(stderr, L"Can't register Windows log provider.n");
        return -1;
    }
    InitializeCriticalSection(&cs); 
    connection_errors = 0;
    fwprintf(stderr, L"Windows Audit Plugin STARTED.n");  
    return 0;
}

static int windows_audit_plugin_deinit(void *arg)
{   
    if (EventUnregisterMySQLWindowsAuditProvider() != ERROR_SUCCESS) {
        fwprintf(stderr, L"Can't unregister Windows log provider.n");
        return -1;
    }
    DeleteCriticalSection(&cs);
    fwprintf(stderr, L"Windows Audit Plugin STOPPED.n");
    return 0;
}

static const char* new_cstr(const char* str, ULONG size) {      
    char* result = (char*) malloc(sizeof(char) * ((size==0) ? sizeof(nullString) : size) + 1);
    memcpy(result, (size==0) ? nullString : str, ((size==0) ? sizeof(nullString) : size) + 1);  
    return result;
}

static void event_log(PCEVENT_DESCRIPTOR descriptor, EVENT_DATA_DESCRIPTOR* data, const struct mysql_event_connection* connEvent) {
    const char* user, *host, *ip, *database;
    EventDataDescCreate(&data[0], &(connEvent->status), sizeof(const signed int)  );

    user = new_cstr(connEvent->user, connEvent->user_length);                                             
    EventDataDescCreate(&data[1], user, strlen(user)+1);

    host = new_cstr(connEvent->host, connEvent->host_length);
    EventDataDescCreate(&data[2], host, strlen(host)+1);

    ip = new_cstr(connEvent->ip, connEvent->ip_length);
    EventDataDescCreate(&data[3], ip, strlen(ip)+1);

    database = new_cstr(connEvent->database, connEvent->database_length);                                                         
    EventDataDescCreate(&data[4], database, strlen(database)+1);

    EventWrite(MySQLWindowsAuditProviderHandle, descriptor, 5, data);

    free((void*) user);
    free((void*) host);
    free((void*) ip);
    free((void*) database);
}

static void windows_audit_notify(MYSQL_THD thd, unsigned int event_class, const void *e)
{ 
    const struct mysql_event_connection *connEvent; 
    EVENT_DATA_DESCRIPTOR eventData[5];

    if (internal_stop_logging || !active) return;   
    EnterCriticalSection(&cs);  

    if (event_class == MYSQL_AUDIT_CONNECTION_CLASS) {
        internal_stop_logging = 1;  
        connEvent = (const struct mysql_event_connection *) e;

        if (connEvent->status > 0) {
            event_log(&Error, eventData, connEvent);
            connection_errors++;
        } else {
            switch (connEvent->event_subclass) {
                case MYSQL_AUDIT_CONNECTION_CONNECT:
                    event_log(&Connect, eventData, connEvent);
                    break;
                case MYSQL_AUDIT_CONNECTION_DISCONNECT:      
                    EventWriteDisconnect(connEvent->status);
                    break;
                case MYSQL_AUDIT_CONNECTION_CHANGE_USER:     
                    event_log(&Change, eventData, connEvent);
                    break;
                default:                    
                    break;          
            }
        }
        internal_stop_logging = 0;
    }

    LeaveCriticalSection(&cs);
}


/*
  Plugin type-specific descriptor
*/

static struct st_mysql_audit windows_audit_descriptor=
{
  MYSQL_AUDIT_INTERFACE_VERSION,                       /* interface version    */
  NULL,                                                /* release_thd function */
  windows_audit_notify,                                /* notify function      */
  { (unsigned long) MYSQL_AUDIT_CONNECTION_CLASSMASK } /* class mask           */
};

/*
  Plugin library descriptor
*/

mysql_declare_plugin(windows_audit)
{
  MYSQL_AUDIT_PLUGIN,                       /* type                            */
  &windows_audit_descriptor,                /* descriptor                      */
  "WINDOWS_AUDIT",                            /* name                            */
  "Jocki Hendry",                         /* author                          */
  "Audit connections to Windows Log",     /* description                     */
  PLUGIN_LICENSE_GPL,
  windows_audit_plugin_init,                /* init function (when loaded)     */
  windows_audit_plugin_deinit,              /* deinit function (when unloaded) */
  0x0001,                                   /* version                         */
  audit_status,                             /* status variables                */
  vars,                                     /* system variables                */
  NULL,
  0,
}
mysql_declare_plugin_end;

Bagian yang diapit oleh macro mysql_declare_plugin(windows_audit) dan mysql_declare_plugin_end; berisi informasi mengenai plugin yang saya buat. Disini saya juga mendaftarkan function windows_audit_plugin_init() sebagai function yang akan dikerjakan saat plugin dimulai (misalnya saat database dinyalakan) dan function windows_audit_plugin_deinit() sebagai function yang akan dikerjakan saat plugin dimatikan. Selain itu, saya juga mendaftarkan audit_status sebagai status yang bisa dilihat melalui perintah SHOW STATUS. Saya juga mendaftarkan vars sebagai variabel yang bisa diatur melalui perintah SET GLOBAL.

MySQL mendukung beberapa jenis plugin. Khusus untuk audit plugin, saya perlu membuat descriptor dengan tipe st_mysql_audit. Sebagai contoh, pada kode program saya, ini diwakili oleh variabel windows_audit_descriptor. Pada descriptor ini, saya mendaftarkan function windows_audit_notify() sebagai function yang akan dipanggil setiap kali ada aktifitas audit. Saya hanya memakai MYSQL_AUDIT_CONNECTION_CLASSMASK sebagai class mask sehingga hanya informasi yang berkaitan dengan koneksi saja yang akan di-audit.

Bagian kode program yang mencatat ke Windows Event Log terdapat di windows_audit_notify(). Pada function ini, saya akan memperoleh informasi koneksi yang sedang di-audit dalam bentuk argumen bertipe mysql_event_connection. Variabel tersebut mengandung informasi seperti status kesalahan (atau sukses), nama user, ip user, database yang hendak diakses dan lokasi host database.

Seusai membuat kode program, saya perlu men-build proyek untuk menghasilkan file DLL. Setelah itu, saya perlu memanggil Wevtutil.exe dan men-copy file DLL ke lokasi plugin di instalasi MySQL Server. Karena langkah-langkah ini akan selalu saya kerjakan setiap kali melihat hasil perubahan program, maka saya membuat sebuah batch file bernama install.bat untuk mengotomatisasikannya:

@echo off

:shutdown_mysql
tasklist | find "mysqld.exe" > nul
if errorlevel 1 goto :uninstall_manifest
echo Shutting down mysqld.exe.
taskkill /f /im mysqld.exe

:uninstall_manifest
wevtutil gp MySQLWindowsAuditProvider 2> nul | find ":" > nul
if errorlevel 1 goto :install_manifest
echo Uninstall information manifest for MySQLWindowsAuditProvider
wevtutil um message.man

:install_manifest
echo Install manifest for MysQLWindowsAuditProvider
wevtutil im message.man

:copy dll file to plugin location
set mysql_plugin_dir="C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin"
echo Copy %1 to %mysql_plugin_dir%
copy %1 %mysql_plugin_dir% > nul

Setelah itu, saya men-klik kanan nama proyek dan memilih Properties. Pada dialog yang muncul, saya memilih Configuration Properties, Build Events, Post-Build Event. Setelah itu, saya mengisi bagian Command Line dengan nilai seperti berikut ini:

Mengatur build event di proyek Visual Studio

Mengatur build event di proyek Visual Studio

Sekarang, Visual Studio akan secara otomatis mengerjakan install.bat seusai menghasilkan file DLL. Untuk menghasilkan file DLL, saya segera memilih menu Build, Build Solution.

Untuk menguji plugin yang telah dibuat, saya segera menjalankan MySQL Server dan men-install plugin dengan memberikan perintah SQL seperti berikut ini (hanya perlu dilakukan bila belum pernah di-install sebelumnya!):

INSTALL PLUGIN WINDOWS_AUDIT SONAME 'mysql_windows_audit.dll';

Untuk memastikan plugin sudah aktif, saya dapat menggunakan perintah SHOW PLUGINS seperti berikut ini:

mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+-------------------------+---------+
| Name                       | Status   | Type               | Library                 | License |
+----------------------------+----------+--------------------+-------------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                    | GPL     |
| WINDOWS_AUDIT              | ACTIVE   | AUDIT              | mysql_windows_audit.dll | GPL     |
+----------------------------+----------+--------------------+-------------------------+---------+

Bila saya membuka Windows Event Viewer, saya akan menjumpai channel baru bernama MySQLWindowsAuditProvider. Semua hasil audit akan tersimpan di channel ini, seperti yang terlihat pada gambar berikut ini:

Contoh log yang dihasilkan di Event Viewer

Contoh log yang dihasilkan di Event Viewer

Selain itu, saya juga bisa melihat jumlah koneksi yang gagal dilakukan sejak database dinyalakan dengan memberikan perintah SQL seperti berikut ini:

mysql> SHOW STATUS LIKE 'windows_audit_%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| windows_audit_connection_errors | 1     |
+---------------------------------+-------+

Saya juga bisa mematikan proses audit yang dilakukan oleh plugin ini dengan memberikan perintah SQL seperti berikut ini:

mysql> SET GLOBAL windows_audit_active = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'windows_audit_%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| windows_audit_active | OFF   |
+----------------------+-------+

Perubahan pada variabel ini juga akan menimbulkan sebuah log baru di Event Viewer, seperti yang terlihat pada gambar berikut ini:

Log yang muncul jika status audit berubah.

Log yang muncul jika status audit berubah.

Berkat fasilitas plugin dari MySQL Server, saya memperoleh kemampuan auditing sesuai dengan yang saya butuhkan secara mudah 🙂

Memahami Authentication dan Auditing Di MySQL Server

Salah satu yang unik pada MySQL Server adalah nama akun untuk pengguna memiliki syntax seperti 'nama_user'@'nama_host'. Dengan demikian, sebuah nama_user yang sama boleh dipakai lebih dari sekali untuk nama_host yang berbeda. Bahkan nilai nama_user boleh dikosongkan (anonymous user) sehingga nama_host tertentu boleh mengakses database tanpa perlu memberikan nama_user.

Nilai nama_host dipakai untuk melakukan validasi host asal dimana user melakukan koneksi. Bila saya tidak menyertakan nama_host pada saat membuat pengguna, maka nilai tersebut dianggap sebagai % yang berarti pengguna boleh melakukan koneksi dari mana saja.

Sebagai contoh, saya bisa membuat sebuah user yang hanya boleh diakses dari IP 192.168.56.0 sampai 192.168.56.255 dengan menggunakan perintah SQL seperti berikut ini:

CREATE USER 'inventory'@'192.168.56.0/255.255.255.0';

Tentu saja membatasi host asal tidak cukup! Saya perlu memberikan password sehingga pengguna perlu memberikan nilai password yang benar saat melakukan koneksi. Untuk itu, saya bisa menambahkan klausa IDENTIFIED BY seperti pada perintah SQL berikut ini:

CREATE USER 'inventory'@'192.168.56.0/255.255.255.0'
IDENTIFIED BY 'password_12345';

Salah satu masalah dengan perintah di atas adalah saya mengetik nilai password secara polos. Ini memiliki resiko tersendiri, terutama bila saya menyimpan perintah ini dalam file SQL sebagai cadangan untuk dikerjakan suatu hari nanti. Oleh sebab itu, akan lebih baik bila saya menyimpan password dalam bentuk hash. Untuk mengetahui nilai hash dari sebuah password polos, saya dapat memberikan perintah SQL berikut ini:

SELECT PASSWORD('password_12345');

Saya kemudian bisa memakai hash yang dihasilkan perintah di atas sebagai password pada saat membuat user:

CREATE USER 'inventory'@'192.168.56.0/255.255.255.0' 
IDENTIFIED BY PASSWORD '*E758CB9320D032FDC632D7FAB56BE5A50920BB89';

Untuk tingkat keamanan yang lebih tinggi, MySQL Server sejak versi 5.6.6 memiliki fitur sha256_password (http://dev.mysql.com/doc/refman/5.6/en/sha256-authentication-plugin.html) sebagai authentication plugin. Plugin ini mensyaratkan penggunaan SSL atau RSA pada saat melakukan koneksi guna meng-enkripsi password yang dikirim dari client ke server. MySQL Community Edition (versi gratis yang di-build dengan yaSSL) tidak mendukung enkripsi RSA sehingga untuk menggunakan sha256_password dibutuhkan SSL. Penggunaan SSL bisa membuat database menjadi lebih lambat akibat beban enkripsi & deskripsi koneksi. Ini adalah pengorbanan yang wajar bila menginginkan keamanan yang lebih baik. Sementara itu, MySQL Enterprise Edition (di-build dengan OpenSSL) memiliki dukungan enkripsi RSA sehingga sha256_password dapat dipakai tanpa mengaktifkan SSL.

Selain itu, bagi pengguna Windows yang memakai MySQL 5.6.10 versi berbayar, terdapat Windows Native Authentication Plugin dimana pengguna dapat login ke server database berdasarkan informasi akun Windows. Dengan demikian, pengguna tidak perlu memberikan password lagi karena ia akan dikenali berdasarkan akun di sistem operasi Windows yang sedang dipakainya.

Langkah berikutnya setelah membuat pengguna adalah menentukan apa saja yang boleh diakses oleh penguna tersebut. Secara default, pengguna yang baru dibuat hanya memiliki akses ke database information_schema dan test. Pengguna tersebut juga tidak memiliki hak untuk membuat database baru. Oleh sebab itu, saya perlu memberikan hak akses atas sebuah database yang sudah ada dengan memberikan perintah SQL seperti berikut ini:

GRANT ALL ON inventory.* TO 'inventory'@'192.168.56.0/255.255.255.0';

Selain itu, bila saya ingin seluruh komputer dengan IP 192.168.56.0 sampai 192.168.56.255 boleh membaca isi tabel pesan di database inventory dan tidak boleh menghapus atau mengubah isinya, saya dapat memberikan SQL seperti berikut ini:

CREATE USER ''@'192.168.56.0/255.255.255.0';
GRANT SELECT ON inventory.pesan TO ''@'192.168.56.0/255.255.255.0';

Perintah SQL di atas akan menyebabkan komputer dengan IP 192.168.56.0 sampai 192.168.56.255 boleh melakukan koneksi ke database tanpa harus memberikan nama user dan password. Tetapi, bila melakukan koneksi secara anonymous seperti ini, mereka hanya bisa melihat isi tabel pesan saja:

C:\>mysql -h mydatabase.com

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| inventory          |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> use inventory;
Database changed

mysql> show tables;
+---------------------+
| Tables_in_inventory |
+---------------------+
| pesan               |
+---------------------+
1 row in set (0.00 sec)

mysql> delete from pesan;
ERROR 1142 (42000): DELETE command denied to user ''@'192.168.56.30' for table 'pesan'

Salah satu fasilitas lainnya yang penting dalam mengamankan database adalah auditing. Fasilitas ini memungkinkan database administrator untuk mengetahui kapan pengguna melakukan koneksi dan apa saja query yang diberikan pengguna tersebut. Pada MySQL Server, auditing dilakukan dengan bantuan Audit Server Plugin. Sayang sekali, walaupun MySQL Server menyediakan API untuk menulis Audit Server Plugin, satu-satunya implementasi resmi berupa MySQL Audit Log Plugin hanya tersedia di MySQL Enterprise Edition (versi berbayar).

Sebagai alternatif untuk melakukan auditing tanpa harus beralih ke versi berbayar, saya dapat menggunakan Audit Plugin dari MariaDB di MySQL Server. Walaupun merupakan bagian dari MariaDB, Audit Plugin diprogram berdasarkan Audit Server Plugin API dari MySQL Server dan telah diuji untuk MySQL Server. Tentu saja alasan utama lain untuk memakainya di MySQL Server adalah karena ia dapat di-download secara gratis di https://mariadb.com/products/connectors-plugins.

Pada saat tulisan ini dibuat, versi terakhir Audit Plugin dari MariaDB adalah 1.1.8. Setelah men-download dan men-extract file server_audit-1.1.8.zip, saya perlu memindahkan file server_audit.dll di folder windows-32 (karena saya memakai Windows 32-bit) ke lokasi direktori plugin milik MySQL Server. Saya dapat mengetahui lokasi direktori plugin dengan memberikan perintah SQL seperti berikut ini:

SHOW VARIABLES LIKE 'plugin_dir';

Setelah men-copy file server_audit.dll pada direktori bersangkutan, saya dapat men-install plugin dengan memberikan perintah SQL seperti berikut ini:

INSTALL PLUGIN server_audit SONAME 'server_audit.dll';

Setelah ini, MariaDB Audit Plugin akan aktif. Saya akan menemukan beberapa variabel baru seperti berikut ini:

mysql> SHOW VARIABLES LIKE 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 1                     |
| server_audit_output_type      | file                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
14 rows in set (0.00 sec)

Karena nilai server_audit_logging adalah OFF, saya perlu mengaktifkannya dengan memberikan perintah SQL berikut ini:

SET GLOBAL server_audit_logging = ON;

Untuk perubahan yang permanen, saya perlu menyimpan nilai variabel di file konfigurasi my.cnf.

Sekarang, saya dapat melihat status auditing dengan memberikan perintah SQL seperti berikut ini:

mysql> SHOW STATUS LIKE 'server_audit%';
+----------------------------+------------------+
| Variable_name              | Value            |
+----------------------------+------------------+
| server_audit_active        | ON               |
| server_audit_current_log   | server_audit.log |
| server_audit_last_error    |                  |
| server_audit_writes_failed | 0                |
+----------------------------+------------------+
4 rows in set (0.00 sec)

Terlihat bahwa MariaDB Audit Plugin akan menulis hasil audit ke file dengan nama server_audit.log. Pada instalasi default MySQL Server di Windows, file ini terletak di folder seperti C:\ProgramData\MySQL\MySQL Server 5.6\data.

MariaDB Audit Plugin menyediakan 3 jenis informasi yang di-audit, yaitu CONNECT, QUERY dan TABLE. Pada MySQL Server, hanya CONNECT dan QUERY yang dapat dipakai. Sebagai contoh, bila saya hanya ingin merekam aktifitas koneksi dan mengabaikan aktifitas query, saya dapat memberikan perintah SQL seperti berikut ini:

SET GLOBAL server_audit_events = 'CONNECT';

Bila ada pengguna yang melakukan koneksi atau memutuskan koneksi, saya akan memperoleh baris baru di file server_audit.log yang isinya seperti berikut ini:

20141207 20:00:46,mydatabase.com,inventory,192.168.56.30,12,0,CONNECT,,,0
20141207 20:01:06,mydatabase.com,inventory,192.168.56.30,12,0,DISCONNECT,,,0

Informasi di atas terdiri atas waktu, nama server database yang dihubungi, nama user dan lokasi host asal yang melakukan koneksi, id koneksi, dan informasi seperti CONNECT & DISCONNECT.

Baris yang sangat penting untuk dipantau adalah baris dimana pengguna gagal melakukan koneksi, yang terlihat seperti berikut ini:

20141207 18:06:06,mydatabase.com,inventory,192.168.56.30,13,0,FAILED_CONNECT,,,1045
20141207 18:06:06,mydatabase.com,inventory,192.168.56.30,13,0,DISCONNECT,,,0

Informasi FAILED_CONNECT menunjukkan bahwa ada pengguna yang gagal melakukan koneksi. Nilai 1045 adalah kode kesalahan yang menyebabkan kegagalan koneksi. Berdasarkan informasi di http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html, nilai ini adalah ER_ACCESS_DENIED_ERROR yang memiliki pesan seperti Access denied for user '%s'@'%s' (using password: %s). Hal ini menunjukkan bahwa pengguna gagal melakukan koneksi karena memberikan password yang salah. Bila baris seperti ini sering terlihat, maka ada kemungkinan pengguna (atau hacker) sedang berusaha mengakses sesuatu yang bukan hak-nya.

Menampilkan Log SQL Yang Dikerjakan MySQL

Kode program yang memakai ORM (object relational mapper) seperti JPA umumnya tidak memberikan query SQL secara langsung karena query akan dihasilkan oleh sang mapper.   Tapi terkadang developer perlu mengetahui apa saja query SQL yang dihasilkan oleh ORM, misalnya untuk keperluan troubleshooting atau mencari bottleneck kinerja.   Bila memakai database MySQL, si developer memiliki beberapa pilihan untuk melihat SQL yang dibuat oleh aplikasi tanpa perlu memakai tool tambahan.

JDBC bawaan untuk MySQL sudah menyediakan fasilitas logging.   Hasil log-nya lebih detail karena dilengkapi dengan nilai parameter dibandingkan dengan yang terlihat di log Hibernate yang hanya menuliskan isi prepared statement dimana nilai masih berupa tanda tanya (?).  Untuk mengaktifkan logging di JDBC, saya perlu menambahkan sesuatu di JDBC URL, yaitu nilai logger dan profileSQL.  Sebagai contoh, bila saya memakai JPA, maka saya mengubah isi berikut ini di persistence.xml:

...
<properties>
   ...
   <property name="javax.persistence.jdbc.url"
      value="jdbc:mysql://localhost/namadb?logger=com.mysql.jdbc.log.Slf4JLogger&amp;profileSQL=true" />
   ...
</properties>
...

Nilai profileSQL menunjukkan agar driver JDBC tersebut akan mencatat setiap SQL yang ada.   Sementara itu, nilai logger menunjukkan tujuan yang menyimpan catatan SQL nantinya.    Saya memilih menggunakan SlfjLogger yang akan mencatat sesuai dengan konfigurasi Log4j saya.  Saya memisahkan kedua nilai tersebut dengan &amp; karena ini adalah XML;  bila tidak memakai JPA, misalnya saya membuat koneksi di kode program, maka saya boleh langsung memakai tanda ampersand (&).

Langkah berikutnya adalah melakukan konfigurasi Log4J.  Bila memakai Griffon, maka perubahan ini dapat dilakukan di file Config.groovy.  Sebagai contoh, saya akan mencatat log SQL yang dikerjakan aplikasi ke sebuah file:

log4j = {
  ...
  appenders {
     rollingFile name: 'sqlLog', file: "${System.getProperty('user.home')/sql.log",
        layout: pattern(conversionPattern: '%d [%t] %-5p %c - %m%n'), maxFileSize: 10485760)
  }
  ...
  debug additivity: false, sqlLog: 'com.mysql.jdbc.log'
  ...

Setelah ini, bila aplikasi dijalankan, saya dapat menemukan seluruh SQL yang dikerjakan di file sql.log yang terletak di folder home (seperti C:\Users\nama\).

Alternatif lainnya, saya dapat melakukan pencatatan SQL ini disisi server MySQL itu sendiri.  Hal ini berguna bila database diakses oleh banyak user secara bersamaan, dan saya ingin melihat seluruh SQL yang dikerjakan oleh user-user yang terkoneksi.  Perlu diperhatikan bahwa pencatatan melalui general query log ini tidak harus berurut sesuai dengan eksekusinya, melainkan berdasarkan urutan saat diterima dari client.

Untuk melakukan pencatatan di sisi server, saya harus melakukan konfigurasi MySQL, misalnya dengan mengubah file my.ini yang ada di folder instalasi MySQL (seperti C:\Program Files\MySQL\MySQL Server 5.6\my.ini).  Saya harus mengubah file tersebut sehingga terdapat baris berikut ini:

log-output=FILE
general-log=1
general-log-file="sql.log"

Setelah me-restart server MySQL, maka seluruh SQL yang dikerjakan oleh server tersebut akan tercatat di file sql.log.  File ini terletak di lokasi yang sesuai dengan nilai datadir (nilainya tertera di file konfigurasi yang sama, atau gunakan perintah SHOW VARIABLES LIKE 'datadir'; untuk melihat nilainya)Secara default, lokasi ini adalah di C:\ProgramData\MySQL\MySQL Server 5.6\data.

Berikutnya, jalankan aplikasi, tunggu hingga log terkumpul (gunakan tail -f untuk memantau bila di Linux), lalu cari query yang menjadi biang kesalahannya 🙂

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 adalah daftar 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

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:

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: ALL
possible_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: ALL
possible_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_ref
possible_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 field faktur_id milik tabel penjualan dengan perintah berikut ini:

ALTER TABLE `penjualan`
ADD INDEX `faktur_id_idx` (`faktur_id` ASC) ;

Berikut ini adalah hasil EXPLAIN setelah penambahan index:

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: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: xxx
        Extra: Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: penjualan
         type: ref
possible_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_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 20
          ref: toko2.penjualan.brg_id
         rows: xxx
        Extra: NULL
3 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:

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: 0

mysql> 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 key pada tabel penjualan:

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:

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:

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:

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

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:

$("#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:

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');

}

MySQL for Excel: Menyatukan Microsoft Excel Dan Database

MySQL for Excel adalah sebuah fitur tambahan yang menarik dari MySQL, terutama bagi yang sering memindahkan data dari Microsoft Excel ke tabel di database (dan sebaliknya).  Untuk men-install MySQL for Excel, saya harus memberi centang pada MySQL for Excel 1.1.1 di tahap Feature Selection saat melakukan instalasi MySQL dari installer, seperti yang terlihat pada gambar berikut ini:

Memilih MySQL for Excel Saat Instalasi MySQL

Memilih MySQL for Excel Saat Instalasi MySQL

Btw, setelah instalasi MySQL 5.6 selesai, saya menemukan bahwa file my.ini yang dihasilkan ternyata tidak dipakai.  Berdasarkan dokumentasi,  lokasi file konfigurasi di %PROGRAMDATA%\MySQL\MySQL Server 5.6\my.ini yang seharusnya dibaca, tetapi faktanya tidak demikian (bila mysqld dijalankan secara manual dari console).  Salah satu solusi yang mudah adalah men-copy file yang ada disini ke lokasi instalasi MySQL di Program Files.

Kembali ke MySQL for Excel, bila saya membuka Microsoft Excel, pada tab Data, saya akan menemukan icon MySQL for Excel seperti yang terlihat pada gambar berikut ini:

Tampilan Icon MySQL for Excel

Tampilan Icon MySQL for Excel

Bila saya men-klik tombol tersebut, akan muncul sebuah side pane yang berisi daftar koneksi.  Daftar koneksi ini sama seperti yang sudah saya definisikan di MySQL Workbench.  Contoh tampilannya akan terlihat seperti pada gambar berikut ini:

Tampilan MySQL for Excel

Tampilan MySQL for Excel

Setelah memilih salah satu koneksi yang ada, saya dapat memilih schema atau database yang akan dipakai, seperti yang terlihat pada gambar berikut ini:

Memilih Schema Yang Akan Dipakai

Memilih Schema Yang Akan Dipakai

Sekarang, saya akan mencoba memasukkan data dari Excel menjadi sebuah tabel (yang belum ada sebelumnya).  Sebagai contoh, anggap saja seorang pramusaji memberikan daftar menu pada saya dalam bentuk file Excel.  Untuk mengubah daftar menu ini menjadi sebuah tabel di MySQL Server, saya perlu memilih seluruh data yang ada, kemudian men-klik tombol Export Excel Data to New Table, seperti yang terlihat pada gambar berikut ini:

Membuat dan Mengisi Tabel Dengan Data Dari Excel

Membuat dan Mengisi Tabel Dengan Data Dari Excel

Akan muncul sebuah dialog Export Data.  Pada kotak dialog ini, saya dapat mengisi nama tabel yang akan dibuat.  Saya juga bisa menentukan primary key yang dipakai dan tipe data untuk setiap kolom.  Saya akan membiarkan primary key berupa angka berurut.  Tampilan dialog Export Data akan terlihat seperti pada gambar berikut ini:

Tampilan Dialog Export Data

Tampilan Dialog Export Data

Setelah selesai, saya men-klik tombol Export.  Setelah proses  pembuatan tabel dan pengisian data selesai, akan muncul sebuah kotak dialog.  Pada kotak dialog ini, saya juga bisa men-copy SQL CREATE TABLE dan INSERT yang telah diberikan, misalnya untuk keperluan dokumentasi atau backup, seperti yang terlihat pada gambar berikut ini:

Akhir Dari Proses Export

Akhir Dari Proses Export

Selain untuk meng-export data dari Excel ke tabel, MySQL for Excel juga dapat dipakai untuk meng-edit data di tabel secara cepat.  Caranya adalah dengan memilih nama tabel yang akan di-edit kemudian men-klik tombol Edit MySQL Data.  Bagi beberapa orang yang terbiasa memakai Excel, mengedit data secara langsung di Excel akan terasa lebih cepat dibandingkan memakai Workbench.  Cell di Excel yang telah di-edit akan berubah warna menjadi hijau, seperti yang terlihat pada gambar berikut ini:

Mengedit tabel di Excel

Mengedit tabel di Excel

Setelah perubahan selesai dilakukan, pengguna dapat memilih Commit Changes untuk melakukan perubahan ke tabel, atau memilih Revert Data untuk membatalkan perubahan.  Bila pengguna memilih Commit Changes, setelah perubahan selesai dilakukan pada database, akan terdapat sebuah dialog dimana pengguna dapat melihat dan men-copy SQL yang yang mewakili perubahan yang dilakukannya, seperti yang terlihat pada gambar berikut ini:

Dialog Perubahan Data Yang Sukses

Dialog Perubahan Data Yang Sukses