How MySQL Tuning Dramatically Improves the Drupal Performance

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 Drupal Commerce. If we can showcase the value of MySQL tuning, we believe that enterprises and organisations may be keen to incorporate this practice on a larger scale.

Testing Approach

Our testing procedure for Drupal 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:

1. Deploy Drupal Commerce Kickstart.
2. Seed database with data.
3. Prepare test for JMeter.
4. Run test for 10 minutes — Ran JMeter test using the Blazemeter
5. Tune MariaDB configuration — After default configuration testing, our setup remained the same, but MariaDB was tuned for workload, server resources, and database size.
6. 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:

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 Response time, CPU Utilization and Queries per second metrics to compare the workload.

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 1Gb and 3Gb 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 is set to the default configuration with database sizes 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

Tuned Configuration for Drupal Commers Kickstart 1Gb

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

Tuned Configuration for Drupal Commers Kickstart 3Gb

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

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:

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:

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 prepared the code for seeding the database.

Conclusion

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

Responce Time (Latency) dropped between 63–97%, while CPU Utilization fell between 63–73%. Queries per second increased in every case but ranged widely between 2% for Drupal 1GB and 268% for Drupal 3GB.

With this research, we hope to showcase the value of MySQL tuning as a means to improve the performance of Drupal applications and encourage Drupal 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.