3

When performing a DELETE against a large number of rows in the Prices table, the DELETE becomes progressively slower. If deleting 15,000 rows it runs in about 15 seconds. 20K rows takes 3 or 4 minutes. 40,000 rows takes 15 minutes, 100,000 rows runs for well over an hour.

The below After DELETE trigger updates the Items table with a count of the available price types. This is used when calculating prices to speed up that process in production.

Both tables are InnoDB, and I have updated innodb_buffer_pool_size to 4G, with no effect.

I have verified that all SQL statements are using indexes. There is a PriceType + ItemID index on the Prices table, and ItemID is the primary key for the Items table.

Unfortunately this schema is determined by another application and I can't modify the table structure. I can modify the triggers, indexes, etc. That application cannot be changed to update the counts in the Items table directly.

BEGIN

    DECLARE iPriceTypeA INT;
    DECLARE iPriceTypeB INT;
    DECLARE iPriceTypeC INT;

    SET iPriceTypeA = (SELECT COUNT(*) FROM Prices WHERE PriceType='A' AND ItemID=OLD.ItemID),
        iPriceTypeB = (SELECT COUNT(*) FROM Prices WHERE PriceType='B' AND ItemID=OLD.ItemID),
        iPriceTypeC = (SELECT COUNT(*) FROM Prices WHERE PriceType='C' AND ItemID=OLD.ItemID);

    UPDATE Items 
        SET PriceTypeA = iPriceTypeA,
            PriceTypeB = iPriceTypeB,
            PriceTypeC = iPriceTypeC,
        WHERE ItemID = OLD.ItemID;

END

This doesn't seem like it ought to create a problem, but when deleting 120K rows the server becomes unusable for a couple of hours. Why would this query become essentially exponentially slower with more rows?

Edit: updating with Prices table schema

ItemCode varchar(30)
PriceType char(1)
Method char(1)
Factor decimal(7,2)

Edit:

I am still mystified why this trigger causes the delete to become slower the greater the number of records. As I said if I remove the trigger call the delete is very quick. I would really like to get to the bottom of the performance issue.

TO get things working for now my solution was to switch to MyISAM since this table is only used for selects in our application.

  • Have you considered just using a view for that? – paparazzo Aug 17 '16 at 17:54
  • I would try something like `update items set (pricetypea , pricetypeb, prisetypec) =( select count(case when pricetype = 'A' then 1 end), count( case when...),... FROM prices where itemid = OLD.... – Lennart Aug 17 '16 at 21:22
  • Not sure on the view. I would want to avoid doing an outer join on the application side, so I'd want the view to have 4 columns: ItemCode, TypeACount, TypeBCount, TypeCCount. We use these counts to determine whether or not we need to run a calculation (which is a fairly expensive process) for that type or not. – Craig Jacobs Aug 17 '16 at 23:40
  • I can try the updated SQL, I'm not sure what the bottle neck is - the update or the counts. I suspect it's the update. – Craig Jacobs Aug 17 '16 at 23:44
  • Please provide the DELETE statement and SHOW CREATE TABLE. Are you deleting by date? Or by something else? – Rick James Aug 18 '16 at 1:28
5

With InnoDB, deleted rows must be held in case there is a crash or other reason to rollback the action. This is costly, and may get more costly with larger counts, as you are seeing.

Plan A: Delete in smaller chunks and COMMIT after each chunk. (Similarly, chunking should be applied to Updates.)

Plan B, C, D, ... See more tips

If no WHERE

No WHERE? It would be much better to create a new table, then use RENAME TABLE to atomically swap the new one into place. And finish with DROP TABLE. No down time; no costly delete.

Query rewrite

If you are running 5.7, there is a new feature there which lets you "rewrite" queries to accommodate various kinds of nasties. More discussion. You could probably turn a delete without a where into DROP and CREATE. (This is not quite as good as the previous suggestion.)

  • Yes, I have noted this behavior before. I think the problem is in the update in the trigger. When I remove the trigger the delete only takes a few seconds. I have given innobd 8 GB to play with. – Craig Jacobs Aug 18 '16 at 14:29
  • Sounds like a good reason to move the trigger code into application code and get rid of the trigger. – Rick James Aug 18 '16 at 14:39
  • Unfortunately I don't have access to that application, as that would be my first choice. – Craig Jacobs Aug 18 '16 at 18:35
  • Do you have any control over the number of rows being deleted at once? And what does the DELETE look like? – Rick James Aug 18 '16 at 20:13
  • No - the delete comes over the wire from the other application. It's a delete without a where. Basically when they do a massive price update in the system, they clear out the table and reload it. When I do the deletes from this end I can delete in chunks. – Craig Jacobs Aug 19 '16 at 0:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.