MySQL’s BLACKHOLE Storage Engine

The BLACKHOLE storage engine is named as “black hole” because it accepts data and does not store it. If you wiil insert any record it will prompt you that tables are updated nut when you will hit a select query on same table, you will be return an empty result:

Below is the example,

       The BLACKHOLE storage engine comes by default with  in MySQL. If you build MySQL from source, then parse the –with-blackhole-storage-engineoption.

      When you create a BLACKHOLE table, only the .frm file is created, because it does not reuire to store data so no .MYD and .MYI file needed. You can check whether the BLACKHOLE storage engine is available by running below MySQL statement on mysql prompt.

      Inserts into a BLACKHOLE table do not store any data, but if the binary log is enabled, the SQL statements are logged and replicated to slave servers.

      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 bunch of slaves reading the log from that master which in short increase the network traffic, disk I/O, CPU power and others, this affect the performance of a Master Server.

      So to solve this problem you can take a help of MySQL’s BLACKHOLE storage engine as shown on below example.

      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.

      The master writes to its binary log. The “Blackhole” server acts as a proxy slave server, it does not actually store any data, so there is little processing overhead incurred and other SLAVE server will read a binary log from this server.

    
Other possible uses for the BLACKHOLE storage engine include:

* Verification of dump file syntax.
* Measurement of the overhead from binary logging, by comparing 
  performance using BLACKHOLE with and without binary logging   
  enabled.