Phpmyadmin Table Overview MyIsam Innodb

High Traffic E-commerce Optimisation Checklist & Tips for Developers

In E-commerce, the greatest high-traffic event every year happens during Black Friday and Cyber Monday. The days follow the US holiday of Thanksgiving and mark the start of the Christmas shopping season. Retailers make many special offers and promote them in all channels, and the website traffic surges to new highs!

Preparing your WooCommerce or Magento shop for high traffic means increasing the site’s ability to scale. These days, the website needs to be truly performant, and like Jono Alderson at Yoast says, “There is no magic bullet. We’re on a journey of 1000 tweaks“. There is always more you can do to improve the performance of your site, and most performance improvements will also improve your shop’s ability to scale. The question is, where should your start? It really depends on your specific site and setup because every site has different traffic patterns and pain points.

There are many things that can go bust when traffic increases and the most common thing that takes down sites is CPU resource exhaustion. This can be caused by several things, but it is usually either the database that gets too busy or PHP (your Magento or WooCommerce application) that spends too many resources. This article is about the basic things you should always do for any website where the ability to scale is important.

The key to great performance is to save processing time, and it is the sum of all small parts that, in the end, will deliver great performance. The most common advice people give is shortcuts, like “enable caching.” That can be a feasible thing to do – when the groundwork is done, but if you start with such advice – you will most likely run into a major crash at an unwanted time. From experience, bugs and misconfiguration are the most common causes of downtime during Black Friday.

This article gives you a step-by-step checklist you should can through, from the database to the backend application and the frontend – and which will result in a measurable better user experience, increased scalability, and better overall performance. It is all about making sure that all parts of your application use databases correctly, make correct queries, and that small bugs won’t blow up when the traffic comes.

Make the Database Ready to Scale

Basic database preparation and maintenance

A common source of slowness in the database is tables that are of an ancient table type (like MyISAM). On Black Friday the number of account registrations and orders skyrockets, and then it is important that tables are not locked while writing. InnoDB allows for row-level locking, which allows for better concurrency when updating or inserting new data. Many outdated plugins make tables with the old table type MyISAM; these need to be converted to InnoDB.

Note! Back up your database before doing this

Log in to PhpMyAdmin, and look at the overview of the table for your database.

Phpmyadmin Table Overview MyIsam Innodb

Convert all MyISAM tables to InnoDB

  1. Click on the table name of a MyISAM table
  2. Click on “Operations” in the top bar
  3. Under Table Options > Storage Engine, select “InnoDB” instead of MyISAM and click “Go”
  4. Repeat this for all MyISAM tables that you find in the table overview

Run “Analyze table” on all tables in your database

The reason why databases can retrieve a very specific piece of information very quickly is because it uses indexes to find it. Indexes can grow large and get overhead, especially in busy tables where data is deleted and inserted.

  1. In the table overview, scroll to the bottom and hit “check all.”
  2. In the dropdown menu right next to it, select “Analyze table” and hit OK.

This forces all indexes to be rebuilt and optimised, which may give you a performance improvement.

While there is no traffic on your site, you may also run “Optimize tables”

This is a very heavy job and may stall your database, so do this while your website traffic is at an absolute minimum. It will recreate tables – and will lock the database while doing so. This optimises the order and size of your tables and indexes and hence improves performance.

More Advanced Database Tasks

The Mysql Slow Log

The database keeps track of queries that take a long time to process. Usually, queries are executed in a fraction of a second, but sometimes queries can be slow. Some slow queries may be OK, for example, queries made by maintenance jobs like cron, but if a click on a product page or any other page on your site causes slow queries, this can be a killer when traffic increases.

You can usually find the slow log in /var/log/mysqld.slow.log, and if you don’t have access to it you should ask your hosting provider to check the log for you and send you any slow queries.

Query optimisation – make sure all frequent queries make proper use of indexes

Lacking the use of indexes is the most common cause of slow queries in the front-end application of your website. Queries that are made that either don’t have an index on the column from which data is requested – or that are prevented from using indexing for other reasons. Resolving these issues can be hard but extremely rewarding.

On Servebolt, we recommend all users of WooCommerce to install the Servebolt Optimizer plugin, and run its database optimisation feature. The plugin adds the most commonly lacking indexes for WordPress and can slice large parts of your time to the first byte.

Limit the number of database queries

In your application, you should be able to track what queries are executed on every page. It is normal to run a few hundred queries for a one-page load, but sometimes developers make code that escalates this into thousand or thousands. This will not, and can not perform well under any circumstances and should be fixed in your application.

The Back-end Bugs, Errors, and Warnings in Magento or WordPress

