Memakai Graph Database Dengan Neo4j

Selama beberapa dekade terakhir ini, database relasional berbasis tabel boleh dibilang merupakan jenis database yang paling sering dipakai dan paling dewasa. Database relasional tidak akan hilang dalam jangka waktu dekat ini, tapi mereka memiliki keterbatasan sendiri. Sebagai contoh, database relasional akan kewalahan melakukan query yang dibutuhkan untuk keperluan jejaring sosial seperti Facebook dan Twitter. Database relasional memang memiliki JOIN untuk menggabungkan tabel, tapi JOIN yang mereka miliki tidak memiliki arah dan label. Salah satu jenis database yang diciptakan untuk mengembalikan makna ‘relasional’ tersebut adalah graph database.

Graph yang dimaksud pada graph database adalah definisi graph pada graph theory yang merupakan bagian dari ilmu matematika diskrit. Paper pertama yang membahas graph theory ditulis oleh Leonhard Eulear pada tahun 1736 yang membahas tentang permasalahan Konigsberg Bridge.

Pada kesempatan ini, saya akan mencoba menggunakan sebuah graph database populer, Neo4j. Saya harus mulai dari mana? Pada database relasional, saya bisa mulai dengan merancang tabel dengan merancang skema dengan menggunakan ERD. Berbeda dari database relasional, sebuah graph database tidak memiliki skema seperti tabel dan kolom yang berbeda dengan isi (record). Segala sesuatunya adalah data! Oleh sebab itu, pada saat merancang untuk graph database, saya bisa langsung memakai contoh data. Sebagai latihan, saya akan membuat database yang menampung produk yang dibeli oleh pengguna seperti berikut ini:

Contoh rancangan untuk graph database

Contoh rancangan untuk graph database

Pada rancangan di atas, masing-masing lingkaran mewakili sebuah node. Setiap node terhubung ke node lain melalui garis yang memiliki arah yang disebut sebagai edge. Baik node maupun edge boleh memiliki properties. Berkebalikan dari database relasional, nilai properties pada node atau edge akan lebih baik bila sebisa mungkin di-‘normalisasi’ menjadi node sehingga bisa dipakai secara efisien di query.

Setelah menentukan rancangan database, sekarang saatnya untuk memasukkan data. Saya sudah menyiapkan beberapa data dalam bentuk CSV untuk dimasukkan sebagai isi dari database Neo4j. Saya akan mulai dengan membuat seluruh node yang mewakili produk. Untuk itu, setelah menjalankan Neo4j, saya membuka browser dan menampilkan http://localhost:7474. Ini adalah halaman web dimana saya bisa mengelola database Neo4j dan juga mengerjakan query (dalam bentuk Cypher). Sebagai contoh, saya memberikan perintah seperti pada gambar berikut ini:

Mengisi data dari file CSV

Mengisi data dari file CSV

Saya juga melakukan hal yang sama untuk menciptakan node yang mewakili pengguna, misalnya dengan memberikan perintah Cypher berikut ini:

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:C:/user.csv" AS row
CREATE (:User {id: row.id});

Karena kedua node ini akan sering dicari berdasarkan id, maka saya bisa memberikan index. id bersifat unik sehingga saya bisa menggunakan perintah CREATE CONSTRAINTS yang akan menciptakan unique index yang lebih cepat dibandingkan index biasa. Sebagai contoh, saya memberikan perintah berikut ini:

CREATE CONSTRAINT ON (p:Produk) ASSERT p.id IS UNIQUE;
CREATE CONSTRAINT ON (u:User) ASSERT u.id IS UNIQUE

Sekarang adalah saatnya untuk membuat edge BELI dari User ke Produk. Saya melakukannya dengan memberikan perintah seperti berikut ini:

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:C:/buys.csv" AS row
MATCH (user:User {id: row.user_id})
MATCH (produk:Produk {id: row.produk_id})
MERGE (user)-[:BELI]->(produk);

