Case Studies & Success Stories

Scaling WooCommerce to 1 million products (my talk at WordCamp Brighton)

Dave Hilditch •
Case Studies & Success Stories article header

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: 

Wordcamp Brighton Slideshow

42 comments

  1. Yehuda

    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 ?

    1. Dave H. Customer

      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.

    2. Dave H. Customer

      I've made a more technical video here: https://www.superspeedyplugins.com/2016/08/19/wordpress-plugin-performance-analysis/

  2. Yehuda

    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 ?

  3. Yasin

    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 ?

    1. Dave H. Customer

      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.

      1. chemistrap

        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

        1. Dave Hilditch

          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.

  4. Abhishek Jain

    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?

    1. Dave Hilditch

      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.

      1. Abhishek Jain

        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?

        1. Dave Hilditch

          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. Abhishek Jain

            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?

          2. Dave Hilditch

            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.

  5. Vidit

    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?

    1. Dave Hilditch

      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.

▲
▼
1/1