Brian Aker recently published good write up about using MySQL replication. The piece I find missing however is good description of warning about limits of this approach as well as things you need to watch out for. You can call me person with negative mind but I tend to think about limits as well. So what would be my list of limits and watch outs in this case ?
– Limited write scaling. As Brian writes this works best for read intensive envinronments. Write scaling however can be real problem. Not moderate increase of write ratio can dramatically reduce performance but you also need to watch out for slave being able to keep up at all. Remember writes to slave are serialized so if you master happens to be 8 core system with 20 hard drives you may be in trouble even if writes only consume 10% of resources as almost only one core and only one disk will be working on slave side.
Now let me clarify what I mean by moderate increasing of write load can cause the trouble. Assume you have same master and your write load loading master 60%, almost same 60% of resources used by the slaves to handle writes. You might have 10 slaves so read load might not be the issue. Now if your write load growths 50% – Master and slaves will start use 90% of their resources for replication (forget for the second replication being single thread, which just makes things worse), which means only 10% will remain for anything else. So your “read” performance will drop 4 times if write load growth by only 50%.
– Delays in replication MySQL replication is fast which means in average delay is small. Typically microseconds. However if there happens to be some bulk query on the master, even on completely unrelated table, replication lag may increase dramatically. Besides monitoring which Brian mentioned three other approaches could be used. First is to use master for real time reads when you want to have 100% guaranty you’re reading latest data. Second to use MASTER_POS_WAIT on the slave(s) to make sure update made it to them – this one has large overhead so should be used only for absolutely critical updates. Third one is to use cache – something like memcached, besides pure caching it can be used to store object version information so you can quickly check if version you read from the slave is current one or if you need to do master read.
– Waste of space Disk is cheap… if it is not a lot of high performance storage. If you would be replicating 1TB of data in 20 copies I guess you would understand me 🙂 For sake of high availability you probably need 2-3 copies. If you need significantly more slaves and they need a lot of storage you might need other solution.
– Waste of cache Memory is much faster than disk and you generally want to have data you’re accessing to be cached in memory by MySQL cache or OS cache. As slaves replicate the same data and often queried in random or round robin fashion their cache would have about same content, so if you would have 10 16GB boxes your “effective cache” would be still about 14GB not even close 140GB as total amount of cache memory you have. Using memcached or similar solutions on the top of things for caching as well as mapping of queries to different slaves helps but only up to certain point. Different solutions are needed if that becomes the problem.
So am I against simple replication ? Not really this is great solution which offers great MySQL Performance in many cases. It is good however to understand if this solution is right for you so you do not have to jump to other schemes without planning it. The simplicity which this solution delivers is more important than its problems for many small-medium sized application.
So in which case I would go using simple master slave replication ?
– Small database size. Small meaning working set fits in memory. This reduces waste of storage, removes problem with cache duplication (as each box can cache everything) as well as normally makes write load to consume small portion of resources.
– Light write load This is needed to avoid scalability problems of single thread and resource waste. I would call load light if it takes no more than 30% of resources if you only leave single disk and single core on the master. So you it can grow well enough before gibing you trouble.
– Few replicas – If you have 3-5 replicas it is fine. Up to 10 probably OK but more than that may mean other solutions would work better. Sometimes even 100 replicas can be OK if database is small and load is virtually read only. you may however want to structure replication with median servers to avoid master overload in such case.
– No long queries – As I already wrote these cause delay in replication even if they work on completely different (but replicated) data sets. Sometimes you do not care about stale data so you’re fine, somethimes you can work it around by skipping replication on of tables affected by long running queries.
That is all for today but far from being all what can be said about MySQL Replication.
