The setup
We are on a dedicated database and server. The site is hosted on Cloudways 16gb plan. There are just over 100,000 orders. Traffic is around 5,000 per day. There are around 30 plugins activated. These are all standard plugins from the WooCommerce store. Disabling all the plugins on our staging site (which is hosted on the same server) with just the core WooCommerce plugin enabled still suffers from 30s+ order search times.
Symptoms
Periodically the server has suffered from periods of high CPU usage. The points where the graph is lowest have meant duplicate orders and multiple payments being taken for orders being marked as "failed" and a customer services nightmare scenario over the busiest period in December:
The high CPU usage is accompanied by spikes in MySQL Database Connections at the same time:
On a good day, searching the database can take up to a full minute if not a server time-out. During the high CPU usage the site is inaccessible. I have installed Query Monitor and see the following:
I have spoken with WooCommerce support and they say 100,000 orders should be easily searchable. 30s-1m is far too long and something is not right here. They suspected that we are on a shared database causing speed issues. We have since confirmed with our host that we are on a dedicated database.
Since our hosting should be sufficient, and our WooCommerce setup is not out of the ordinary, what could possibly be causing such horrendous speed issues?