In continuing with my personal exploration in BLOBs, I thought I would change gears a bit. Previously I had focused on benchmarking the
MyBS engine being designed by Paul McCullagh (the results of which you can find
here,
here, and
here). But this time around I thought I would try something a bit more direct.
I have been playing around storing photos as BLOBs in the database for my photo album application (in preparation for testing how the MyBS engine scales as opposed to storing images on the filesystem). So since I had all that data there already, I thought it might be fun to try and grab the BLOBs directly from the database using a a SELECT...INTO DUMPFILE... I whipped up a quick bash script which first runs an ALTER TABLE ... ENGINE=XYZ and then grabs a 2.8MB image from the images table (which is about 1.5GB in size). I opted to write the output to a file in /dev/shm just to try a mitigate any filesystem issues. I then wrapped each run around the time command and took the last 10 runs of each test (as the first run was always much higher than the rest). The results were a small bit surprising to me:
| Engine | Min | Max | Avg |
|---|
| MyISAM | .089 | .091 | .0901 |
|---|
| InnoDB | .085 | .086 | .0856 |
|---|
| PBXT | .080 | .082 | .0817 |
|---|
As you can see, InnoDB was faster than MyISAM with PBXT actually being the fastest. The difference is quite small, but was fairly consistent across all the tests. I was not surprised to find PBXT as the winner since one of the design goals, as I understand it, was efficient blob handling. I was a bit surprised, however, to find that InnoDB was faster than MyISAM. Particularly given that I was using a stock MySQL configuration (the only difference being that max_allowed_packet=16M).
Truth be told, I do not have a good answer for why it is faster. Even so, as with most of my benchmarks, the results should be taken with a grain of salt, and really are there only to spark interest. Mileage, after all, varies. For one, this was a single-threaded and somewhat clunky test. It would be interesting to see how things scale over a multi-threaded tests, particularly when grabbing random images from the database. But that test is for another day as it is time to start enjoying my weekend :)
Tags: