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:
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.