Slower delete performance in PXC8 compared to PXC5.7?

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

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

1 Like
2

Hi @NeilBillett,
The first thing that jumped out at me is the use of UTF8MB3. I’ve seen several Percona engineers and support staff mention this character set as a point of performance degradation. Is there any way you can convert to utf8mb4 and re-test?

It is well-known in the MySQL community that single-thread performance in MySQL 8 is worse than 5.7. MySQL 8 focused on improving performance for “the big guys” (ie: 64+ threads_running) at a cost of worsening the little guys.

Each time you delete a row, you also have to fetch and delete the pages for each index. It makes sense that having only 1 index is faster to delete as there is literally less data to remove.

Have you tried using the new PXC8 / Galera 4 streaming replication?
https://galeracluster.com/library/documentation/streaming-replication.html

The above question is more along the lines of “is this a galera issue or ‘mysql’ issue”.

1 Like
3

As always - thank you Matthew for the quick response.

I will certainly try the test with utf8mb4 and report back.

Haven’t tried the new replication as yet but will also have a look at that.

many thanks,

Neil

1 Like