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
Last activity: Aug 11, 2023, 06:23 PM