mysqlguy.net

innodb

Innodb RAID performance on 5.1

I've been doing some benchmarking recently to satisfy the curiosity about 5.1's performance compared with 4.1.  The major question this time revolves around how much additional performance an external RAID array can provide (for us it's typically beyond the 6 drives a Dell 2950 can hold). 


These tests are done on using an MSA-30 drive enclosure with 15k-SCSI drives.  The testing framework is sysbench oltp.  The test names are hopefully fairly obvious:  selects = single selects, reads = range tests, xacts = transaction tests, etc.   Transaction tests are counting individual queries, not transactions.   The "Rdm" tests are using a uniform distribution, whereas the non-'Rdm' tests are 75% of queries are using 10% of the rows.  

Innodb RAID performance testing on 5.1

Innodb RAID performance testing on 5.1


Innodb Multi-core Performance

There's been a lot of rumors floating around internally at Yahoo that it's best to turn off some of your CPU cores when using Innodb, especially if you have a machine with > 4 cores.  At this point there's no question in my mind that Innodb doesn't perform much better when you double your cores from 4 to 8, but I really wanted to know if 8 actually performed worse. 


To test, I used a Dell 2950 with 6 drives and a simple mysqlslap test script.  There's basically no I/O going on here, just a small table in memory being queried a lot.  To be fair, I actually got this test from Venu.  I used maxcpu=4 in my grub.conf to limit the cpus (I also tested with tasksel and it seemed to have the same effect as maxcpu).

Innodb Core Performance

Innodb Core Performance


The scoop on Innodb reads

I spent some time going through the source the other day in order to try to understand the difference between these SHOW STATUS variables in 5.x:


Innodb_buffer_pool_read_requests
Innodb_buffer_pool_reads
Innodb_pages_read

Here's what I discovered that I'm not sure is 100% right, but I suspect is at least on the right track:

Innodb_buffer_pool_read_requests:  requests to get something from the buffer pool.  This isn't really that great of a mystery.  I suspect these are 'direct' read requests, meaning those caused directly from a query of some kind.  

About Me

Jay Janssen
Yahoo!, Inc.
jayj at yahoo dash inc dot com
MySQL
High Availability
Global Load Balancing
Failover
View Jay Janssen on Twitter  View Jay Janssen's LinkedIn profile View Jay Janssen's Facebook profile