Setelah ini, saya bisa mulai belajar mencari data. Bila pada database relasional terdapat SQL, maka Neo4j menggunakan query khusus yang disebut sebagai Cypher. Bahasa query ini juga deklaratif seperti SQL dan dirancang agar mudah dipahami. Sebagai contoh, untuk menampilkan 25 node user pertama, saya dapat menggunakan Cyhper seperti:

MATCH (n:User) RETURN n LIMIT 25;
Hasil query dalam bentuk tabel

Hasil query dalam bentuk tabel

Tidak ada yang berbeda dari database relasional bila node dikembalikan dalam bentuk tabel. Agar lebih menarik, saya bisa menampilkan hasil query dalam bentuk grafis seperti yang terlihat pada gambar berikut ini:

Hasil query dalam bentuk graph

Hasil query dalam bentuk graph

Tidak seperti hasil query dari database relasional yang sulit dicerna secara langsung, hasil query pada graph database cukup menarik untuk diamati. Pengguna juga bisa menemukan pola data secara visual.

Contoh lain dari query Cypher adalah:

MATCH (u:User {id:"xxx"})-[:BELI]->(p:Produk)
RETURN p;

Klausa MATCH pada query di atas akan mencari node User yang memiliki id "xxx" dan memiliki asosiasi BELI terhadap node Produk. Setelah itu, klausa RETURN akan mengembalikan seluruh node Produk yang ada. Dengan kata lain, query ini akan mengembalikan seluruh produk yang dibeli oleh pengguna dengan id "xxx". Tentu saja query seperti ini juga bisa dilakukan secara mudah dengan menggunakan SQL di database relasional.

Untuk menunjukkan sesuatu yang tidak gampang dilakukan melalui SQL, saya akan memberikan query Cypher seperti berikut ini:

MATCH (u1:User)-[:BELI]->(p:Produk)<-[:BELI]-(u2:User) 
RETURN p;

Hasilnya akan terlihat seperti pada gambar berikut ini:

Hasil query dalam bentuk graph

Hasil query dalam bentuk graph

Dengan menggunakan arah panah seperti -[:BELI]-> dan <-[:BELI]-, query di atas akan mengembalikan seluruh node Produk yang minimal sudah dibeli oleh 2 pengguna. Karena saya tidak peduli dengan node User pada query di atas, saya bisa mengabaikannya dengan mengubah query menjadi seperti berikut ini:

MATCH ()-[:BELI]->(p:Produk)<-[:BELI]-()
RETURN p;

Selain mengembalikan node, saya juga bisa mengembalikan property sehingga memperoleh hasil dalam bentuk tabel. Sebagai contoh, saya bisa memberikan query seperti berikut ini:

MATCH (u1:User)-[:BELI]->(p:Produk)<-[:BELI]-(u2:User)
RETURN u1.id AS user1, p.url AS produk, u2.id AS user2;

Ingin sesuatu yang lebih sulit dilakukan melalui query SQL? Sebagai contoh, saya akan mencari produk lain yang dibeli oleh pengguna lain yang juga membeli produk yang sama dengan seorang pengguna dengan menggunakan query seperti berikut ini:

MATCH (u1:User)-[:BELI]->(p1:Produk)<-[:BELI]-(u2:User),
      (u2)-[:BELI]->(p2:Produk)
RETURN p1, p2;
Hasil query dalam bentuk graph

Hasil query dalam bentuk graph

Karena graph database memang dirancang untuk keperluan seperti Cypher di atas, ia bukan hanya menawarkan kemudahan, tapi juga kinerja yang lebih baik dibandingkan query serupa yang memakai database relasional. Walaupun penelusuran tetapi dilakukan satu per satu seperti pada JOIN di database relasional, setiap node bisa memiliki path yang berbeda. Begitu path untuk node sudah melenceng dari yang diharapkan, ia bisa segera diabaikan sehingga penelusuran bisa segera dilanjutkan.

Saya juga menggabungkan MATCH di Cypher di atas sehingga menjadi seperti berikut ini:

