How MySQL Tuning Can Dramatically Improve WordPress Performance

We hypothesize that MySQL tuning can significantly affect the performance of WordPress. If we can showcase the value of MySQL tuning, we believe that enterprises and organizations may be keen to incorporate this practice on a larger scale.

How to Improve Application Performance

Improving application performance with tuning is best achieved with a comprehensive approach that addresses the following areas:

• Server Resources — CPU, Memory, Storage
• Software Configurations — Linux, Nginx, Php…
• Database Management System (DBMS) Configurations — MySQL, PostgreSQL
• Optimize database scheme and change indexes
• Optimize applications — Code, Queries, Architecture…

Many experienced WordPress developers don’t look at database performance tuning as an opportunity to improve the performance of their apps because they know little about this domain. They spend a lot of time optimizing the codebase, but it reaches a point where it no longer brings a valuable result for the time and energy invested. Our research on how MySQL tuning positively affects the performance of popular open-source web applications is aimed at showcasing this fact to developers.

Testing Approach

Our testing procedure lets us compare the WordPress performance before and after configuration. By first running the test with the default configuration, we gain valuable control results to compare the tuned configuration against.

For our WordPress test, we used:

• WordPress version 6.2.2 with the Twenty Twenty-Three theme (version 1.1). We installed FakerPress, WooCommerce, and WP Dummy Content Generator plugins to enrich our test scenario.
• AWS EC2 instance c5.xlarge with installed Ubuntu 22.04 as the operating system, Caddy v2.6.4 as the web server, MariaDB 10.6 set to the default configuration with a database size of 3 GB. We used the following process to prepare and test each application:

1. Deploy Application.
2. Seed the database with 3GB of dummy data using FakerPress and WP Dummy Content Generator modules.
3. Prepare test for Locust.
4. Our test duration was 2 days. To handle this longer testing period, we switched from BlazeMeter (max test duration of 20 minutes) to Locust, an open-source load-testing tool.
5. Tune MariaDB configuration after 1 day — our setup remained the same, but MariaDB was tuned for workload, server resources, and database size.

We published Locust tests, MySQL Status, and MySQL Variables during tests on GitHub.

What metrics we looked at?

The metrics we looked at during this research are:

1. Response Time ( Latency ) is the time between sending the request and processing it on the server side to the time the client receives the first byte. It is the important metric that gives you insight into server performance.
2. Queries per second is a metric that measures how many queries the database server executes per second.
3. CPU Utilization.

We collected CPU Utilization and Queries per second metrics to compare the workload.

WordPress WooCommerce

WordPress is a widely-used content management system (CMS) for building and managing websites and blogs. It powers millions of sites globally, making it an integral part of the web landscape.

WordPress offers flexible design and functionality options, allowing users to create everything from simple blogs to complex eCommerce stores, with the ability to support massive amounts of content. It is available in over 200 languages and has been downloaded over 60 million times.

WooCommerce is a popular, free, open-source plugin for WordPress that transforms the WordPress website into a fully functional e-commerce online store. WooCommerce is used by many high-traffic websites and is one of the key players in the e-commerce platform market.

MySQL Configuration

The configuration applied for WordPress WooCommerce is as follows:

Tuned Configuration for WordPress

[mysqld]
query_cache_type=1
query_cache_size=134217728
query_cache_limit=16777216
query_cache_min_res_unit=4096
thread_cache_size=0
key_buffer_size=8388608
sort_buffer_size=2097152
read_rnd_buffer_size=262144
bulk_insert_buffer_size=8388608
myisam_sort_buffer_size=8388608
innodb_buffer_pool_chunk_size=134217728
max_heap_table_size=16777216
tmp_table_size=16777216
max_connections=151
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16777216
innodb_write_io_threads=4
innodb_read_io_threads=4
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
innodb_purge_threads=4
innodb_change_buffering = changes
innodb_change_buffer_max_size = 15
thread_cache_size = 0
innodb_buffer_pool_size = 2952790016
innodb_log_file_size = 738197504
myisam_sort_buffer_size = 8388608
join_buffer_size = 8388608
table_open_cache = 2048
table_definition_cache = 1408
optimizer_search_depth = 0
thread_handling = pool-of-threads
thread_pool_size = 3

We published a detailed MySQL Performance Tuning Tutorial.

Testing Results

The WordPress WooCommerce testing results showcased sizeable performance improvements between the default and tuned configurations.

The optimization of MySQL resulted in a significant improvement in the average server Response Time, which was reduced from 860 milliseconds to a snappy 250 milliseconds.

As previously stated, we transitioned to using Locust for testing our WordPress site, which introduced an extra measure of performance — the Requests per second. This metric shows how frequently the testing tool is making requests to the website. Before we made any adjustments, the value stood at 3 requests per second. However, after fine-tuning the server settings, this figure doubled to 6 requests per second, indicating a 100% increase. This increased rate suggests that the optimized server is now capable of accommodating a larger number of users.

Average CPU utilization fell by 86%, while Queries per second increased by a whopping 106%.

The graph of the results is available below:

Response Time (Latency) (-42%), WordPress Tuned MySQL Configuration vs Default

Requests per Second (RPS) (+100%), WordPress Tuned MySQL Configuration vs Default

CPU Utilization (-37%), WordPress Tuned MySQL Configuration vs Default

Queries Per Seconds (+106%), WordPress Tuned MySQL Configuration vs Default

Community Contributors

For our testing setup and environment, we partnered with Adam Makowski, the CEO and Founder of MYFT, a firm specializing in enterprise-class WordPress Cloud Hosting and WooCommerce Cloud Hosting. Adam brings a wealth of knowledge and experiences catering to demanding clientele. We are deeply appreciative of his contributions to our endeavors.

Adam was instrumental in setting up the WordPress WooCommerce website for our tests. His expertise was invaluable in preparing the environment and seeding the database, ensuring a comprehensive and rigorous assessment of WordPress’s performance.

Conclusion

Our testing procedure, using WordPress Wocommerce, showed dramatic improvements in Response Time (Latency), CPU Utilization, and Queries per second after configuring the database server configuration.

Responce Time (Latency) dropped between 42%, while CPU Utilization fell 37%. Queries per second increased WordPress WooCommerce by 106%.

With this research, we hope to showcase the value of MySQL tuning as a means to improve the performance of WordPress applications and encourage WordPress developers to consider this practice when optimizing the performance of their websites.

Using tools like Releem, you can tune databases for optimal performance and keep them fast, secure and reliable.