The mysql proxy project has tremendous potential to make mysql administration and usage easier. I decided to throw some load at it to get a feel for how stable and performant it is.

On EC2, I set up 6 “small” images in an example proxy setup:

- One client machine to run sysbench
- One machine to act as a mysql proxy machine, running 0.6.1 (FC4 binary)
- Four identical database servers, running mysql 5.0.45

The database configuration was largely default, with InnoDB configured for 64MB buffer pool (just enough to ensure the sysbench table was entirely in memory), 512MB log files, and 1024 max connections.

mysql-proxy was run with the following command:

mysql-proxy –proxy-backend-addresses=ip-10-251-66-63.ec2.internal:3306 –proxy-backend-addresses=ip-10-251-71-21.ec2.internal:3306 –proxy-backend-addresses=ip-10-251-43-70.ec2.internal:3306 –proxy-backend-addresses=ip-10-251-75-52.ec2.internal:3306

Using the sysbench database benchmark utility to generate some heavy load on the boxes individually (to verify they were all more or less equal), and on the proxy itself, I got some interesting results:

Read/write operations/second:



Transactions/second:

Somewhat as I expected, mysql-proxy presents a bit of overhead when load is light. With only 8 concurrent threads, running against the proxy pointing to 4 equal database servers is slower than hitting one server itself.

As load goes up, however, you can see that the proxy scales reasonably well, while the accesses to the direct systems begin to tank rapidly after 64 concurrent threads (I lost some of my 128 thread results inadvertently and the instances are destroyed I’m fairly comfortable in interoplating the result to be roughly in between 64 and 256 )

Somewhat unsettling, however, was the fact that sysbench or mysql proxy failed to work beyond about 500 concurrent threads. My debug output for sysbench just stops here:

DEBUG: mysql_real_connect(0×9adec70, “ip-10-251-75-160″, “sbtest”, “(null)”, “sbtest”, 4040, “(null)”, CLIENT_MULTI_STATEMENTS)
DEBUG: mysql_init(0×9ae51b0)
DEBUG: mysql_options(0×9ae51b0, MYSQL_READ_DEFAULT_

On the servers themselves, I can see the 125+ connections to each individual box, and all looks normal. I tried tweaking some of the thread settings in ulimit and sysbench itself to no avail, but didn’t have a ton of time to play with it. I decided the results were already interesting enough and will see if I can revisit this another time.

Another reason i am not so concerned is that more than one proxy can be utilised in many typical environments, so the ability of a single mysql-proxy instance to handle 512+ concurrent connections might not be show-stopper.

If anyone out there has done some similar tests with mysql proxy, i welcome your feedback!

A good rule of thumb is that a DBA should not let the size of his/her database grow to much more than 50% of the available disk space. I’m sure everyone has a story of being burned in some way by not following this advice.

Before promoting 5.1 to some beefier hardware, I’ve got a few large tables sitting on a small SATA-based machine. Noticing that a few were growing faster than I expected, i decided to pause my loading and convert another one of them to the archive storage engine to save some space.

I certainly saved space.

By trying to do too much at one time, I had some other temp files lying around on the same filesystem, and my conversion to the archive storage engine failed at about 80% through with my disk full.

mysql> alter table w_stats engine = Archive;
ERROR 1030 (HY000): Got error -1 from storage engine

mysql 5.1 has an unfortunate bug that I discovered, and reported, that causes the unfinished archive files to remain after the failed conversion, and not the original data. My disk space problems are no longer much of a problem

Now I know that this was basically my fault, and that I should have been more careful with my system, but I would have preferred maybe an embarassing error message, a slap on the wrist, not the loss of a 150GB table! For example:

mysql> alter table w_stats engine = Archive;
ERROR 1030 (HY000):  df -h is your friend

Well, it sounded funny in my head at least…

Testing MySQL sequential IO performance with different IO schedulers - Part 1

I stumbled upon an interesting article discussing the different Linux I/O schedulers that are available in the 2.6 kernel, and also discussing some of the deficiencies of the 2.4 “Linus elevator” I/O scheduler.

The default scheduler as of Kernel 2.6.18 is the “Completely fair queuing” scheduler, or CFQ. The previous default was the anticipatory scheduler between 2.6.0 and 2.6.17. Also available is a buffed-up version of the 2.4 scheduler called the deadline scheduler, and a “noop” scheduler. This article has a great discussion on what these schedulers mean for databases at a higher level.

Although old, some documentation in the kernel states  that database workloads should typically use the deadline scheduler. This tuning guide for Oracle on Linux gives, without much explanation, the same advice .

I don’t have the time at the moment to do a full analysis (hence part 1), but I did some quick tests and already got some pretty surprising results. I’m hoping to see if anyone else has done some tests along these lines that can comment further.

The test

I have a 140GB table sitting on a test machine with a simple 320GB SATA drive.

Changing the scheduler in newer kernels is a simple tunable parameter. You can change it to the deadline scheduler for your sda device, for example, as follows:

echo deadline > /sys/block/sda/queue/scheduler

I ran a simple count(*) on one of the partitions, 13GB in size, to see if there was a measurable difference between the schedulers. Memory size on this system is 4GB.

These are the results I got on an otherwise idle machine. The CFQ and anticipatory results were very consistent, almost to the second each time, while the deadline and no-op schedulers fluctuated as much as 50s per test. Not sure at this point why this is the case.

Now, these results come with the following caveats:

- These are not extensive, robust benchmarks, they’re just a few tests I ran against my very specific workload. I encourage people to try the different schedulers on different workloads and hardware and share results!
- These are test results with a large, sequential I/O workload that is very different from the random I/O intensive tests that most people are interested in.
- These are test results against a single ATA drive. These results are probably irrelevant in a hardware RAID context

In a future blog post, I will try to dig a bit deeper into these results and try the tests against hardware and software RAID. What i think can be taken from this already, and the referenced articles, is that CFQ should probably be used if your kernel has direct knowledge of the physical layout of your disks, eg. through LVM. Otherwise, deadline or even noop is probably your best bet if you are using hardware RAID, putting the burden on the hardware to decide how best to execute reads and writes.