8 Hal yang Harus Diperhatikan Tentang Logging ke MySQL- 1. Database atau filesystem?
- 2. MySQL?
- 3. Database Sama/Berbeda?
- 4. Tipe Data
- 5.
Indeks
- 6.
Denormalisasi
- 7.
Konkurensi/Locking
- 8.
Rotasi
- Penutup
Hong
Kong STock exchange
recently has a incident that stopped their trading system
for a hour because 2 records have the same key values. I don't know whether they use GUID to
generate keys but it makes me worried that even large organization have these problems.—A
comment on news://borland.public.delphi.oleautomation.
Bayangkan situasi berikut. Anda sudah membuat dan memasang aplikasi web untuk klien. Aplikasi telah berjalan selama beberapa minggu. User-user telah dibuat, login, membuat dan mengupdate data mereka.
Lalu suatu hari klien Anda bertanya, “Berapa user yang login
dan logout setiap harinya?” Anda menjawab, “Maaf,
tidak tahu.” Klien Anda bertanya lagi, “Saya curiga dengan user X.
Bagaimana cara saya memantau kapan si user login dan
apa saja yang dikerjakannya saat login?” Anda menjawab,
“Maaf, tidak bisa.” Lalu keesokan harinya terjadilah
bencana. Semua data user hilang. Kemungkinan ada user yang berhasil meng-crack aplikasi Anda dan
memperoleh akses administrator aplikasi lalu menghapus data-data yang ada.
Anda perlu melacak siapa, dari mana, dan bagaimana ini
terjadi. Sambil meringis Anda berkata pada diri sendiri, “Maaf, tidak
ada log sama sekali.”
Praktis semua aplikasi multiuser, dan
nyaris semua aplikasi yang sudah cukup kompleks, membutuhkan logging. Logging adalah catatan apa-apa saja yang terjadi selama aplikasi
berjalan. Jadi, di sepanjang jalur eksekusinya aplikasi Anda
meninggalkan jejak-jejak yang bisa amat membantu dalam berbagai hal: 1) untuk
security records dan auditing (siapa
saja yang melakukan apa saja dan kapan); 2) untuk debugging (mengapa program kita menghasilkan output yang salah,
kapan dan di bagian mana kira-kira penyebabnya); 3) untuk lebih mengerti user (bagaimana jalur masuk dan keluar si user,
halaman-halaman mana saja yang diakses, kapan user paling banyak mengakses dan
ke mana, kesalahan-kesalahan apa yang sering dilakukan user, dsb); 4) dll.
Dengan kata lain, logging itu amat sangat penting. Tidak
terbayang kalau program-program seperti daemon (Apache, FTP server, dsb) atau
bahkan OS sendiri tidak memiliki fasilitas logging.
Logging bisa dilakukan dengan berbagai cara
dan ke berbagai medium. Yang paling sederhana adalah dengan
perintah-perintah print atau echo, baik ke layar ataupun ke file.
Logging pun bisa dilakukan ke tabel database atau ke daemon lain
di network (“dioper ke mesin lain”). Dan tersedia pula
framework-framework untuk melakukan logging secara fleksibel, seperti log4perl, log4php, log4py, dan Log4r (keempatnya terinspirasi dari
paket log4j yang amat popular di
komunitas Java). Framework-framework ini memungkinkan kita melakukan
logging sambil nantinya mengatur output logging ke mana saja, statement mana
saja yang masuk ke output (pengaturan “debug level” atau “verbosity”), dan
bagian program mana saja yang loggingnya masuk output.
Logging ke database adalah salah satu
praktik yang popular. Dan ini yang menjadi topik
bahasan kita kali ini.
1. Database atau filesystem?
Sebelum memutuskan melakukan logging ke database,
terlebih dahulu Anda perlu meyakinkan diri akan alasan-alasan
apa yang membuat database tepat sebagai medium target logging. Seperti kita
ketahui, di Unix praktik yang umum adalah melakukan
logging ke file (umumnya dalam format teks polos). Selain sederhana (tinggal fopen(“file”,”a”)
diikuti fputs() diikuti fclose()), tidak butuh komponen software tambahan (tidak ada mysqld atau
database server lain tidak masalah, toh semua OS mampu melakukan read/write ke
file), juga tidak ada overhead
operasi network atau parsing SQL, dsb.
Beberapa alasan utama orang melakukan logging ke server
database RDBMS adalah: 1) ingin bisa melakukan realtime analysis atau query
terhadap log; 2) server database memungkinkan logging ke mesin lain; 3) server
database memungkinkan sharing data log; 4) server database memungkinkan replikasi/load
balancing. Poin 1 adalah yang terpenting. Logging ke
tabel database memungkinkan analisis yang lebih mudah karena data log yang
dimasukkan sudah terstruktur (dalam bentuk row dan columns) dan database
menyediakan indeks dan bahasa SQL untuk menggali data ini. Poin 2, 3, dan 4
sebetulnya bisa juga dilakukan oleh filesystem (filesystem bisa dishare ke
network dan bisa diload balance dengan RAID misalnya) namun keberadaan server
database nampaknya seringkali dipandang lebih afdol dalam memanage data dalam
jumlah besar.
Jadi pertimbangkan dulu apakah Anda memang
membutuhkan fitur-fitur yang ditawarkan database sehingga merelakan kode
logging Anda lebih kompleks dan aplikasi Anda bergantung pada database.
Jika ya, mari lanjut.
2. MySQL?
Pertanyaan kedua adalah: apakah MySQL cocok sebagai
database target? Jawaban sebagian besar pembaca mungkin ya, karena selain MySQL
memang ringkas, tidak rewel/bertele-tele dalam instalasi, dan cukup cepat dalam
melakukan INSERT/SELECT, sebagian besar pembaca juga telah familiar dengan
produk yang satu ini. Tapi Anda mungkin ingin bereksperimen dengan database
lain.
Jika membutuhkan kecepatan INSERT yang
lebih tinggi misalnya, Anda bisa mencoba SQLite.
SQLite juga memiliki keuntungan yaitu ukurannya yang kecil,
mudah diembed ke dalam aplikasi (meskipun MySQL 4.x pun sekarang dapat
diembed), dan seluruh database dapat disimpan dalam satu file saja. Ada
database embedded lain seperti BerkeleyDB,
namun tidak menyediakan interface SQL.
Anda juga bisa mencoba PostgreSQL yang menawarkan tipe-tipe
data “antik” seperti alamat IP atau tipe-tipe data geometrik (titik, garis,
dsb) yang mungkin kelop dengan aplikasi-aplikasi Anda.
Dan terakhir, jika aplikasi Anda benar-benar berskala besar
dan tingkat logging amat tinggi (mis: ratusan-ribuan statement per detik) maka
database yang mungkin lebih cocok dalam kondisi clustering dan skalabel hingga
mesin-mesin berspek tinggi adalah database komersial seperti Oracle dan DB2.
3. Database Sama/Berbeda?
Rata-rata aplikasi hanya menggunakan
satu database saja. Database ini berisi data aplikasi
dan juga data logging (dalam tabel-tabel terpisah tentu saja). Tapi ada
kalanya logging ke database berbeda menjadi pilihan yang menarik dengan
alasan-alasan berikut: 1) Database logging dapat ditaruh di mesin yang lebih
secure/terfirewall (penting jika ingin melakukan logging untuk auditing); 2)
Database logging dapat melayani beberapa aplikasi sekaligus secara terpusat; 3)
Database logging dapat dituning secara terpisah (logging adalah operasi yang
mayoritasnya INSERT, sementara pola akses database untuk aplikasi mungkin
seimbang antara INSERT, UPDATE, DELETE, dan SELECT).
Namun perlu diperhatikan bahwa jika Anda menggunakan 2 database
berbeda, apa yang harus Anda lakukan seandainya salah
satu database sedang tidak available? Misalnya mesin logging
sedang mati atau terputus koneksinya. Karena logging
adalah operasi yang kritikal, maka jika database logging tidak bisa dipakai,
hentikan aplikasi dengan pesan kesalahan fatal.
4. Tipe Data
Pemilihan tipe data yang sekompak
mungkin menjadi penting karena logging berpotensi menghasilkan banyak sekali
record. Contohnya, dalam sebuah aplikasi saya melakukan logging header
Referer dan User-Agent klien, saya memilih menggunakan CHAR(80)
dan CHAR(40) saja untuk tujuan ini. Meskipun cukup banyak string yang terpotong
karena melebihi 80 dan 40 karakter, ini tak mengapa dalam kasus yang saya
hadapi dan saya memilih menghemat ruang space karena setiap minggunya bisa ada
jutaan record yang dilog.
Menjaga sebuah row agar tetap fixed length juga bisa jadi
cukup penting dalam menjaga kinerja, apalagi jika tabel database akan banyak di-SELECT/di-scan. Ini artinya, hindari VARCHAR() dan TEXT/BLOB dan gunakan CHAR(). Lebih jelasnya
mengenai hal ini bisa dilihat pada manual MySQL bagian 7.1.2.1 Static
(fixed-length) table characteristics.
Ada pelajaran lain yang saya petik dari pengalaman me-log ke database. Pertama, hati-hati jika memberi tipe data kolom waktu sebagai TIMESTAMP. Kolom TIMESTAMP akan
selalu diupdate oleh MySQL jika sebuah UPDATE
ke record tidak menyertakan nilai kolom itu secara spesifik. Meskipun
teorinya tabel logging hanya di-INSERT dan record yang sudah ada tidak pernah
dimodifikasi, kecelakaan bisa saja terjadi. Perhatikan jangan sampai
catatan waktu di tabel log Anda berubah semua gara-gara UPDATE Anda salah.
Dalam meng-update, jangan lupa melakukan seperti ini:
UPDATE tablename SET ...,kolomwaktu=kolomwaktu WHERE ...
Agar si kolomwaktu yang
bertipe TIMESTAMP tidak berubah.
5.
Indeks
Banyak
tabel logging struktur datanya seperti ini:
CREATE TABLE tbl_log (
time DATETIME NOT NULL, index(waktu),
ipaddress INT UNSIGNED NOT NULL, index(ipaddress),
userid INT UNSIGNED NOT NULL, index(userid),
taskname CHAR(20) NOT NULL, index(taskname)
);
Struktur seperti ini mirip dengan fact table, istilah yang sering dipakai di data warehousing.
Fact table adalah tabel yang memuat data namun field-fieldnya
adalah foreign key ke tabel lain (kecuali field yang mengandung data itu
sendiri, dalam kasus ini taskname).
Pada contoh di atas, userid
adalah foreign key untuk tbl_users
misalnya. Pada umumnya, untuk mempercepat logging, tidak semua field
berupa foreign key (mis: time).
Bentuk tabel seperti ini amat praktis dalam mengizinkan kita
menyortir/menyeleksi berdasarkan field-field foreign key (field-field
dimension) karena field-field ini terindeks. Pada contoh di atas, kita
dengan mudah dapat melakukan WHERE dan ORDER BY terhadap kolom-kolom time, ipaddress,
dan userid. Untuk
melihat username, useremail, dan field-field tambahan
lainnya, kita tinggal melakukan join dengan tabel-tabel dimensi ybs (tbl_users, dsb).
Kadangkala,
kita juga menginginkan full-text searching untuk kolom-kolom yang berisi teks:
CREATE TABLE tbl_log2 (
time DATETIME NOT NULL, index(waktu),
ipaddress INT UNSIGNED NOT NULL, index(ipaddress),
userid INT UNSIGNED NOT NULL, index(userid),
taskname CHAR(20) NOT NULL, index(taskname),
note VARCHAR(255) NOT NULL, fulltext(note)
);
6.
Denormalisasi
Dalam database relasional kita sering bicara normalisasi. Tapi dalam auditing kadangkala kita harus melakukan denormalisasi.
Apa itu denormalisasi? Yaitu mencatat data-data yang
tidak bergantung pada kolom lain, meskipun pada
akhirnya mengakibatkan banyak terjadi duplikasi data. Tujuannya adalah agar
semua informasi yang Anda perlukan harus terkandung di tabel log itu sendiri
dan tidak bergantung pada tabel lain.
Contoh, Anda ingin selalu mencatat alamat email user pada waktu
tertentu, karena alamat email user berubah-ubah. Maka,
kolom userid pada contoh
sebelumnya harus diganti dengan useremail.
Jika tetap userid, ini berarti
Anda harus melakukan JOIN untuk mengetahui
apa alamat email si user dengan id userid, dan tabel tbl_users hanya akan mencatat alamat email
terbaru user. Alamat emailnya mungkin tidak sama
dengan alamat email pada waktu kegiatan/perbuatan si user tercatat di log
audit. Atau bagaimana jika record user tersebut dihapus dari tbl_users (berhubung MySQL tidak menjaga
foreign key integrity) atau userid dengan id tersebu telah direcycle untuk user
lain? Maka catatan audit Anda akan berubah. Padahal log audit harus merupakan rekaman kejadian sewaktu terjadi.
Jika
Anda ingin melakukan analisis, umumnya pun tabel yang terdenormalisasi paling
cepat, karena tidak ada operasi JOIN
antartabel sama sekali, hanya INNER JOIN (jika memang perlu).
7.
Konkurensi/Locking
MySQL menyediakan beberapa table handler seperti MyISAM dan InnoDB.
Jika tingkat konkurensi aplikasi tinggi (mis: aplikasi web Anda bisa diakses
oleh puluhan klien pada waktu bersamaan) maka ada baiknya Anda mempertimbangkan
menggunakan InnoDB ketimbang MyISAM karena InnoDB menawarkan konkurensi yang
jauh lebih baik daripada MyISAM. Apalagi jika Anda melakukan UPDATE/DELETE
massal terhadap tabel (untuk tujuan rotasi misalnya). Dalam satu aplikasi yang
saya maintain, sebuah tabel log dapat berisi 1 hingga
3 juta row pada suatu saat. INSERT log secara umum masih cukup cepat meskipun
ada banyak klien konkuren, karena saya menggunakan INSERT DELAYED. Namun pada waktu rotasi sekali sehari (di mana saya melakukan
DELETE pada ratusan-ribuan row pada tabel tersebut) maka klien-klien lain
langsung terblokir aksesnya ke tabel tersebut. Selama
operasi DELETE yang makan waktu hingga bermenit-menit, klien pun bertumpuk
karena menunggu akses terhadap tabel log. Dengan multiversioning yang
dimiliki InnoDB ini bisa dihindari, karena sewaktu melakukan DELETE klien-klien
lain masih dapat membaca/tulis ke tabel yang sama.
Namun
yang perlu diperhatikan, tabel InnoDB jauh lebih lambat dari MyISAM dalam
operasi-operasi tertentu, seperti SELECT COUNT(*).
8.
Rotasi
Untuk menghindari penumpukan tanpa akhir, tabel database harus
dirotate. Ada berbagai cara yang bisa dilakukan. Pertama, berpindah-pindah tabel
(mis: Anda membuat tabel bulanan: tbl_log_Jan,
tbl_log_Feb, …)
dan me-log ke tabel bulan pada bulan ybs dan mengosongkan tabel 12 bulan yang
lalu (mis: di awal Jan 2004 Anda akan kembali ke tbl_log_Jan dan mengosongkan data Jan 2003). Kedua, melakukan DELETE row-row tua secara berkala (mudah
dilakukan, mengingat praktis semua tabel log memiliki kolom waktu). Namun, ingat isu konkurensi selama melakukan DELETE pada tabel yang
berukuran besar.
Anda
juga bisa menumpahkan data di tabel yang lama ke dalam file lalu
mengkompresinya. Data log lama kemungkinan besar tidak pernah di-SELECT lagi
sehingga bisa dibuang dari database. Namun, Anda tetap
menyimpannya dalam arsip jika suatu saat diperlukan.
Penutup
Akhir kata, happy logging.
[Last-Modified: Thu May 8 05:33:26 2003]
| 
