Sample Header Ad - 728x90

Performance benchmarking numeric parameters of MariaDB with InnoDB using Sybench

1 vote
3 answers
325 views
I am conducting a performance benchmark of parameters with numeric values (i.e., innodb_buffer_pool_size, max_heap_table_size, query_cache_size, tmp_table_size) on MariaDB (version 10.11.2) using sysbench. My aim is to observe which parameters influence the performance overall. To establish this, I am using different numeric values of the parameters (i.e., different configurations of MariaDB). However, I found that none of the previously mentioned parameters does affect the performance when executing sysbench, which is supicious. In my experiments, I use one dedicated machine acting as the server and one dedicated machine acting as a client. Both the server and the client are connected via one single network switch (1GBit/s) and use a minimal installation of Debian 11. On the client, I use the oltp_read_write.lua workload from sysbench using 50000 events, 4 threads, and set time to 0. Further, my configuration file for the server is as follows:
[mysqld]
bind-address=0.0.0.0
basedir=
datadir=
skip_name_resolve=ON
server_id=1
max_connections=100
innodb_buffer_pool_size=64M
max_heap_table_size=16M
query_cache_size=0
tmp_table_size=1M
Note that I vary only the values of innodb_buffer_pool_size, max_heap_table_size, query_cache_size, and tmp_table_size throughout my experiments; all other parameters are set to their default value. I use the following different values for these parameters: - innodb_buffer_pool_size: 64M, 128M, 192M, 256M, 320M - max_heap_table_size: 1M, 16M, 32M, 48M, 64M - query_cache_size: 0, 16M, 32M, 48M, 64M - tmp_table_size: 1M, 16M, 32M, 48M, 64M I chose these small values to increase the chance of observing changes in the runtime behavior of the server in my small setup. While benchmarking, I observed a CPU load of about 25% on the server. In ensured that no other services are running except for the server or client process. Furhter, I measure the time needed until the sysbench benchmark is completed. I repeated my measurements for each configuration 5 times and had a relative standard deviation of about 0.1%. The configuration above has a runtime of about 131 seconds. Varying the values of these parameters ends up in a runtime of about 130 seconds; I only observe very slight deviations, but nothing really relevant. I expected to get larger runtime changes. I ensured that the configuration files are correctly created and used; tests with invalid values of these parameters lead to errors while starting the server. Am I missing something important while performing the benchmarks? Or are other parameters with numeric values of more relevance?
Asked by C. K. (13 rep)
Apr 20, 2023, 01:27 PM
Last activity: Aug 11, 2023, 06:23 PM