[MDEV-10409] OLTP performance drop 10.0.22 - 10.0.26 Created: 2016-07-20  Updated: 2017-07-19  Resolved: 2017-07-19

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - XtraDB
Affects Version/s: 10.0.24, 10.1.12, 10.2.0
Fix Version/s: 10.1.24, 10.0.31

Type: Bug Priority: Major
Reporter: Nirbhay Choubey (Inactive) Assignee: Axel Schwenke
Resolution: Fixed Votes: 0
Labels: SUSE, performance

Attachments: Text File commit_6532572.txt    
Sprint: 10.0.28, 5.5.55, 10.0.30

 Description   

Sysbench OLTP test shows a performance drop of about 2.16% between
10.0.22 and 10.0.26.

System

uname -a
 
Linux ubuntu-trusty-amd64 3.13.0-10-generic #30-Ubuntu SMP Tue Feb 18 23:06:46 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

Build command

cmake .. -DWITH_SSL=system -DCMAKE_INSTALL_PREFIX=../install && make -j5 install

TPS of 3-runs:

MariaDB 10.0.22: 173294 (2888.12 per sec.), 174620 (2910.24 per sec.), 173791 (2892.08 per sec.)
 
 
 
MariaDB 10.0.26: 169879 (2831.20 per sec.), 169983 (2832.94 per sec.), 170327 (2838.68 per sec.)

Detailed sysbench output:

On MariaDB-10.0.22:
 
 
 
sysbench --test=oltp --oltp-table-size=100000 --mysql-db=test --mysql-user=root --mysql-socket=/tmp/mysql.sock prepare
 
sysbench --test=oltp --oltp-table-size=100000 --mysql-db=test --mysql-user=root --mysql-socket=/tmp/mysql.sock --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
 
 
 
sysbench 0.4.12:  multi-threaded system evaluation benchmark
 
 
 
No DB drivers specified, using mysql
 
Running the test with following options:
 
Number of threads: 8
 
 
 
Doing OLTP test.
 
Running mixed OLTP test
 
Doing read-only test
 
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
 
Using "BEGIN" for starting transactions
 
Using auto_inc on the id column
 
Threads started!
 
Time limit exceeded, exiting...
 
(last message repeated 7 times)
 
Done.
 
 
 
OLTP test statistics:
 
    queries performed:
 
        read:                            2426116
 
        write:                           0
 
        other:                           346588
 
        total:                           2772704
 
    transactions:                        173294 (2888.12 per sec.)
 
    deadlocks:                           0      (0.00 per sec.)
 
    read/write requests:                 2426116 (40433.72 per sec.)
 
    other operations:                    346588 (5776.25 per sec.)
 
 
 
Test execution summary:
 
    total time:                          60.0023s
 
    total number of events:              173294
 
    total time taken by event execution: 479.5250
 
    per-request statistics:
 
         min:                                  1.22ms
 
         avg:                                  2.77ms
 
         max:                                154.85ms
 
         approx.  95 percentile:               2.87ms
 
 
 
Threads fairness:
 
    events (avg/stddev):           21661.7500/311.98
 
    execution time (avg/stddev):   59.9406/0.00
 
 
 
 
 
On MariaDB-10.0.26:
 
sysbench --test=oltp --oltp-table-size=100000 --mysql-db=test --mysql-user=root --mysql-socket=/tmp/mysql.sock prepare
 
sysbench --test=oltp --oltp-table-size=100000 --mysql-db=test --mysql-user=root --mysql-socket=/tmp/mysql.sock --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
 
sysbench 0.4.12:  multi-threaded system evaluation benchmark
 
 
 
No DB drivers specified, using mysql
 
Running the test with following options:
 
Number of threads: 8
 
 
 
Doing OLTP test.
 
Running mixed OLTP test
 
Doing read-only test
 
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
 
Using "BEGIN" for starting transactions
 
Using auto_inc on the id column
 
Threads started!
 
Time limit exceeded, exiting...
 
(last message repeated 7 times)
 
Done.
 
 
 
