Slow Query on large WooCommerce site

[Nick Pelton]

We have a client on WP-Engine who runs a large WooCommerce site. It's currently weighing in at 20,000+ product posts and they recently hit some performance issues. They bumped up to WP-Engine Dedicated, but are still seeing painfully slow page loads on the product pages. Surprisingly WP-Engine hasn't been much help and are pointing at WooCommerce. My guess is since e-commerce can't leverage their caching (it needs to be off for all the dynamic stuff going on), they don't have a solution. 

I've narrowed it down to a few slow queries below. Some googling hasn't really turned up anything. Wondering if anyone has any insight:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 

FROM wp_posts 

INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 

INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)

INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 

WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (980) ) 

AND wp_posts.post_type = 'product' 

AND (wp_posts.post_status = 'publish' 

OR wp_posts.post_status = 'private') 

AND ( (wp_postmeta.meta_key = '_visibility' 

AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog'))

AND (mt1.meta_key = '_stock_status' 

AND CAST(mt1.meta_value AS CHAR) = 'instock') ) 

GROUP BY wp_posts.ID 

ORDER BY 1 

DESC 

LIMIT 0, 100 

/* From [/index.php:17] */

require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts #154 (20,429.9ms)

SELECT wp_posts.ID FROM wp_posts 

INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 

INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)

INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 

WHERE 1=1 

AND ( wp_term_relationships.term_taxonomy_id IN (980) ) 

AND wp_posts.post_type = 'product' 

AND (wp_posts.post_status = 'publish') 

AND ( (wp_postmeta.meta_key = '_visibility' 

AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog'))

AND (mt1.meta_key = '_stock_status' 

AND CAST(mt1.meta_value AS CHAR) = 'instock') ) 

GROUP BY wp_posts.ID ORDER BY wp_posts.post_date 

DESC 

/* From [/wp-content/plugins/woocommerce/classes/class-wc-query.php:337] */

require('wp-blog-header.php'), wp, WP->main, do_action_ref_array, call_user_func_array, WC_Query->get_products_in_view, get_posts, WP_Query->query, WP_Query->get_posts #165 (14,645.5ms)

Thanks,

Nick

[Chris Dohman]

hi nick, 

i'm curious. did the performance issues grow gradually over time, or did the issue all of sudden occur? did it by chance coincide with a recent woocommerce update?

chris

[Nick Pelton]

Hey Chris,

We didn’t build the original site or know exactly how long this issue has persisted. We're were simply called in to dig deeper and identify the underlying issues (which are definitely the large and slow queries). I'll inquire on their recent maintenance path and see if there have been any recent updates that could be related - it definitely could be related to a recent plugin update.

Thanks,

Nick

--
You received this message because you are subscribed to the Google Groups "Minneapolis St. Paul WordPress User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mpls-stpaul-word...@googlegroups.com.
To post to this group, send email to mpls-stpau...@googlegroups.com.
Visit this group at http://groups.google.com/group/mpls-stpaul-wordpress.
For more options, visit https://groups.google.com/groups/opt_out.

[David Paul Ellenwood]

In both instances it looks like WooCommerce is requesting the total number of products found (query #1) or the product ID for the products found (query #2) for all visible, in stock products that also have a specific term applied.  In query #1, it does limit the result to 100.  The second query doesn't apply any kind of limit.  It'd be good to know what taxonomy & term apply to the term_taxonomy_id #980 since this is the only real limiter on the query.  I'm guessing that this is a product category, but that's only an educated guess.

Have you tried running these queries directly on your DB at WP Engine in PHPMyAdmin?  That would help you see how many results are found for these queries and you can also "Explain" the query such that you can see what each part of the query returns.  

I'd be curious to know what aspects of WooCommerce are incompatible with caching.  I have several customers using WooCommerce on WP Engine and I've still cached the heck out of the sites (.  Granted the sites I'm managing have no where near this large an inventory, but that should really matter.

Have you tried enabling DB/Object caching and then run some test orders, etc?

I realize I'm not really giving you any answers here, but maybe some ideas or avenues to try?  I'm interested to hear what you find going forward. Keep us posted...

[Nicholas Ciske]

WooCommerce is easy to blame ;-)

Performance issues I've run into with WooCommerce:

Catalog Visibility Extension: runs lots of queries to check for category level visibility toggles. On large installs it quickly brings a site to it's knees.

Wishlist Extension: Default setup creates lots of empty lists (bots and search engines hit the url and create single item lists). Also bloats post meta DB due to the way it stores lists.

Post meta in general seems to be the achilles heel of WordPress at scale and WooCommerce exacerbates the situation by using CPTs and post meta for *everything*. With the number of products you have... that's probably where the bottle neck is -- since that's holding meta for products, orders, customers, etc.

When you start trying to join thousands of products with taxonomies through a massive post meta table, you can get into trouble. Woo putting visibility in meta vs using a post status (i.e. in the posts table) makes things worse ;-)

Trimming any fat you can there *may* help (e.g. orphaned records, post revisions).

Here's plugin that may help as well:

http://www.nathanfranklin.com.au/plugins/introducing-horizontal-meta-performance-benchmarks

Here's how to reroute meta settings/getting if you want to roll your own solution (though I'm guessing WooCommerce may have issues with custom sql queries that assume the standard post meta structure?) you may be able to move other meta (or woo meta not used in joins) out of that table to make room for woo:

