[Solved] MySQL CPU Usage Surge up When Multiple Post Editor Pages are open

Good Muyis Asks: MySQL CPU Usage Surge up When Multiple Post Editor Pages are open
I am managing WordPress server 2Core CPU 8Gb Ram, hosted on Openlitespeed + MariaDB 11.4, PHP 7.4. Redis and OPache are installed. On the WordPress site, Redis Object Cache and Litespeed LSCache are plugins installed and enabled.

Memory Limit: 512MB, Max Execution time: 120

The site has over 140k posts, browsing the site pages and content is a smooth experience.

But the problem is whenever the editor login to create a post, the CPU usage surge to 90% or more. and the wp-admin/post-new.php become as slows as a snail, sometimes I could take more than minutes before it gets ready, sometimes crashed that publish button will be greyed-out. It gets worse when they open multiple instances of wp-admin/post-new.php.


enter image description here

I have tried to disable all plugins, change the theme with the plugin disabled, it doesn’t fix.

It only gets a little bit better if I disable Classic Editor, using the default Gutenberg editor.

My Test and Findings​

When I install Query Monitor I discovered that a query is running slow taking up to 25 seconds to complete, on other tabs or instances of wp-admin/post-new.php it could read up to 100 seconds, some case makes the site stop responding.

Code:
SELECT DISTINCT meta_key 
FROM wp_postmeta
WHERE meta_key NOT BETWEEN '_'
AND '_z'
HAVING meta_key NOT LIKE '\_%'
ORDER BY meta_key LIMIT 30


enter image description here

I thought Installing Redis on the VPS and the Redis Cache Object would give a better performance. This could be true as other parts of the website are fast loading except for the post editor page where the query above is running.

Presently, I have disabled the classic editor, but the user (editors) preferred it. Please what do I need to do more to optimise the site?

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.