I was interested to verify those results in the context of MySQL 5.4.0.
The patch can be found here.
We've analysed this patch both on a large SPARC system and on my
benchmark x86/Linux machine. Our results tend to be positive for
readwrite benchmarks but sometimes negative for readonly
benchmarks. Also the gain is much smaller than found in the
blog.
Also this patch has two negative effects, the first is that it
provides an upgrade problem, this can probably be handled in the
InnoDB code, but requires quite some digging. The other is that
instead of writing UNDO results to one UNDO log, we write it to
several UNDO logs, thus decreasing the buffering effect to the
file system.
On Linux I found on readwrite benchmarks up to 7-8%
improvements of the top results. On readonly it sometime dropped
about 1-3%. I also tried with varying numbers of rollback
segments and found 4 and 8 to be better than 16. So from the
above point of view the number of rollback segments set to 4 is
probably best. The patch uses 8 (it's actually set to 9 since
the system rollback segment is a bit special).
Here are some graphs from Dimitri running it on some fat SPARC
server (MySQL-5.Perfb9-gcc43 is 5.4.0 plus the above patch).
The first graph shows the behaviour when InnoDB Thread Concurrency
is 0, here we see a speedup in the range of 3-5%.
The same results for the readonly benchmark shows positive results as well.
When one sets InnoDB Thread Concurrency equal to 16, 24 or 32
the behaviour is different. It turns out that we get worse
performance using 16 but get more positive impact using 24 and
even more using 32. So it seems that this patch requires less
limits to parallelism to get the best behaviour.
So one impact of this patch is that it can sustain a higher
number of concurrent threads and there is a small positive impact
on the performance as well.
Mikael,
ReplyDeleteRollback segments and UNDO slots are two different things. Our rollback segment patch does not touch UNDO slots.
Also effect of this patch is visible under special workloads, not all of them can be emulated by dbStress.
I was using the term UNDO logs which is what the rollback segment is, simply a synonym of the term which is the one I'm used to using.
ReplyDeleteWhich type of workloads do you see most effect on, don't recall if that was mentioned on the original blog?
Mikael,
ReplyDeleteI see effect of patch for rollback segments in multi-thread INSERT load,
i.e. if run sysbench like:
sysbench --num-threads N --max-requests 0 --max-time 300 --thread-stack-size 32K --init-rng on --validate off --test oltp --oltp-test-mode complex --oltp-sp-name --oltp-read-only off --oltp-skip-trx off --oltp-range-size --oltp-point-selects --oltp-simple-ranges --oltp-sum-ranges --oltp-order-ranges --oltp-distinct-ranges --oltp-index-updates --oltp-non-index-updates --oltp-nontrx-mode select --oltp-auto-inc off --oltp-connect-delay 10000 --oltp-user-delay-min 0 --oltp-user-delay-max 0 --oltp-table-name sbtest --oltp-table-size 10000000 --oltp-dist-type uniform --oltp-dist-pct --oltp-dist-res --db-ps-mode auto --mysql-host localhost --mysql-port 3306 --mysql-socket /data/vadim/benchwork/var/mysql_benchwork.sock --mysql-user user --mysql-password --mysql-db sbtest --mysql-table-engine innodb --myisam-max-rows 1000000 run
(not sure if formatting correct)
And I said UNDO is different, because we have two patches:
- split rollback segments
- extend undo slots.
First one does not broke compatibility with previously created database. Only second does.