http://sltaylor.co.uk/blog/custom-meta-tables-wordpress/

Depending on how the site is built you can also look into:

Caching some pages (like the main catalog pages) if they aren't personalized)

Fragment caching with W3 Total Cache (e.g. move to alternate hosting like WebSynthesis) if there is personalization throughout the site

Serving non account pages as cached files and filling in the personalized bits via ajax call(s)

WP Engines caching being either on or off and the same for everyone is my major gripe with them -- it definitely makes some sites harder to run on their infrastructure (which is really more tuned to content sites vs e-commerce or membership sites).

_________________________

Nick Ciske

http://thoughtrefinery.com/

@nciske

[jus...@foell.org]

How long do the queries by themselves (without all the PHP / WordPress overhead) take to run? Doing an explain plan (running the query as "explain select ..." shows that there's temporary tables and a filesort going on that are likely major factors. You can get rid of them by eliminating the group by/order by, but the dataset is not very useful without it. For the sake of comparison I'd still like to know how these queries perform without the group/order.

Are these queries from the wp-admin -> products page, or a products page on the front end? I wonder if there's some additional limiting you can enforce to reduce the total amount of products being sorted in that query. For instance, is taxonomy term ID 980 "uncategorized"? Might be time to put things into more categories and make people choose.

With 20k products you're likely pushing the envelope of the WooCommerce and WordPress in general. You are probably in for some (interesting) customization to work around this. I bet WooThemes would be interested in your findings - they may be a good starting point to find out if any of their customers are managing more than 20k products.

Justin

On Thursday, December 5, 2013 4:25:44 PM UTC-6, Nick wrote:

[Nick Pelton]

Thanks for all the input guys. I've inquired a bit more on the site history but don't have any information yet.

Nick/Justin/Chris:

They are running an large amount of WooCommerce plugins, but none of them seem to be the source of the slow queries. The main shopping page is a category that has 20819 posts assigned (paginated displaying 100 per page) -  so limiting the main shopping page to a smaller count would definitely speed things up :) If WP-Engine can't fix the problem with the initial research, I'll do some performance testing and let you guys know what I find.

David:

ECommence plugins like WooCommerce use Cookies/Sessions to track user cart data. WPEngine drops all cookies (and session with them) on page loads due to their caching/proxy layer (http://wpengine.com/support/cookies-and-php-sessions/) So you need to request they not cache specific pages on the site (http://wpengine.com/support/wpengine-ecommerce/). My initial hunch was they turned off caching on pages that don't need it (like the product listing pages) when they probably should only turn them off on the needed checkout pages.

I've becoming increasingly interested in scaling WordPress lately concerning this project and others. Anyone have any good resources they'd like to share on the subject?

Thanks,

Nick

--

[Reid Peifer]

My partner Peter has done a lot of work on scaling over the last year. He’s got a WordCamp talk he gave on it which might be of some help, but I can also probably hook up a conference call if you want to chat.

I should note, we’ve got a bunch of sites on WpEngine, but nothing that is of significant size or traffic because their caching approaches have been problematic. We’ve managed to have a couple calls with their leadership team as well, and it doesn’t sound like they’re going to be shifting things around much.  They reached out to us for insight on how to attract more enterprise level clients, but when we went through the problems we have the response we got was “that’s a bummer”.  Nice, but not very helpful.

®

Reid Peifer

reid....@gmail.com

[Justin Foell]

"The main shopping page is a category that has 20819 posts assigned (paginated displaying 100 per page)"

That sounds like an easy fix right there. This is not really a Woo/WP issue as much as a database issue because you're literally asking for too much. Any time you ask the DB to sort 20k records to get the first (or nth) 100 rows it's going to have to pull all of them, sort them and then throw away the other 19,900.

Now you've got me curious... just what are these guys selling? :)

Justin

--
You received this message because you are subscribed to a topic in the Google Groups "Minneapolis St. Paul WordPress User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mpls-stpaul-wordpress/ihGW0yi1Rmc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mpls-stpaul-word...@googlegroups.com.

[Nick Pelton]

Sweaters, lot's of sweaters.

[David Paul Ellenwood]

This is not really a Woo/WP issue as much as a database issue because you're literally asking for too much. Any time you ask the DB to sort 20k records to get the first (or nth) 100 rows it's going to have to pull all of them, sort them and then throw away the other 19,900.

Ditto.  That is exactly the issue.  the two queries you pointed out are trying to index every single product in the store in a round-about way: through the category used for the products landing page.  I have a hunch that simply avoiding using a category to display the products landing and using the actual products archive index instead would eliminate your issue.

I would also still suggest tweaking your caching settings.  As I mentioned earlier I have several WooCommerce sites running on WP Engine, with no cache configuration exceptions set up and they accept & process orders without issue.  While it's true that both WooCommerce and WP E-Commerce used to have issues with caching and their checkout process, both of them have made great strides to internalize that process specifically to avoid caching issues.

But, I digress...Trying to index all ~20,000 products through a category view (even paged) is certainly going to cause performance issues.  Definitely start there. :)

