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

innodb, Linux, MyISAM, MySQL, performance, Tuning, Ubuntu Hide

I was working on a small web application that creates Subversion branches and tags. In short it just executes SVN commands on the repository. Whenever a user executes an SVN command the SVN client tries to check user’s local home folder for the .subversion configuration directory. The issue that I was running into was that for some reason apache’s home folder was pointing to our system’s administrator home folder which in turn would result in a permission denied error when apache would try to access the .subversion folder.

It just didn’t make any sense. Turns out if you start a service through /etc/init.d/ it starts that service with environment variables belonging to the user that started the service. In this case our system’s administrator started the service using his own user.

To start services in a clean environment a special utility called service should be used. It usually resides in the /sbin directory. So for example instead of starting apache like this:

$ sudo /etc/init.d/httpd start

It should be started like this:

$ sudo /sbin/service httpd start

Which will result in $HOME environment variable being empty and the SVN client not getting a permission denied error.

Apache, centos, Linux, redhat, services, SVN Hide

I’ve been using Windows as a development platform for more than three years now. Couldn’t say I find it always productive, but I also wouldn’t say that it is very troublesome.

I have been a Linux user for a few years quite a while ago. Tried debian, redhat, slackware and gentoo. Went through the pleasures of compiling kernels, trying to enable the right audio drivers, not being able to make my ATI video card work, experimenting with Wine, going through a guide to setup hibernation, trying to set the right opacity for my terminal screen. I would dare to say Linux systems were still in dark ages. It was challenging to get everything right and have all the shizzle that Windows users had by default. At some point I just couldn’t be bothered anymore and started using Windows again.. Next, Next, Finish.

And Windows is great. Up to a point. First you get all the default stuff taken care of like for example enabling a printer over network, plugging in a new USB device, setting up hardware drivers, sharing files. Setting up a development environment is as easy as downloading Xampp, an editor of your choice and you can start whipping some source code. And Cygwing helps a lot by delivering a lot of every day use tools to a Windows based platform.

Until you hit the rocks. For example a famous Xdebug PHP extension has problems working with Windows Vista and will often crash PHP processes. Extensions like ffmpeg-php are not maintained on Windows and are only available on Linux and it makes matters worse when you for some reason can’t do without them. Or for example good luck developing Drizzle on windows and building it using Cygwin, when some networking library just won’t compile. Suddenly you realize that your whole career is built on top of opensource tools which most of them were built and are maintained on Linux systems.

That is why I’ve decided to give Linux another go. I have selected Ubuntu Jaunty 9.04 to carry on my experiments. Here follows my list of pros and cons of using Ubuntu as a development platform.

I’ll start with cons:

1. The latest version of PHP is 5.3.0. Ubuntu package management system offers 5.2.6. I found this a bit dissapointing. I really don’t want to be bothered to compile PHP or to search for 3rd party packages. I understand the philosophy behind this, but it still saddens me as a developer. Especially when PHP 5.3.0 builds are available on Windows as next, next, finish packages.
2. Same goes for Eclipse IDE. Not only there were no packages for Eclipse 3.5 but I had to download Eclipse myself, find out which other dependent packages are required (like Java runtime) and then follow someone’s guide to make it work by passing magic parameters to the launcher.
3. My favorite broswer Chrome is not available on Linux. There is ofcourse Chromium which still has problems with Flash and Printers.
4. Trying to set up dual screens was a bit troublesome. Ubuntu couldn’t identify the best native resolution for my external display and I had to find out how to add the resolution myself. Not to mention about setting which display is primary and what is the location of the other display compared to primary one. It’s still a complete mistery to me.
5. Configuring apache. Don’t get me started. How to start apache? Where’s the main config file? Oh it’s different from Windows! How to enable mod rewrite? How to add a virtual host? Had to learn about the a2* utilities familly to solve my problems. It’s easy when you know how they work and that they exists, but if you don’t …
6. When my laptop suspends and recovers it has problems accessing samba network shares. Still not solved.
7. What is the shortcut to go to Desktop? What is the shortcut to logout? Was hoping it would be the same as on Windows. It’s not. Though it’s not a big issue.
8. Ubuntu has no nice default screensavers (joking)

Enough with the bashing. The pros:

1. Superior speed. Starts a lot faster that Windows. Especially if compared to my Lenovo laptop which comes with a lot of preinstalled crap applications which I didn’t want in the first place.
2. Lot’s of problems solved. Audio, USB, printers, network sharing, video playing just works.
3. Amazing package management solution. Whatever you need, just search and install. And you are sure the packages are clean from viruses (hopefuly)
4. All opensource tools are available. When a package is not available it’s possible to find a 3rd party build or build it yourself. Helps a lot when you need that one magic extension.
5. A lot more easy to set up a development environment than it was before. I have never installed phpmyadmin that easy.
6. Virtualization is easier than ever. You can even have windows applications running along with Linux ones.
7. Some applications are more well built. Like Skype for example allows me to disable contact request spam notifications. Which is a huge problem on Windows nowdays.
8. Completely FREE.
9. Looks nice! Has lovely eye-candies! The Cube!

So far that’s my experience. I’ll keep using Ubuntu at work seeing how it goes. My main wish for Ubuntu is for it to be 3 times more faster than it already is. Just like Chrome.. All in all I must say Linux desktop has advanced a lot further than expected by me. I would highly recommend it for schools, universities and home users who don’t play games.

cons, Linux, migrating, pros, windows Hide

Have you ever wanted to make your CMD window fullscreen? You did? And it didn’t work? Too bad. Have you ever wanted to search something in your files using grep, awk, less, sort? Have you ever tried to mass rename your files ? You did? And it didn’t work? Too bad.Be worried no more! First you need Cygwin. Cygwin is a Linux-like environment for Windows. It also provides you a lot of tools which provide linux look and feel. Install cygwin while changing none of the default settings. Now you can do something like this:

Notice that this is a simple CMD like window but it now has some nice colors and new shiny tools.Now in order to make it even more cool you need a tool named PuTTYcyg. PuTTYcyg is a patched version of PuTTY that, in addition to telnet, rlogin, ssh, and serial connections, can also be used as a local Cygwin terminal instead of the Windows console. Download the package and unzip it somwhere. I put it in C:\Program Files\puttycyg. Now double click on a file named putty.exe. Now fill everything as in the image bellow and press save.

Now make a shortcut of this putty.exe on your Desktop. And change it’s properties like this:

Click apply and you are ready to go myfriend! Double click on the shortcut and you should be able to do thins like this including full screen resize yay!

Now .. Begone CMD!

cygwin, Linux, terminal, windows Hide