Flite Careers

Introducing Pt-reanimate

Most of the time I like pt-kill, but sometimes it gets a little overzealous and kills a query that I need. That’s why I’m glad Percona created pt-reanimate so I can bring those important queries back to life. Of course the queries are not exactly the same when they come back, but at least they come back.

Here’s an example of pt-reanimate in action. First I add a column to a large table:

1
alter table big_table add column new_col int;

Here is my DDL in the processlist table:

1
2
3
4
5
+---------+------+----------------+----------------------------------------------+
| command | time | state          | info                                         |
+---------+------+----------------+----------------------------------------------+
| Query   |  146 | altering table | alter table big_table add column new_col int |
+---------+------+----------------+----------------------------------------------+

Since I’m using MySQL 5.6 this is an online operation, and it does not block reads or writes on the table. Unfortunately pt-kill doesn’t know that, and due to the long runtime of my DDL pt-kill killed the query, and it disappeared from the processlist.

Luckily I am also running pt-reanimate, and it brought my query back to life! After pt-reanimate recovered my query, this is what it looked like in the processlist table:

1
2
3
4
5
+---------+------+--------+----------------------------------------------+
| command | time | state  | info                                         |
+---------+------+--------+----------------------------------------------+
| Zombie  |  187 | brains | alter table big_table add column new_col int |
+---------+------+--------+----------------------------------------------+

The good news is the query eventually completed successfully. The bad news is it tried to eat some of my other queries, but they were all fast-running queries so they escaped unharmed.


Comments