Detecting And Killing Duplicate Queries

Problem statement: A rogue application process is sending multiple copies of exactly the same query to the database so you end up with a ton of them

DBA charge: (a) write a query that checks for these, and (b) for my own purposes, a quick way to kill the duplicate offenders.

-- output just the thread IDs of the duplicates, keeping the oldest version intact
SELECT
  DISTINCT(p2.id)
FROM
  information_schema.processlist p1
  INNER JOIN information_schema.processlist p2
    ON p2.info = p1.info
    AND p2.id > p1.id
    AND p1.time > 300
    AND p1.command IS NOT NULL;
-- returns actual executable KILL statements for the duplicates, suitable
-- for a mysql -N -B command line
SELECT DISTINCT
  (CONCAT('KILL ', p2.id, ';')) AS killstatements
FROM
  information_schema.processlist p1
  INNER JOIN information_schema.processlist p2
    ON p2.info = p1.info
    AND p2.id > p1.id
    AND p1.time > 300
    AND p1.command IS NOT NULL;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: