In discussing with people I like to ask them a basic question — if you have a server with 16GB of RAM which will be dedicated for MySQL with large Innodb database using typical Web workload what settings you would adjust and interestingly enough most people fail to come up with anything reasonable. So I decided to publish the answer I would like to hear extending it with basics of Hardware OS And Application optimization.
I call this
Innodb Performance Optimization Basics so these are general recommendation which work well for many applications, though the optimal settings of course depend on the workload.
Hardware
If you have large Innodb database size Memory is paramount. 16G-32G is the cost efficient value these days. From CPU standpoint 2*Dual Core CPUs seems to do very well, while with even just two Quad Core CPUs scalability issues can be observed on many workloads. Though this depends on the application a lot. The third is IO Subsystem — directly attached storage with plenty of spindles and RAID with battery backed up cache is a good bet. Typically you can get 6-8 hard drives in the standard case and often it is enough, while sometimes you may need more. Also note new 2.5? SAS hard drives. They are tiny but often faster than bigger ones. RAID10 works well for data storage and for read-mostly cases when you still would like some redundancy RAID5 can work pretty well as well but beware of random writes to RAID5.
Operating System
First — run 64bit operating system. We still see people running 32bit Linux on 64bit capable boxes with plenty of memory. Do not do this. If using Linux setup LVM for database directory to get more efficient backup. EXT3 file system works OK in most cases, though if you’re running in particular roadblocks with it try XFS. You can use noatime and nodiratime options if you’re using innodb_file_per_table and a lot of tables though benefit of these is minor. Also make sure you wrestle OS so it would not swap out MySQL out of memory.
MySQL Innodb Settings
The most important ones are:
innodb_buffer_pool_size 70-80% of memory is a safe bet. I set it to 12G on 16GB box.
innodb_log_file_size — This depends on your recovery speed needs but 256M seems to be a good balance between reasonable recovery time and good performance
innodb_log_buffer_size=4M 4M is good for most cases unless you’re piping large blobs to Innodb in this case increase it a bit.
innodb_flush_log_at_trx_commit=2 If you’re not concern about ACID and can loose transactions for last second or two in case of full OS crash than set this value. It can dramatic effect especially on a lot of short write transactions.
innodb_thread_concurrency=8 Even with current Innodb Scalability Fixes having limited concurrency helps. The actual number may be higher or lower depending on your application and default which is 8 is decent start
innodb_flush_method=O_DIRECT Avoid double buffering and reduce swap pressure, in most cases this setting improves performance. Though be careful if you do not have battery backed up RAID cache as when write IO may suffer.
innodb_file_per_table — If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth which you can’t reclaim. This option was added in MySQL 4.1 and now stable enough to use.
Also check if your application can run in READ-COMMITED isolation mode — if it does — set it to be default as
transaction-isolation=READ-COMMITTED. This option has some performance benefits, especially in locking in 5.0 and even more to come with MySQL 5.1 and row level replication.
There are bunch of other options you may want to tune but lets focus only on Innodb ones today.
Application tuning for Innodb
Especially when coming from MyISAM background there would be some changes you would like to do with your application. First make sure you’re using transactions when doing updates, both for sake of consistency and to get better performance. Next if your application has any writes be prepared to handle deadlocks which may happen. Third you would like to review your table structure and see how you can get advantage of Innodb properties — clustering by primary key, having primary key in all indexes (so keep primary key short), fast lookups by primary keys (try to use it in joins), large unpacked indexes (try to be easy on indexes).
With these basic innodb performance tunings you will be better of when majority of Innodb users which take MySQL with defaults run it on hardware without battery backed up cache with no OS changes and have no changes done to application which was written keeping MyISAM tables in mind.
Comments (14)
RSS Collapse / ExpandI’ve seen great speed improvements using just these tips alone. What I don’t see here which is something that many novice administrators or tuners may not know is that if you set your buffers and settings too high and restart your mysql server, mysql wont instantly complain. What I think happens is it either ignores these settings completely and uses defaults or it uses them, discovers that they dont work for the session, reverts to the defaults or recovers in some other way which is slow. This can seriously impair your performance!
My only wishes for mysql would be that they would allow you to log queries which trigger counters of things like sort_merge_pass, full joins and tmp tables on disk so you could actually better find the queries causing slowdowns or poorly written queries in your applications, AS WELL AS a tool that would allow you to see how your buffers were being used in a visual way rather than just guessing through examining the raw numbers. These two changes would make administration lightyears more advanced than it is now for novice or intermediate developers/admins. Out of 801,000 tmp tables created, only 3,762 of those were on disk. It still bugs me that I can’t just look at a log and find them to fix them. I do have 0 Select_full_join and 0 Sort_merge_passes though finally.
What is most confidence inspiring is thinking about the day when i can take the kid gloves off and run my database on a 64bit machine with a more acceptable amount of ram. After being hamstrung this long with 32bit chips, I can’t wait to see how things perform with the newest tech out there!
mikkomiss
I tend to tell people to leave a few GB for the operating system, and let the buffer pool use the rest. 4G might not be too unreasonable on a 16G box, depending on what else is going on, but I’d probably start with 2G and work up if needed. It’s super important to use O_DIRECT when tuning this, otherwise the OS will snatch up all of your free RAM for fs caching.
Tyler
Good post though Agrees with much of what I tell people at Yahoo.
Tyler
Michael
mikkomiss
Your advice of leave a bit for MySQL and OS needs and give the rest to Innodb Buffer Pool is good but how one would know how much memory is needed for these?
Also note not everything may work as you would expect it in theory. For example even with O_DIRECT OS may be swapping out portions of MySQL due to IO pressure which may come from logs, disk based sorts or disk based temporary table.
Another thing you need to keep into account is caching Innodb logs. As IO to Innodb logs is unaligned you better have them fit in the cache otherwise you will be getting read-around-write stalls every so often.
But you’re right of course for 64GB you would want the buffer pool to be significantly higher than 50G
WebDesigner
thanks
winmonaye
Only Registered and authorized users may post comments