To unsubscribe from this group and stop receiving emails from it, send an email to mpls-stpaul-wordpress+unsub...@googlegroups.com.

[Chris Dohman]

it sounds like you have something good to start with by knocking down the main shop page query to a reasonable number like the guys suggest.

i see a couple large product woocommerce shops are soulbrother.com (20k+) and kinder-book.de. (60k+). they have some cat queries that return large numbers. maybe you could pick the minds of one of their guys to see if they implemented some query tweaks. i see @bradydan of http://www.consil.co.uk/work/soul-brother-records/ did the soulbrother site.

it really would be helpful if woo did start publishing some more scaling tips since bigger shops are starting to use their woocommerce solution. i'm sure they will come some time.

[Nick Ciske]

It won't fix slow queries, but WC 2.1 is addressing some of the session/caching issues on Varnish cache using hosts like WP Engine.

http://mikejolley.com/2013/12/problems-with-cart-sessions-and-woocommerce

tldr: new _wp_ cookie naming scheme will allow caching on most WC pages until the user has some cart data, at which point caching will be disabled by the presence of the session cookie. Not a silver bullet, but will help mater greatly for "browsing" traffic.

[George Velev]

Hi guys,

Do you know what kind of product filtering kinder-book.de is using - for instance here, located on the left side: http://www.kinder-book.de/produktkategorie/kinderjugendbuch/ ? What is your opinion - is it custom made or plugin?

And one more thing - I want to cerate a partner referral ecommerce site in my country (outside US) - no physical products but links to other retailers' promotions (something like promotions catalog).

For this goal I am going to use a combination of Genesis 2.0 + Dynamik Website Builder + WooCommerce. Since I will disallow cart, shippings, orders, checkouts, etc. because they are useless in my case, do you see any potential issues with WPEngine host and their caching structure?

And do you know by any chance a plugin to help me attach pixel tracking code to my visitors? This is important since my partnership revenue share model should work based on actual sales and I have to be able to track them. WooCommerce offers an extension called Exetrnal products (links to external retailers product pages behind the Buy button), but there is no tracking options for PPS. So far I am failing to find such solution among WP plugins.

Thanks in advance for your reply.

If you believe my questions are not suitable for this topic, I'll open a new one. Still, I believe my questions are interesting to discuss.

Have a nice day,

George

[Johann]

Hi George, I am the owner of kinder-book.de - the filter ist the standard WooCommerce Layred Nav Filter (Values from WooCommerce Attributes) - no additional Plugin or customizing. The Clickboard is easy php code - the values are from the WooCommerce Attributes.

Cheers and hav fun with your Project.

[George Velev]

Hey Johann,
Thank you very much for sharing this info!
And congratulations for the good job - your site looks great.
Layered navigation plugin is in my buying list so I'll definately use it.
I wish you success with your project also.
Cheers,
George

[Dan Brady]

Dan Brady here. Yes, with the Soul Brother site (and other large WooCommerce sites we've worked on) part of our approach is to identify slow queries and eliminate them wherever we can. WooCommerce is getting better all the time but some plugins are poorly coded. The debug bar add-on helps identify slow queries. We also employ caching which takes some configuration but does help.

Some of the sidebar widgets for filtering products do slow things down. It looks like the German book site doesn't use many, whereas on Soul Brother we use a lot, so we did re-write a couple.

One thing to bear in mind is that it's not just the number of products that can affect performance – it's anything which bloats the database. For instance a very large number of orders or comments. This is as much of a WordPress issue as a WC issue, but it all needs to be factored in when optimising a growing site. In other words, there's no one 'silver bullet' to optimising WooCommerce.