How MySQL Configuration Impacts the Performance of Web Applications
JAN 26, 2022 • WRITTEN BY ROMAN AGABEKOV
MySQL Configuration tuning is an important component of database management implemented by database professionals and administrators. It aims to configure the database to suit its hardware and workload. But beyond the database management sphere, the usefulness of MySQL Configuration tuning is largely ignored.
We hypothesize that MySQL tuning can significantly affect the performance of web apps. 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.
We hypothesize that MySQL tuning can significantly affect the performance of web apps. 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.
- Testing Setup
- MySQL Configuration
- Testing Results
- Community Contributors
4. Drupal Commerce Kickstart
- Testing Setup
- MySQL Configuration
- Testing Results
- Community Contributors
5. WordPress WooCommerce
- Testing Setup
- MySQL Configuration
- Testing Results
- Community Contributors
6. PrestaShop
- Testing Setup
- MySQL Configuration
- Testing Results
- Community Contributors
7. Conclusion
- MySQL Configuration
- Testing Results
- Community Contributors
4. Drupal Commerce Kickstart
- Testing Setup
- MySQL Configuration
- Testing Results
- Community Contributors
5. WordPress WooCommerce
- Testing Setup
- MySQL Configuration
- Testing Results
- Community Contributors
6. PrestaShop
- Testing Setup
- MySQL Configuration
- Testing Results
- Community Contributors
7. Conclusion
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…
Testing Approach
Our testing procedure for each web app lets us compare the app's performance before and after configuration using seeded data. By running the test with the default configuration first, we gain valuable control results to compare the tuned configuration against.
We used the following process to prepare and test each application:
We published JMeter tests, MySQL Status, and MySQL Variables during tests on GitHub.
We used the following process to prepare and test each application:
- Deploy Application.
- Seed database with data.
- Prepare test for JMeter/Locust.
- Run test for 10 minutes – Ran JMeter test using the Blazemeter/Locust performance testing platform.
- Tune MariaDB configuration – After default configuration testing, our setup remained the same, but MariaDB was tuned for workload, server resources, and database size.
- Re-run test – Repeated the JMeter test using Blazemeter for the tuned configuration.
We published JMeter tests, MySQL Status, and MySQL Variables during tests on GitHub.
What metrics we looked at?
The metrics we looked at during this research are:
We collected CPU Utilization and Queries per second metrics to compare the workload.
- 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.
Laravel Aimeos
Aimeos Laravel is a popular e-commerce web app framework for creating online shops, marketplaces, and B2B apps. With Aimeos, users can create API-first eCommerce shops for Laravel that can scale to support over 1 billion items. It's available in over 30 languages and has over 300,000 installs.
Testing Setup
To test Aimeos, we started the test with ten users, but we had to decrease the number of users because we couldn't finish the test with the default configuration.
We seeded the database with 500 Mb data.
Our test duration was 10 minutes.
We used:
AWS EC2 instance c5.xlarge with installed Debian 11 as the operating system, Apache as a web server, MariaDB 10.5 set to the default configuration with database size 500 MB.
We seeded the database with 500 Mb data.
Our test duration was 10 minutes.
We used:
AWS EC2 instance c5.xlarge with installed Debian 11 as the operating system, Apache as a web server, MariaDB 10.5 set to the default configuration with database size 500 MB.
MySQL Configuration
The configuration used for Aimeos Laravel is as follows:
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
max_allowed_packet=1073741824
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
innodb_buffer_pool_size=805306368
max_heap_table_size=16777216
tmp_table_size=16777216
join_buffer_size=8388608
max_connections=151
table_open_cache=2048
table_definition_cache=1408
innodb_flush_log_at_trx_commit=1
innodb_log_file_size=201326592
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
optimizer_search_depth=0
thread_handling=pool-of-threads
thread_pool_size=2
query_cache_size=134217728
query_cache_limit=16777216
query_cache_min_res_unit=4096
thread_cache_size=0
key_buffer_size=8388608
max_allowed_packet=1073741824
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
innodb_buffer_pool_size=805306368
max_heap_table_size=16777216
tmp_table_size=16777216
join_buffer_size=8388608
max_connections=151
table_open_cache=2048
table_definition_cache=1408
innodb_flush_log_at_trx_commit=1
innodb_log_file_size=201326592
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
optimizer_search_depth=0
thread_handling=pool-of-threads
thread_pool_size=2
Testing Results
The Aimeos Laravel testing results showcased dramatic 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 1.4 seconds to under 800 milliseconds.
Response Time ( Latency ) fell by 42% and average CPU utilization by 86%, while Queries per second increased by an incredible 291%, from 12 to 35 queries per second.
The graph of the results is available below:
The optimization of MySQL resulted in a significant improvement in the average server Response Time, which was reduced from 1.4 seconds to under 800 milliseconds.
Response Time ( Latency ) fell by 42% and average CPU utilization by 86%, while Queries per second increased by an incredible 291%, from 12 to 35 queries per second.
The graph of the results is available below:
Latency Aimeos Tuned MySQL Configuration vs Default
CPU Utilization (%), Aimeos Tuned MySQL Configuration vs Default
Queries Per Seconds, Aimeos Tuned MySQL Configuration vs Default
Community Contributors
We teamed up with Laravel developers Gevorg Mkrtchyan and Sergey Sinitsa from Initlab company to investigate this line of questioning and are very grateful for their expertise.
Sergey deployed Aimeos, and Gevorg prepared code for seeding the database.
Sergey deployed Aimeos, and Gevorg prepared code for seeding the database.
Drupal
Drupal is a free and open-source content management system (CMS) written in the PHP programming language. It is used to build websites and web applications of all sizes, from small personal blogs to large corporate and government sites.
Drupal Commerce Kickstart is a distribution of the Drupal, specifically tailored for e-commerce websites. It provides a pre-configured set of modules and features that make it easy to create a fully functional online store.
Drupal Commerce Kickstart is a distribution of the Drupal, specifically tailored for e-commerce websites. It provides a pre-configured set of modules and features that make it easy to create a fully functional online store.
Testing Setup
To test Drupal Commerce Kickstart, we tested with 20 users and prepared the test with around 20 page views and cart actions.
We seeded the two databases with 1 Gb and 3 Gb data.
Our test duration was 10 minutes.
We used:
AWS EC2 instance c5.xlarge with Debian 11 as the operating system, nginx and php-fpm as a web server, MariaDB 10.5 set to the default configuration with database size 1GB AND 3GB.
By repeating the tests with databases of two different sizes, we can see how tuned configurations perform at different scales.
We seeded the two databases with 1 Gb and 3 Gb data.
Our test duration was 10 minutes.
We used:
AWS EC2 instance c5.xlarge with Debian 11 as the operating system, nginx and php-fpm as a web server, MariaDB 10.5 set to the default configuration with database size 1GB AND 3GB.
By repeating the tests with databases of two different sizes, we can see how tuned configurations perform at different scales.
MySQL Configuration
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
max_allowed_packet=1073741824
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
innodb_buffer_pool_size=1342177280
max_heap_table_size=25165824
tmp_table_size=25165824
join_buffer_size=8388608
max_connections=151
table_open_cache=2048
table_definition_cache=1408
innodb_flush_log_at_trx_commit=1
innodb_log_file_size=335544320
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
optimizer_search_depth=0
thread_handling=pool-of-threads
thread_pool_size=2
query_cache_size=134217728
query_cache_limit=16777216
query_cache_min_res_unit=4096
thread_cache_size=0
key_buffer_size=8388608
max_allowed_packet=1073741824
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
innodb_buffer_pool_size=1342177280
max_heap_table_size=25165824
tmp_table_size=25165824
join_buffer_size=8388608
max_connections=151
table_open_cache=2048
table_definition_cache=1408
innodb_flush_log_at_trx_commit=1
innodb_log_file_size=335544320
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
optimizer_search_depth=0
thread_handling=pool-of-threads
thread_pool_size=2
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
max_allowed_packet=1073741824
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
innodb_buffer_pool_size=4026531840
max_heap_table_size=16777216
tmp_table_size=16777216
join_buffer_size=8388608
max_connections=151
table_open_cache=2048
table_definition_cache=1408
innodb_flush_log_at_trx_commit=1
innodb_log_file_size=1006632960
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
optimizer_search_depth=0
thread_handling=pool-of-threads
thread_pool_size=2
query_cache_size=134217728
query_cache_limit=16777216
query_cache_min_res_unit=4096
thread_cache_size=0
key_buffer_size=8388608
max_allowed_packet=1073741824
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
innodb_buffer_pool_size=4026531840
max_heap_table_size=16777216
tmp_table_size=16777216
join_buffer_size=8388608
max_connections=151
table_open_cache=2048
table_definition_cache=1408
innodb_flush_log_at_trx_commit=1
innodb_log_file_size=1006632960
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
optimizer_search_depth=0
thread_handling=pool-of-threads
thread_pool_size=2
Testing Results
Because Drupal underwent two rounds of testing with different-sized databases, we’ve separated the results into two sections so it’s easy to review and track the results of each round:
1GB Drupal Commerce Database
The Drupal Commerce Kickstart application showed marked improvements in latency and CPU utilization when comparing the default configuration to the tuned configuration.
The optimization of MySQL significantly reduced the average server Response Time, from 150 milliseconds to 60 milliseconds.
Response Time ( Latency ) fell 63% and remained highly stable with the tuned configuration. CPU utilization was reduced by a dramatic 63%. Queries per second increased by a smaller factor, at only 2%, from 692 to 707 queries.
The graph of the results is available below:
The optimization of MySQL significantly reduced the average server Response Time, from 150 milliseconds to 60 milliseconds.
Response Time ( Latency ) fell 63% and remained highly stable with the tuned configuration. CPU utilization was reduced by a dramatic 63%. Queries per second increased by a smaller factor, at only 2%, from 692 to 707 queries.
The graph of the results is available below:
Latency, Drupal 1GB Tuned MySQL Configuration vs Default
CPU Utilization (%), Drupal 1GB Tuned MySQL Configuration vs Default
Queries Per Second, Drupal 1GB Tuned MySQL Configuration vs Default
3GB Drupal Commerce Database
The Drupal Commerce Kickstart application showed even better improvements in latency and CPU utilization for the 3GB database when comparing the default configuration to the tuned configuration.
The optimization of MySQL led to a substantial decrease in the average server Response Time, from 8 seconds to less than 200 milliseconds.
Response Time (Latency) fell by 97% and remained highly stable with the tuned configuration. CPU Utilization was also reduced by 73%.
And while Queries per second only increased by 2% with the 1GB database, we observed a 268% increase with the tuned configuration for the 3GB database, from 760 to 2040 queries per second.
The graph of the results is available below:
The optimization of MySQL led to a substantial decrease in the average server Response Time, from 8 seconds to less than 200 milliseconds.
Response Time (Latency) fell by 97% and remained highly stable with the tuned configuration. CPU Utilization was also reduced by 73%.
And while Queries per second only increased by 2% with the 1GB database, we observed a 268% increase with the tuned configuration for the 3GB database, from 760 to 2040 queries per second.
The graph of the results is available below:
Latency, Drupal 3GB Tuned MySQL Configuration vs Default
CPU Utilization (%), Drupal 3GB Tuned MySQL Configuration vs Default
Queries Per Second, Drupal 3GB Tuned MySQL Configuration vs Default
Community Contributors
We collaborated with Gevorg Mkrtchyan, a Drupal developer from Initlab, to explore this topic and appreciate their expertise. Gevorg set up and prepare the code for populating the database.
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 more than 60 million times.
WooCommerce is a popular, free, open-source plugin for WordPress that transforms 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.
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 more than 60 million times.
WooCommerce is a popular, free, open-source plugin for WordPress that transforms 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.
Testing Setup
For our WordPress test, we used WordPress version 6.2.2 with the Twenty Twenty-Three theme (version 1.1). We installed plugins FakerPress, WooCommerce, and WP Dummy Content Generator to enrich our test scenario.
We seeded the database with 3GB of dummy data using modules FakerPress and WP Dummy Content Generator.
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.
We used:
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 database size 3 GB.
We published the Locust results, MySQL Status, and MySQL Variables on GitHub.
We seeded the database with 3GB of dummy data using modules FakerPress and WP Dummy Content Generator.
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.
We used:
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 database size 3 GB.
We published the Locust results, MySQL Status, and MySQL Variables on GitHub.
MySQL Configuration
The configuration applied for WordPress WooCommerce is as follows:
[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
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
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:
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
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.
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.
PrestaShop
PrestaShop is a specialized, free, and open-source e-commerce platform developed in PHP, aimed at providing businesses, from startups to large enterprises, with the infrastructure to launch, manage, and scale their online stores.
Offering a rich selection of customizable themes and a comprehensive suite of e-commerce features, PrestaShop supports a wide array of functionalities, such as product management, secure payments, shipping, and inventory management, directly out of the box.
Offering a rich selection of customizable themes and a comprehensive suite of e-commerce features, PrestaShop supports a wide array of functionalities, such as product management, secure payments, shipping, and inventory management, directly out of the box.
Testing Setup
The version of PrestaShop being utilized for testing is 8.1.2, which is specifically optimized for use with PHP 8.1. Detailed guidance on installing this version of PrestaShop can be found in the official documentation.
We installed the PrestaShop with the default installation with demo data, export all demo products and import it as a new products multiples times, then we install multiples languages for the e-commerce.
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.
We used:
GCP Compute Engine instance c2-standard-4, equipped with Debian Bullseye for the operating system, Apache for the web server, and MariaDB 10.5 in its default configuration with a database size of 1GB.
We installed the PrestaShop with the default installation with demo data, export all demo products and import it as a new products multiples times, then we install multiples languages for the e-commerce.
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.
We used:
GCP Compute Engine instance c2-standard-4, equipped with Debian Bullseye for the operating system, Apache for the web server, and MariaDB 10.5 in its default configuration with a database size of 1GB.
MySQL Configuration
[mysqld]
innodb_change_buffering=none
innodb_change_buffer_max_size=25
innodb_adaptive_flushing_lwm=25.000000
innodb_max_dirty_pages_pct=70.000000
innodb_autoextend_increment=48
thread_stack=299008
transaction_prealloc_size=8192
thread_cache_size=172
max_connections=172
query_cache_type=1
query_cache_size=134217728
query_cache_limit=33554432
query_cache_min_res_unit=4096
key_buffer_size=8388608
max_heap_table_size=16777216
tmp_table_size=16777216
innodb_buffer_pool_size=3355443200
innodb_log_file_size=25165824
innodb_file_per_table=1
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
join_buffer_size=8388608
table_open_cache=2048
table_definition_cache=512
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16777216
innodb_write_io_threads=4
innodb_read_io_threads=4
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
optimizer_search_depth=0
innodb_purge_threads=4
thread_handling=one-thread-per-connection
thread_pool_size=6
innodb_change_buffering=none
innodb_change_buffer_max_size=25
innodb_adaptive_flushing_lwm=25.000000
innodb_max_dirty_pages_pct=70.000000
innodb_autoextend_increment=48
thread_stack=299008
transaction_prealloc_size=8192
thread_cache_size=172
max_connections=172
query_cache_type=1
query_cache_size=134217728
query_cache_limit=33554432
query_cache_min_res_unit=4096
key_buffer_size=8388608
max_heap_table_size=16777216
tmp_table_size=16777216
innodb_buffer_pool_size=3355443200
innodb_log_file_size=25165824
innodb_file_per_table=1
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
join_buffer_size=8388608
table_open_cache=2048
table_definition_cache=512
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16777216
innodb_write_io_threads=4
innodb_read_io_threads=4
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
optimizer_search_depth=0
innodb_purge_threads=4
thread_handling=one-thread-per-connection
thread_pool_size=6
Testing Results
The performance evaluation of PrestaShop revealed significant enhancements post-optimization.
Notably, the response time experienced a remarkable decrease from 610ms to 370ms, marking a 39% improvement. Similarly, the website's ability to handle requests surged by 50%, from 8 to 12 requests per second.
CPU utilization saw a reduction, moving from full capacity at 100% down to 90%, a 10% decrease in load. Most impressively, MySQL's query processing capability jumped from 5078 to 7816 queries per second, a substantial increase of 53%.
We recorded screencast to show the impact on Response Time after applying recommended MySQL configuration.
Notably, the response time experienced a remarkable decrease from 610ms to 370ms, marking a 39% improvement. Similarly, the website's ability to handle requests surged by 50%, from 8 to 12 requests per second.
CPU utilization saw a reduction, moving from full capacity at 100% down to 90%, a 10% decrease in load. Most impressively, MySQL's query processing capability jumped from 5078 to 7816 queries per second, a substantial increase of 53%.
We recorded screencast to show the impact on Response Time after applying recommended MySQL configuration.
Prestashop Tuned MySQL Configuration vs Default
Graphs of the testing results are available below:
Response Times (ms), PrestaShop Tuned MySQL Configuration vs Default
CPU Utilization (%), PrestaShop Tuned MySQL Configuration vs Default
Queries Per Seconds, PrestaShop Tuned MySQL Configuration vs Default
Community Contributors
In setting up our test environment, we collaborated closely with Giuseppe Pompeo, CEO of Multiservicios Austral. Giuseppe's firm is at the forefront of digital services in Chile, particularly known for its expertise in e-commerce through PrestaShop. Their team, recognized and certified by PrestaShop, has been delivering specialized and professional services in this field for over six years.
Giuseppe's involvement was crucial in getting our server ready, launching our website, and populating it with initial data. His vast experience with cloud infrastructure and a keen focus on optimizing e-commerce platforms like PrestaShop played a significant role in ensuring our tests ran smoothly. We're immensely thankful for the knowledge and efficiency Giuseppe brought to our project.
Giuseppe's involvement was crucial in getting our server ready, launching our website, and populating it with initial data. His vast experience with cloud infrastructure and a keen focus on optimizing e-commerce platforms like PrestaShop played a significant role in ensuring our tests ran smoothly. We're immensely thankful for the knowledge and efficiency Giuseppe brought to our project.
Conclusion
Our testing procedure, using Aimeos Laravel, Drupal Commerce Kickstart, WordPress WooCommerce and Prestashop, 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-97%, while CPU Utilization fell between 37-86%. Queries per second increased in every case but ranged widely between 2% for Drupal 1GB and 268% and 291% and 106% respectively, for Drupal 3GB and Aimeos Laravel 500MB and WordPress WooCommerce.
Responce Time (Latency) dropped between 42-97%, while CPU Utilization fell between 37-86%. Queries per second increased in every case but ranged widely between 2% for Drupal 1GB and 268% and 291% and 106% respectively, for Drupal 3GB and Aimeos Laravel 500MB and WordPress WooCommerce.
In conclusion, MySQL tuning is an essential aspect of database management that can have a significant impact on the performance of web applications. Poorly performing web applications can lead to increased page load times, slow request handling, and a poor user experience, which can negatively affect SEO and sales. By optimizing the performance of web apps with MySQL tuning, enterprises and organizations can increase sales, pageviews, conversion rates, and SEO rankings.
With this research, we hope to showcase the value of MySQL tuning as a means to improve the performance of web applications and encourage developers to consider this practice when optimizing the performance of their apps.
Using tools like Releem, databases can be quickly and easily configured for optimal performance, reducing the burden on software development teams.
With this research, we hope to showcase the value of MySQL tuning as a means to improve the performance of web applications and encourage developers to consider this practice when optimizing the performance of their apps.
Using tools like Releem, databases can be quickly and easily configured for optimal performance, reducing the burden on software development teams.
WRITTEN BY ROMAN AGABEKOV