2

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 ( '[email protected]' ) ) 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?

Thanks

Improve this question
3
  • You should see my post : wordpress.stackexchange.com/questions/90272/… Commented Feb 19, 2014 at 18:05
  • You could add indexes on itemmeta (meta_key, meta_value, order_item_id) and postmeta (meta_key, meta_value, post_id) Commented Feb 19, 2014 at 22:38
  • run an analysis on the query execution and post it. Then we can see how the database is actually processing the request and where specifically the slowness is happening. Commented Feb 19, 2014 at 22:43

1 Answer 1

Reset to default
2
  • If you are using MySQL v5.6 because Performance_Schema has lot of metadata about your query/table and how the processing of a query happened (almost everything that is helpful for you to debug a problem/query internals)

You can have a look at my existing answer here: Is there a way to get slow-query-log-like profiling information on a single query?

  • Otherwise, You can try Show Profiling functionality of MySQL to find out where most of the time has been taken by a query execution and optimize based on result:

    set profiling=1;

    Run your slow query (eg SELECT * FROM table WHERE name='xxx';

    SHOW PROFILES;

http://dev.mysql.com/doc/refman/5.0/en/show-profile.html

Analyze EXPLAIN EXTENED output and optimize query accordingly and profile the query again to see gain in performance.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.