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:
|
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
|
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 |
Resources
RELATED POSTS
