Saturday, February 26, 2011

Why the Facebook/Percona versions of MySQL are so much better

One of my roles at Yahoo is to provide a rock solid infrastructure for MySQL-based projects. In order to provide that infrastructure, I have to satisfy many groups at Yahoo: Developers need an easy way of deploying the db server in their environment. Software Engineering (Operations) is looking for features to help scale MySQL, and DBAs are looking for more knobs and gauges to help tune the server. After about one year of attempting to manage the infrastructure, and following the MySQL sagas, my job is getting easier thanks to companies like Facebook and Percona as well as one of my predecessors (jcole) for releasing quality code that extends MySQL in so many ways than just raw performance. I've been able to take the best code from each company and mold together an internal version that satisfies all crowds. MySQL/Oracle, you should do the same.

Tuesday, February 15, 2011

Are you using the deadline scheduler? (Part 2)

The deadline scheduler has many advantages over the cfq scheduler, which is the default in operating systems like RedHat Enterprise Linux. In my previous post, I quickly showed how much of a performance gain can be had by switching to the deadline scheduler. Now I will show some real performance numbers for different RAID configurations.

2 disk RAID-0 4 disk RAID-0 6 disk RAID-5 6 disk RAID-10
CFQ 461.467 947.067 845.943 862.763
Deadline 851.067 2876.933 2145.866 2580.430

All of the tests were performed on a Dell PowerEdge 2950 with 2xQuad Core Xeon,16GB of memory and 6x146Gb SAS drives on a Perc/5 RAID controller, and all filesystems were standard EXT3. The TPCC benchmarks were conducted with a smallish buffer pool (2GB) and a 1GB log file size. The database is approximately 7GB in size (100 warehouses). I wanted to show what performance an I/O bound test would yield. The numbers here show that its possible just to get that added boost without resorting to re-creating the entire database with a different filesystem (like XFS). I will come back to XFS later as it provides the best performance.