Hi,
I’m looking for some insights into why deletes from a fairly simple table seem to be much slower in PXC8 than in PXC5.7. The specific versions in play are PXC 8.0.29 and PXC 5.7.36 but I think the behaviour may be coming from underlying MySQL itself.
Apologies in advance if this is a little fluffy at this stage and I’ve anonymised the table/column names.
We have a table:
CREATE TABLE things
(
thing_id BIGINT AUTO_INCREMENT,
other_id BIGINT, -- parent id from another table
type INT,
timestamp BIGINT,
creator INT,
PRIMARY KEY (thing_id),
KEY (other_id,timestamp),
KEY (timestamp,type),
KEY (type,timestamp)
)
CHARACTER SET "UTF8MB3"
ENGINE = InnoDb;
…which accumulates a lot of rows (typically millions) and periodically we prune it down to remove old entries. It has a complicated relationship with ids in other tables so we can’t prune it based purely on timestamp unfortunately so everything has to be done by ids.
When we do the prune, a working table old_things is built to contain all of the ids of the things to be deleted:
CREATE TABLE old_things (thing_id BIGINT primary key);
…and then we delete in looping batches from the things table using a JOIN with old_things and a BETWEEN range of ids (calculated on each loop using max/min of a query with LIMIT - usually 1000 rows):
DELETE IGNORE th.* FROM things th JOIN old_things t USING (thing_id) WHERE t.thing_id BETWEEN from_thing_id AND to_thing_id;
We’re seeing, from slow query performance analysis, that this DELETE command above is taking typically 75% longer in PXC8 than it is in PXC5.7
For example, a run in which that DELETE query is performed ~33K times (deleting 1000 rows each time) results in 44s total execution time in PXC5.7 and 77s in PXC8.
I should add that there is no other load on the DB - this is a dedicated performance test.
I appreciate I haven’t talked about mysql configuration but we’ve done our best to give the two databases identical settings on identical hardware and we are not using binary logging/replication - this is a standalone PXC node.
Are there any obvious first ports of call to investigate?
I was wondering if it was something to do with the numerous compound indexes on that table - if I strip away the indexes on the things table and have only a single index (on other_id) then the ~33K execution time in PXC8 drops dramatically to ~10s.
To support this theory we have an accompanying table that has typically the same rowcount but with many more large columns but with only one index (on other_id) and deletes from that table using the same method above are actually showing as faster in PXC8 than PXC5.7 (51s vs 58s for 33K executions of that query with 1000 rows deleted each time).
Those numerous compound indexes were put in place to support various queries involving the “things” table but some may be legacy and up for review/removal.
Thank you in advance for any responses.
Neil