MATCH (u1:User)-[:BELI]->(p1:Produk)<-[:BELI]-(u2:User)-[:BELI]->(p2:Produk)
RETURN p1, p2;

Tergantung pada selera, versi yang terakhir ini mungkin lebih mudah dipahami dibandingkan dengan versi sebelumnya.

Sama seperti di SQL, untuk mebatasi query di Cypher saya juga dapat menggunakan klausa WHERE. Sebagai contoh, bila saya hanya tertarik pada produk dengan id 'xxx', maka saya bisa memberikan query seperti berikut ini:

MATCH (u1:User)-[:BELI]->(p1:Produk)<-[:BELI]-(u2:User)-[:BELI]->(p2:Produk)
WHERE p1.id = "xxx"
RETURN u1, p1, u2, p2;
Hasil query dalam bentuk graph

Hasil query dalam bentuk graph

Pada gambar di atas, id 5969 adalah nilai internal yang diberikan untuk produk 'xxx'. Terlihat bahwa user seperti user 329, 3953, 5149 dan sejenisnya membeli produk 'xxx' bersamaan dengan produk lain.

Pada query diatas, akan ada banyak produk ganda yang ditampilkan karena beberapa pengguna berbeda bisa saja melihat banyak produk yang sama. Sama seperti di SQL, untuk mengembalikan hanya produk yang unik, saya dapat menggunakan klausa DISTINCT seperti pada contoh berikut ini:

MATCH (u1:User)-[:BELI]->(p1:Produk)<-[:BELI]-(u2:User)-[:BELI]->(p2:Produk)
WHERE p1.id = "xxx" 
RETURN DISTINCT p1, u2, p2;
Iklan

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.

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.

Memeriksa Waktu Eksekusi SQL Di Oracle TimesTen

Pada artikel sebelumnya, saya menuliskan bagaimana memantau kinerja database TimesTen melalui procedure di package TT_STATS.   Pada artikel ini, saya akan mencoba memeriksa kinerja eksekusi masing-masing SQL dengan menggunakan procedure bawaan TimesTen seperti ttStatsConfig, ttSQLCmdCacheInfo dan sebagainya.

Saya akan mulai dengan mengambil sampel untuk setiap eksekusi SQL di TimesTen dengan memberikan perintah berikut ini:

Memulai proses pengambilan sampel

Memulai proses pengambilan sampel

Pada anonymous PL/SQL di atas, saya mengaktifkan pengambilan sampel untuk setiap kali eksekusi SQL dengan memanggil ttStatsConfig("SQLCmdSampleFactor", 1).   Selain itu, saya juga menghapus statistik yang sudah diambil sebelumnya dengan memanggil ttStatsConfig("SQLCmdHistogramReset", 0).   Saya menentukan jenis statistik yang akan diambil dengan memanggil ttStatsConfig("StatsLevel", "TYPICAL").

Setelah mengaktifkan proses pengambil sampel, saya perlu menjalankan aplikasi dan melakukan operasi yang berkaitan dengan database di aplikasi saya.   TimesTen akan mengambil statistik untuk setiap SQL yang dikerjakan oleh aplikasi  secara otomatis.   Setelah selesai, saya mematikan aplikasi dan kembali ke SQL Developer.

Saya dapat menampilkan statistik yang telah terkumpul dengan memberikan perintah seperti berikut ini:

CALL ttSQLCmdCacheInfo;

Untuk melihat informasi khusus untuk SQL tertentu, saya dapat memanggil ttSQLCmdCacheInfo dengan melewatkan parameter berupa id dari perintah SQL tersebut, seperti yang terlihat pada gambar berikut ini:

Melihat statistik untuk SQL tertentu

Melihat statistik untuk SQL tertentu

Saya juga dapat memanggil ttSQLCmdCacheInfo2 yang menyediakan informasi tambahan berupa fetchCount, startTime, maxExecuteTime, lastExecuteTime, dan minExecuteTime.

Untuk melihat histogram yang menunjukkan persebaran waktu eksekusi untuk perintah SQL yang ada, saya memberikan perintah seperti yang terlihat pada gambar berikut ini:

