WooCommerce is the most used webshop system in the world and its awesome! However, using WordPress as its application framework has some drawbacks. In particular, you might get a very slow WooCommerce with many products. When I point that out in client meetings, I inevitable get the same questions: How many products exactly result in a slow WooCommerce frontend/backend etc – and what can you do about it?
Besides all knowledge I have about server architecture, clean theming and WordPress coding, that question isn’t so easy to answer. That’s why I did set up my own little test app.
Recommendation: Many slow WooCommerce sites just don’t have the right type of hosting. A good WordPress/WooCommerce hosting service with great support and performance optimizing technical addons like Redis and ElasticSearch is Kinsta. Read more about their WooCommerce hosting here.
Slow WooCommerce with Many Products – The Test Environment
I’ve done all of the following tests on my local computer. It’s a Dell XPS 13 the following hardware:
- Intel Core i7-7500U
- 8GB LPDDR3 1866MHz
- 256GB PCIe Solid State Drive
Operating System and services:
- Ubuntu 14.04
- Apache 2.4.7
- MySql 5.5.54
- PHP 7.0.16
The WordPress installation I’m testing with is a clean install of:
- WordPress 4.7.2
- WooCommerce 2.6.14
- Twenty Seventeen Theme
No other plugins or code adjustments.
Furthermore, I wrote a small npm application, which incrementally adds products to WooCommerce by using the API. After each insertion of data, the application pauses five seconds, to give garbage collection and MySql a break and then performs some tests, like reading products from the WooCommerce API or requesting the “Shop”-page from the front-end, while measuring both request-time and the average of the MySql cpu and memory usage.
The inserted products are simple WooCommerce products, with auto-generated lorem-ipsum titles, description and a random price.
Checkout the test application on GitHub, feedback and contributions very welcome:
https://github.com/danielauener/slow-woocommerce-tests
After cloning, go to the app folder and run
npm install
Then adjust the configuration parameters in index.js to your environment and run the application with a simple
npm start
After completing the test cycle, the test data is written to a benchmark csv file in the benchmark directory of the app directory.
What’s tested
There are three questions, I wanted to answer.
- Is WooCommerce really getting slower with many products and how much?
- Is it WooCommerce slowing down the performance or is it actually WordPress, which is slow?
- If we can confirm a slow WooCommerce with many products, why is that happening?
Besides these general questions, I was interested in two use-cases. The obvious one was request times for the WooCommerce front-end, because that’s impacting conversions and sales. The second one was the insertion speed of new products with a big database. That one is important, because most shops with around 10000 products and more, most likely have some kind of import process, which might add load to the entire server. Since we talking about imports, which can go on for hours, that’s quite relevant.
Slow WooCommerce performance with 50 000 Products?
In my first test I wanted to check a really huge product database, so I could get a full picture of what’s happening. After some tries and time calculations, I decided to run with 50 000 products, starting my test tool with the following environment variables (MySql and WP configuration in the index.js file).
MYSQL_PID=4333 ITERATIONS=500 POST_INSERT_CHUNK=100 npm start
CPU and Meta Table
After a good nights sleep, the test was finally through. What I could say from how my computer behaved during the test (noisy and warm), there had to be some CPU-usage, which you can see in the first image. The other two lines in the chart are the amount of added post in each step (confirming the test went as planned) and the size of the wp_postmeta-table.
As you see, is the size of the meta-table (yellow), rising much faster than the size of the products-table (green), which we will discuss later. The CPU-usage seems to increase logarithmic, slowly approaching the 100%.
Just to clarify: the CPU-usage is measured only when the requests are done, not during the insert product calls.
Requests
The next chart I’ve created, shows, how the CPU-usage affects the front-end request times. After every product insertion cycle, test requests where made to the shop-page, which is listing the first ten products and a single product page.
The overall development of the chart shows, that all the time, the single product page loading times are quite stable. The shop pages loading times increase directly from the beginning though. That’s what I would expect, because the shop-page queries the entire products table and needs to load meta data for at least ten products, while the single product page only needs to do that once.
Furthermore, we can see two quite obvious changes in the chart. Around 15 000 products (step 150) the CPU aproaches 50%, and we get a bigger variation in the loading times. Nonetheless, the single product times seem to be quite stable, but the shop-page times clearly increase. The same happens again, when the CPU-usage passes 70%, around 35 000 products.
Insertion Speed
The speed of adding new products obviously increases with the amount of products, which is expected. I didn’t expect such an increase, though. I would have thought, that you have to operate WooCommerce with many more products, to get from around 4 seconds per 100 products to much more than a minute.
For the last dip of increase time around 45 000 products I have no explanation. Might be that some resources were freed on my computer, not really sure, though.
Slow WooCommerce performance with 10 000 Products?
To double-check the previous results, I ran the same tests with only 10 000 products. I both wanted to see, if the test with lesser products would lead to the same conclusions and decrease test-time for further runs. Before this new test, I restored the initial database, from before the other test, than ran the following command:
MYSQL_PID=4333 ITERATIONS=100 POST_INSERT_CHUNK=100 npm start
CPU
The CPU-usage is rising, even with 10 000 products. It goes up to about 30%, which matches the 10 000 product value of the previous test. Meta-table and product count behave as expected.
Requests
Even the requests show the same picture. The shop-page request time seems to rise accordingly to the CPU-usage, which was the same in the 50 000 test, below the 15 000 products mark. The single product page loads steadily between 100 and 150ms.
Insertion Speed
The same goes for insertion speed, which shows a similar picture as above.
What Causes a Slow WooCommerce with Many Products
Now, one of my questions was, what causes slow WooCommerce queries and if the problem actually is related to WooCommerce? From experience with prior projects, I knew the answer already and had a quite good idea what the reason was. However, it’s always nice to confirm your assumptions with data.
Big Meta Table = Long Loading Times
My experience was, that WooCommerce performance problems with many posts are mainly related to the size of the meta table. As we have seen above, is the table growing quite fast when you add more products. After adding 50 000 products, it contained around 650 000 rows. To test if the meta-table assumption was accurate, I added a function to my application, which removes all WooCommerce meta-fields after each product insertion cycle. The following command runs the 10 000 test with that addition.
MYSQL_PID=4333 ITERATIONS=500 POST_INSERT_CHUNK=100 npm start
CPU
The CPU-chart already shows, that the idea seems to be correct. Without the big meta-table, the CPU-usage is slightly increasing, but almost stable, compared to before.
Requests
The requests-chart gives us the same impression. The shop-page request time follows the usage values and is stable between 80 and 100 milliseconds.
Insertion Speed
Only the insertion speed is painting another picture. Here we have no performance advantage at all, removing the meta-fields. I’ve added the grey line of instertion times from the previous test and it actually is almost identical. That was surprise, I have to admit, I thought there would be at least some difference.
Conclusions
So, that was fun. But is there something we can do about it? Shouldn’t we use WooCommerce for more than 15 000 products?
Yes, you should use WooCommerce, even for many products. But with the knowledge we gathered here, we are able to make some decisions early on, when building a shop, which should help us along the road.
Some tips to avoid slow WooCommerce:
- Check before you start building, how many products the shop will be selling, now and in the future.
- Avoid more meta-fields than absolutely necessary, that includes meta-fields on other post-types as well.
- As much as we love Advanced Custom Fields: for a big shop, you might want to skip on it. For every field you add to a product with ACF, you get 2 meta-fields in the database.
- Think about an eventual import process very early and test it several times, while building the shop. You need to know how much time it will take.
- Inform everyone about eventually long import sessions and the extra load on the servers they are causing.
- Use a good caching-plugin, which takes load form your server and frees resources for other stuff.
Even if you do everything right, at some point all precautions wont help. Then you have to think about widening you infrastructure. There are some tools which can help, like Elastic Search or Redis, and there are surely a hundred ways to code your way around WooCommerce limitations (e. g. two of my workarounds for the admin panel). And, last but not least, there is always the possibility of a bigger server or a more advanced architecture.
Wow, so great post ! Thank you for all the tips.
WooCommerce 3.0 had some speed improvements. Could you redo your tests with WooCommerce 3.0 ?
WC3 isn’t much better yet, as it’s more in preparation for the forthcoming database changes.
But I will say, doing things the WC3 I find there’s a few case scenarios where it’s a hell of a lot slower than before.
i.e. to grab products with certain meta you need to loop through each product, create the product object and then access the meta to check.
Thankfully there is solution in the works – https://github.com/woocommerce/woocommerce/issues/12677
Hi there,
How does woocommerce treat product variations though?
If i have 1 products with 10 different variations, is that counted as one products or 10 products?
Thank you,
Each variation takes up one entry in the wp_posts table, so for 1 product with 10 variations you’d have at least 10 entries, excluding any auto-drafts and similar cruft. Of course all those variations will also have a bunch of meta fields as well.
I’m working on a shop right now which only has 200 products, but over 12 000 variations of those products. wp_postmeta is at about 300 000 entries.
@stanislav – It’s not just the wp_postmeta table that’s an issue. When a product is first visited, it creates transients to cache the price values. The wc_var_prices transient creates three arrays: one for regular price, one for sale price, and one for price. Each array stores a price value for every single combination of product options. So if a dress has 20 sizes and comes in 20 colors, it creates a transient of 400 x 3 = 1200 values! If you visit an archive page with 10 products, that’s 12,000 values written on page load. It’s crashing the site I’m working on, and I don’t know how to fix.
Awesome post, thank you for sharing.
You should really add share buttons to your articles 🙂 I have the urge to share. They are really good! Keep em coming! 🙂
Very interesting article!
Have you analyzed the impact of an increasing number of orders even with a limited amount of products?
I noticed that even orders are saved in the posts-meta table and can imagine that it will continue to grow as time goes by.
I was considering that orders could be deleted or moved to a dedicated table periodically.
What do you think about this aspect?
Thanks in advance.
You are right, every order adds to the size of the meta-table and you run into similar problems when you get a serious amount of them.
As you mention, it would help to archive finished orders after some time, either to an additional table or some other kind of storage, to remove them from the database completely. Still, these are just temporary solutions, when your data is constantly growing.
Long-term solutions, like splitting the meta-table or dedicated tables for products/orders, involve a serious amount of coding and eventual maintenance to adapt to future updates.
In any case: the bigger your database gets, the more work you can expect for maintenance and unique workarounds and solutions.
I have resorted to regularly delete orders after having them backed-up to a csv file.
Then it just takes a few lines of SQL to clear:
DELETE FROM wp_woocommerce_order_itemmeta
DELETE FROM wp_woocommerce_order_items
DELETE FROM wp_comments WHERE comment_type = ‘order_note’
DELETE FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = ‘shop_order’ )
DELETE FROM wp_posts WHERE post_type = ‘shop_order’
The weird thing is that we also have the wp_woocommerce_sessions table that grows and has to be periodically emptied.
We are now migrating to the latest WooCommerce version which seems to have a different database schema and we’ll see how it handles things.
Good article. Also using quality caching plugin is one of the best way to speed up WooCommerce website
Good research. At just 1 500 products and 750 orders we already have a postmeta of 131 000 rows. Gotta check what’s going on here and clean up non needed postmeta.
I’ve heard this is still an issue, can anyone confirm? Thank you!
I have a fairly large and growing tshirt site and I’m noticing a few slow downs in the archive and category pages. Is there a way to speed this up, I’m not sure why it’s creating a bottle neck in those
I can confirm this is still an issue – I’ve got a eCom site that has 8k variations on one product (there are a few of these) – Load time on a none cached page is 300seconds :O
I have just ran a query, and on the category page alone, there are 38,131 requests to the database. WooCommerce seems to load every single bit of information on the product on the category pages (even though it only needs to basics – title, price).
I’m struggling on thinking if there’s a fix for this, or to split out the variations.
I can also confirm this is a major issue.
3,000 products each with multiple colors and sizes plus prices.
980,800 postmeta records.
Terribly slow and not possible at all to import price updates as it can take 10 hours to update 300 products.
If there is a fix I would love to know about it….
Hi Mark, there are several potential problems you can investigate. To speed up a large meta-table, look at prefix-indexing of the meta_value column. But even the options-table can slow down. Check if the autoload column is indexed (it contains max 3 letters, so a prefix index works great here as well) and if your transients and cron-jobs are blowing up the table. I’ve reached good performance with 11 Million meta-rows and 700 000 products, it’s not impossible. Good luck to you!
i have never tried woocommerce but i am planning to launch an ecommerce website with woocommerce. If the site will be slow down then it will be a clear headache to maintain.