It is often the smallest thing that brings down your website. It is very difficult, if not impossible, to find these before it occurs. What you should do, is to make sure that you are best prepared to spot the killer fast when the problem arises. The way you do this is by fixing all the errors, bugs, and warnings you can find beforehand. Developers have a tendency to say, “oh, it’s just a warning so it doesn’t matter”. That may be true under normal circumstances, but we have seen excessive logging bringing down websites. Also, if the logs are full of warnings that you usually don’t care about, it will be impossible to spot the specific error that brought down your website – because the log volume under high traffic will get overwhelming.

Log files

All webservers log both what they have done and what errors occurred in their logs, independent of whether you use Nginx or Apache.

The Apache Access Log

On Servebolt, you will find the Apache access log in your site root folderlogs/AccessLog, accompanied by logs/ErrorLog. On other systems, you may find them in /var/log/httpd/ or in /var/log/apache/ or /var/log/apache2/. If you are using nginx you’ll usually find the log files in /var/log/nginx/.

The AccessLog file contains a list of all requests that are served by Apache, and their HTTP status. There are a few things you should check this log for:

  1. Amplification requests
    When a page is requested, this should not result in multiple requests to the same page. There are a variety of plugins and themes (hello WordPress) that by mistake, do such things, for example, checking the file size of an image by requesting the file from the website instead of the file system. You can spot such mistakes by tailing the log file while browsing your site. You can often check this by seeing if the IP of the server your website is hosted on is present in the logfile. If it is, examine why!
  2. Fix 403 / 500 / 503 and other errors
    403 Access Denied / Forbidden
    404 File not found
    500 Internal Server Error
    Check the logs for occurrences of these errors. Often other log files may give you hints about why an error happened.
  3. Fix 404 errors
    Missing elements may not sound like a big deal, but it makes the front end slower (the browser needs to figure out that this does not exist) – and more importantly, every 404-request is passed all the way to the backend server, bypassing any proxies, and it will spend resources which are just wasted.
  4. Limit Redirects
    Make sure you link correctly on your pages. WordPress, for instance, redirects with a trailing slash for all pages – so links should point directly there. This saves round-trips and speeds up the user experience.

The Apache Error Log

The rule of thumb is that the ErrorLog should be empty. However, some unavoidable things get logged, like requests to non-existent files (outdated files or bots probing for scripts, for example). Magento tests access for the local.xml file, which should make access denied warnings that are normal – and exactly what they should do.

When it comes to PHP errors and warnings, you should try to get rid of all of them. This can be hard when using third-party plugins – but if plugins make errors in your log, you should spend some effort and report the bug to the plugin maintainer. Specify your PHP version and plugin version and submit the error message – and someone might solve it for a future update. This is how the open-source community moves forward.

This error log is the most valuable source you have to start debugging when something irregular happens – and all PHP errors are logged here (unless logging is disabled).

Web Application Logs

For WordPress, you can turn on debug logging while you are fixing errors, but it is very important to turn this off when you are finished working. The extensive logging will slow your site down, and the log files can quickly grow very large under high load.

Magento’s logging system is extensive and often excessive and often lacks log rotation. Magento fills up the var/log/ folder with system.log exception.log, and in addition, it will record error events to var/reports/. You should do the same as with the PHP log with all of these and make sure that all error and warning events are fixed. Keep in mind that every tiny resource saved will benefit your site’s scalability, performance, and end-user experience.

Cron Jobs

Review your cron jobs, and know how often they run, what they do, why they do it, how long they run, and how much resources they consume. It is common to have cron jobs that fire every minute and run tasks that are scheduled. While it makes sense to reindex a catalog at 00:01 most days during the year, this is a very bad plan if your super sale starts at the same time.

Also, overlapping cron jobs is a common issue – especially with Magento. Make sure you plan the schedules so that you don’t have two cron tasks doing the heavy lifting on the same database tables simultaneously. Reindexing products while updating prices is a bad idea, updating prices first and then reindexing will most likely finish a lot earlier and produce a steadier catalog. Spending fewer resources on processing cron jobs, means that your server can handle more transactions!

Consider making a plan to postpone or manually run cron jobs on days you expect high traffic, because maintenance jobs often are database heavy and may cause a queue to build up, and hence possibly bring the site down.

WordPress users should make sure to Run Cron as real cron jobs. It is common to either rely on WordPress’ built-in cron (which runs in the browser) or to run the cron jobs using wget. Cron jobs should be executed directly on the server, without being routed out over HTTP, and back through the webserver. Running cron jobs directly with either wp-cli or php will spend less resources, be more efficient and less prone to errors.