Melihat histogram waktu eksekusi SQL

Melihat histogram waktu eksekusi SQL

Pada hasil di atas, terlihat bahwa terdapat 10.971 eksekusi SQL yang dikerjakan sejak saya memulai proses pengambilan sampel.   Total waktu yang dibutuhkan untuk mengerjakan seluruh SQL tersebut adalah 1,0693366 detik.   Dari seluruh SQL tersebut, terdapat 9.669 eksekusi yang membutuhkan waktu lebih dari 0,00001562 detik hingga 0,000125 detik.   Terdapat 1.207 eksekusi yang membutuhkan waktu lebih dari 0,000125 detik hingga 0,001 detik.   Dan seterusnya.

Berdasarkan hasil histogram, terlihat bahwa kebanyakan eksekusi SQL di aplikasi saya memiliki waktu eksekusi antara 0.00001563 detik hingga 0.000125 detik.   Tidak ada SQL yang waktu eksekusinya mencapai hingga 1 detik.   Ini adalah hasil yang cukup memuaskan mengingat isi tabel yang diproses adalah kumpulan transaksi jual beli selama beberapa tahun.

Memantau Kinerja Oracle TimesTen Dengan Package TT_STATS

Sebuah sepeda motor perlu dirawat secara berkala.   Perawatan yang dikenal sebagai engine tune-up ini berguna untuk menjaga agar kinerja mesin motor tetap optimal dengan cara melakukan kalibrasi ulang karbulator, mengganti spark plug (busi) yang usang, dan sebagainya.   Hampir semua pemilik motor mengerti bahwa sepeda motor yang mereka beli tidak bisa dipakai selamanya tanpa di-tune-up di bengkel.   Begitu juga dengan sebuah database; ia perlu dirawat dan dipantau secara berkala.   Bedanya, tidak seperti pemilik sepeda motor yang menyadari pentingnya tune-up, beberapa programmer pemula menganggap bahwa setelah program selesai dibuat, maka database dapat dibiarkan begitu saja hingga suatu hari nanti menjadi penuh.

Lalu, bagaimana cara memantau kinerja database TimesTen?   Saya dapat menggunakan tool bawaan ttStats atau langsung memanggil procedure yang ada di package TT_STATS.   Pada artikel ini, saya akan mencoba memakai beberapa procedure yang ada di package TT_STATS.

Saya akan mulai dengan memanggil procedure TT_STATS.CAPTURE_SNAPSHOT() seperti yang terlihat pada gambar berikut ini:

Memulai proses capture

Memulai proses capture

Pada pemanggilan TT_STATS.CAPTURE_SNAPSHOT di atas,  saya melewatkan nilai parameter capture_level berupa 'ALL' sehingga seluruh metrik yang ada akan di-capture.   Pilihan lainnya adalah 'NONE', 'BASIC' dan 'TYPICAL'.

Selain itu, saya juga menggunakan TT_STATS.SHOW_SNAPSHOTS untuk menampilkan seluruh snapshot yang tersedia.   Terlihat bahwa id snapshot yang barusan dibuat adalah 8.

Saya kemudian menjalankan aplikasi dan mengerjakan beberapa operasi yang melibatkan database.   Setelah selesai, saya kembali membuat sebuah snapshot baru di TimesTen.   Karena perintah tersebut sudah ada di worksheet SQL Developer, saya hanya perlu men-klik icon Run Script atau menekan tombol F5.

Sekarang, procedure TT_STATS.SHOW_SNAPSHOTS akan menampilkan dua buah snapshot berbeda, yaitu snapshot dengan id 8 dan id 9, seperti yang terlihat pada gambar berikut ini:

Men-capture snapshot kedua

Men-capture snapshot kedua

Output yang saya harapkan adalah laporan yang membandingkan kedua snapshot tersebut.   Saya dapat menggunakan procedure TT_STATS.GENERATE_REPORT_HTML untuk menghasilkan laporan dalam bentuk HTML, atau TT_STATS.GENERATE_REPORT_TEXT untuk menghasilkan laporan dalam bentuk teks biasa.   Sebagai latihan, saya akan menampilkan laporan dalam bentuk teks dengan perintah seperti berikut ini:

Membuat laporan teks

Membuat laporan teks

Pada gambar di atas, saya membuat sebuah anonymous PL/SQL yang memanggil TT_STATS.GENERATE_REPORT_HTML.   Parameter pertama dan parameter kedua adalah id dari snapshot yang hendak dibandingkan.   Parameter ketiga adalah sebuah associative array yang menampung isi laporan yang dihasilkan baris per baris.   Karena nilai associative array tidak dapat ditampilkan secara langsung, saya kemudian menggunakan looping for untuk mencetak isi associative array tersebut.

Sebagai langkah penutup, saya akan menghapus yang telah dihasilkan guna menghemat memori dengan memberikan perintah berikut ini:

Menghapus snapshot

Menghapus snapshot

Procedure TT_STATS.DROP_SNAPSHOTS_RANGE menerima parameter berupa id snapshot pertama hingga id snapshot terakhir yang akan dihapus.   Bila keduanya bernilai 0, maka seluruh snapshot yang ada akan dihapus.

Memakai Index Advisor Di TimesTen

Pada query SQL yang mengandung klausa WHERE, database perlu menyaring data yang harus dikembalikan.   Pencarian isi tabel yang dilakukan berdasarkan seluruh data yang ada, baris per baris, disebut sebagai full table scan.   Pada full table scan, semakin banyak baris pada tabel maka semakin lambat eksekusi query tersebut.

Full table scan dapat dihindari bila kolom dalam klausa WHERE sudah di-index sebelumnya.   Index adalah struktur data yang mewakili kolom di tabel yang dirancang untuk mempercepat pencarian.   Sebagai contoh, bila saya mendefinisikan sebuah range index pada kolom NILAI, maka database dapat menentukan dengan cepat baris mana yang memenuhi kondisi SQL WHERE NILAI > 60,  tanpa harus menelusuri seluruh baris yang ada (full table scan).   Walaupun penggunaan index meningkatkan kinerja query,  ia akan mempengaruhi proses seperti INSERT dan UPDATE karena pada saat isi tabel berubah, maka index juga perlu diperbaharui.   Oleh sebab itu, penggunaan index perlu dipertimbangkan secara seksama 🙂

Oracle TimesTen memiliki fasilitas index advisor yang dapat memberikan saran kolom mana yang perlu di-index bagi database administrator (atau developer, terutama yang ingat merawat database!).   Index advisor hanya memberikan rekomendasi saja;  database administrator perlu mempertimbangkan lagi apakah index perlu dibuat atau tidak.

Saya akan mencoba memakai index advisor untuk melihat rekomendasi index untuk sebuah aplikasi yang memproses faktur.   Langkah pertama yang saya lakukan adalah memperbaharui statistics untuk tabel.   Di SQL Developer, saya men-klik kanan Tables, lalu memilih Statistics, Update.   Setelah itu, saya men-klik tombol Apply.

Memperbaharui statistik

Memperbaharui statistik

Pada langkah berikutnya, saya memanggil procedure ttIndexAdviceCaptureStart() untuk menjalankan index advisor seperti yang diperlihatkan pada gambar berikut ini:

Memulai proses capture

Memulai proses capture

Agar dapat bekerja dengan baik, index advisor perlu mengamati SQL yang diberikan selama proses capture sebagai bahan pertimbangan dalam merekomendasikan index.

Saya memberikan nilai 1 sebagai nilai parameter pertama, captureLevel, karena SQL sebagai akan dikerjakan oleh aplikasi melalui koneksi yang berbeda.   Bila SQL akan diberikan oleh koneksi yang sama, saya dapat menggunakan nilai 0.

Setelah memulai proses capture, saya kemudian menjalankan aplikasi dan melakukan operasi dari aplikasi seperti layaknya saat dipakai oleh pengguna.   Saya mencoba mensimulasi beberapa aktifitas yang paling sering dilakukan oleh pengguna.   Saya juga mencoba menampilkan laporan, terutama yang membutuhkan waktu lama.

