Our MySQL master is allocated 24GB of RAM (innodb_buffer_pool_size = 24GB), but was curious if there was some standard amount to allocate to a replication slave?
Right now the slave is just used to take dumps without locking tables on master, and in theory be used as an emergency master if something happens (hasn't come up in the last 18mo). If master fails I could always quickly allocate the slave 24GB, but how much does a slave really need to just sit there replicating?
The amount of memory should be the same, the config should be the same (except config that pertain to Master / Slave only).
Reason is when you fail over, you don't want to spend time trying to find out what value should be there or not. I would even create 2 my.cnf files: my.cnf and my_failover.cnf (prepare my.cnf on Slave in case master fails. File can contain binlog=1 (turn on bin log).
All you should need to do is:
mv my.cnf mybk.cnf, my my_failover.cnf my.cnfThen your slave will start login.
Regarding caching queries, well, all Select statement running on Master won't replicate to Slave. If your Master is 80% Select / 20% write, it should not be a problem.
If you want to cache your Select from Master, turn on Slow Query log for query > 1 sec then apply that to Slave. You could do that once a day (in morning).
Chances are, soon you'll be using your slave for reporting.
You could also use Percona Playback.
It should have the same amount of RAM. In fact, it should have the same hardware configuration.
Here is why:
The MyISAM key cache, InnoDB Buffer Pool, and the query cache will not be filled with the same amount of data. Naturally, the Master will have more data loaded in its caches.
What if you have to fail over to the Slave to be the new Master? You would get what some call a CACHE STORM. It is a condition where you use a Slave with the expectation of running queries you ran on the Master to now run on the Slave. Here is the Storm Part: If the Slave does not have the needed cached data in its caches, it has to spend time reading it from disk. For a Slave, that's just plain brutal.
I would use mk-slave-prefetch on the Slave. Here is what it does: It will read all relay logs, convert INSERTs, UPDATEs, and DELETEs into SELECTs, and run the SELECTs. This will force mysqld on the Slave to populate the MyISAM key buffer and InnoDB Buffer Pool with the required pages of data and index info, making the caches on the Slave resemble the same caches on the Master as close as possible.
This tool you install on the Slave, and run it every minute. When you failover to the Slave, 95% of more of all caches in the Master will be on the Slave (barring Slave Lag).
You are probably wondering why I would look to MAATKIT and not the more advanced Percona Toolkit. Percona did not include mk-slave-prefetch in the Toolkit. Click here to download mk-slave-prefetch.
I have discussed this before
Aug 20, 2011 : MySQL High Performance for Lots of SELECTs/INSERTs/UPDATEs/DELETEsSep 27, 2011 : mysql replication master master (ServerFault)Jan 17, 2012 : Recover a crashed MySQL master server from the slave (ServerFault)You have benefits to implementing mk-slave-prefetch
High Availability Setups with Data Redundancy work well with balanced hardware. Users of MySQL Cluster, Percona XtraDB Cluster, Schooner, and Oracle RAC usually have identical configs for hardware on every server. Any server with less automatically makes that server the weakest link in the Cluster.
For MySQL Master/Slave, if the Slave is just for reporting and backups, there may not be an issue. Once you need the Slave for anything beyond reporting and backups, upgrading the hardware so that Master and Slave are identical is an absolute must.
