So not too long ago, I ran some simple (and perhaps archaic) benchmarks pitting different engines against each other in terms of how they performance with BLOBs. While I ran into some interesting results, there was one fairly important storage engine that I neglected to test - Falcon. So this time around, I thought I would try some similar tests against MyISAM, InnoDB, and Falcon. I wanted to do PBXT as well, but was unable to get it to work with MySQL 6.0, and didn't think it fair to run PBXT on MySQL 5.1 since 6.0 appears to be slower (likely due to debugging options I would imagine).

Anyways, this time around I opted to use SQLBuster, a little PHP-based script I wrote to help test various queries and concurrency. It works quite well, but the problem is that I am using multiple processes, not threads, to perform the test, which I think tends to skew the results. So for this test, I used only 4 threads over 1,000 iterations. All tests were against the following table:

CREATE TABLE `PhotoAlbumImages` (
  `photoid` int(10) unsigned NOT NULL,
  `thumbnail` mediumblob,
  `midsize` mediumblob,
  `fullsize` longblob,
  PRIMARY KEY (`photoid`)
) 

For these, I had the script randomly pick a photo from the database (using PHP's rand() function running over the range of the id's). The first tests used three queries - one for each item (thumbnail, medium-sized image, and the full size original). The last three tests focused on grabbing just one photo from the database, including just the thumbnail and the fullsize variant. In all tests, the results were fairly consistent:

Test	MyISAM	InnoDB	Falcon
Default Configuration	4.53	6.02	5.05
innodb_buffer_pool=384M innodb_additional_mem_pool_size = 20M falcon_page_cache_size=384M	5.31	3.75	5.61
Same As Above + falcon_disable_fsync=true	5.65	4.02	6.24
Same As Above + falcon_record_memory_max = 256M	4.41	3.90	6.35
Grabbing Same Row	5.17	3.11	5.21
Grabbing Same Thumbnail	2.76	0.08	0.09
Grabbing Same Fullsize Photo	10.05	8.17	13.56

When making good use of InnoDB's buffer pool, it really seems to shine. Oddly enough, I was unable to really tweak Falcon to get more performance out of, at least for this test. I am not sure while, but I can only assume that it is because it is still alpha. That or the configuration parameters may not affect BLOBs since Falcon, as I understand it, treats BLOBs differently (hence the desire to test it).

As with basically all of my benchmarks, you should treat them pretty much with a grain of salt. I don't claim that these are accurate, or even useful. I just do these as food for thought and to hopefully spark some deeper discussions on the matter. Even so, I found the results rather interesting and am curious to see how things change as Falcon matures...

Tags: