Downloads

Blog

Contact Us

Another scalability fix in XtraDB

January 23, 2009

Author

Vadim Tkachenko

Benchmarks

Percona Software

Share this Post:

Recent scalability fixes in InnoDB and also Google’s and your SMP fixes almost made InnoDB results acceptable in primary key lookups queries, but secondary indexes were forgotten for some time. Now having Dell PowerEdge R900 on board (16CPU cores, 16GB RAM) I have some time for experiments, and I played with queries

SELECT name  FROM sbtest WHERE country_id = ? LIMIT 5

1	SELECT name FROM sbtest WHERE country_id = ? LIMIT 5

against table

CREATE TABLE IF NOT EXISTS sbtest (
                  id int(10) unsigned NOT NULL auto_increment,
                  name varchar(64) NOT NULL default '',
                  email varchar(64) NOT NULL default '',
                  password varchar(64) NOT NULL default '',
                  dob date default NULL,
                  address varchar(128) NOT NULL default '',
                  city varchar(64) NOT NULL default '',
                  state_id tinyint(3) unsigned NOT NULL default '0',
                  zip varchar(8) NOT NULL default '',
                  country_id smallint(5) unsigned NOT NULL default '0',
                  PRIMARY KEY  (id)
                 ) 
CREATE INDEX email_idx on sbtest(email);	
CREATE INDEX country_id_idx on sbtest(country_id,state_id,city);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

CREATE TABLE IF NOT EXISTS sbtest (

id int(10) unsigned NOT NULL auto_increment,

name varchar(64) NOT NULL default '',

email varchar(64) NOT NULL default '',

password varchar(64) NOT NULL default '',

dob date default NULL,

address varchar(128) NOT NULL default '',

city varchar(64) NOT NULL default '',

state_id tinyint(3) unsigned NOT NULL default '0',

zip varchar(8) NOT NULL default '',

country_id smallint(5) unsigned NOT NULL default '0',

PRIMARY KEY (id)

)

CREATE INDEX email_idx on sbtest(email);

CREATE INDEX country_id_idx on sbtest(country_id,state_id,city);

with table size 1mil records fully fitting in memory. I run it with innodb_thread_concurrency=16 to match threads inside InnoDB with count of cores.

Results for InnoDB-plugin-1.0.2 were kind of discouraging, dropping down just after 8 connections, si I decided to test MySQL-5.1.30 with standard InnoDB. It was better, but still far from what we can expect.

After investigation Yasufumi pointed to page_hash mutex, which was abused. This mutex is used as mutex even in places where shared read lock is enough – and we replaced page_hash mutex to page_hash read-write lock.

The new results you can see on graph.

The patch for page_hash will be included in next release of XtraDB, and expect new results.

for reference InnoDB was run with next parameters:

--max_connections=3000 
--table_cache=2048 
--query_cache_size=0 
--innodb_data_file_path=ibdata1:100M:autoextend 
--innodb_buffer_pool_size=6GM 
--innodb_additional_mem_pool_size=20M 
--innodb_log_file_size=128M 
--innodb_log_files_in_group=2 
--innodb_log_buffer_size=8M 
--innodb_flush_log_at_trx_commit=2 
--innodb_thread_concurrency=16 
--innodb_file_per_table=1 
--innodb_flush_method=O_DIRECT

1

2

3

4

5

6

7

8

9

10

11

12