26 Comments

  1. Yehuda
    August 17, 2016 @ 10:40 am

    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 ?

    Reply

  2. Yehuda
    August 25, 2016 @ 10:03 am

    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 ?

    Reply

  3. Yasin
    November 28, 2016 @ 11:24 pm

    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 ?

    Reply

    • Dave Hilditch
      November 29, 2016 @ 10:47 am

      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.

      Reply

      • chemistrap
        May 19, 2017 @ 7:24 am

        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

        Reply

        • Dave Hilditch
          May 19, 2017 @ 7:32 am

          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.

          Reply

  4. Abhishek Jain
    May 3, 2017 @ 2:20 pm

    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?

    Reply

    • Dave Hilditch
      May 3, 2017 @ 2:35 pm

      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.

      Reply

      • Abhishek Jain
        May 8, 2017 @ 5:27 pm

        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?

        Reply

        • Dave Hilditch
          May 8, 2017 @ 5:29 pm

          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!

          Reply

          • Abhishek Jain
            May 8, 2017 @ 6:15 pm

            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?

          • Dave Hilditch
            May 8, 2017 @ 6:27 pm

            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
    May 3, 2017 @ 8:24 pm

    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?

    Reply

    • Dave Hilditch
      May 3, 2017 @ 8:31 pm

      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.

      Reply

  6. Vidit
    May 3, 2017 @ 9:04 pm

    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.

    Reply

  7. Vidit
    May 3, 2017 @ 9:27 pm

    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.

    Reply

    • Dave Hilditch
      May 3, 2017 @ 9:31 pm

      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.

      Reply

      • Dave Hilditch
        May 3, 2017 @ 9:32 pm

        Also – if you haven’t seen it already, check out my complete performance guide here: https://www.wpintense.com/performance/

        Reply

        • Vidit
          May 8, 2017 @ 5:41 pm

          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

          Reply

          • Dave Hilditch
            May 8, 2017 @ 5:46 pm

            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.

  8. Vidit
    May 3, 2017 @ 9:38 pm

    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.

    Reply

    • Dave Hilditch
      May 3, 2017 @ 9:41 pm

      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.

      Reply

  9. Vidit
    May 3, 2017 @ 9:39 pm

    I forgot to mention we have PHP 7.0 with Serverpilot stack on 16GB RAM Digital Ocean.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

  • Hullo and welcome! Chat directly to the site owners below.
Latest Message: