Mysql – master-slave-slave replication: master will become bottleneck for writes

database-performanceMySQLmysql-replicationreplication

the mysql database has arround 2TB of data.

i have a master-slave-slave replication running. the application that uses the database does read (SELECT) queries just on one of the 2 slaves and write (DELETE/INSERT/UPDATE) queries on the master. the application does way more reads, than writes.

if we have a problem with the read (SELECT) queries, we can just add another slave database and tell the application, that there is another salve. so it scales well…

Currently, the master is running arround 40% disk io due to the writes.

So i'm thinking about how to scale the the database in the future. Because one day the master will be overloaded.

What could be a solution there?

maybe mysql cluster? if so, are there any pitfalls or limitations in switching the database to ndb?

thanks a lot in advance… 🙂

Best Answer

There is no one-size-fits-all answer to scaling MySQL. A few general tips:

  • Scale "diagonally" as long as you can, ie. keep things on a single MySQL server as long as you're still able to run on commodity hardware. That probably means 2 x quad-core CPUs, 64+ GB RAM, 8 disk RAID 10 -- or higher. The upper end of what is "commodity hardware" is getting faster each year.

  • Have a look at Brad Fitzpatrick's presentations about scaling LiveJournal. They're pretty much classics as far as scaling LAMP goes. On page 25 - 26 of this presentation you see the problem you will eventually face with MySQL replication: The writes consume all available disk I/O.

  • Read "High Performance MySQL". It's a really good book by authors who have seen many high-load MySQL installations.

  • Avoid sharding (spreading data over many MySQL servers) as long as possible. When you start sharding, you give up most benefits of relational databases, and you slow down development. If you have to do sharding, consider using a NoSQL datastore with a built-in multi server model instead -- fx Riak, Cassandra, HBase, MongoDB. Ideally, do "functional partitioning" between MySQL and NoSQL, so that you keep using MySQL for lesser hot data that fits well into an RDBMS, and you use the NoSQL engine for 'hot' data you don't need to join with the MySQL data.

maybe mysql cluster? if so, are there any pitfalls or limitations in switching the database to ndb?

In "Web Operations" there is a chapter on MySQL by Baron Schwartz. He pretty much justs says "No!" to using MySQL Cluster / NDB in a website environment. Quote: ".. it doesn't perform well for joins and GROUP BY queries, and web applications need those.".

Related Topic