maria-developers team mailing list archive

[MARK CALLAGHAN <mdcallag@xxxxxxxxx>]

This includes results from sysbench for Maria 5.2 with/without
userstat enabled and for several other binaries. The results are
throughput (transactions per second) for 1, 2, 4, 8, 16, 32 concurrent
sessions.

------------

This tests many binaries including 5.0.37 with the v2 Google patch (aka 5037v2).
On a configuration I no longer have access to, 5037v2 had a severe performance
regression from code in the SHOW [TABLE|USER|_STATISTICS patch. The regression
was caused by mutex contention. The result on my current platform is
much less severe,
but you can see the impact in simple-allcols and simple-handler listed below
-- compare 5037, 5037v2 and 5037v4.

I am using glibc 2.5 now and used an older glibc version in the past. I am
curious if pthread code is that much better on my current platform.

I thought that the Maria version of that feature would inherit this performance
problem. Performance loss in Maria is 5% to 10% with userstat enabled for
high levels of concurrency -- again see results for simple-allcols and
simple-handle -- and this is much less than the loss in 5037v2, but more
than the loss in 5037v4 which had fixes for user and table stats code.

-----------

This uses a new version of sysbench available on Launchpad:
http://code.launchpad.net/~sysbench-developers/sysbench/0.4

Command lines for each test:

oltp-ro ==
sysbench --test=oltp --oltp-table-size=2000000 --max-t ime=180 \
--max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable \
--mysql-engine-trx=yes --oltp-read-only --oltp-skip-trx \
--oltp-dist-type=special --num-threads=$n --seed-rng=1 run

oltp-rw ==
sysbench --test=oltp --oltp-table-size=2000000 --max-time=180 \
--max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable \
--mysql-engine-trx=yes --oltp-dist-type=special --num-threads=$n
--seed-rng=1 run

This fetches all columns using primary key lookups and SELECT
simple-allcols ==
sysbench --test=oltp --oltp-table-size=2000000 --max-time=180 \
--max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable \
--mysql-engine-trx=yes --oltp-read-only --oltp-skip-trx \
--oltp-test-mode=simple --oltp-point-select-all-cols \
--oltp-dist-type=special --num-threads=$n --seed-rng=1 run

This fetches all columns using primary key lookups and HANDLER
simple-handler ==
sysbench --test=oltp --oltp-table-size=2000000 --max-time=180 \
--max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable \
--mysql-engine-trx=yes --oltp-read-only --oltp-skip-trx \
--oltp-test-mode=simple --oltp-point-select-mysql-handler \
--oltp-dist-type=special --num-threads=$n --seed-rng=1 run

my.cnf settings:
innodb_buffer_pool_size=2000M
innodb_log_file_size=100M
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
max_connections=500
innodb_max_dirty_pages_pct=80
innodb_flush_log_at_trx_commit=2

server:
glibc 2.5
8 core Intel x86

binaries:
4030 - mysql 4.0.30
5037 - mysql 5.0.37
5037v2 - mysql 5.0.37 with v2 google patch
5037v4 - mysql 5.0.37 with v4 google patch
5084 - mysql 5.0.84
5084dev1020 - facebook patch + 5.0.74
5138pi - 5.1.38 + 1.0.4 InnoDB plugin
maria.nostat - Maria 5.2 with userstat disabled
maria.stat - Maria 5.2 with userstat enabled

notes:
5.0 binaries compiled with:
configure --enable-thread-safe-client --prefix=$b --exec-prefix=$b \
--with-plugins=max-no-ndb --without-fast-mutexes \
--with-unix-socket-path=$b/var/mysql.sock --with-extra-charsets=all \
--with-blackhole-storage-engine
C_EXTRA_FLAGS="-g -O2 -fno-omit-frame-pointer"

5.1 binaries compiled with:
configure --enable-thread-safe-client --prefix=$b --exec-prefix=$b \
--with-plugins=csv,blackhole,myisam,heap,innobase --without-fast-mutexes \
--with-unix-socket-path=$b/var/mysql.sock --with-extra-charsets=all \
C_EXTRA_FLAGS="-g -O2 -fno-omit-frame-pointer"

oltp-ro
  1         2           4         8         16       32
concurrent_sessions / binary
505     849     1264    1036    1341    1261    4030
778     1311    2502    4276    4046    3732    5037
683     1280    2239    3526    3268    2730    5037v2
690     1249    2489    4428    3984    3687    5037v4
747     1328    2596    4341    4123    4046    5084dev1020
797     1372    2708    4550    4256    4189    5084
780     1362    2590    4287    4193    4096    5138pi
703     1425    2593    4379    4176    4110    maria.nostat
684     1281    2439    4064    3896    3882    maria.stat

oltp-rw
  1        2         4             8          16         32
concurrent_sessions / binary
258     341       460      477     459      466     4030
475     919     1669    2766    2110    1824    5037
499     887     1537    2208    2099    1811    5037v2
506     923     1694    2798    2178    1980    5037v4
544     919     1740    2858    2295    1977    5084dev1020
514     928     1824    3000    2375    2037    5084
497     932     1754    2953    2799    2642    5138pi
561     903     1703    2964    2745    2653    maria.nostat
494     873     1624    2816    2565    2507    maria.stat

simple-allcols
  1         2           4           8           16         32
concurrent_sessions / binary
12240   20700   35397   53664   49865   47750   4030
12303   22284   48311   87580   81413   76310   5037
12176   22478   42227   59535   51491   39995   5037v2
12409   23952   48429   86180   80773   76895   5037v4
13799   24094   51521   93560   87728   85905   5084dev1020
12664   25330   53115   100289  91328   89076   5084
14797   24481   48254   92323   87722   86436   5138pi
13418   24980   49777   94425   88083   86854   maria.nostat
13062   23378   46636   83449   77714   77245   maria.stat

simple-handler
  1         2           4           8           16           32
concurrent_sessions / binary
13456   22782   39157     55087   51813   50260   4030
15432   29506   61464   122334  112374  104732  5037
15090   27910   52598    79034   70192   59490   5037v2
15529   29691   61225   116625  111384  103562  5037v4
15863   31121   69364   132998  124241  120279  5084dev1020
17233   32641   71906   132926  130436  125125  5084
16241   33986   63226   126277  122702  122002  5138pi
16035   32051   68157   129148  127899  125368  maria.nostat
18644   32239   63443   122932  116283  114760  maria.stat

-- 
Mark Callaghan
mdcallag@xxxxxxxxx