MySQL 8 Performance Benchmark

In this article, we benchmark the performance of MySQL 8 default configuration vs. innodb_dedicated_server enabled configuration vs. the configuration recommended by Releem.

In most cases, databases are instrumental to technological setups and their operation, so their performance and overall efficiency are of critical importance. If your database fails to deliver well, the entire application runs poorly, and poor application performance makes clients sour and and quickly turning into former clients.

For MySQL 8 benchmarking purposes, we used SysBench utility and server with the following configuration:

• Operating System: Debian 10;

• CPU: 2 cores;

• RAM: 4GB;

• MySQL version: 8.0.23

To prepare the database, we ran following command:

sysbench --db-driver=mysql --mysql-user=root --mysql-password='root' --mysql-db=test --mysql_storage_engine=innodb --table_size=10000000 --tables=2 --threads=2 /usr/share/sysbench/oltp_read_write.lua prepare

After that we ran Releem agent to get the recommended configuration.

We compared performance of MySQL 8 server running under different configurations:

• default

• innodb_dedicated_server enabled

• Recommended configuration by Releem

Default MySQL 8 configuration

The default configuration allows launching a MySQL server on a virtual machine with approximately 512MB of RAM.

Innodb_dedicated_server enabled

MySQL 8.0 introduced innodb_dedicated_server. When innodb_dedicated_server is enabled enabled, InnoDB automatically configures the following variables:

• innodb_buffer_pool_size

• innodb_log_file_size

• innodb_log_files_in_group

• innodb_flush_method

Based on the system’s RAM, InnoDB automatically sizes the following:

• innodb_buffer_pool_size:Keep in mind that innodb_dedicated_server allocates 80% of physical RAM for InnoDB buffer pool. - Less than 1G: 128M(default value — if innodb_dedicated_server is off)- Less than or Equal to 4G: Detected Physical RAM * 0.5- Greater than 4G: Detected Physical RAM * 0.75

• innodb_log_file_size:For the log record size, InnoDB needs access to one hour of records, which would enable it to enhance composing the retry log to the disk.- Less than 1G: 48M(default value — if innoDB_dedicated_server is off)- Less than or Equal to 4G: 128M- Less than or Equal to 8G: 512M- Less than or Equal to 16G: 1024M- Greater than 16G: 2G

Recommended configuration by Releem

The configuration recommended by Releem that we got after preparing database:

[mysqld] 
thread_cache_size = 16 ### Previous value : 9 
key_buffer_size = 8388608 ### Previous value : 8388608 
max_allowed_packet = 1073741824 ### Previous value : 67108864 
sort_buffer_size = 262144 ### Previous value : 262144 
read_rnd_buffer_size = 262144 ### Previous value : 262144 
bulk_insert_buffer_size = 8M ### Previous value : 8388608 
myisam_sort_buffer_size = 8388608 ### Previous value : 8388608 
innodb_buffer_pool_instances = 2 ### Previous value : 1 
innodb_buffer_pool_size = 3221225472 ### Previous value : 134217728 
max_heap_table_size = 256M ### Previous value : 16777216 
tmp_table_size = 256M ### Previous value : 16777216 
join_buffer_size = 8M ### Previous value : 262144 
max_connections = 151 ### Previous value : 151 
table_open_cache = 4096 ### Previous value : 4000 
table_definition_cache = 2432 ### Previous value : 2000 
innodb_flush_log_at_trx_commit = 2 ### Previous value : 1 
innodb_log_file_size = 402653184 ### Previous value : 50331648 
innodb_write_io_threads = 4 ### Previous value : 4 
innodb_read_io_threads = 4 ### Previous value : 4 
innodb_file_per_table = 1 ### Previous value : ON 
innodb_flush_method = O_DIRECT ### Previous value : fsync 
innodb_thread_concurrency = 0 ### Previous value : 0 
innodb_buffer_pool_chunk_size = 134217728 ### Previous value : 134217728

MySQL 8 Configurations Benchmarking

Sometimes you must simulate a hefty database workload to get a concise and clear scenario for benchmarking. Performance can differ depending on various factors, and a gain of a few percentage points may not be perceived as a clear win. Moreover, tested in another environment, the configuration may deliver very different results.

Our benchmarking relied on code from github repository.

We ran SysBench tests using different thread counts with the following parameters:

• table_size=10000000

• tables=2

• time=60.

Benchmarking Results

Following diagrams show results of the tests:

1. Reads and writes counts for three MySQL configurations with two threads

2. Read operations count for three MySQL configurations depending on threads count.

3. Write operation count for three MySQL configurations depending on threads count.

Conclusion

The performance benchmark results show that Releem ( MySQL Performance Tuning as a Service ) recommended configuration delivers a 64% boost compared to the default configuration and a 15% boost over what was achieved with innodb_dedicated_server enabled.