Membuat Kill MySQL Sleep Process secara Otomatis

Hallo semua, pernahkah kalian mengalami kondisi pada MySQL server dimana terdapat banyak sekali proses yang berstatus sleep ? Jika pernah, berarti kita sama 😆 . Beberapa hari yang lalu saya pernah mengalami kondisi tersebut yang membuat operasional aplikasi web menjadi terhambat. Penumpukan proses yang berstatus sleep dapat meningkatkan penggunaan CPU dan sumber daya memori lainnya, sehingga akan memperlambat performa server. Proses yang berstatus sleep biasanya terjadi ketika sesi mysql tidak di tutup dengan benar. Banyak kasus yang dapat memicu hal tersebut, kalau pada kasus di server Saya yang memicu terjadinya penumpukan proses yang berstatus sleep adalah saat dimana aplikasi / program gagal memproses data dari mysql, sehingga sesi mysqlnya tidak dapat di tutup dengan benar dan jadilah proses yang berstatus sleep. Jika terjadi hal seperti itu, kita perlu melakukan kill terhadap proses yang berstatus sleep tersebut. Jika tidak (secara default) proses yang berstatus sleep tersebut baru akan tertutup setelah 28800s atau 8 jam. Untuk mempermudah hal tersebut, saya membuat suatu script yang fungsinya melakukan kill mysql sleep process secara otomatis yang saya jadwalkan pada cronjob. Berikut cara membuatnya :

1. Buat file sql untuk men-generate query kill mysql sleep process

Pertama – tama, kita buat file sql untuk men-generate query untuk kill mysql sleep process, sebagai contoh saya beri nama create_kill_query.sql ( kamu bebas mau memberi nama apa ).

vim /usr/local/sbin/create_kill_query.sql

Untuk path nya bisa kamu sesuaikan mau di letakkan dimana, setelah itu isikan kode dibawah ini :

select concat('KILL ',id,';') as '# kill this'
from information_schema.processlist
where Command = 'Sleep' and Time > '30';

2. Buat file shellscript untuk kill mysql sleep process

Kedua, buatlah file shellscript, sebagai contoh saya beri nama kill_mysql_sleep_process.sh ( kamu bebas mau memberi nama apa ).

vim /usr/local/sbin/kill_mysql_sleep_process.sh

Untuk path nya bisa kamu sesuaikan mau di letakkan dimana, setelah itu isikan kode dibawah ini :

#!/bin/bash

USER="yourUser"
PASSWORD="yourPassword"
HOST="yourIpHost"

cat /dev/null > /usr/local/sbin/kill_query.sql &&
mysql -u $USER -p$PASSWORD -h $HOST < /usr/local/sbin/check_kill_query.sql > /usr/local/sbin/kill_query.sql &&
cat /usr/local/sbin/kill_sleep_query.sql > /var/log/mysql_kill_sleep_query.log &&
mysql -u $USER -p$PASSWORD -h $HOST < /usr/local/sbin/kill_query.sql &&
( echo "`date` === Sleep query has been killed" && echo "================================================") >> /var/log/mysql_kill_sleep_query.log 2>&1

Di dalam shellscript yang saya buat ini, saya mencatat prosesnya kedalam log yang saya letakan di /var/log/mysql_kill_sleep_query.log . kalian bisa melakukan test terlebih dahulu dengan menjalankan shellscript tersebut dengan  sh /usr/local/sbin/kill_mysql_sleep_process.sh. Jika sudah berhasil dan log terbentuk, kalian bisa lanjutkan langkah ke-3 untuk memasangkannya pada cronjob.

3. Membuat cronjob untuk menjalankan secara otomatis

Terakhir, kita tinggal atur pada cronjob untuk menjalankan secara otomatis sesuai waktu yang kita inginkan, disini saya contohkan untuk membuatnya berjalan secara otomatis setiap 30 menit sekali pada jam 08.00 – 17.00 setiap harinya. Pertama, kita buka editor cronjobnya

cronjob -e

lalu atur waktu untuk menjalankan shellscript seperti ini

*/30 08-17 * * * sh /usr/local/sbin/kill_mysql_sleep_process.sh

setelah itu simpan. Maka setiap 30 menit sekali pada jam 08.00 – 17.00 cronjob akan menjalankan shellscript untuk melakukan kill mysql sleep process secara otomatis. Untuk aturan pada cronjob lainnya, kamu bisa baca tulisan saya mengenai cronjob di Aturan pada CronJob.

Baiklah, itulah sedikit informasi yang dapat saya bagikan, semoga informasi ini dapat bermanfaat, Terima kasih 😀