Setelah selesai dengan aplikasi, saya perlu mematikan proses capture dengan memanggil procedure ttIndexAdviceCaptureEnd()seperti yang terlihat pada gambar berikut ini:

Menyelesaikan proses capture

Menyelesaikan proses capture

Untuk melihat apa saja SQL yang berhasil diperoleh oleh index advisor, saya memanggil procedure ttIndexAdviceCaptureInfoGet()seperti pada gambar berikut ini:

Melihat informasi yang di-capture

Melihat informasi yang di-capture

Hasil dari procedure ttIndexAdviceCaptureInfoGet() memperlihatkan bahwa sebuah proses capture secara global (CAPTURELEVEL=1) telah selesai dilakukan (CAPTURESTATE=0).   Proses capture tersebut menampung 25.196 prepared statement dan telah mengerjakan 25.200 perintah SQL.

Sekarang, saya akan melihat apa rekomendasi dari index advisor berdasarkan hasil capture yang diperolehnya dengan memanggil procedure ttIndexAdviceCaptureOutput() seperti yang terlihat pada gambar berikut ini:

Menampilkan hasil capture

Menampilkan hasil capture

Pada hasil di atas, kolom createStmt berisi perintah SQL untuk membuat index, sementara kolom stmtCount menunjukkan jumlah perintah SQL berbeda yang akan diuntungkan oleh index tersebut.   Sebagai contoh, bila saya memberikan index yang berisi kombinasi kolom (DTYPE,TANGGAL) pada tabel FAKTUR,  maka akan ada 33 perintah SQL berbeda (di aplikasi) yang diuntungkan oleh index tersebut.

Saya tidak akan mengerjakan seluruh saran yang diberikan di createStmt karena operasi insert, update, dan delete akan menjadi lambat.  Semakin banyak index yang perlu diperbaharui, maka semakin lambat operasi perubahan data pada sebuah tabel.  Walaupun demikian, index advisor setidaknya sudah memberikan titik terang berupa kandidat kolom yang perlu di-index.

Mengakses TimesTen Dengan Oracle SQL Developer

Selama ini saya mengakses database TimesTen melalui tool bawaan yang berbasis command line, yaitu ttIsql.   Namun proses administrasi database akan lebih mudah bila seandainya terdapat tool berbasis GUI.   Sebagai contoh, di MySQL Server ada MySQL Workbench dan di Oracle Database ada SQL Developer, juga ada aplikasi komersial seperti Toad.   Lalu bagaimana dengan Oracle TimesTen?   Saya dapat menggunakan SQL Developer untuk mengakses database TimesTen.

Tapi saat pertama kali mencoba menghubungi TimesTen di SQL Developer, saya mengalami kendala.  Saat saya mengakses TimesTen melalui metode direct connection, SQL Developer menampilkan pesan kesalahan seperti berikut ini:

[TimesTen][TimesTen 11.2.2.5.0 ODBC Driver][TimesTen]TT0837:
Cannot attach data store shared-memory segment, error 8 -- file
"db.c", lineno 9934, procedure "sbDbConnect"

Error 8 adalah pesan kesalahan bahwa memori tidak cukup.   Dokumentasi TimesTen menyebutkan bahwa manajemen shared memory di Windows berbeda dengan sistem operasi Unix atau Linux.   Keterbatasan di Windows menyebabkan sulitnya memetakan seluruh isi database ke lokasi memori secara berlanjut (tanpa fragmentasi).   Dokumentasi juga menyarankan agar memakai Windows 64-bit untuk menghindari kesalahan ini.

Tapi bagaimana bila saya tidak akan beralih ke Windows 64-bit?  Saya tetap ingin mengakses TimesTen melalui SQL Developer di Windows 32-bit.   Salah satu solusinya adalah dengan menggunakan koneksi client server di komputer yang sama.   Btw, hanya DSN yang didefinisikan pada System DSN saja yang dapat diakses sebagai server.   Sebagai contoh, gambar berikut ini DSN di ODBC Data Source Administrator yang akan dijadikan sebagai server:

