Working with BLACKHOLEs in MySQL

If you have ever worked with MySQL, chances are you have stored your data inside of it. If you have ever stored data inside of it, chances are you have used one or more of MySQL engines. This blog post should provide you some insight about one of the engines MySQL offers – we are going to be talking about a BLACKHOLE.

What is a BLACKHOLE?

Before we actually tell you how you should work with BLACKHOLE engines in MySQL, we should probably tell you what a BLACKHOLE actually is. A BLACKHOLE is one of MySQL storage engines – the engine accepts input (data), but does not store it and always returns an empty result. Think about space for a second – a black hole in space is a region of space where gravity is so strong that nothing can escape from it. Essentially, it’s a place in space where even light cannot escape: once something gets pulled into it, even the light cannot get it out. The BLACKHOLE storage engine in MySQL is an equivalent of a black hole in space.

What’s the use of a BLACKHOLE Storage Engine in MySQL?

Now that we have answered what a blackhole actually is, we should probably tell you how it works in MySQL. In MySQL, a BLACKHOLE is a storage engine that accepts data, but never stores it and always returns an empty result.

Since a BLACKHOLE engine never stores data, you might wonder whether there’s any benefit of using such an engine in the first place? The answer is yes, there are benefits to such an approach.

Such an approach might be useful in a replicated environment where all queries are run on all nodes, but only need some nodes to store the result. The engine can also be used to verify the syntax of dump files or it can also be used to find performance bottlenecks not related to the storage engine itself. It can also be used as a binlog server with reduced capacity requirements.

All inserts to such tables can be logged when performing binary logging, but updates and deletes are handled differently depending on which form of logging is in use: when row-based logging is in use, updates and deletes to such types of tables are simply skipped and not written to the binary log. A warning is generated afterwards (see the changes in MySQL 5.6.12 in MySQL 5.6 release notes for bug #13004581).

Summary

Think of the BLACKHOLE storage engine in MySQL as a blackhole in space – it is not useless and definitely has its purposes, though those purposes might only be applicable in very specific scenarios. Though when used to its full potential, the BLACKHOLE storage engine can definitely help you to solve certain problems related to MySQL including verifying the syntax of dump files or finding certain performance bottlenecks.