DimitriK's (dim) Weblog

MySQL Performance: post notes after UC2010

It's not notes about the Conference itself, but about Performance :-) There were many topics discussed and presented around InnoDB performance during UC2010, but time was limited to start long talks as another session interesting as well should start within 5 min :-))

So I've prepared a short summary with some points I consider important on configuring InnoDB to run it optimally, but if I missed something, or you don't agree with me - please comment! :-))

Storage layout - I strongly advice you to separate your data and redo logs, and place them on the different storages/arrays/disks.. - Why?.. - just because of the different I/O nature: redo log activity will mainly generate sequential writes, while data reads/writes are mainly random (specially indexes) - so if you mix these I/O activities together you'll have worse performance for sure (even if you use SSD).. As well if you have binlog activated - don't forgot to place it on the third different storage place to avoid to loose at once the data and binlogs in case of storage corruption..

Double Write Buffer - very nice feature of InnoDB, but if you want to avoid any performance problems when using it you have to move it on the same storage place as your redo logs [see detailed analyze of the problem on my previous post: InnoDB Doublewrite Buffer Impact ]

I/O Capacity - choice for this setting depends directly of your storage capacity, so first check what is the limit of your I/O subsystem in term of write operations/sec (not only sequential, but also random writes - and choose something in the middle) - the I/O capacity setting then is used to drive the speed of buffer flushing (dirty pages writes) to the storage; setting this value higher will involve more aggressive writes, lower - less aggressive; you even may use it to limit the write speed of one of the MySQL instances ;-)) - but keep in mind, if your activity out-passing the buffer flushing speed - earlier or later your redo log space will be nearly full, which will involve "urgent flushing" which ignoring any capacity settings :-) - and will just require to flush all blocks as soon as possible.. - My usual setting here is 2000 for a decent storage. But the real answer you'll have only if you monitor your system activity (I/O stats) and your database (checkpoint age, history length)..

I/O Read/Write threads - this setting is also directly depending on the storage capacities: you have to know how many I/O operations your storage is able to process in parallel (many storage may be limited to 16-32 parallel I/O operations (depends on controller capacity too)) - so you're better to check before to be sure about your values; of course monitoring here again is the best friend :-)) I'm usually set the number of read and write threads to 16, however rarely seen more than 4 parallel I/O operations addressed to my storage even on dbSTRESS (and it's hard to say is it because my storage is fast enough, or because I/O threads are not sending fast enough :-)) - BUT! since 5.5.4 there is a new feature - native AIO  support on Linux! - which in theory may very easily generate 256 requests in parallel (or much more) - which in some cases may simply kill your storage performance.. - so you may they need to find a way to limit the number of parallel I/O requests on the system level (very easy on Solaris, no idea about Linux :-)) - as well it'll be probably fine to have such an option included within InnoDB to avoid to bother with OS ;-)

InnoDB thread concurrency - this setting is still useful while your database is serving many user sessions: as there are still a lot of lock contentions inside of MySQL/InnoDB, more working sessions you have in parallel - more lock contentions you have; so then your 128 users working in parallel will not have better performance if they were only 4 or 8... For the 5.5.4 version the current most optimal setting is 32 (if needed) - before 5.5.4 it was 16 - and what is curious, it's completely independent on the number of CPU/cores you have on your server (so it's clear we made a huge progress with 5.5.4 :-)). But again, you have keep in mind that InnoDB thread concurrency management is absolutely not aware if one of the sessions "allowed for execution" is doing I/O operations or not! - so if your workload is not CPU-bound, but I/O-bound - it's very possible that you'll need a higher value to get better throughput. Once again - monitoring is your friend :-) and looking on how many sessions are on execution (inside) or in queue may help to understand and better adapt your database to your activity.. Also, this setting is dynamic, so can be changed at any time according workload.

Number of buffer pool instances - this is a very new feature coming with 5.5.4 and need a particular attention. On most of our tests the most optimal result was obtained by setting it to 16. But - it doesn't mean it will be optimal in any cases ;-) To understand why you have to understand the initial problem of the buffer pool mutex: before for various page operations within a buffer pool you were need to acquire the buffer pool mutex - and as it was the only one(!) single mutex for all your sessions - it's clear there was a huge bottleneck... - It was clear from a long time, but it was unclear at all how better to fix it :-) One of the possible solutions was proposed within XtraDB. But 5.5.4 implementation seems to be better as it also showing improvements even on a read-only workloads! And I would say you it was the major step in the whole 5.5.4 performance improvement effort. - Why? - just because it was the main door to open before to see any improvement from all other features! - for example applying only 128 rollback segment feature without having buffer pool mutex fixed will lower your performance by x2 or x3 times! - just because by removing rollback segment mutex contention you'll hit harder than ever the buffer pool mutex!.. So, I'd like to send my kudos to 2 folks here - because even if I've oriented and analyzed solution prototypes on my tests, the main code was invented and written by Mikael Ronstrosm, and then finalized (which has a lot of merit) by Sunny Bains. And I see in this work the first step of the common efforts from the MySQL and InnoDB engineering! :-) Kudos, Gents! - it was absolutely great! :-)

But, finally, why the setting of 16 instances may be still not optimal?.. - don't forget that your data are less or more random, and randomly they may be places to one of 16 buffer instances, and randomly your workload may access pages from one instance more often than from others, and even mostly access only one from 16 instances - and in this case you'll see a similar contention as before with a single buffer pool mutex... - So the work is still continuing, stay tuned ;-))

Redo log file size - 128MB may be optimal in many cases, but with a bigger value you have higher performance (moving to 1024MB  on dbSTRESS shows more than 30% better performance) - but in case of crush you may spend much more on recovery (but if you'll move to 5.5.4 this recovery time probably will be way shorter then you seen before? ;-)) - as well you should not forget about the innodb_log_files_in_group setting, because it's innodb_log_files_in_group * innodb_log_file_size will determine the whole size of your redo space (don't leave it by default) - for my part I'm using 3 files of 1024MB on my tests..

Flush commit policy - of course for the best data security you have to set innodb_flush_log_at_trx_commit=1 - but what is the real danger to set it to 2? (means redo log is still written on each commit, but flushed once per second) - if you don't trust your server and it crashes all the time I understand it'll be very risky.. - But it your server is running like a switch watches - what is the danger then?.. ;-) All the stuff needed to be written is written, and even if MySQL itself will crash - files still will be flushed to the storage by OS within next few seconds.. - And in case of the real HW problem you'll probably anyway need to restore the latest backup... - So why limit performance of a swiss watch?.. ;-))

I'll be curious to know what is your preferred setting generally, and particularly for 5.5.4 :-)) Please extend and comment (specially if you're as me sticked in SFO due canceled flights)...

(BTW, speaking about monitoring - don't miss dim_STAT (UC2010 presentation is here ))

Then few words about UC2010 itself anyway :-)) - personally I was impatient and absolutely happy to meet all folks face to face and discuss finally live :-)) - you may tell me what you want about a virtual reality around us, and sometimes it even looks like real.. :-)) - but nothing is better than meet real people around a real beer! :-))