WPI Scalability Pro

Description

The Scalability Pro Plugin is the core WP Intense plugin to transform the speed and scalability of your WordPress site. It does so primarily by eliminating table scans and using index seeks instead.

A highly scalable site will not slow down as your site gets bigger. Without Scalability Pro, you’ll likely see a degredation of performance on your website once you exceed 5000 products, orders or users. Caching can help, but what you really need is to make sure your website scales, regardless of how big your website becomes. A scalable website is as perceptibly fast at generating a web page when there are 1,000,000 users as it is when there are 10,000.

Speed up Slow Themes

There are many slow themes that request images from the database using the GUID against wp_posts. There is no index on this column, so as your wp_posts table grows in size these queries become slower and slower as without an index the query needs to read the entire contents of the table.

Scalability Pro adds the missing indexes your theme needs in order to perform as fast as when you bought it.

Scalability Pro speeds up your archive pages by speeding up WP_Query

Archive pages are the pages on your WordPress site that contain lists of other pages. Your blog archive, your /shop/ page, your custom post type archive, your category or author archives. All of these lists become slower to generate once you have more of them.

Scalability is about avoiding slower speeds as data volumes get bigger. By avoiding table scans, it’s possible to have a 1 million product store respond as quickly as a 20 product store.

Scalability Pro speeds up your imports

Plugins like Datafeedr and WP All Import store information in wp_postmeta about imports and the SQL queries they use cause table scans to happen. When they are importing, they will check wp_postmeta for certain values (meta_value column on the table). By default, WordPress has no index on this table – probably because meta_value is a ‘text’ field meaning unknown size. You can still create an index on the first few characters however and massively speed up the majority of checks against this table.

Scalability Pro also provides an option to let you Defer Term Counts until 2AM. Term Counts involve recounting the number of items (posts, products, custom post types) within a category or taxonomy. When you are importing, this can mean term counts after every individual item. If you are importing 10,000 products then by deferring the term counts, you can run the count once when the import is finished rather than 10,000 times.

If you’ve been experiencing your imports becoming slower the bigger your database gets, Scalability Pro will definitely help you.

Scalability Pro speeds up wp-admin and your site in general

The indexes speed up multiple queries around your site including wp-admin.

Additional options are available to speed up wp-admin archive pages (e.g. post lists or product lists) and editing pages.

How it works

• Makes WP_Query lightning fast (0.4 seconds uncached vs over 180 seconds for 820,000 products on /shop/ page)
• Works by optimising your underlying database and queries and eliminating table scans
• Provides options to alter or remove slow WooCommerce and WordPress features
• Complements caching systems perfectly
• Speeds up SLOW queries – install the Query Monitor plugin and view your slowest pages, if you see slow queries being reported, Scalability Pro will help. If you instead see thousands of queries occurring, there is a separate solution for that type of performance problem.
• It adds indexes to your key WordPress tables to help optimise underlying SQL queries
• It alters database queries to make them more efficient, avoiding table scans
• It provides options to disable, alter or microcache slow WordPress/WooCommerce/Import functionality

If Scalability Pro is implemented and there are still table scans occurring, we will fix them.

Three-pronged approach to real scalability

Scalability Pro works best in conjunction with Redis object caching and a good quality hosting stack. Any one of these on their own is not enough. If too many queries (over 1000) are being registered in Query Monitor when you have Redis activated, then the related plugin is not using an object cache. If this is the case, you should contact the plugin author and tell us too. We can advise them on how to fix their plugin to scale properly with good use of the object cache. If you’re still seeing slow queries, as opposed to too many queries, then Scalability Pro has missed something — please let us know and we’ll endeavour to get it fixed.

Five problem areas for Scalability

Slow queries as your site gets bigger – this is what Scalability Pro helps with. It makes slow queries run quickly when your site is bigger.

Too many queries – the Redis object cache helps with this, unless the plugin author has not implemented object-caching support

Too much RAM and CPU consumption in PHP – you’ll see low SQL time, low numbers of queries, but long page generation time and large RAM usage. You’ll probably find some queries that return large row counts, often over 5000 rows returned or more. These plugins are filling objects with those 5000 rows every page load. Normally, this can be fixed by implementing an object cache – but it depends on the type of data.

Slow API calls – your site may be using ajax, or it may be calling third-party APIs. This is especially true when an admin is logged in, and especially so on the dashboard and the plugins page and especially so if you have unlicensed plugins that keep trying to update themselves.

Poor hosting – make sure you get a good quality hosting stack. PHP 7, MySQL 8, nginx, fastcgi_cache or some kind of http accelerator, redis in-memory-only and a proper configuration.

A note about Table Scans and Page Caching

WordPress has many occasions where table scans occur against the database.  For example, the Price Filter causes a table scan to obtain the maximum and minimum price range and means your page is reading every item in your database to gather this information. When you only have a few hundred products in your WooCommerce store, you would never notice. But once you get to a few thousand or tens or hundreds of thousands this becomes dreadful for your server.

