My gameserver utilizes a Master and a slave on one server. The reason I am wanting to use a slave is because I want to beable to run select queries (read only) to get data without affecting the Master database.

For example, a table on the Master gameserver that is being replicated is called rpg_user_items. It stores hundreds of thousands of items, and I want to select some of this data to show to the player (without being in-game); think of a web inventory browser.

My dilemma is: Would selecting the data from the slave be the more appropriate thing to do? And would it technically take load off the Master server? (Maybe, the Master server can even perform better since select queries are not being used on it all the time?)

Edit: My MYSQL version is 5.6.24, using InnoDB. The frequency of insertions are around 20+ req/s. (Inserting 1 row into rpg_user_items) with 15 row fields being a mix smallint and tinyint data types.


Yes. A slave can (and should) offload SELECTs from the Master.

If you outgrow one slave, you can have many. This gives you unlimited read scalability.

Keep in mind that every write that happens on the Master must be replayed on every Slave. Using Row Based Replication helps make this less of a burden on the Slaves. But, eventually, you may become write bound. (That's a separate topic; at 20/sec, you are probably nowhere near that.)

A "critical read" is one that depends on something that was just written. Regular replication is "asynchronous", hence a write can take arbitrarily long to get to the Slave. Hence, if, say, the user posts a comment, he will want to see it immediately. The post must happen on the Master. The "see" is a SELECT that would be nice to do on the Slave, but cannot be safely done ("critical read"). Other reads -- sure, do them on the Slave.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.