Thanks for the monitor upload. It looks like the Query Monitor plugin is active? Is that correct? You may want to deactivate it for production.
Try adding the high-performance keys to posts as well as postmeta. Lots of queries join those two tables, and the high-performance keys usually work well together.
Does your 100% cpu usage go back down if you don’t use the high-performance keys?
Your monitor didn’t show any absurdly slow queries. It was pretty much ordinary stuff one would expect from a site with an active persistent object cache. I may be able to tell more if you run a monitor for a longer time. Also, please upload your server metadata (from the plugin’s About tab). You can use the same upload id as before.
Some things I see on your uploaded monitor.
You’re running WordPress 6.1. Upgrade! 6.1.1 was a security release, and it also contained a few significant performance increases. 6.2, coming soon, has even more performance increases.
Considering you have 3.8 million postmeta rows and 60 thousand posts rows, it seems likely you have a lot of “orphaned” rows in your postmeta (rows that were left behind when a post was deleted. Younes JFR’s Advanced Database Cleaner plugin has an option to clean orphaned postmeta rows. Give it a try. (Make a backup first, of course.) After you
It looks like your MariaDb server’s buffer pool cache size is 256MiB, with 230MiB in use. That’s “budget”-level service provisioning. If there’s any way to give MariaDB more RAM, that may help.
