- Discord Performance Community Server and some new server rules - November 27, 2024
- Calling for early access test files for Super Speedy Imports - November 27, 2024
- Cyber Monday Deals Live – grab a bargain before the Scalability Pro licensing model changes! - November 21, 2024
I’ve just been sent the link to my filmed talk down in Brighton at the WordCamp there.
I hate seeing myself recorded, but if you want to watch my talk made on the day this site launched then here it is. I don’t get into too many technical details, but I do sort of talk a bit about performance and getting WordPress and WooCommerce to scale.
If you have any questions about WordPress scalability, let me know in the comments, or if you want to learn more about scaling check out my complete wordpress performance guide.
Since making this video, we have distilled all of Dave’s knowledge into our Super Speedy Plugin pack. Join our happy customers who no longer have to worry about performance ever again.
Slides for ‘Scaling WooCommerce to 1 million products’
For a quick overview and informative images of what was covered by Dave Hilditch at his Wordcamp Brighton talk, check out these slides:
Great talk Dave!
Luke from LW recommended you. Would like to chat about some consulting and also had some plugin pre-purchase questions. I sent an email via the chat form but not sure if you got it?
Also I noticed the sorting drop down on foundthru doesn’t work after facets have been selected. Steps to reproduce:
1. Go to https://foundthru.com/dogs/dog-food-and-treats/original/beige/
2. Select an additional facet or two
3. Try changing the sorting dropdown
Cheers
Thanks for the comments – foundthru is actually a dev server as well as a demo server so it frequently has some random stuff set up on it.
If you’d like to talk about consulting, use the quote request form.
hello
how you doing?
what’s your recommended hosting for large woocommerce websites I’m using https://www.cloudways.com/en/hosting-woocommerce.php any recommendation?
Gridpane
Sorry I just want to subscribe to the comments.
hi david,
i need to develop a site with dropshipping wordpress/woocommerce, which import data from amazon (plugin availables, to keep product status live/cron synced), i want to hold 1+ million products on it, how scalable and possible is this, i’ve tried this in the past and failed with other developers, are you actually taking custom jobs like such? many thanks.
We take on *some* projects like this, but only really if they coincide with the future or current direction of our related plugins.
You can submit a quote request here: https://www.superspeedyplugins.com/request-quote/wpintense/
Hi Rodrigo, you can submit a request for a quote here: https://www.superspeedyplugins.com/request-quote/wpintense/
Hi David,
I loved your talk and the information your are sharing on this site. I have two questions:
1) You mention caching quite a bit in your talk. My site only shows prices after users log in. I heard that caching is no longer going to help me. Is that correct for all forms of caching, like page caching, browser caching, etc?
2) I am having the hardest time finding someone with your level of knowledge on scaling woocommerce related to databases and queries. Are there any developers you could recommend or are you available yourself?
Thank you in advance for your help. And keep up the good work!
1) It depends if the prices are displayed to your logged in users on the page, or after the page has loaded using ajax. But yeah it’s most likely you won’t be able to use page-caching for your logged in users. You can still use object caching and browser caching however. And when your site is optimised, caching is required less in any case.
2) There’s a great Facebook group called ‘WordPress SpeedUp’ – there might be developers there able to help you. I’m available to help on larger projects and you get my help if you have my plugins too.
So glad I found You today as I searched the limitations of WP. I just built a store that has 24k products with the intention of growing that number up to 1M. I just switched to cloud hosting so I have that covered but I’m concerned about assessing which plugins are no good and whether my theme can handle a large store.
Secondly, a large store requires a good search option and a robust filtering system. Does your plugin solve for that? Did you say that the Woocommerce filters shouldn’t be used because it reads the whole table? If so,
have you thought about building a product filter plugin or can you recommend an alternative solution?
For stock quantities I need to run daily cronjobs through WP All Import. Do you recommend setting up a different server so it doesn’t slow down the site?
Ps definitely buying your plugin
Get the performance plugin pack. There’s faster woo widgets plugin, super speedy search plugin as well as scalability pro.
Re woo filters, look at the product page for faster woo widgets here on my site and you’ll see a video where I show the performance penalty you incur by using the normal woocommerce widgets.
Hey Dave!
Wow wow wow!!! Nice article and lots of gold in the comments! I’m doing a proposal for a potential client with 500k to 1mil products. From what i understand, a Good server (I’m thinking vultr through Cloudways, but i can also get a DO droplet), MariaDB and the other stuff mentioned along with your plugins would do the job?
Client would also like a search form for auto maker / auto model / auto year / optional keyword. Search form is a drop-down.
Are your plugins compatible with facetwp?
I’m not aware of facetwp. Presumably it’s using Custom Post Types?
Faster Woo Widgets has an upgrade coming out today, but it IS focused on WooCommerce. There’s no real reason why I can’t alter the code slightly though to allow different post types other than ‘product’.
Let me know if you’re interested in that.
re: Cloudways, I’ve heard great things about them and they use Digital Ocean if you wish. MariaDB is great, definitely better than vanilla MySQL, although I’ve reverted to using PerconaDB about 6 months ago thanks to the performance toolkit and other analytical tools available with Percona.
In any case, 1 million products is achievable. I can help out if we can make the work I do part of plugin upgrades (I’m trying to avoid client work so the plugins develop faster)
Hi Dave!
Thanks for getting back to me and sorry for being so late :-/
I would definitely be interested in working with you on this and whatever goes into this project can most definitely be part of plugin upgrades. What would be the best way to reach you? The proposal is on hold and I feel like if I included the search like he asked for, then we might get a green light!
BTW feel free to drop me a note to my email so we can continue the discussion.
PS: this will be a WooCommerce store and import using WP All Import.
Talk soon!
I forgot to mention we have PHP 7.0 with Serverpilot stack on 16GB RAM Digital Ocean.
I’ll check the Performance guide right away and then go for the Scalability Plugin. Would the plugin help with terribly slow back end as well?
The back end takes approximately 10 or more seconds to load and then is super slow while opening / searching orders or other pages.
Yes it speeds up the back-end MASSIVELY – although the latest dev version shaves even more time off the back-end wp-admin pages – that’ll also be out by Friday.
Thanks Dave. Your quest to scale & optimise Woo-commerce has been really encouraging for us to stick with it instead of moving to Shopify or Magento.
I’ll be waiting for the D.I.Y 3 Node Cluster at the end of the week and would go for it as soon as it is published.
Would you suggest we purchase the Scalability Pro plugin or the Performance Optimisation and other bespoke work?
We have a lot of plugins and the hosting cost is getting really expensive for us. Would really like to optimise the website so that it loads quickly and does not cause as much CPU load.
You should get Scalability Pro plugin – if it doesn’t reduce your Time To First Byte to circa 300ms, let me know and I’ll come take a look for you for free. Often I can fix it for free too.
If there’s bespoke work required – e.g. due to custom code or some funky plugins, I can give you an estimate for the bespoke work if needed.
Also – if you haven’t seen it already, check out my complete performance guide here: https://www.superspeedyplugins.com/performance/
Hi Dave,
Any update on the D.I.Y guide for the rocket stack?
Also, should I get the scalability pro right now or shall I wait for the new version to be released?
Thanks
re: DIY guide for rocket stack, first DIY guide coming is technically for the cluster. Ran into snag, should get it out this week.
But if you want to upgrade an existing host you have to be like the Rocket Stack it’s basically this:
MariaDB instead of MySQL, Nginx instead of Apache, Varnish, Redis, PHP 7, Letsencrypt – plus a bunch of optimisations especially to my.cnf file to improve db performance.
Thanks for your reply Dave. Do you have a guide on how to upgrade to MariaDB or PerconaDB from MySql? Would you suggest I purchase the Scalability Pro Plugin and stick with MySQL or shall I upgrade the database and then go with the Scalability Pro?
I like the 3 Cluster Rocket Stack but it is too expensive for me to purchase at the moment. Is there a D.I.Y guide that I can follow to install that set up?
We would be running Imports Weekly.
To upgrade mysql to MariaDB, plugin in your OS and version here: https://downloads.mariadb.org/mariadb/repositories/#mirror=babylon-fr&distro=Ubuntu&distro_release=xenial–ubuntu_xenial&version=10.1
It gives you commands you need to run – no need to uninstall MySQL or anything, it’ll upgrade in place.
If you’re using Digital Ocean, take a snapshot of your server before you upgrade just in case.
re: 3-node cluster – the DIY guide is coming – working on finishing touches to it all day today – should be ready by the end of this week.
Hi Dave,
Great Talk. I have a Woo-commerce website with around 50k products which is going to be increased to 300k. The only bottleneck is that the website CPU usage goes really high with traffic as little as 60 concurrent users on the website. The cause of high CPU usage is MySQL.
1. Do you think that this plugin will help cut down the CPU usage for MySQL queries?
2. Would you suggest we upgrade MySQL to Maridadb or PerconaDB and then install this plugin?
3. Would you suggest a 2 server setup with web server and database on different servers?
Current hosting stack – Digital Ocean – 16GB RAM Plan
Planned upgrade – 2 server Set up with 8GB RAM for Database + 8GB Ram for Web server / Would you rather suggest Debops or Rocket stack?
The Rocket Stack is a variant on Debops, so if you’re confident with Debops then go for it. The Rocket Stack has additional custom configs of course. I’ll get the details published asap – first publication will be how to build a cluster though.
1) 300k products is not many but if you have any table scans you’ll struggle to scale because you can’t cache everything. And yes, Scalability Pro cuts down CPU and disk usage for MySQL queries.
2) MariaDB and PerconaDB give you about a 2x boost in MySQL performance. Scalability Pro gives you Nx where N can be a large number depending on how many products/posts you have – e.g. 1000x is possible if you’re eliminating table scans from a large enough database.
3) No, I wouldn’t recommend this really – it’s better than a single server for sure, but presumably with 300k products you are going to have imports running daily? If so, you should aim to separate these imports from your user activity. That means building a 3-node cluster, each with db+web, node1 for wp-admin and wp-cron and node2 and node3 serving user traffic. If you AREN’T planning any daily batch jobs then sure, go for the DB+web server separated out, just ensure you set up private networking properly.
Hi David,
I came across your video, it does answer my query to some extent but I am still in doubt since you didn’t include technical details. I have a wordpress woocommerce website that has around 500k products. Currently, when I am trying to load products under certain category(around 30k products), it doesn’t load and gives me server timeout, when I am trying to load a category with just 83 products, it is taking more than 30 seconds to show the products.
I used query monitor to analyse the queries, it says there are 945 duplicate queries just for category with 83 products. For categories with larger products the page is not loading so query monitor is not able to show results.
I asked my developer for suggestion, he says I will have to buy dedicated server since shared hosting is bad. He is recommending me against optimising my database. I am currently using Bluehost’s wordpress customised server with 6 cpus and 10 gb RAM while bluehost is recommending me to optimise my database rather than going for dedicated server.
I don’t know much about woocommerce or wordpress, but what can be solutions to the problem? I have wp-super cache enabled, it did improve to some extent. Will building indexes solve my problem for page load time or should I go with dedicated server to reach page load time under 3 seconds?
I would really be grateful if some alternatives can be suggested?
The reason for your slow speed is multi-fold.
Firstly – you are using shared hosting – for this many products, it’s only a matter of time before the shared hosting company kicks you off. Why are you using shared hosting? You can get a VPS for $10pcm.
Secondly – your dev is wrong to tell you not to optimise your database as it’s the primary source of your performance issues. Basically, you are causing table scans to happen. It’s not your fault, but you’ll find if you remove all your sidebar widgets from your shop that the pages load.
Thirdly – the 945 duplicate queries sounds odd – something bespoke maybe or an unusual plugin I’m unaware of.
Fourthly – upgrading to a dedicated server WITHOUT optimising your database will not help you, not really – it might cut the time in half, but if your pages are timing out that’s no good. You need 1000x increase which only comes with avoiding table scans and having key-seeks instead.
Lastly – check out my Scalability Pro plugin – it’s designed to help your specific case.
Final point – it doesn’t actually sound like you’re on shared hosting – 6 CPUs + 10GB RAM is PLENTY to do what you’re doing, so in that case you need to optimise your database. Check out Scalability Pro – it’s the solution you need. It includes all the indexes and replacement widgets to eliminate WooCommerce widgets. You can confirm the issue is what I’m saying by removing all your sidebar widgets and loading the pages – the woo widgets are running table scans to count the number of products per category.
Hi Dave,
You were right, table scan was the culprit. Page loading time improved a lot(from 300 seconds to 3 seconds). But I ran into another problem, whenever I try to edit any page on wp-admin through pages tab, it times out, and I am not able to edit any page. Is it possible to fix that?
Yes – there are table scans happen in the back end of WordPress too – get Scalability Pro and it fixes front AND back-end performance. You shouldn’t be putting up with 3s response times, never mind timeouts!
1) It depends which plugin it is and how it was written/what it searches. If it’s using free-text search, you can disable the free-text search changes made by Scalability Pro by deleting the ‘full text index’. If it’s searching wp_terms, or wp_postmeta etc, it’ll almost certaintly benefit from the indexes created by scalability pro.
2) You can get the single site license in this case and use it freely on your dev site then live site
FYI, Scalability Pro is splitting into THREE plugins – scalability pro, faster woo widgets and super speedy search – any purchases made now will get all 3 of those future plugins included.
Thank you, Dave. Your answers really helped me. I am going to buy your plugin. I just have few doubts:
1. I have custom search function implemented to search a products based on different attributes, will that be fixed by the plugin or will I have to hire a developer to do that?
2. I just have to use the plugin on 1 website but I got 2 sub domains(1, I am using for testing), will I have to buy 1 or more website licenses?
Hi, I can not understand because my engilish is not good. Is it possible 1 millions ? what do you think dave what is limit ? 1 million, 5 million, 15 million ?
What is your opinion ?
The limit in core WooCommerce (unchanged) is probably about 5,000 to 10,000 depending on your server. If you modify WooCommerce slightly, e.g. using the WPI Scalability Pro plugin then you can go > 1 million products.
Hello Dave,
We want to build large products (about 1 million) shop page with Woocommerce ( cause that my only working area ) but i could not decide which server should i choose and how can be handle this large products situation. Now, im using a server which unlimited features ( mails, files, traffic etc) but im going to buy dedicated server or i will setup my own server for managament all of server and root access, which is the best opinion?
And secondly i review your plugin which name is wpi scalable pro plugin and some your cusomters putted good review and what do you think about it?
when i start wordpress+nginx+mariadb+hhvm and still necessary that plugin? And i will use a theme which selling on themeforest so as you know mostly themes uses custom plugins custom codes etc. is that any problem for that?
Thnx
For only 1 million products, a dedicated server is a bit of overkill. I would still recommend you use a digital ocean droplet – something like their $40pcm servers would be fast enough for 1 second page response times IF you follow the other steps too.
You need to use Scalability Pro if you 1 million products – either that, or you need to mimic what it does. Firstly – it adds indexes that are missing from key tables – without them, you have table scans which over 1 million products takes between 20 and 40 seconds on a $40pcm digital droplet. On top of that, Scalability Pro rewrites a number of queries to be index-friendly.
Regarding themes – It depends on the theme – some themes from themeforest have really AWFUL code – e.g. loading all products from the database and working through them 1 by 1 with PHP just to display 5 most recently viewed products (for example). But mostly the themes are OK.
The other problems (table scans and other bad code) tend to come from things like: Filter or sidebar woocommerce widget plugins, other woocommerce widgets e.g. yith wishlist prevents page caching, some recently viewed products widgets prevent page caching, some text-search plugins cause table scans etc.
Finally, with 1 million products you’re going to be using a lot of storage space for images – you can use a cheap Digital Ocean volume to cover this, OR you can use my External Images plugin to avoid downloading affiliate images to your store in the first place – that speeds up imports and eliminates the need for massive storage volumes.
Hi,
For WooCommerce, just for experience the process myself ( Scaling to 50,000 )
1. Server Stack (clear from the video)
2. Create Indexes for WooCommerce ?
Love it, too bad you didn’t get in the technical stuff or more detailes.
I think you must make a video or posts series about this.
Is there a place download the slides ?
I’ve made a more technical video here: https://www.superspeedyplugins.com/2016/08/19/wordpress-plugin-performance-analysis/
I am making videos covering the technical details. I actually did a screen-cast session the other night but it was too long for my YouTube account so it never uploaded. I will re-do to cover the various technical aspects involved in this kind of optimisation.