Site performance is not only affected by code quality and content optimisation - they can be meaningless if the settings on the server suffer.
We at Initlab have long been involved in server configuration, and we know very well how much it means for website performance. To prove how important it is to work on server configuration optimisation, we decided to conduct an experiment that will clearly show how settings affect site speed and other important parameters.
Our experts have deployed 3 online shops and checked how much the database configuration has affected their performance. We will report the results of our study in this article.
Slow site: How can I adjust MySQL?
A fast website is important on all fronts: promotion, reputation, speed of business processes, etc. Many people fixate only on optimising the site itself: clean up the code, lighten the files, optimise the layout. But what if all this is in order, but site visitors are still waiting a critical 3-5 seconds for each page?
It could be a matter of configuring the databases.
Database (DB) - a place to store information about products, website users, etc.
A DBMS (database management system), in our case MySQL, is a database management tool.
When you install MySQL on the server you are greeted by a configuration file with the default database "power" settings: how much memory it can use, how much cache it can use, etc. Generally there are hundreds of these parameters, but about a dozen are taken into account here. This base file is designed for a small site and a small amount of memory.
The task of our administrators is to reconfigure important server parameters for the client's site and the planned load: allocate more RAM for the database, half for PHP-tasks, etc. Thanks to their experience they know the rules of how to configure these parameters in an optimal way.
If the configuration is left as it is or set up incorrectly, the database will be slow and slow, and with it the website.
Imagine you're running the game on a powerful computer, but the creators have adjusted the basic settings so that it runs even on weak hardware. It seems to run fine, but it doesn't max out. Exactly the same story with an unconfigured database configuration.
Proper configuration will speed up websites and web services on any CMS and CMF: Drupal, Laravel, Bitrix, Wordpress, Joomla - any of them are based on databases.
There is no difference in the "age" of a website, web service or application. If no one has made such adjustments, performance will change for the better, whether the project is just starting up or has been in existence for a long time.
Our experiment
We tested a typical online shop on Drupal 10 + Drupal Commerce Kickstart and a shop on Aimeos (Laravel). We didn't optimize the online shops' settings, because we only tested the influence of the database settings on their performance.
For those who understand the technical details and want to know more, please see the full article in English. Here is a condensed version with basic figures for a wide range of readers.
The Releem team has invited us as experts for this study. Releem's task is to find an optimal database configuration that is at least better than the default one. This service also automatically analyzes the state of MySQL, the server, the current load and can recommend to engineers a configuration to improve the performance of the database.
In this experiment, we just showed that the basic configuration "fails" even a poorly populated website. These are the figures we looked at:
Response time , also known as time to first byte, is the time between sending a request and its processing by the server until the client receives the first byte.
The number of requests per second is how many pages the web server can send to the client per second.
CPU load. We have collected CPU load figures on the server to compare the workload.
We carried out the test before and after the setting. Its essence is that it is as if a conditional 20 people come to the site and start using the site at the same time. At this point, its performance is measured.
Aimeos Laravel test results
Testing has shown a significant improvement in performance between default configurations and those configured with Releem:
- the average server response time was reduced from 1.4 seconds to less than 800 milliseconds;
- the response time (latency) has been reduced by 42%;
- the average CPU load is reduced by 86%;
- the number of requests per second has increased by an incredible 291%, from 12 to 35 requests per second.
The size of the database for the test is 500 MB. The duration of the test is 10 minutes.
Drupal Commerce Kickstart test results
Drupal has gone through two rounds of testing with different sized databases.
1 GB database
Testing has shown noticeable improvements in latency and CPU utilisation when comparing the default configuration to that configured with Releem:
- the average server response time was reduced from 150 milliseconds to 60 milliseconds;
- response time (latency) was reduced by 63% and remained very stable with the changed configuration;
- CPU utilisation has been reduced by an impressive 63%;
- the number of requests per second increased by 2%, from 692 to 707.
3 GB database
Even better results were obtained for a 3GB database:
- the average server response time has dropped significantly: from 8 seconds to less than 200 milliseconds;
- response time (latency) was reduced by 97% and remained very stable with the changed configuration;
- CPU utilisation has also decreased by 73%;
- the number of requests per second increased by 268% (!) - from 760 to 2040 requests per second (although in the 1GB variant the difference was only 2%).
The duration of the test is 10 minutes for both databases.
These are the results we got. Of course, some of the gains were small and some were fantastic. But our original hypothesis was not to achieve specific figures. We wanted to show that even small adjustments to the parameters have a significant effect. Not to mention that all the results can be honed to perfection by the hands of experienced admins.
Conclusion
Tuning MySQL will definitely give you a performance boost, as we have tried to show you.
At Initlab, we know the value of every second of waiting, and we do everything we can make it as easy and fast as possible for our clients to interact with their websites. Setting up MySQL is guaranteed to benefit projects that never bothered with it before.
If you need to test and upgrade the performance of your MySQL database for your project, please contact us.
Add new comment