Mariadb Low transaction and query per second



  • We have a MariaDB 10.3 on a Redhat Server (VM) with 90 GB Ram and 20 VCPUs. I'm trying to optimize the Database. I installed Sysbench for Benchmarking and after a 50 seconds test I got this values (with default MariaDB variables):

    #Benchmarking command:
        sysbench oltp_read_write --threads=2 --report-interval=3 --histogram 
        --time=50 --table-size=1000000 --db-driver=mysql --mysql-host=firstserver
        --mysql-db=sbtest  --mysql-user=sbtest_user --mysql-password=password run
    

    SQL statistics:
        queries performed:
            read:                            271040
            write:                           77440
            other:                           38720
            total:                           387200
        transactions:                        19360  (387.15 per sec.)
        queries:                             387200 (7742.96 per sec.)
        ignored errors:                      0      (0.00 per sec.)
        reconnects:                          0      (0.00 per sec.)
    

    General statistics:
    total time: 50.0054s
    total number of events: 19360

    Latency (ms):
    min: 3.75
    avg: 5.16
    max: 26.99
    95th percentile: 6.55
    sum: 99964.49

    Threads fairness:
    events (avg/stddev): 9680.0000/2.00

    For this Benchmarking the innodb_buffer_pool_size was only 2GB. I tried to optimize the DB to get more transactions and queries per second. I changed this variables:

    #set memory (was 2 G)
    innodb_buffer_pool_size=70G 
    

    #set log file size (was 64MB)
    innodb_log_file_size=2G

    #set log buffer size (was 16MB)
    innodb_log_buffer_size=128M

    #set temporary in memory table size (was 16MB)
    tmp_table_size=64M
    max_heap_table_size= 64M

    set query cache (was 1MB)

    query_cache_size=64M

    But the result changed barely.



  • I notice you have 20 CPU cores, but you're only running sysbench with 2 threads. You probably can get better results with more threads. The guideline often given is 2x the number of CPU cores, so try it with at least --threads=40.

    I have also found sysbench itself has scalability limits (as any client app would). To get the best result, I ran multiple instances of client hosts, all running sysbench and connecting over the network to the same database.

    Increasing the buffer pool 35x would not help at all if your data is small anyway, which I suppose it is for a sysbench run. It's like upgrading your 35,000 square foot warehouse to a million square foot warehouse to store your personal photo collection.

    It's not clear why you thought increasing the other variables would help. Did you have some measurement that pointed to those being bottlenecks? How many times did your innodb commits need to wait for the log buffer to flush? What percentage of your queries used temp tables, but had to write the temp table to disk instead of using in-memory temp tables?

    My point is that making random tuning choices to see what effect it has on the benchmark is not going to lead you to optimize your server. You're not approaching it like a scientist or like an engineer. If one of these made a difference, how do you know which one was important? Or would you therefore make all the same changes on your production MySQL server? If so, how is that not a superstitious ritual, like throwing salt over your shoulder for good luck?




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2