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:
- Deploy Application.
- Seed the database with 3GB of dummy data using FakerPress and WP Dummy Content Generator modules.
- Prepare test for Locust.
- 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.
- 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:
- 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.
- Queries per second is a metric that measures how many queries the database server executes per second.
- 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.
Top comments (2)
I would like to make some clarifications having tried this configuration first hand
If you use MySQL 8.x, you need to remove these:
The
thread_pool_sizeparameter is only available in commercial versions of MySQLThere are two definitions of
thread_cache_size(we can do it only once)However, some values may vary depending on the server
for example, I have a 6 core with 16gb of ram and I did
Having a lot of ram we can push up to 70% of it
Setting
thread_cache_size to0, each new connection will have to create a new thread, which can slow down the system if there are many connections. A higher value reduces the cost of creating threads for new connections. For a system with 6 cores, a value like 8 or 16 could improve performance:The values of
tmp_table_sizeandmax_heap_table_sizeare set to 16MB. If your application uses many large temporary tables, you may want to increase these values.The
table_open_cachevalue is set to 2048 andtable_definition_cacheto 1408. You can increase these values if you have a lot of tables and see a lot of “table_open_cache misses” in your MySQL status logSetting
innodb_thread_concurrency = 0allows MySQL to dynamically manage thread concurrency, which is often ideal on multi-core servers. On a 6-core server if you are experiencing concurrency bottlenecks, you could experiment with a value of 2 * the number of cores (12 in this case):I will do some testing to see the differences
if you have any other suggestions or can do a test with this configuration let me know
Thanks for your comment.
Mostly we'd like to show that default conf not so good. This configuration was built for:
I agree you should make new configuration for your server and workload. You can do it manually or using automation like MySQLTuner or Releem.
Do you mean to make a test for WooCommerce, or other WordPress plugin?