Chapbook

Detecting and killing duplicate queries

November 12, 2020 RLByrd Snippets, Technology, MySQL snippets

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;