Log in
Sign up free

MySQL 8
Performance Benchmark

APR 09, 2021 • WRITTEN BY ROMAN AGABEKOV

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 the previous article, we did the benchmarking for MySQL 5.7. Configuration recommended by Releem delivered a 30% boost over default MySQL configuration.

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 quickly turning into former clients.

For 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
  • 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 recommended configuration delivers a 64% boost compared to the default configuration and a 15% boost over what was achieved with innodb_dedicated_server enabled.
WRITTEN BY ROMAN AGABEKOV
Ready to dive in?
Try Releem today for FREE! No credit card required.
Sign Up Free