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?