How Much Resources Does the BLACKHOLE Storage Engine Consume？

Question

I'm going to add a MySQL Relay Slave which use BLACKHOLE Storage Engine.

The relay slave will just filter some databases binlog to downstream slave.

Our MySQL Master generates about 15G a day in binlogs.

How much CPU and Memory will be enough for this use case?

Answer 1

If a relay slave (a.k.a. Distribution Master) is using nothing but the BLACKHOLE storage engine for application tables, you really should not have to tune anything for MyISAM or InnoDB since there are no data or index pages to cache.

You will have a nominal amount of disk I/O (mostly reads) since the .frm file for every BLACKHOLE is still accessed for metadata purposes. This same behavior also happens to MEMORY tables. I wrote a post about this. I helped the person asking the question realize that dropping and creating MEMORY tables created disk I/O.

I presume you are not going to drop and add tables all that often since the relay slave needs to remain all BLACKHOLE. Nevertheless, CPU and Memory should not be a big factor on a relay slave. You should be concerned with the following on the Relay Slave:

• Sufficient Disk Space for Binary and Relay Logs
• Fast Enough Disk for Writing
• Setting sync_binlog=1 in my.cnf for flushing binlogs thoroughly
• Rotation of Binary Logs (setting a proper expire_logs_days)
• Avoid replicating the following:
  • storage-engine specific ALTER TABLE statement that BLACKHOLE can't work with
  • ALTER TABLE statements not accepted by BLACKHOLE, InnoDB, and MyISAM
  • creating tables with ENGINE=InnoDB or ENGINE=MyISAM because you want all tables in the relay slave to remain BLACKHOLE.

I have addressed this scenario before

• Aug 20, 2012 : How to use MySQL Server 5.6 for GeoIP replication?
• Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?

UPDATE 2014-08-11 11:47 EDT

Additional Clarification : A pure Relay Slave (Distribution Master) should never have MyISAM and InnoDB tables present. Suppose you run CREATE TABLE (...) ENGINE=InnoDB; on the Master. The Relay Slave will attempt to create the InnoDB table on the Relay Slave. If the Relay Slave has skip-innodb configured, it could possibly end up as a MyISAM table. You want to avoid that because it will increase disk I/O and InnoDB or MyISAM activity.

If the Relay Slave has both skip-innodb and default-storage-engine=BLACKHOLE configured, you would have to run a create table with identifying the storage engine (CREATE TABLE (...);).