Page Caching helps – especially if you can implement it at the Nginx or Varnish level. But still, when you have a huge site, you cannot cache everything, so your site still needs to be fast for newly generated pages.

This is why eliminating Table Scans is fundamental to solving your WordPress scalability issues. Instead of reading every item in the database, well written queries read only the 20 items they need to read in order to present 20 items on page.

Scalability Pro Installation & Options

Go to Plugins->Add New and browse to your zip file for Scalability Pro. Upload then activate.

After you have installed and activated Scalability Pro, you should visit the Settings->Scalability Pro page.

Click the big button on the settings page to create your indexes. This is a one-time operation and the indexes will remain in place even after you de-activate the plugin. If you wish to remove the indexes before de-activation, you should click the button to delete the indexes.

On larger sites creating these indexes may take some time but you can leave the page and come back later if you need to.

Below the list of indexes you’ll find the various options for Scalability Pro.

Scalability Pro Options

The options below involve either removing, altering or microcaching certain slow parts of WordPress.

We have tried to group these as best as possible, and we have enabled some key defaults. Please read the description next to each item carefully so you know if you should activate it or not. These options are ‘safe’ in that when you change the option back, your site will be as it was before.

Each option tells you which page types to check for before & after performance. You can read more about each option below, and see Dave using the various options on our demo site with one million products in the video below.

Faster Archive Pages – Optimising WP_Query

To view your current WP_Query speed, install and activate the free Query Monitor plugin then visit your slowest archive page. Archive pages are any page which list multiple other pages. You have a list of blog articles – that’s an archive page. You might have pages of products, these are archive pages. You might have category pages listing items in that category, any list of posts is an archive page.

Remove Sort Options

This option lets you use the natural index sort order on wp_posts. When WP_Query (used by WooCommerce, Custom Post Types and Blog archives) to fetch data, it provides sorting options and these sorting options require a full sort of the retrieved data. On /shop/ or large top level categories that means a full table or index scan which can take a few seconds and thrash the disk and CPU.

Note: Using the natural sort order will disable any sort options you currently provide for your users and revert to the natural sort order (normally sort by Date DESC). If you choose this route, you should hide those sort options.

Remove Pagination

When WP_Query fetches posts/products, it also calculates the total number of matching products. This is useful to display at the top of your shop/page, e.g. Showing 1 – 50 of 650,000. However, this count requires either an index scan or table scan. Removing it, can result in faster WP_Query speed.

Depending on your setup, you may need to switch to the natural sort order above too for successful use of the indexes.

Note: Infinite scroll is recommended if you remove pagination.

Alter main query to use EXISTS rather than LEFT JOIN

WARNING – EXPERIMENTAL! (80% compatibility roughly) This option will attempt to alter the main WP_QUERY SQL call to use WHERE EXISTS rather than a LEFT JOIN.

This means that the SQL Query can avoid using a GROUP BY. This feature also removes SORTING of results.

In many cases it can cause the indexes to be used properly and can avoid table scans. On our reference server, it turns a 4.6 second query (820,000 products) to a 0.05 second query.

It definitely will not alter admin queries otherwise we might accidentally break your wp-admin pages. In future, once it’s proven resilient, it may be used to optimise wp-admin calls too.

Optimise WooCommerce Group By SQL

In some situations, WooCommerce runs a GROUP BY query even when there is no need. That causes either an index scan or a table scan, followed by a sort. If you see compatibility issues, this is one option you might try disabling, otherwise choose to optimise.

Faster Product Detail or Single Post Pages

If your product detail or single pages include widgets which run SQL which is optimised by the ‘archive’ optimisations above, then these pages will subequently be faster as a result.

There is also this option, specifically for WooCommerce Product Variations

Remove WooCommerce Ajax Variations calculations

If you have products with LOTS of variations, WooCommerce runs some slow code to count the variations on the product detail page. By disabling this, it forces some Ajax code to run when the user clicks the variation option which  is far faster.

Faster Imports – Optimising wp_postmeta

Defer term counting

Helps optimise imports and other bulk modifications by deferring term counts (recounting items per category) to a nightly job. If you are importing 10,000 products, this changes 10,000 time-intensive recounts into 1 recount.

Specific WooCommerce Optimisations

The options above all help WooCommerce, but if you have a large site or a large number of orders, then these specific WooCommerce options will help you.

Optimising WooCommerce Updates

WooCommerce has code on multiple pages which forces deletes against wp_options in relation to WooCommerce version upgrades. These delete operations are written in such a way that they cannot use indexes on wp_options. This means, if you have a lot of options (e.g. a lot of transients) that your wp-admin pages will intermittently be locked – on our reference site (820,000+ products) this intermittent shutout can last for up to 3 minutes. This in turn means these ‘updates’ never truly complete.

Enabling this option makes Scalability Pro rewrite these delete operations to be able to use the indexes and makes the delete operation virtually instant. This option is enabled by default.

Remove WooCommerce Order Summary

