The slow decline of using MySQL read slaves

Once upon a time, it was a very common MySQL choice to have an application split its queries between two connections, sending reads to a slave and writes directly to the master.


I won't say this technique is no longer used (it certainly is) but I have been observing its popularity decline slowly over the years. Today I wanted to try and provide insight into some of the possible reasons:

  1. InnoDB as default. MyISAM table locks are very high impact for high throughput systems - worse still, MyISAM does not have MVCC, an algorithm that allows InnoDB reads to be non blocking. Running with multiple replication slaves was very important to distribute read-locks across an array of servers. With InnoDB, for most people this should now be a non-issue.
  2. Single-threaded replication. When hardware has multiple CPUs and multiple disks in RAID (and many SSDs in singular) it often requires multiple concurrent threads to be able to exploit all of the capacity that it can offer. Master servers can exploit this capacity very well, but they then replicate to slaves through a single thread. This creates a situation where the problem is not read scaling (which splitting reads/writes solves very well) but write scaling. The slaves very quickly become the bottleneck as they can not keep up.
  3. Increased desire for ACID behaviour. MySQL replication slaves are asynchronous[1], which means that if the application does an immediate read-after-write, then there is a good chance stale data might be served from a slave. Actually, it's worse than that: with single-threaded slaves, if there is a 10 second update on the master, there is at least a 10 second delay on all slaves.

    Since the data is in an inconsistent state, mixing reads and writes between masters and slaves like this is not ACID compliant and introduces what I would call "asynchronous failures". This leads to more time spent debugging errors.

    MySQL has also been reducing the performance hit to run ACID (including durability) in recent versions. I recommend it for most people.

    [1] Semi-sync introduced in MySQL 5.5 only ensures one slave is up to date.

  4. Increased performance-per-server. From MySQL 5.5 onwards there has been a huge focus on performance. Add to that increased memory in new servers, fast SSDs, it is possible that for some users their lifetime performance needs can be answered by a single server without the need for architectural changes.

Of these points, I suspect 1, 2, & 3 are the most likely, and to some degree 2 (single-threaded replication) makes 3 (consistency) worse because of the single threaded blips. There has been a lot of progress made to improve this in new versions:

  • MySQL 5.6 introduced parallel replication slaves across schemas. This increases the write throughput of slaves significantly, you just have to split tables across multiple databases then set --slave-parallel-workers to take advantage.
  • MySQL 5.7 DMR2 takes this one step further and offers intra-schema parallel replication. This offers parallel slaves and can keep all tables in the same schema.

So it is possible that we may actually see a return of read/write split in coming years. But it is also possible that as MySQL Fabric matures we may see users just rely on sharding instead, even though these two solutions should be orthogonal. It is so hard to predict the future.

I should also point out: I still see slaves immensely valuable for HA/DR as well as a place to send very heavy reporting queries to. This hasn't changed.

What do you think about read/write split?

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I've also worked at Percona and InPowered.

  • Shlomi Noach

    I did not observe this demise, on the contrary. It is always refreshing and insightful to attend a talk by one of the "big players" and see how they all use the "one master - multiple slaves" topology for scaling reads.

    I know we are using this solution heavily. Moreover, you may sometimes dedicate specific slaves or groups of slaves for specific tasks, and so keep a different usage on different slaves, leading to warmer, appropriate caches for the different tasks.

    Some thoughts on your bullets:
    1. I believe we are way past the time for comparing MyISAM vs InnoDB; it's been so many years since the last student asked me "should we use MyISAM or InnoDB?".
    2. The single threaded slave is with us since ages; in this respect tings have been static and I see no correlation to a demise in using read/write split.
    3. The desire has always been there. Maybe the knowledge or understanding has increased. The write-then-immediately-read problem is also well known and ancient. I do not see correlation to a demise in read/write split.
    4+ Better performance is the one bullet that does have immediate impact on the need to scale reads.

    It would be a shame not to mention the one technology that is perhaps the most relevant in this debate: Galera replication by Codership.

    • http://www.tocker.ca/ Morgan Tocker

      With (1) maybe my observation timeline is just different. I left the MySQL world briefly around the time 5.5 was released, and noticed a much larger mental shift. As I just mentioned in another comment: it is not so much the migration, but the legacy of an architectural piece once desperately required. I like to think of this as "scaring from the first cut"
      (2) has been with us for ages, but impact is higher with multi-core machines, and SSDs offering more concurrency + other bottlenecks removed.

      Agree on 3, and Galera suitability (high read rate + consistency).

      • gggeek

        Well, we are certifying our CMS for the versions of mysql which come bundled with currently supported linux distributions. So mysql 5.6 is still far away, not to mention 5.7 goodies...

        • http://www.tocker.ca/ Morgan Tocker

          I would recommend certifying for MySQL 5.6! There are just too many features you want to take advantage of (online ddl, performance_schema etc.) The official repos announced yesterday make this pretty easy.

  • max

    "I won’t say this technique is no longer used (it certainly is) but I have been observing its popularity decline slowly over the years." I dont know which is your source of information but is really 'big' news, or maybe is just your point of view that is not for sure a representative of real.In scaling on reading is still really really common solution and with a good implementation of cache. I can not believe there is a point "MyIsam / Innodb", this is a middle age. The single thread is a limit for sure, but still a good implementation . Also A lot of nosql async cluster use slaves/secondary to scale in reading. And what about Galera? you did not mentioned at all

    • http://www.tocker.ca/ Morgan Tocker

      It's definitely my point of view - I am not trying to argue otherwise. I am then hypothesizing potential reasons.

      Sometimes people develop habits, and then they forget why they were doing things in the first place. I was not creating a MyISAM/InnoDB discussion, just pointing out the effect of a legacy.

  • http://www.iheavy.com/blog/ Sean Hull

    Great post Morgan on many of the strides MySQL is making, in performance & reliability.

    That said I do still see one big reason why my clients like to have a lot of read-only slaves around. Coupled with an application READ-ONLY or browse only mode, it means the application is very unlikely to fall over.

    Availability dramatically increases with multiple copies of your data, even better if you have a few in various Amazon regions, and maybe a Joyent or Rackspace slave thrown in for good measure.

    It's like keeping an extra mobile phone on hand for good measure. Batteries charged, contacts & calendars synced. I'm a business that's never down!

    • http://www.tocker.ca/ Morgan Tocker

      Right - agree with you here. I said in my last paragraph that it is very useful to have servers to failover to, and GTIDs in 5.6 makes this even easier. So I haven't seen any change there.

      • http://www.iheavy.com/blog/ Sean Hull

        yep.