i have a couple of Wordpress website running on a VPS, each one of them has 1M+ posts. When adding new posts queries just hangs for a long time until the website crashes. I use XML-RPC to post to my websites via C# application. most of the times what i see when running show processlist on my PHPMyAdmin is

copying to a temp table

and some query or

sending data and the query is:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.post_date DESC LIMIT 0, 10

I really don't know how to fix this or why is this happening. is it the VPS?


Best to work with your host on this type of specific issue. In general, when you have slow queries, a few things may help:

  1. Optimize the database - check and see if any tables are bloated compared to others. You may be able to turn off revisions and autosaves, or reduce the number of allowed revisions, to help some. Plugins may also be creating huge tables so determining which tables are huge and finding out why is a good place to start. There are also database optimization plugins available - be sure to back everything up fully, but they can sometimes drastically reduce the db size and give you quite a speed boost.

  2. Optimize your theme - you can either try out a few different themes or use a query analysis plugin to see where the bottlenecks in your theme are. While this doesn't affect wp-admin directly, if you have heavy traffic and your front end is being bogged down by visitors, this will affect the performance of your whole site, back end included.

  3. Enable better caching and/or better code - if you're frequently running uncached queries, it's a big hit to your database. Caching can make everything run a lot faster.

You might also want to look into the REST API as it may be more performant than XML-RPC.

  • First, thank you for your answer. second, did all the above and nothing helped. And by REST API do you mean to work with the Wordpress core functions wrapped with php server to get the info by GET/POST and post it through wp_insert_post() or there's an known API out there to work with? Feb 25 '18 at 5:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.