7 Too Many Connections Error Posted on : 12/09/2008 01:17:00 AM

Sejak MySQL 5 mulai diperkenalkan tahun 2005 lalu, masih banyak menyimpan sejarah traumatis bagi server-server yang menggunakan engine tersebut. Spesifikasi yang makin serius mengarah ke real DBMS Engine, berangsur-angsur membawa MySQL lebih ke penggunaan enterprise. Ngga sedikit web-web server yang harus melakukan upgrade perangkat keras utk mengoptimalkan MySQL -- walau masih jauh lebih ekonomis daripada standard minimum system requirements utk server kelas berat macam MS SQL Server ataupun Oracle.

Salah satu issue besar yang acapkali menimpa server yg menggunakan MySQL, adalah berhubungan dengan error Too Many Connections. Setidaknya, apa yg gw omongkan di sini bukan hoax -- tanyakanlah itu pada admin Kaskus.us, admin MWN, admin diskusiweb, admin ekomit (hehehe, sumpah bukan gw) -- error Too Many Connection itu bener-bener menyebalkan. SANGAT-SANGAT-SANGAT-SANGAT-MENYEBALKAN.

Sebenarnya, apa sih error Too Many Connections itu? Dan kenapa bisa (sering bangedd) terjadi. Artikel gw kali ini akan mencoba membahasnya.

Too Many Connections itu terjadi karena beberapa habit, seperti :

1. terlalu banyak koneksi yang terjadi secara simultan --> cukup sering dijumpai oleh site-site yang emang ramai pengunjung.
2. terjadi flooding paket cacat ke port yg dipakae MySQL --> ini bisa di-test dengan melakukan telnet ke 3306, coba aja lakukan beberapa kali : insyaAllah MySQL Server langsung down, ato bisa juga karena terjadi kegagalan koneksi berkali2.
3. terlalu banyak koneksi yang udah obsolete, tapi belum di-release / di-free-kan.

Utk habit pertama, yang terjadi karena emang karena kondisi (atau resiko?) yang memaksa terjadi koneksi simultan. Popularitas emang gag bisa di-hindari. Klo emang 'takdirnya' emang udah saatnya ntu web jadi populer, ya udahlah... -- cara ngatasin Too Many Connections model gini adalah melakukan tweak pada mysql configuration file (my.ini atau my.cnf) dan ningkatin value dari variabel max_connections. Default-nya, ntu variabel nilainya 100. Untuk website yg rame dikunjungin, mungkin kurang settingan segitu. Atau, klo penggunaan MySQL utk keperluan aplikasi desktop, hitung aja jumlah client yg ngakses -- ya minimal kira2 segitu lah.

Utk habit kedua, terdapat banyaknya paket cacat di pool MySQL, cara nanganinnya adalah dengan melakukan flush menggunakan tools mysqladmin. bikin batch-script yang isinya mysqladmin flush-hosts -- trus pasang sebagai handler di Web Server atau di program desktop (sesuaikan dengan environmentnya) kalo2 menemukan error di MySQL engine dengan kode error SQLSTATE = 08S01 (utk server error yg berhubungan dengan bad packet) atau 46000 (utk server error yg berhubungan dengan koneksi), atau ndeteksi dari kode error ODBC -2147467259 (ni value yg gw trap dari error ADODB makae MyODBC).

Utk habit ketiga, terlalu banyak koneksi yg udah obsolete, tapi belum di-release atau di free-kan -- ini adalah masalah budaya coding juga sih. Code yang jorok, akan meninggalkan banyak junk di cache server. Bagi para coder, mestinya *sadar dikit* tentang cara melakukan koneksi ke DBMS, apakah dengan cara persistent-connection atau non-persistent. Klo cara yang dipakae adalah persistent-connection (kelebihan klo makae teknik ini adalah *memungkinkan utk melakukan transaction-safe queries*) -- perhatikan benar di masalah threading (silakan liat artikel ini utk referensi : http://forums.devshed.com/php-development-5/mysql-pconnect-22495.html). 

 

Persistent connection merupakan cara koneksi yang *ngga akan diputus ama server* walaupun kita meng-eksekusi function close macam mysql_close() -- ketika ada koneksi baru terjalin, dia akan nge-cek apakah ada existing persistent connection yg menggunakan parameter koneksi yg sama (sama user, sama pwd, sama host, sama database). Klo ditemukan existing persistent connection, code akan makae koneksi itu. Kelemahannya, ya kembali ke settingan max_connections -- klo udah mencapai limit, koneksi baru dari client baru ngga akan terbentuk.

Utk me-manage dari sisi administrasi server mengenai persistent connection ini, ada 2 variabel server yg perlu diperhatikan.
1. max_connections --> seperti pembahasan pada habit pertama;
2. wait_timeout --> MySQL mengendalikan "umur" sebuah persistent connection melalui variabel ini, dimana nilai default-nya adalah 8 jam. Silakan ubah nilai variabel ini sesuai dengan server-load yg terjadi dalam produksi.

Sedangkan utk handle non-persistent connection, dimana ini adalah teknik umum yg dipakai (klo di PHP, makae mysql_connect() atau liat library yg disediakan oleh connector yg dipakae). Untuk kebersihan code, *always-selalu-musti* lakukan closing terhadap object koneksi klo proses querying selesai. lakukan mysql_close() segera ketika dibutuhkan.

Kekurangan mysql_connect() ini ketimbang mysql_pconnect() dalam persistent connection adalah ngga ada mekanisme utk memakai "existing non-persistent connection" -- jadi, bisa saja dalam satu modul aplikasi yg diakses oleh satu client menjalin 2-3-4-atau lebih koneksi, which is : ini pemborosan jumlah koneksi!

Be aware dengan non-persistent-connection! Kerapian code Anda menentukan kualitas aplikasi Anda sendiri!

Emm...,

Tapi yang namanya programmer itu juga manusia, punya rasa punya hati, -- juga punya khilaf. Kalo *seandainya* koneksi-koneksi non-persistent tersebut di-create asal dan ngga di-close, kan bakal banyak juga tuh koneksi nyangkut. MySQL mengantisipasi masalah ini dengan juga memberikan "umur" koneksi (sama seperti penanganan persistent-connection di atas), hanya saja, variabel server yang dipergunakan adalah interactive_timeout.

Anehnya, settingan default interactive_timeout ini JUGA 8 jam! sama seperti settingan default wait_timeout. Padahal koneksi non-persistent itu ditujukan utk keperluan transaksi query jangka pendek. Sangat-sangat-sangat-pendek, malah. Buka koneksi, kirim query, tunggu bentar, dapat hasil resultset, tutup. Bener-bener singkat, bukan?

Nah, saran gw, ubahlah interactive_timeout ini menjadi 3-5 menit lah (hehehe, jauh banged yak dari settingan standard 8 jam). Ini utk 'an-ti-si-sapi' bro... -- ada banyak sebab kenapa query bisa nyangkut n ngga ketutup walo pakae mysql_close() - terutama pada fase development - misal karena terjadi kegagalan atau kesalahan dalam melakukan query join, ato dapat error "MySQL Server Has Gone Away", atau cursor melakukan infinite loops, atau ... macam-macam dah. Dengan meminimalisasi interactive_timeout ke nilai waktu yang lebih representatif, kemungkinan terjadi error Too Many Connections bisa di-eliminir.

--

Apa yang paling menyebalkan klo kita dapetin error Too Many Connections?

Restart Server!! cuman itu solusinya.

*males bangedd deh...

_______________________


Rizky Prihanto
Software Architect PT Cinox Media Insani

1 Membandingkan Stored Procedure Antar 2 Database di MySQL Posted on : 12/04/2008 10:53:00 PM

Pernah coding secara massive di pemrograman internal DBMS makae stored procedures/functions?

Bekerja dalam tim?

Punya beberapa database yang dipakae dalam setiap fase development? misal :

• db_ini_dev : dikonsumsi oleh para coder-coder cakep kita
• db_ini_test : dikonsumsi oleh para tester-tester cantik kita
• db_ini : dikonsumsi ama client-client baik kita

Atau pernah bekerja dalam tim yang tersebar di beberapa tempat yang terpisah, dimana masing-masing punya local database untuk kegiatan coding masing-masing?

• db_ini_dev_bandung : dikonsumsi oleh para coder-coder cakep kita di Buah Batu Regency
• db_ini_dev_solok : dikonsumsi oleh para coder-coder tampan kita di Perumnas Koto Baru Solok
• db_ini_test : di-host di sebuah server public dengan spesifikasi medium di sebuah gedung di Kuningan, Jakarta
• db_ini : di host di server public yang sama dengan db_ini_test

Ketika small-release udah digulirkan, mau-ngga-mau setiap perubahan struktur database (tabel/routines/trigger/view) harus di-broadcast ke database-database kita tersebut.

Puyeng meng-analisis diferensial?

Silakan coba trik gw untuk membandingkan stored procedures antara dua database di bawah ini. (Owya, gw pakae MySQL 5 -- dan trik ini hanya bisa jalan di MySQL 5 ke atas)

Ini script gw untuk "mendeteksi" apakah ada SP yang beda definisi (atau SP baru) dari dua database yang identik. Silakan donlot di sini : http://qvezst.googlepages.com/xp_routines_compare.zip

Pertama-tama yang harus dilakukan adalah dengan meng-execute ntu script ke database mysql. Yupe! Database mysql (ini merupakan database yang 'pasti ada' di setiap engine MySQL Server -- yang isinya adalah informasi user, informasi schema, de-el-el). Klo sukses ke-attach, ntar di database mysql loe bakal nambah 2 stored procedure yang namanya xp_execute dan xp_routines_compare.

xp_execute adalah routine yg gw bikin untuk memudahkan pemanggilan PREPARED STATEMENTS di dalam stored routines, daripada tiap kali harus nulis PREPARE ... EXECUTE ... DEALLOCATE -- mending tinggal panggil call xp_execute("select blablabla");

dan xp_routines_compare, adalah inti dari artikel ini -- memiliki dua parameter input yaitu SOURCE DATABASE NAME dan TARGET DATABASE NAME. Untuk meng-compare dua database, loe masukin aja nama-nama database yg mo loe bandingin tersebut dalam format string (alias loe kasi kutip satu ato kutip dua). Kalo bingung, coba loe liat sampel pemanggilan ntu procedure di bawah ini :


mysql>call mysql.xp_routines_compare('ekomit_dbrisma','ekomit_dbrisma_test');

+------------------------------------+--------------------------------------+

| routine_yg_berbeda                 | letak_perbedaan                      |

+------------------------------------+--------------------------------------+

| sf_psb_get_nama_sekolah            | routines baru di db : ekomit_dbrisma |

| sf_report_get_count                | routines baru di db : ekomit_dbrisma |

| sf_statistik_getcount              | routines baru di db : ekomit_dbrisma |

| sp_disdik_kategori_laporan         | routines baru di db : ekomit_dbrisma |

| sp_disdik_lihat_laporan            | routines baru di db : ekomit_dbrisma |

| sp_psb_cluster_daftar_sekolah_save | PARAMETER BODY                       |

| sp_psb_sekolah_luar_cluster_combo  | BODY                                 |

| sp_psb_sekolah_prs_view            | PARAMETER BODY                       |

| sp_psb_sekolah_prs_view1record     | routines baru di db : ekomit_dbrisma |

| sp_stats_agama                     | routines baru di db : ekomit_dbrisma |

| sp_thnajaran_kini_dan_sebelumnya   | routines baru di db : ekomit_dbrisma |

| xf_properdate                      | BODY                                 |

+------------------------------------+--------------------------------------+

12 rows in set

Yak! akan terlihat di mana letak perbedaan routines di dalam dua database tersebut yang akan terdeteksi beda dari ntu routines ada di PARAMETER, di BODY, atau beda di RETURN (khusus stored functions). Dan klo ada routine baru, akan langsung di-kasih-tau ntu routines baru ada di database mana..

Gimana-gimana? Keren kan?

Oh iya, -- mencoba obyektif -- gw juga pengen beritaukan LIMITASI dalam pengimplementasian xp_routines_compare() gw ini, yaitu :

• xp ini hanya bisa di call ama user yang punya GRANT PRIVILEGES sekelas root -- atau minim punya privileges SELECT ke database mysql.
• ngga bs nge-compare dari 2 databases yang BERBEDA ENGINE -- karena database yang beda engine artinya beda database mysql nya.. Ntar lah kapan-kapan gw coba bikin versi cross-engine-nya (manfaatin fitur mysql replikasi, tentunya) -- tp gw gag janji dalam waktu dekat. Maklum, lagi banyak Pe-eR yg musti digarap nieh. hihihi...
  

Tunggu kelanjutan kisah ini...

_______________________


Rizky Prihanto
Software Architect PT Cinox Media Insani