If you have a lot of orders, you will notice your wp-admin pages slowing down. WooCommerce runs the Order Summary dashboard script which you probably never look at or use and it can add multiple seconds to wp-admin page load, depending on your order-volume.

Order summaries are still available by going into WooCommerce -> Reports, this option just removes the dashboard widget and is enabled by default.

Faster WP-Admin Page, Post & Product Archives

Remove cast on postmeta queries

There is a CAST function applied to the ‘value’ column on many wp_postmeta queries. This cast is redundant since MySQL auto-casts where necessary, but worse, because a function is applied, mysql cannot use any indexes we create on these columns. Highly recommend you remove it.

Remove post-type category drop down

If you have a lot of items (posts, products, whatever) of one type, your wp-admin archive will be slow.

There are 3 causes – post_type dropdown, post_status count and post category dropdown.

This option lets you optimise all 3 of these dropdowns so that your archive pages will be lightning fast. You can either remove the functionality or microcache it. If cached, the dropdowns will regenerate nightly.

Faster WP-Admin Page, Post & Product Editing

Remove custom-meta select box

Page and post editing pages in wp-admin include a really badly written sql query to populate the ‘custom meta’ select box. This is redundant because if you want to edit meta, you can type in the meta name. Highly recommended you remove it.

How to tell if Scalability Pro will help your specific scalability issue

There are 3 common sources of Scalability issues in WordPress.

The first source of scalability issues is if you have a plugin (or multiple plugins) which is causing too many SQL queries to occur.

In this case, Scalability Pro has less impact and the better solution is to figure out which plugin(s) are causing too many queries to occur and replace them with alternative plugins or just remove them.

The second is that SQL queries are taking too long to return. In this case, Scalability Pro will definitely help transform the speed and scalability of your site.

The third is if you have a slow server or server-stack (you should aim for SSD, Nginx, PHP7 and PerconaDB or MariaDB as a minimum)

To check if you have too many queries or if you have slow queries, install the Query Monitor plugin and visit your slow URLs. Look at the top Query Monitor bar and look for the 4 values it provides:

Using the example above, the figures are:

• Total time including PHP and MySQL: 2.92 seconds
• Page size:  9.95MB used to generate this page
• Total MySQL time: 2.36 seconds of DB time (that’s a LOT!)
• Total MySQL queries: 54 queries (that’s not a lot)

With the example above, there are not too many queries – 54 is not a lot for WordPress. If you have more than 400 queries you should use Query Monitor to figure out the culprit plugin.

How to fix large numbers of queries

If you have a high number of queries, you should click the Query Monitor bar and ‘View Queries by Component’. Query Monitor will give you a rundown of which plugins are causing these queries.

Note: If you have a large count in one plugin (e.g. WooCommerce) it can frequently be caused by plugins or widgets for WooCommerce since those plugins or widgets will hook into WooCommerce ‘actions’ making it difficult for Query Monitor to tell you the real source of the query.

You can drill down into some queries and see which files were used to generate this query. By tracing backwards, you can find the problem plugin.

Another way is simply to disable half your plugins, check if the query count has dropped, if it has, re-enable half of those plugins until you narrow down to the problem.

If you have a low query count and your slow page speed is caused by slow queries, install and activate Scalability Pro and let it solve these issues for you.

Consider replacing your WooCommerce widgets and WooCommerce shortcodes

The default WooCommerce shortcodes cause hundreds or thousands of queries to occur as well as table scans – this will kill performance even with Scalability Pro installed.

The default WooCommerce Price Filter causes table scans – this will also kill performance even with Scalability Pro installed.

If you’re looking for replacement filters, check out our Faster Woo Widgets plugin.

Scalability Pro complements caching plugins

Scalability Pro works perfectly in harmony with all caching plugins and you should not consider Scalability Pro to be a replacement for a caching plugin. It optimises uncached performance, not cached performance. Therefore, you should get W3 Total Cache or WP Rocket or a similar page caching plugin.

Scalability Pro also works brilliantly with other performance-enhancing plugins from WP Intense:

• Faster Woo Widgets – replacement filters and widgets that are far faster than the vanilla WooCommerce widgets
• Auto Infinite Scroll – there is an advanced option in Scalability Pro to remove pagination. If you do this, you need an infinite scroll plugin. Our Auto Infinite Scroll is highly performant, comes pre-configured for WooCommerce and has the added benefit of caching scrolled archive pages in the local browser storage system. That means, when a user visits a specific product page and then clicks back, your server does not need to refresh the content and the user will return to the exact spot in their scroll down your archive.
• Super Speedy Search – if you need free text search for WooCommerce, Super Speedy Search works out the box. It optimises the default WooCommerce free-text search, but it also provides an ajax free-text widget which updates the product archive as the user types.

Scalability Pro Demo

You can see the speed of a demo/dev site here on a $20 per month Digital Ocean server with NO page-caching enabled. It has almost 1 million products, but it is also our test bed for plugin alterations so you may see the style and features available on the demo changing regularly: https://www.foundthru.com/shop/