As I don’t develop C# application at work anymore, I was finally allowed to switch from Windows to Linux. So I grabbed the 64-bit version of Mint Debian Xfce, installed it and moved my Thunderbird profile and my Python projects. After installing new Python virtual environment I launched the tests for our biggest project using Twisted. Tests passed, but it seemed to me that they run way too long. So I launched Windows once again and compared the time. Under Windows XP tests run for about 40 seconds and under Linux – for over 200 seconds. So I wondered how the hell was this possible, Linux can’t be 5 times slower than Windows.
I run the tests under Linux and checked the system performance stats. The CPU cores utilization was about 12%. After further investigation I discovered that during tests over 40% of the time CPU was waiting for the disk. So I installed the iostat tool, which showed me that 95% of the IO operations were made by the jbd2 process.
When installing Mint, I decided to give the ext4 partition a chance, as I haven’t used it before. The jdb2 process is updating the ext4 journal. As far as I know ext3 has a journal too, but I didn’t noticed any problems with it.
After wasting some time trying out some random solutions I googled for, I found out that adding the nobarrier flag for my ext4 partition solves my performance problem. It seems the MySQL is using the fsync extensively, which can lead on ext4 to some serious performance problems. Disabling the barriers fixed the problem, but I wasn’t happy with it, because this option may result in hard filesystem crashes in case of power failure.
Test results
And so to investigate the problem I launched the Mint Live CD, shrunk my root partition and created a new 30GB partition for my MySQL data. After that I ran my integration tests while changing the type of this new MySQL partition. Here are the results:
| Partition type | Time (in seconds) |
|---|---|
| ext2 | 49 |
| ext3 | 40 |
| ext4 | 160 |
| ext4 * | 201 |
| ext4 (nobarrier) | 42 |
| jfs | 54 |
| xfs | 124 |
| btrfs | 166 |
* both the testing application and the database were on the same ext4 partition
Conclusions
Well, as any of us, I want my applications to respond quickly to our users. And sometimes the database can be a bottleneck, so I very carefully design the table schema and create the indexes. As I discovered, the performance downgrade can come from an unexpected direction. In my example, it was a partiotion type, so learn from my mistake and DO NOT CREATE EXT4 PARTITION FOR MYSQL DATA :). If you, for some reasons, have to use ext4 for that, you can disable the barriers, but be aware of the potential data loss in case of power failure. Right now ext3 seems to me to be the the best choice – despite having a journal, it is faster than ext2 (in fact it’s the fastest partiotion type for MySQL).
The ext4 doesn’t seem to be ready right now. The interesting part about it was that two ext4 partitions on the same disk (one for the system and testing code and the second for the database) were faster than a single ext4 partition – it seems that MySQL fsyncs slowered down the whole system.
Luckily there are a lot of performance tracking tools (like top or iostat) that monitor the CPU/memory/IO/network usage. This takes some time, but helps to track down the problem.
And the most important thing – at the end, the Linux proved to be faster that Windows, so I don’t have to go back to my XP installation. Phew! 🙂
Interesting article!
We are currently struggling with our database performance in a new system using ext4 compared to older systems using ext3 – the behavior you describe here may just be the same we observe (high I/O waits).
Unfortunatelly, ext3 is no real option for us, as we need the support of 32K+ subdirectories. Using nobarrier might be an option, though.
Toblas, the nobarrier should help, if you run your server on laptop with a battery or have an UPS device. Otherwise it can cause some data loss.
I wonder why ext3 is not an option. You can have your whole system using ext4, which would support 32K+ subdirectories. Just create single ext3 partition and mount it under the MySQL data directory. I doubt this single dir will have 32K+ subdirs 🙂
Great post. I was really looking for concrete info on which file system to use for mysql on linux. Also, my scenario is the same, moving from windows to linux and this project may not fail. I am also finding the lack of portable benchmarking tools a problem. I also had some other problems lately with ext4 and logical volume management and this suggests that ext4 may not be ready yet for prime time.
hi we are in the end of 2012.
Can you do this bench on actual ext4 for see? thanks
I’m not sure why you see ext3 with nobarrier as preferable to ext4 with nobarrier, I mean it may be true but the above presents no argument for prefering it.
The Linux world have been running MySQL on ext3 for a long time, and in a data centre environment it proves very robust (more so than NTFS), although typically in a datacentre you have little risk of sudden power outages to the disk array (e.g. the type being safeguarded against).
We are now in 2015. These ext4 problems still persists. Thank you for your very useful article that solved my problems.
Thanks Man!
This may really help a lot. I’ll move from ext4 to ext3 and check if my disk performance problem, which is bothering me now for some time, is solved.