Capabilities of InnoDB INSERT Performance

Hi I am running the most recent version of Percona Server.

Server version: 5.5.24-55 Percona Server (GPL), Release 26.0

I have a 10 cpu box of these characteristics.

processor       : 0
vendor_id       : AuthenticAMD
cpu family      : 16
model           : 9
model name      : AMD Opteron(tm) Processor 6128
stepping        : 1
microcode       : 0x10000d9
cpu MHz         : 800.000
cache size      : 512 KB

It has SSD and 64GB of RAM. Innodb is approx 10GB, so innodb_buffer_pool_size set to 10GB.

I have a table that is as follows :

create table TODAY
( symbol_id       integer not null
, openp           decimal(10,4)
, high            decimal(10,4)
, low             decimal(10,4)
, last            decimal(10,4) not null
, volume          int
, last_updated      datetime        -- the time of the last quote update
, prev        decimal(10,4) null
, PRIMARY KEY ( symbol_id )
)

If I start with an empty table and do an insert of 23,000 rows it takes around 10 seconds. If I subsequently do an update where every column of every row is updated (except symbol_id of course) it takes a bit more like 11-12 seconds.

Is this generically the write performance I should expect from Innodb? Is there any suggestion for improving this performance? updating 23,000 rows is an extreme case, as typically during a trading day I need to update approx 1000 rows every 5 seconds (so, that is the more realistic constraint I am dealing with).

Other relevant mysql.cnf settings I have changed :

innodb_buffer_pool_size = 10G
innodb_log_file_size    = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

BTW if instead of Innodb I create the table with ENGINE=MEMORY it takes around 4 seconds to do the insert, 6 seconds to do the update.

Many TIA if someone can help me figure out what the benchmark for this type of query is, or help me improve the time.

Don

PS full Innodb settings.

mysql> show global variables like 'innodb%';
+-------------------

You need to tune your InnoDB settings in the following areas:

• Make InnoDB access all your cores

• Increase innodb_buffer_pool_size to 12G

• Increase innodb_buffer_pool_instances to 2 (First run numactl --hardware to determine the number of Physical CPUs. What every number of CPUs it reports, use that number. I learned this recently in Jeremy Cole's Blog)

• Increase Log File Size (innodb_log_file_size) to 2047M

• support separate tablespace files for individual InnoDB tables (enaled innodb_file_per_table)

• support either high performance or high durability (ACID Compliance)

• High Performance : innodb_flush_log_at_trx_commit set to 0 or 2

• High Durability : innodb_flush_log_at_trx_commit set to 1 (Default)

• Increase Size up the innodb_log_buffer_size in conjunction with the number of transactions per second (perhaps 32M)

• Your current setting for innodb_flush_log_at_trx_commit is good

• Your current setting for innodb_flush_method is good

• Increase innodb_read_io_threads to 64

• Increase innodb_write_io_threads to 64

• Increase innodb_io_capacity to 10000

Here are links to my past posts on tuning the InnoDB storage engine:

• Is the CPU performance relevant for a database server? (Apr 26, 2012)

• Why does InnoDB store all databases in one file? (Mar 25, 2012)

• Multi cores and MySQL Performance (Sep 20, 2011)

• Possible to make MySQL use more than one core? (Sep 12, 2011)

• About single threaded versus multithreaded databases performance (May 26, 2011)

• How to safely change MySQL innodb variable 'innodb_log_file_size'? (Feb 16, 2011)

• How do you tune MySQL for a heavy InnoDB workload? (Feb 12, 2011)

• Howto: Clean a mysql InnoDB storage engine? (October 29, 2010)

StackExchange Database Administrators Q#20372, answer score: 11