I have a website running on Wordpress Multisite + Woocommerce with over 5700 users on a Nginx + Apache server. I recently experienced some down time but didn’t get any errors, just a blank page.
I contacted my hosting company (WP Engine) and they said the following:
your site is suffering from slow queries being run on tables with too many rows.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Name Rows wp_users 6046 wp_groups_user_group 6867 wp_rg_form_view 7657 wp_posts 12574 wp_rg_lead_detail 12925 wp_woocommerce_order_items 13510 wp_term_relationships 14581 wp_options 17169 wp_comments 57730 wp_commentmeta 65568 wp_usermeta 176243 wp_woocommerce_order_itemmeta 278636 wp_postmeta 491916 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The reason the errors are happening is because the queries on the above tables are not completing fast enough. This is due to excessive table rows. Here is an example query that is having the issue:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ # Time: 140219 13:00:28 # User@Host: Websitename @ localhost [127.0.0.1] # Thread_id: 3948879 Schema: wp_dbname Last_errno: 0 Killed: 0 # Query_time: 2.394413 Lock_time: 0.000171 Rows_sent: 1 Rows_examined: 510794 Rows_affected: 0 Rows_read: 510794 # Bytes_sent: 89 SET timestamp=1392814828; SELECT COUNT( order_items.order_item_id ) FROM wp_woocommerce_order_items as order_items LEFT JOIN wp_woocommerce_order_itemmeta AS itemmeta ON order_items.order_item_id = itemmeta.order_item_id LEFT JOIN wp_postmeta AS postmeta ON order_items.order_id = postmeta.post_id LEFT JOIN wp_term_relationships AS rel ON postmeta.post_id = rel.object_ID LEFT JOIN wp_term_taxonomy AS tax USING( term_taxonomy_id ) LEFT JOIN wp_terms AS term USING( term_id ) WHERE term.slug IN ('completed','processing','on-hold') AND tax.taxonomy = 'shop_order_status' AND ( ( itemmeta.meta_key = '_variation_id' AND itemmeta.meta_value = '1261' ) OR ( itemmeta.meta_key = '_product_id' AND itemmeta.meta_value = '1261' ) ) AND ( ( postmeta.meta_key = '_billing_email' AND postmeta.meta_value IN ( 'firstname.lastname@example.org' ) ) OR ( postmeta.meta_key = '_customer_user' AND postmeta.meta_value = '2936' AND postmeta.meta_value > 0 ) ) /* From [example.com/product/example_product/] in [example.com/wp-content/plugins/woocommerce/woocommerce-core-functions.php:1862] */; ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
And finally they suggested for me to work with my database/web developer to slim the database and this will speed the queries up and allow them to work properly.
As I am not database expert, I just wanted to ask here - is what WP Engine recommended to me reasonable? I ask because I know there are many websites running on much bigger databases that are larger than mine and they're working properly! That being said, Could it possibly be that WP Engine just has slow server processing or should I do what they recommended?