David’s Blog

Mysql IF_IDLE patch

Posted in Systems Engineering / Unix Systems Operations by david415 on October 7, 2008

I isolated Google’s IF_IDLE feature from SqlChanges; part of the Google V2 patch.
I diffed this IF_IDLE patch for mysql-5.0.68-percona-highperf but it’ll patch mysql-5.0.67 as well.

This simple and brilliant change allows us to
attempt to kill idle mysql processes while avoiding race conditions.

KILL IF_IDLE <id>

mysql> SHOW PROCESSLIST;
+----+-------------+-----------+-----------+---------+------+----------------------------------+
| Id | User        | Host      | db        | Command | Time | State                            | Info
+----+-------------+-----------+-----------+---------+------+----------------------------------+
|  2 | system user |           | NULL      | Connect | 2192 | Waiting for master to send event | NULL
| 13 | root        | localhost | NULL      | Query   |    0 | NULL                             | show processlist
| 30 | root        | localhost | NULL      | Sleep   |    2 |                                  | NULL
+----+-------------+-----------+-----------+---------+------+----------------------------------+
4 rows in set (0.00 sec)
mysql> KILL IF_IDLE 30;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW PROCESSLIST;
+----+-------------+-----------+-----------+---------+------+----------------------------------+
| Id | User        | Host      | db        | Command | Time | State                            | Info
+----+-------------+-----------+-----------+---------+------+----------------------------------+
|  2 | system user |           | NULL      | Connect | 2357 | Waiting for master to send event | NULL
| 13 | root        | localhost | NULL      | Query   |    0 | NULL                             | show processlist
+----+-------------+-----------+-----------+---------+------+----------------------------------+
3 rows in set (0.00 sec)

2 Responses

Subscribe to comments with RSS.

  1. Arjen Lentz said, on November 3, 2008 at 3:55 am

    Thanks for the contribution David, this patch (and innodb_freezev2) have just been added to the OurDelta d7 patchset.

  2. [...] hotcopy script utilizes LVM. Previously we didn’t use LVM and so this script used to do an Innodb Freeze. The simple idea here is restore a mysql shard replica’s data from another [...]


Leave a Reply