The Developer Day | Staying Curious

Nov/10

25

Optimizing MySQL on Ubuntu 10.10 Maverick

Since Ubuntu 9.04 Jaunty Jackalope Ubuntu ships with EXT4 as the default file system. Surprisingly it makes MySQL writes extremely slow. This post is targeted to developers who work on Linux using MySQL and who would like to optimize MySQL performance.

Disk Performance Tuning

First start by tuning your disk performance. To do that you’ll have to sacrifice data consistency over data write speed. First start by enabling journal_data_writeback on your partition. This will allow to write to disk before updating the EXT4 journal. If your box crashes before updating the journal you might loose new data or some deleted data might reappear.

sudo tune2fs -o journal_data_writeback /dev/sda1 (use the right partition)

Next step is editing your /etc/fstab to change ext4 mounting options. My fstab file looks something like this:

UUID=irrelevant / ext4 errors=remount-ro,noatime,nodiratime,data=writeback,barrier=0,nobh,commit=100,nouser_xattr 0 1

There’s a few non default options added to improve write performance over consistency. Journal data writeback is enabled by data=writeback. The main option which is slowing down MySQL is barrier=0. You could actually change this single option and MySQL write performance would increase dramatically. Disabling this option makes your new data less safe when a system crash happens. Option nobh tries to avoid associating buffer heads and offers a minor performance improvement. Another option commit=100 says that all your updates are written to disk every 100 seconds. The default is 5 seconds. If your machine crashes you’re likely to loose 100 seconds of updates. Large commit values like 100 provide big performance improvements. And the last option nouser_xattr disables extended options on your filesystem and provides a minor performance boost.

Double check your /etc/fstab syntax and reboot.

Tuning MySQL configuration

MySQL configuration settings depend on what database engines you’re using. The most common ones are MyISAM and InnoDB. I will assume that you use both.

Warning! Some of the configuration changes will or might make your database inaccessible. Therefore backup all your databases by dumping them to SQL to a safe location. Make sure to include triggers and stored procedures. Double check that you will be able to reimport your backups and only then proceed further. Some options will make your InnoDB database stop working. I’ll mark those. Also backup your MySQL configuration. Just in case.

MySQL settings depend on how much memory you have. I will assume a normal working station will have 4GB of RAM. Open your MySQL configuration file which on Ubuntu is located at /etc/mysql/my.cnf and set the following options.

transaction-isolation = READ-COMMITTED

As a developer you will probably not have transactions running in parallel. If you don’t care about transactions and still use InnoDB set the isolation level to READ-COMMITED. This will make your transactions only see committed data but won’t prevent phantom rows. Setting it to READ-COMMITED will also improve performance.

key_buffer = 512M

By far the most important option for MyISAM. MyISAM indexes are cached using in the key buffer. It’s usually a good bet to set it from 25% to 40% of memory available. As a developer you might not need that much but do not leave it at a default.

query_cache_size = 256M

Caches query results. Especially useful if your applications don’t have caching.

innodb_buffer_pool_size = 1024M (requires a backup and an import)

InnoDB buffer pool size is the most important option for InnoDB. If your whole database is InnoDB you can try and fit your whole database in memory. If you don’t have that much memory you can generally set 70% – 80% of memory available. On a development box you will probably want to have extra RAM for things like Gnome or your IDE.

innodb_additional_mem_pool_size = 32M
innodb_log_buffer_size = 4M
innodb_log_file_size = 128M

innodb_flush_log_at_trx_commit = 2

This option tells InnoDB to only flush log data every two seconds. On development machines you can set this even higher because the only risk is losing transactions during a system crash. If your development machine crashes you probably won’t care about lost transactions. Experiment!

innodb_flush_method = O_DIRECT

This options tells InnoDB to skip filesystem cache and write straight to disk since InnoDB already has it’s own cache – the buffer pool. You save yourself some RAM.

table_cache = 1024

Caches open tables. Might not be very useful on a single dev box but useful in general on any database server.

myisam_use_mmap = 1

Mmap is a new MyISAM feature available with MySQL 5.1. Should improve MyISAM write/read performance ~6%.

To sum up all the settings on a 4GB work environment:

transaction-isolation = READ-COMMITTED
key_buffer = 512M
query_cache_size = 256M
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 32M
innodb_log_buffer_size = 4M
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
table_cache = 1024
myisam_use_mmap = 1

Buy an SSD disk

This is by far the best upgrade you can do. SSD does not have any moving mechanical parts therefore doing a random read or write is as fast as doing a sequential read or write. My work laptop Lenovo T400 can push 3.5 MB with random writes, 35 MB with sequential writes, 2.6MB with random reads and 38MB with sequential reads per second. The same test with an SSD disk can push 220MB random writes and 330MB random reads with similar numbers for sequential reads and writes. So for IO access you can expect 10 – 100 times performance difference.

Summary

It’s easy to squeeze some extra performance out of your development environment by sacrificing data safety. In my case these changes made our database integration test suites run a lot quicker. So far I haven’t experienced any downsides from the above settings though you have to accept that one day it most likely will. Most of the database settings I’ve mentioned are those considered most when tuning production database servers. My final advice is take everything you read here with a pinch of salt as I am by far not an expert in these matters and everything listed here is gathered from various resources online.

Resources

InnoDB performance optimization basics
Tunning MySQL server after installation
MyISAM MMAP feature
MySQL transaction isolation levels
Why you should ignore key cache hit ratio
Tweaks to boost EXT4 performance
|SSD Benchmarks

RSS Feed

7 Comments for Optimizing MySQL on Ubuntu 10.10 Maverick

shinguz | November 25, 2010 at 6:51 AM

Hi Žilvinas,

Do you have any numbers in performance increase for the file system tuning where I can see it is worth the effort?

Just because 5% improvement I would not start with those measures…

Further search around about setting MySQL to read-committed. I think I have seen some articles why it is NOT a good idea to change this. 2 reasons are: Most applications expect repeatable-read and no one knows what happens if this is changed and you leave the main track and may experience problems nobody else have experiences before…

When you really have performance problems on a dev machine you should think about queries and architecture first…

Regards,
Oli

Author comment by Žilvinas Šaltys | November 25, 2010 at 9:31 AM

Hi Shinguz,

Thanks for your reply.

It’s not 5%. If you’re using MySQL on Ubuntu with EXT4 just by setting barrier=0 will increase your write performance from 5 to 10 times. Maybe I’ll provide some numbers later.

I disagree with you on “most applications expect repeatable read”. That would be true for most applications that use transactions heavily. Some might use InnoDB but not rely on transactions. Actually it’s very well known what happens when you change this setting. Basically if you set it READ-COMMITED your transactions will no longer have repeatable reads. That is If you read from a table and get two rows, if some other transaction deletes a row from that table and commits and you do a read again you will see one row less. While that does matter on your production environment in most cases it won’t matter on your dev box since you’re the only one running transactions.

You can potentially have problems but you have to understand what you’re doing.

John | February 4, 2011 at 10:25 AM

Excellent post – incredibly useful.

By completing the EXT4 section of your guide the runtime of my test application went from 221 seconds to 48 seconds!

Thanks.

mark | January 16, 2012 at 10:56 AM

yeah, this is cowboy stuff. do this on a production server and expect to be fired.

Author comment by Žilvinas Šaltys | January 24, 2012 at 10:01 AM

Well the FS changes are cowboy, but most of mysql settings are standard.

Ryan | May 16, 2012 at 3:52 PM

Some of these things aren’t as cowboy as you may think.

Turning off barriers is something that many of the distros are starting to do (i.e. RHEL in the performance tuning). It all depends on the hardware you have (battery backed cache, redundant power supplies, etc). It may be worth lowering the barrier instead of disabling it completely.

Mounting noatime,nodiratime are good ideas.

I wouldn’t set the journal to writeback and commit to 100 are bad ideas on production.

Other thoughts:
Move you mysql data to some other partition off of root (and ideally on a different set of disks) and set the mount options for this partition.

Upgrade from 10.10 (which is why I found this post). Write performance on 12.04 is far better due to the years of patches in place for ext4, the kernel, LVM, etc.

Piotr | September 26, 2012 at 2:13 PM

excellent! RoR rspec tests from 3900 seconds to 620 seconds!
only with the fs tune-up.

Leave a comment!

<<

>>

Find it!

Theme Design by devolux.org