Why is the MySQL Blackhole storage engine useful?

When reading about the different MySQL storage engines, I was wondering why on earth anyone would want to use the Blackhole engine.

“Why would you want to run an insert query that didn’t actually insert anything?”… And today the answer hit me (well, a few months ago, but I only got bothered to blog it today). For me, the perfect use of the MySQL blackhole storage engine is Testing.

I want to run unit tests that check all my queries run fine, but I don’t care about the actual data being inserted. Two actual examples from my work are: - We have a cron that runs nightly raising and lowering “Flags” for our clients (E.g. low balance, low interaction) - A communication log which archives all emails/sms sent from the system

Neither of which we care about when running our automated test suites. So to increase performance and reduce disk IO we now alter the tables to a blackhole before the tests run.

Do you have any other examples of uses of the blackhole engine? Comment them below :)

Update

It’s been pointed out to me a lot that the engine is particularly useful for replication slaves. That is, when you want a middleman server that delivers content to read slaves in a MySQL replication setup you have that machine use the blackhole engine so it doesn’t execute the queries itself saving on disk/cpu IO, but passes along the queries to its slaves via the query log.

There’s a good write up by Daniel Schneller here.

comments powered by Disqus