As you may know there are many storage engines in MySQL MyISAM, InnoDB, Falcon, CSV, Blackhole, Archive, etc...There is a storage engine that comes with the MySQL Max Download called the "Blackhole Engine". According to the documentation it basically dumps it's storage to /dev/null. A storage engine that doesn't store anything? What good could that be?

Well if you run in a high volume production system where you may have one or more master databases for writes/updates/deletes and a whole farm of slaves reading the log from that master than this may be of interest to you. The concept is pretty simple. You have a Master database that is in charge of all your inserts, deletes, updates which in turn has connections to all those slaves. That means network traffic, disk I/O, CPU power all taking up resources that you really want for the Master's primary goal of collecting and maintaining data.

This is where the Blackhole Engine comes in. The actual process of logging the SQL statements that hit the Master database that the slaves consume lives above the storage engine level in the main MySQL server level. So with the Blackhole Engine piping data to /dev/null you can actually use it as a proxy to your slaved databases without the need to duplicate the data on that machine (it could very well be on the same machine!). See below for an example image...

What you then have is the Master only replicating to one database, increasing the Master's capacity to process transactions. The slaves consume the log file from the Blackhole server. It acts as a proxy at this point, with the benefit of freeing up resources on the Master's server. This could also benefit multi continent replication set ups where you can have farms of blackhole servers farming data to farms of slaves around the global for faster local access times. The possibilities are endless!

Ready for More?

Follow Me @jimplush