MySQL gets slow before optimizing table

Question

We have a custom made webshop built on the Laravel PHP framework which processes, on a busy day, 7000 orders a day. Since all orders are sent to a third-party point of sale system via an API, the retention of the orders table is 30 days. Old orders are deleted every day.

Table relations with volume

Table relations with volume

Number of orders per hour of a busy day

Number of orders per hour

On a typical busy day like this one, there's a good chance something happens to the orders table which causes the MySQL query queue (SHOW PROCESSLIST) to build up and everything becomes really slow. MySQL CPU usage goes to 1400 % (it's a VPS). At these times, a couple of hundred queries are in the queue, waiting to be executed.

When I optimize the orders table (OPTIMIZE TABLE orders) which triggers a recreate + analyze, the queue quickly drains and within a couple of minutes, everything is back to normal.

What is the cause of this problem and how can we prevent this? Please let me know if there's additional information needed.

CentOS 7.9 (Linux), MySQL 8.0.33, InnoDB, utf8mb4_unicode_ci collation

Answer 1

the retention of the orders table is 30 days

Then I recommend PARTITION BY RANGE(..) to have daily partitions. This lets you very efficiently DROP PARTITION at, say, midnight. And, since it is not messing with the BTree in the way that DELETE does, there is no need for OPTIMIZE. More: Partition You probably need to Partition all three tables and do a DROP on each.

Also, REORGANIZE PARTITION future INTO tomorrow and a new, empty, future. as explained in the blog. (Do this just before midnight.)

CPU usage goes to 1400 %

This is likely to be curable by improving the INDEXes and/or query formulation. Discover the slowest queries (SlowLog) and let's work on them.

(If the high CPU usage goes away sufficiently after eliminating OPTIMIZE, then this step may not be needed. However, it would be wise to watch the slowlog for further growth.)

You should probably never run OPTIMIZE.

• Rogier: Thank you for your answer. The provided blog says Don't use PARTITION unless you will have >1M rows. What are your thoughts on this? Since our table will be around 72k rows.
• Rick James: 1M: It is not a strong rule. Just a reminder that Partitioning does not help much.