1

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=<pathRemoved>
datadir=<pathRemoved>
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?

2
  • What problem are you solving with benchmarking? If its a current problem then the actual workload is better than an off the shelf sysbench representation.
    – danblack
    Apr 24 at 22:34
  • My intent is to monitor how different options influence the performance of MariaDB overall. I am not focusing on solving specific performance issues, but only want to assess empirically in which way the change of the options above impacts the performance in a certain setting. I am aware that this will not provide an overall picture on how these options influence the performance, but only a very specific one with regards to the used benchmarks.
    – C. K.
    Apr 25 at 7:39

3 Answers 3

1

Well done on knowing the aspects of performing a benchmark correctly. It seems your gaps are in the understanding of MariaDB tuning parameters and the correlation to the test.

Changing innodb_buffer_pool_size to bigger won't have a gain if the entire dataset is already fits in the buffer. Increasing the tables/table size in sysbench could impact this. As you scale up, look at doing a 5-10 minute test. It will also be significant as to if the test is a cold start or if everything is in memory originally.

As you increase this, increasing the innodb_log_file_size will facilitate the write section of the test better.

max_heap_table_size, being an OLTP test, this isn't exercised sufficiently, it would come in during analytics forms of sorting of datasets and larger joins.

query_cache_size, the default of 0 would eliminate the contention of the query cache. With 4 threads there isn't sufficient concurrency to observe the contention on reads and potentially the writes will very quickly invalidate the query. Recommend testing with a lot more threads.

tmp_table_size, also really only has an effect on analytics workloads.

So your tuning items, even the lowered ones, are within the capabilities of MariaDB for the sysbench test.

2
  • I will increase the threads to even more threads by using more than only one client. I will also try to extend the benchmark time. Should I also run other benchmark scenarios additionally to benchmark max_heap_table_size and tmp_table_size?
    – C. K.
    Apr 24 at 15:32
  • There's a threads option in the client. Yes, look at olap tests for the other two system variables.
    – danblack
    Apr 24 at 22:33
0

Your approach to benchmarking seems counterproductive. It's like trying random medicines to treat an illness that you don't know exists. Instead, you need to identify the disease, if there is any, by observing its symptoms, and treat it with the specific medicine.

Run the baseline benchmark. Monitor various database and system metrics to identify the bottleneck. Find and adjust a parameter, one at a time, that you think should resolve the bottleneck, and rerun the test. Repeat until you reach your target performance.

0

Benchmark programs rarely help in judging setting for real-life apps -- unless the benchmark runs 'real' queries.

innodb_buffer_pool_size=64M

That is terribly small. For real work, it should be set to 70% of available RAM (unless you have a tiny RAM).

max_heap_table_size=16M
tmp_table_size=1M

Those 2 are important for only a small fraction of queries. I recommend ignoring them to start with; but not setting them to more than 1% or RAM.

query_cache_size=0

Good. MySQL has already gotten rid of the query cache; any multi-Primary or clustering (eg Galara) setup cannot use the QC. So, yes, set it to 0 and turn off `query_cache_type. In the past it was useful only if exactly the same query came along before the table was written to or the QC entry was flushed. Most apps cannot make use of the QC, and the QC's overhead is non-zero.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.