Fix of InnoDB/XtraDB scalability of rollback segment

January 18, 2009

Author

Vadim Tkachenko

Benchmarks

Percona Software

Share this Post:

Recently I wrote about InnoDB scalability on 24-core box, and we made research of scalability problems in sysbench write workload (benchmark emulates intensive insert/delete queries). By our results the problem is in concurrency on rollback segment, which by default is single and all transactions are serialized accessing to segment.
Fortunately InnoDB internally has mechanism to support multiple rollback segments – and Yasufumi just made patch to enable it.

I rerun benchmarks on different server (Dell PowerEdge R900, 16-way Intel Xeon, 32GB of RAM, RAID 10 on 8 disks) to compare mysql-5.1.30-XtraDB-1.0.2-pre-release3 with default (1) and 16 rollback segments.

For reference
MySQL parameters:

[mysqld]
#mysqld options in alphabetical order
user=root

default_table_type=MYISAM

innodb_buffer_pool_size=6G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=8M
innodb_log_files_in_group=2
innodb_log_file_size=128M
innodb_thread_concurrency=0

innodb_extra_rsegments=16

max_connections=3000
query_cache_size=0
skip-name-resolve

table_cache=2048

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

[mysqld]

#mysqld options in alphabetical order

user=root

default_table_type=MYISAM

innodb_buffer_pool_size=6G

innodb_data_file_path=ibdata1:10M:autoextend

innodb_file_per_table=1

innodb_flush_log_at_trx_commit=2

innodb_log_buffer_size=8M

innodb_log_files_in_group=2

innodb_log_file_size=128M

innodb_thread_concurrency=0

innodb_extra_rsegments=16

max_connections=3000

query_cache_size=0

skip-name-resolve

table_cache=2048

sysbench parameters

/data/vadim/benchwork/benchmarks/sysbench/bin/sysbench --num-threads 1 --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

1

/data/vadim/benchwork/benchmarks/sysbench/bin/sysbench --num-threads 1 --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