Subtle changes, bugs and feedback

Hmm... actually found it... this query:

--DeleteOldUpdates
WITH keep AS (
SELECT MAX(u.update_id) update_id
      ,u.for_profile_id
      ,u.parent_item_type_id
      ,u.parent_item_id
  FROM updates u
 WHERE u.update_type_id IN (1,4)
 GROUP BY u.for_profile_id
      ,u.parent_item_type_id
      ,u.parent_item_id
), lose AS (
    SELECT update_id
      FROM updates
     WHERE update_type_id IN (1,4)
       AND for_profile_id != 0
       AND parent_item_type_id != 0
       AND parent_item_id != 0
       AND update_id NOT IN (SELECT update_id FROM keep)
)
DELETE FROM updates
 WHERE update_id IN (SELECT * FROM lose);

Was scheduled to run every 10 minutes.

The updates table has got to 8.4m rows... the query took longer than 10 minutes.

Then the next one would lock, and it would start a degrading cycle where every 10 minutes a query would lock and queue and consume memory and a connection in the meantime.

Earlier is was using 128GB of RAM. Ooops.

Changed the cron to once per day, and pruned rows older than 1 year as the stats say no-one looks at them (or if they do, so few people that they are not visible in the samples I keep for metrics).