Selasa, 30 Desember 2008

Konversi MySQL ke SQLite Sebagai Database Cadangan

Masalah yang terjadi di ketawa.com akhir-akhir ini adalah adanya overload koneksi ke database MySQL. Sebelumnya untuk meningkatkan performa koneksi ke server MySQL saya mengganti mysql_connect() menjadi mysql_pconnect(). Namun setelah beberapa lama, hal itu bukanlah solusi jangka panjang, karena ketika lebih dari 600 user terhubung ke server MySQL, masih ditambah kemungkinan besar koneksi oleh user lain di server yang sama --karena kami menggunakan shared hosting--, maka akan terjadi overload MySQL dengan kode error 1040 atau 1203.

Ada beberapa solusi yang bisa dilakukan:
  1. Pindah ke server dengan spesifikasi performa lebih tinggi, atau menyewa dedicated server.
  2. Mencari hosting MySQL remote, agar server ketawa.com bisa menggunakan database cadangan apabila sedang down.
  3. Membuat hosting duplikat di mesin lain, misalnya server2.ketawa.com apabila server utama overload.


Dengan masalah yang ada tersebut, saya mencoba membuat solusi 'miskin' agar website tetap dapat diakses, tanpa harus mengeluarkan biaya tambahan untuk menyewa server yang lebih besar atau menyewa server untuk hosting database MySQL. Solusi itu adalah dengan menggunakan database cadangan, yaitu SQLite yang fiturnya sudah disediakan oleh penyedia hosting kami. Yang menguntungkan adalah syntax MySQL dan SQLite mirip, sehingga tidak ada kesulitan dalam proses transisi query.

Saya memodifikasi script shell dan diberi nama sqlite.sh untuk konversi dari MySQL ke SQLite:

#!/bin/sh

u=namadatabasenya
p=passwordnya

if [ "x$1" == "x" ]; then
echo "Usage: $0 "
exit
fi

rm $1.db -f

/usr/bin/mysqldump -u $u -p$p --compact --compatible=ansi --default-character-set=binary $1 |
grep -v ' KEY "' | grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' | sed 's/ unsigned / /g' |
sed 's/ auto_increment/ primary key autoincrement/gi' |
sed 's/ smallint([0-9]*) / integer /gi' | sed 's/ tinyint([0-9]*) / integer /gi' |
sed 's/ int([0-9]*) / integer /gi' | sed 's/ character set [^ ]* / /gi' |
sed 's/ enum([^)]*) / varchar(255) /gi' | sed 's/ on update [^,]*//gi' |
perl -e 'local $/;$_=;s/,n)/n)/gs;print "begin;n";print;print "commit;n"' | perl -pe '
if (/^(INSERT.+?)(/) {
$a=$1;
s/\'''/''''/g;
s/\"/"/g;
s/\n/n/g;
s/\r/r/g;
s/),(/);n$a(/g;
}
' > $1.sql
cat $1.sql | sqlite3 $1.db > $1.err
ERRORS=`cat $1.err | wc -l`
if [ "$ERRORS" == "0" ]; then
echo "Conversion completed without error. Output file: $1.db"
rm $1.sql
rm $1.err
else
echo "There were errors during conversion.Please review $1.err and $1.sql for details."
fi


Lalu membuat cron di server agar setiap 8 jam SQLite direplikasi dari MySQL.


MAILTO=webmaster@ketawa.com
SHELL=/bin/bash
PATH=/usr/bin:/bin
0 */8 * * * /bin/sh /home/sqlite.sh namadatabasenya


Untuk script PHP koneksi ke database:

/* Koneksi ke database */
@mysql_pconnect(GI_DB_HOST, GI_DB_USER, GI_DB_PASSWD);

if (mysql_errno() == 1040 or mysql_errno() == 1203 or mysql_errno() == 2003) {
// Trigger SQLITE menjadi on, koneksi Menggunakan Script SQLite
} else {
// Menggunakan MySQL secara default
}


Kelemahan SQLite adalah apabila proses update terjadi sangat signifikan dengan hits yang besar, maka akan ada kemungkinan terjadi deadlock atau busy, sehingga di ketawa.com, server SQLite diaktifkan hanya untuk query "SELECT" saja, sambil menunggu server MySQL ngadem, karena biasanya server MySQL down hanya pada jam tertentu dan kurang dari 10 menit.

Lalu bagaimana kalau servernya yang down? Atau bagaimana kalau besok databasenya sudah sangat besar? Ya udah, besok edisi ini bersambung untuk cari solusi lainnya to... Atau bisa ngintip bocoran solusi 1,2,3 di atas.

7 komentar:

Ady Wicaksono mengatakan...

mas, sampeyan kecilin aja thread stack mysql dari default valuenya, nanti jumlah concurrent connection mysql akan lebih gede

preaxz mengatakan...

Hmm .. nice work.
Cobain ah.. thx, and salam kenal

Porfirio Lampron mengatakan...

This weblog post is basically out from the box. Numerous other posts i've seen on this subject but this may be the one particular i was searching for. I would like to subscribe to your feed for my streaming movies blog and also bookmarking this website now.

Esti mengatakan...

Saya sedang binggung nih nyari tuning buat optimas mysql. thanks infonya rupanya harus mencoba pconnect.

Yohanes Budi Lelono mengatakan...

Mas, dicoba beberapa cara:
- Pake mod_deflate
- Pake mod_gzip
- Pake CDN
- Pake cache plugin.

Kalo hostingnya mendukung cloudflare, saya sarankan agar di aktifkan. Karena sangat mendukung untuk mengurangi resources.
Cache juga sangat mengurangi jumlah query di mysql.

Semoga berguna.

Patra E37 mengatakan...

Good article mengenai database SQL

info mengatakan...

sepertinya programer nih,aku pingin banget pintar program desain web atau blog,tapi ilmunya jalan ditempat, aku belajar dari sumber internet, agan punya tutorial ga tentang data base sql.?

Posting Komentar