Increasing your traffic capacity with multiples

If you follow the steps above, your shop (if none of this work ever has been done before) should be able to double or triple the ability to scale the traffic, without upgrading the hosting. This is the most reasonable thing to invest in for almost all shops, because stores that have to scale the back-end horizontally, always are required to be well-functioning. It is simply not possible to scale websites that are poorly built or have a lot of errors.

The cost of scaling will also escalate the moment you leave a single-server environment. Your largest costs are not the added servers – but the increased complexity of a fragmented setup will also increase your development costs, maintenance costs and training costs. One of Norway’s busiest WooCommerce stores is still running on a single webserver setup, handling hundreds of transaction on normal days and thousands during the Black Friday and Cyber Monday. Their developer is completely obsessed with optimisation, and still has hosting costs in below $1000/month, while the shop processes orders exceeding $1 million in a normal month.

At Servebolt, all WooCommerce plans and Magento plans will easily scale to 500+ simultaneous users. If the shop is well-functioning and free of bugs, we have had shops that on our 4GB plans have handled more than 2500 simultaneous users online without any negative performance impact.

Get the love for fixing bugs, and your life will change!

Front End – HTML, CSS and Javascript

When the back end is well-functioning, it is time to start addressing the end-user performance. There are a thousand things you can do to improve the front end performance too, but like with the back-end, the most rewarding work in terms of performance will be to apply the same principles. Waste fewer resources!

The importance of error-free HTML, CSS, and JS should not be underestimated. Your page may look like it should, but the browser may spend way too many resources rendering it. Or, it looks nice in your browser – but the visitor using another browser or device might not have a browser with the same capabilities as yours.

The best way to make sure that your front-end behaves as you have planned is to make sure that it is free of errors. The browser spends most of its time rendering your page. Any error, ambiguity, warning, or duplication will take extra time to resolve compared to a bloat-free, bug-free site.

Clean up and get rid of bloat

We usually start our optimisation work with cleaning up. Uninstall and remove everything that you do not need. If you need something later, you can always reinstall it – but if you’re not using it now, erase it. All extra code (js, CSS, HTML, or PHP) will spend some sort of resources.

People totally underestimate the importance of fixing bugs. A bug-free website will render nicer, display faster, and be interactive sooner. If you haven’t tried this recipe before, you should get right to it – because the feel of a well-functioning site is just amazing. Every click is a pleasure, and waiting time is history.

Start working by cleaning up:

  1. Uninstall unused or unnecessary plugins
  2. Remove unused external scripts
  3. Remove unused/unnecessary local CSS and JS files added by plugins and themes
  4. Rethink step 2, do you really need all those scripts? Check out this article for some motivation
  5. Get rid of the bloat in your HTML

When your code base is reduced, and the loaded assets are fewer, it’s time to do the hard work:

Fix all bugs

  1. Fix HTML Bugs (use W3C Validator to check your various page types and make sure it validates)
  2. Fix CSS Bugs (use CSS Validator)
  3. Fix JS Bugs (check the error console in your browser)
  4. Check your console for mixed content, missing elements, or any other errors – and fix them.

If you have come this far, and actually have done this work – you are likely obsessing, which is the only way to get really good at web performance. Your website should be well-functioning and a lot more scalable than before you started.

But what about full-page caching?

Full-page caching is for scaling. And it is a good idea to make use of full-page caching to offload the highest traffic peaks. However, you should not do it just yet!

First, you should make a qualified guess of the traffic peak you will get. How many page views did you get the busiest minutes last year? Are your marketing lists larger this year? Will you increase the marketing spending? Sum, multiply, and make the best guesstimate of what your traffic peak will be this year. With the guesstimate of your traffic peak, you can estimate how many visitors your current setup will handle. Have a chat with your hosting provider, and make sure that the hosting you have will scale at least to your estimated peak.

Make sure to switch off server features like developer mode and make sure that caching of static elements is properly enabled. On Servebolt, that’s a button in the control panel. Oh, almost forgot about that full-page caching part. Since your website is now super well functioning, it will most likely get through the traffic peak without full-page caching. However, adding caching will free up extra server resources and take off the worst traffic peaks, so yes – if it’s easy, go for it.

Scalability can be tricky sometimes to solve. There are so many parameters involved that require constant monitoring and improvements until optimum results are produced. We felt the same itch while helping Servebolt customers that later became the reason for developing Accelerated Domains. It is a service that helps with scaling your eCommerce store without adding more servers or adding complexities like Load Balancers. It manages everything in a set-and-forget manner.

Good luck!

This article was first published on November 7, 2017, and has been reworked for 2018.