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

    ### Matthew Montgomery

    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.

    DELIMITER |
    CREATE FUNCTION `kill_run_aways` ( runtime TINYINT UNSIGNED )
    RETURNS INT
    LANGUAGE SQL
    NOT DETERMINISTIC
    BEGIN
    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;

    OPEN cur1;

    REPEAT
    FETCH cur1 INTO connid;
    IF NOT done THEN
    KILL connid;
    END IF;
    UNTIL done END REPEAT;
    CLOSE cur1;
    RETURN connid;
    END|
    DELIMITER ;

    CREATE EVENT IF NOT EXISTS `kill_run_aways`
    ON SCHEDULE EVERY 2 SECOND
    ON COMPLETION PRESERVE ENABLE
    DO CALL kill_run_aways(2);


    ### Domas Mituzas

    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.

    Add a comment to this post:

    Your name:

    Your email: Email me new comments on this post
      (Your email will not be published on this site, and will only be used to contact you directly with a reply to your comment if needed. Oh, and we'll use it to send you new comments on this post it you selected that checkbox.)


    Your website:

    Comments: