|MySQL scaling: query snipers|
I've been wanting a query sniper for a while for MySQL (some people seem to call them "query killers"). They're basically programs which inspect the MySQL "show processlist" command and decide to kill some processes if they violate specified rules. I have a prototype of one in python, and couldn't find any other implementations until tonight when I came across Querybane, which is what Wikipedia use to do exactly the same thing.
Now, I need to think further about if querybane does what I want, especially given I can't find the source code. Perhaps it's hidden in the media wiki code somewhere? The only code for a project named "servmon" (it's parent project) that I can find is four years old and in PHP.
(Oh, by the way, the Wikipedia MySQL docs are really interesting, for example this page has some nice practical advice about replication log corruption. I wish I had found this before now for two reasons: it's nice to see how other people do this stuff; and there is some stuff I can totally steal from here.)
So, does anyone know where I can find recent querybane code? Alternatively, what do other people use as a query sniper? Is it worth open sourcing mine?
Update: Apparently what I needed to do was sleep on it, and use Google Code Search. The querybane code appears to be at http://svn.wikimedia.org/svnroot/mediawiki/trunk/servmon/. In the end it's not going to work for me, as it seems to be really based around a world in which you use IRC to keep a track of the state of your servers, which isn't something I want to do.
posted at: 11:03 | path: /mysql | permanent link to this entry
This only works in 5.1, This is 100% server based the argument of the function sets the amount of time that a query must be in running status before it is killed off. There might be a better way to find the privileges of the executor but I've not yet worked through that. So far this works.
CREATE FUNCTION `kill_run_aways` ( runtime TINYINT UNSIGNED )
DECLARE done INT DEFAULT 0;
DECLARE connid INT UNSIGNED;
DECLARE cur1 CURSOR FOR SELECT ID FROM information_schema.PROCESSLIST where CONCAT("'", USER, "'@'", HOST, "'") in ( SELECT `USER_PRIVILEGES`.`GRANTEE` AS `GRANTEE` from `information_schema`.`USER_PRIVILEGES` where not(`USER_PRIVILEGES`.`GRANTEE` in (select GRANTEE from `information_schema`.`USER_PRIVILEGES` where `USER_PRIVILEGES`.`PRIVILEGE_TYPE` = 'SUPER' group by
`USER_PRIVILEGES`.`GRANTEE`))) AND COMMAND='Query' and TIME >= runtime;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
FETCH cur1 INTO connid;
IF NOT done THEN
UNTIL done END REPEAT;
CREATE EVENT IF NOT EXISTS `kill_run_aways`
ON SCHEDULE EVERY 2 SECOND
ON COMPLETION PRESERVE ENABLE
DO CALL kill_run_aways(2);
Oddly enough, we're (at Wikipedia) not running querybane/servmon for more than a year.
Which query killer? none! Simply optimizing slow tasks was the main job, not hiding them.
And we didn't have binlog corruptions either.