Mass killing MySQL connections

เมื่อเกิดเหตุการ MySQL มีจำนวน connection มากเกินไปจนทำให้เครื่องทำงานช้า หรือเกิดเหตุการ Table ใน database เกิดการ Lock ขึ้นมา จากเหตุการณ์เช่นนี้เราจำเป็นต้องทำให้ระบบกลับมาให้สามารถทำงานได้โดยเกิด down time น้อยที่สุด สำหรับผมแล้วการแก้ไขปัญหาดังกล่าวจึงเลือกที่จะ kill connections ที่ค้างอยู่ออกให้หมด โดยเลือกจะเลือกที่จะเลือก connection ที่เป็น Query และมีการทำงานที่นานกว่า Query อื่นๆ หรือมีสถานะเป็น Locked เป็นหลัก และหลีกเลี่ยง connections ที่เป็น Update และ Delete เพื่อป้องกันไม่ให้ข้อมูลสูญหาย

วิธีการ kill connections สามารถทำได้หลายวิธี และในบทความนี้จะพูดถึงการ kill connections แบบ Mass ซึ่งจะทำให้สามารถ kill connections ได้หลาย connections โดยรันคำสั่งเพียง 2-3 คำสั่งเท่านั้น ซึ่งมีขั้นตอนดังนี้

1. ตรวจสอบ Connection ต่างๆ เพื่อจะได้ระบุ connection ที่ต้องการ Kill ได้ถูกต้อง
รันคำสั่ง เพื่อตรวจสอบ connections ต่างๆ ว่ามี connection ไหนบ้างที่เราต้องการ Kill โดยใช้คำสั่ง select * from information_schema.processlist; หรือ show processlist;

ตัวอย่างคำสั่ง select * from information_schema.processlist;

mysql> select * from information_schema.processlist;
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
| 9839 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist |
| 9855 | root | localhost | DB1 | Query | 0 | | select * from Table2 |
| 9879 | root | localhost | DB1 | Query | 1000 | Locked | select * from Table1 |
| 162 | User1 | localhost | User1 | Query | 204 | Locked | select * from DBUSER1 |
| 373 | User1 | localhost | User1 | Query | 804 | | select tag from DBUSER1 |
| 672 | User1 | localhost | User1 | Query | 504 | | select ezylinux from DBUSER1 |
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
6 rows in set (0.00 sec)

ตัวอย่างคำสั่ง show processlist;

mysql> show processlist;
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
| Id | User | Host | Db | Command | Time | State | Info |
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
| 9839 | root | localhost | NULL | Query | 0 | executing | select * from information_schema.processlist |
| 9855 | root | localhost | DB1 | Query | 0 | | select * from Table2 |
| 9879 | root | localhost | DB1 | Query | 1000 | Locked | select * from Table1 |
| 162 | User1 | localhost | User1 | Query | 204 | Locked | select * from DBUSER1 |
| 373 | User1 | localhost | User1 | Query | 804 | | select tag from DBUSER1 |
| 672 | User1 | localhost | User1 | Query | 504 | | select ezylinux from DBUSER1 |
+------+--------+-----------+--------+---------+------+-----------+----------------------------------------------+
6 rows in set (0.00 sec)

2. เมื่อพบ Connection ที่ต้องการ Kill แล้วให้รันคำสั่งด้านล่างโดยใสเงื่อนไขที่ต้องการ (ตรง where)

mysql> select concat('KILL ',id,';') from information_schema.processlist where command='Locked' into outfile '/tmp/kill.txt';

เมื่อรันคำสั่งด้านบนแล้ว จะได้ข้อมูลทีเก็บไว้ที่ไฟล์ /tmp/kill.txt เป็น

KILL 9879;
KILL 162;

ซึ่งข้อมูลดังกล่าวจะเก็บเป็นแบบ text file ดังนั้นเมื่อต้องการให้รันคำสั่งที่อยู่ใน text file เราจะใช้คำสั่ง source เข้ามาช่วย

mysql> source /tmp/kill.txt;

ตัวอย่าง การ Kill connections ที่เป็น Query
ต้องการ kill connections ที่เป็น Query ให้รันคำสั่ง

mysql> select concat('KILL ',id,';') from information_schema.processlist where command='Query' into outfile '/tmp/querykill.txt';

จากนั้นให้รันคำสั่ง source /tmp/querykill.txt;

mysql> source /tmp/querykill.txt;

ตัวอย่าง การ Kill connections ที่มากจาก User1

รันคำสั่ง

mysql> select concat('KILL ',id,';') from information_schema.processlist where user='User1' into outfile '/tmp/kill.txt';

จากนั้นให้รันคำสั่ง

mysql> source /tmp/kill.txt;