Server DSN

Server DSN

Saya akan membuat sebuah client DSN baru dengan men-klik tombol Add…, memilih TimesTen Client 11.2.2 lalu men-klik tombol Finish.

Pada kotak dialog yang muncul, saya men-klik tombol Servers….   Lalu, pada kotak dialog Oracle TimesTen Logical Server List, saya men-klik tombol Add… untuk mendefinisikan server baru, yang isinya seperti berikut ini:

Mendefinisikan server baru

Mendefinisikan lokasi server baru

Setelah itu, saya men-klik tombol OK. Saya memilih server yang barusan saya buat, lalu men-klik tombol Close.   Kemudian saya mengisi kotak dialog yang ada menjadi seperti pada tampilan berikut ini:

Atribut untuk client DSN

Atribut untuk client DSN

Saya men-klik tombol Test Oracle TimesTen Server Connection dan Test Data Source Connection untuk memastikan koneksi dapat dilakukan dengan baik.   Setelah itu, saya men-klik tombol OK dua kali untuk menyelesaikan proses pembuatan client DSN ini.

Sekarang, saya akan menjalankan SQL Developer.   Tapi sebelumnya, saya perlu memastikan terlebih dahulu bahwa library TimesTen yang dibutuhkan sudah terdaftar di environment variable CLASSPATH.   Agar lebih pasti, saya menjalankan SQL Developer dari command prompt dengan memberikan perintah seperti berikut ini:

C:\>c:\TimesTen\tt1122_32\bin\ttenv.bat
Setting environment variables for TimesTen

set PATH to "..."

set CLASSPATH to "..."

C:\>c:\sqldeveloper\sqldeveloper

Tidak lama kemudian SQL Developer akan muncul di layar.  Saya hanya perlu sekali saja untuk mengerjakan SQL Developer seperti di atas, setelah itu saya dapat menjalankan SQL Developer melalui shortcut.

Langkah pertama yang saya lakukan di SQL Developer adalah men-klik tombol new connection pada SQL Developer untuk membuat koneksi baru ke database TimesTen, seperti yang diperlihatkan oleh gambar berikut ini:

Membuat koneksi baru

Membuat koneksi baru

Saya kemudian mengisi kotak dialog New / Select Database Connection menjadi seperti pada gambar berikut ini:

Mengisi informasi koneksi ke TimesTen

Mengisi informasi koneksi ke TimesTen

Bila tab TimesTen tidak muncul seperti di gambar di atas, pastikan bahwa JDBC driver untuk TimesTen sudah didefinisikan di environment variable CLASSPATH.

Saya kemudian men-klik tombol Test dan memastikan bahwa terdapat indikator Status: Success.   Setelah yakin, saya men-klik tombol Save, lalu diikuti dengan men-klik tombol Connect.

Setelah ini, saya dapat melihat isi schema untuk LATIHAN_CLIENT melalui SQL Developer, seperti yang terlihat pada gambar berikut ini:

Melihat isi schema database

Melihat isi schema database

Pada bagian Reports, saya juga dapat mengerjakan beberapa query ‘siap jadi‘ yang berkaitan dengan administrasi database TimesTen, seperti yang diperlihatkan oleh gambar berikut ini:

Query siap jadi untuk administrasi TimesTen

Query siap jadi untuk administrasi TimesTen

Untuk melakukan operasi pada sebuah tabel, saya dapat men-klik kanan sebuah tabel dan memilih salah satu akses yang diinginkan, seperti yang diperlihatkan pada gambar berikut ini:

Aksi pada tabel

Aksi pada tabel

Pada SQL Worksheet, saya dapat memberikan query SQL ataupun mengerjakan PL/SQL, seperti yang diperlihatkan oleh gambar berikut ini:

Mengerjakan PL/SQL dari worksheet

Mengerjakan PL/SQL dari worksheet