OLTP test statistics:
 
    queries performed:
 
        read:                            2378306
 
        write:                           0
 
        other:                           339758
 
        total:                           2718064
 
    transactions:                        169879 (2831.20 per sec.)
 
    deadlocks:                           0      (0.00 per sec.)
 
    read/write requests:                 2378306 (39636.74 per sec.)
 
    other operations:                    339758 (5662.39 per sec.)
 
 
 
Test execution summary:
 
    total time:                          60.0026s
 
    total number of events:              169879
 
    total time taken by event execution: 479.5191
 
    per-request statistics:
 
         min:                                  1.23ms
 
         avg:                                  2.82ms
 
         max:                                194.25ms
 
         approx.  95 percentile:               3.34ms
 
 
 
Threads fairness:
 
    events (avg/stddev):           21234.8750/428.81
 
    execution time (avg/stddev):   59.9399/0.01



 Comments   
Comment by Axel Schwenke [ 2016-09-27 ]

I reproduced the same slight regression from MariaDB 10.0.22 to 10.0.26. I also took 10.0.25 into the loop and our latest release 10.0.27. Peak performance is very much the same for all 4. However 10.0.27 has greatly improved performance at low thread counts. This wears off at higher thread numbers though.

Numbers are transactions per second (bigger = better):

# data set 01 -> 10.0.22
 
# data set 02 -> 10.0.25
 
# data set 03 -> 10.0.26
 
# data set 04 -> 10.0.27
 
 
 
# read only
 
#thd    01      02      03      04
 
1       756.15  729.48  723.00  1023.2
 
2       1514.3  1481.4  1490.3  1792.0
 
4       3110.7  3008.5  3097.6  3307.0
 
8       6431.4  6322.7  6286.3  6343.8
 
16      12293   12035   11989   11999
 
32      17261   16867   16846   16802
 
64      17632   17265   17142   17121
 
 
 
 
 
# read/write
 
#thd    01      02      03      04
 
1       559.59  550.41  551.54  755.75
 
2       1175.7  1156.4  1158.7  1383.0
 
4       2445.3  2395.6  2398.0  2520.7
 
8       4881.5  4860.4  4855.3  4878.1
 
16      9197.9  9009.3  9022.5  9017.0
 
32      12513   12140   12241   12242
 
64      12297   12185   12108   11997

Comment by Axel Schwenke [ 2016-10-08 ]

I was now able to reproduce the reported regression. In order to trigger the behavior one needs:

  • a dataset bigger than the buffer pool; i.e. an OLTP table with 1 mio rows = 250MB and the default 128MB buffer pool
  • a workload that accesses the complete data set; i.e. by using a uniform random distribution and long range scans (i.e. oltp-range-size=1000)

example command line for sysbench 0.4.x:

sysbench --test=oltp --oltp-dist-type=uniform --oltp-table-size=1000000 \
 
 --num-threads=16 --oltp-read-only --oltp-range-size=1000 \
 
 --mysql-socket=... --mysql-user=... run

Numbers for a 16-core machine:

OLTP read-only TPS
 
threads 10.0.22 10.0.25
 
1       275.93  84.150
 
2       408.31  85.120
 
4       590.42  85.040
 
8       812.04  85.850
 
16      799.09  85.890
 
40      795.40  85.750
 
128     795.48  79.620

The behavior is caused by a new algorithm for determining the adaptive LRU flush rate, merged from upstream XtraDB.

While this is an edge case - the active set of a database should fit into the buffer pool - the performance impact is too heavy to keep it like so. We will revert that change to XtraDB.

Comment by Axel Schwenke [ 2017-03-01 ]

Attached the bad commit from Percona. This should be reversed in the storage/xtradb subdir

Comment by Axel Schwenke [ 2017-07-19 ]

This issue needs retesting. It should now be fixed upstream and we would then have the fix merged. I added a test case to the benchmark regression suite.

Comment by Axel Schwenke [ 2017-07-19 ]

This issue has been fixed by merging the upstream (Percona XtraDB) bugfix. The regression was fixed in MariaDB 10.1.24 and MariaDB 10.0.31.

Affected versions:

  • MariaDB 10.0.23 .. 10.0.30
  • MariaDB 10.1.12 .. 10.1.23

MariaDB 5.5 was never affected. MariaDB 10.2 is also not affected as it is using InnoDB as default InnoDB implementation.

Generated at Thu Feb 08 07:42:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.