MySQL (2 blogmarks)

Reading through the ActiveRecord Migrations docs I came across an example demonstrating how to specify database-specific options like ENGINE=BLACKHOLE.

What is ENGINE+BLACKHOLE I wondered.

The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result

The provide the following code block to demonstrate the above:

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
Empty set (0.00 sec)

Reading further into the MySQL docs on this, there are all kinds of interesting behaviors and use cases:

Suppose that your application requires replica-side filtering rules, but transferring all binary log data to the replica first results in too much traffic. In such a case, it is possible to set up on the replication source server a “dummy” replica process whose default storage engine is BLACKHOLE

To avoid contention on a single high-write counter column, you can create a generic slotted counter table with polymorphic associations to anything that needs counting. Then to update the count, you increment the count in one of one hundred random slots. You can either sum the counts for a specific record and type to get the count, or you can have a process to roll up the count periodically and store it nearer to the original record.

I wonder what heuristics you could use to scale the number of slots you use for a given entity. That way for a relatively low-update entity, you spread the counts over, say, 3 counter slots. And with a very high-update entity, you spread it across, say, 50 or 100 slots.