MySQL: The BLACKHOLE Storage Engine, Perform Dummy Insert on Table

Have you ever wished that you do not want to insert any data, but want to execute INSERT command ?

This is looking like a very strange requirement, but this is possible in MySQL.

MySQL has a BLACKHOLE Storage Engine which we can use for the empty or the dummy INSERT on Table.

When we created a BLACKHOLE table, It creates with only .frm table format file and It has no any other file associated with it.

When you execute your INSERT on BLACKHOLE Table, It stores only binary log and It does not store any kind of data.

When we should use BLACKHOLE Storage Engine?

When we require to measure the performance only on the binary logging process. Mostly we require to test binary logging performance for bulk operations.

When we require to find performance bottlenecks not related to disk-storage.

When we require slave-side data validation and filtering, we can create a dummy BLACKHOLE table at the master-side which reduces the overall traffic.

Check the status of BLACKHOLE Engine:

Create BLACKHOLE table:

Try to insert few records:

Check inserted data, which return Empty Set:


Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Sort by:   newest | oldest | most voted
kuldeep sirohi
Guest

this is actually beneficial when there are multiple slave and instead of connecting to the main master server you connect them to a log server ” which is actually having tables in blackhole”. this reduces the load on master server upto some extent.

wpDiscuz