mysqlguy.net

MySQL Master HA at Yahoo

I was asked to write a blog post about MySQL High Availability at Yahoo, particularly for writes. Our standard practice is not particularly high-tech, but we've been using it for over 4 years now and it has become a company-wide standard with a few exceptions.
 
Let me start by saying that at Yahoo! we consider a datacenter as a Single Point of Failure (SPoF). We build and manage many of our own datacenters, and we still don't assume they are invulnerable. How many people can attest to the fact that however to configure your racks, how many redundant switches, power supplies, drives, etc. you buy, if your leased datacenter has power or network issues, you are at their mercy.
 

When should we expect the next stable MySQL release beyond 5.1?

I'm not sure I care what the version number is (5.3,5.4,5.4,6.0), and you can talk about milestone releases all you want. What really matters to me is when we'll have something beyond 5.1 marked as GA. Will it be 5.5? When should I expect that?

 

5.4 was announced last year at the conference with lots of performance fixes for the mysql server itself. Great. Until those changes percolate up into a stable release, they aren't worth too much to me. With all the hoopla about the release cycle, I haven't seen any results in the form of new features/fixes making it to stable any faster than 5.1 did.

 

What's the point of any release cycle beyond getting good solid code to stable in a timely (i.e., not 3 years) and safe (i.e., well tested) manner? Can't we have both timely and safe?

innodb_io_capacity

Doing some performance testing on some modern hardware comparing Innodb plugin 1.0.4 with stock Innodb. I'm running a sysbench transactions test (reads and writes) with 200M rows in my table (table size is around 46G, RAM is 16G, buffer pool is set to 12G).

I was puzzled to see the innodb plugin to be decent, but not really as great as I expected, I was doing about ~6100 RW operations a second (individual statements within transactions). Then I compared it to the stock innodb and shockingly I got ~7K ops. I thought about what I tuned that was different in the plugin and came up with the innodb_io_capacity.

I/O Thread delay trick

  I was debugging some repl delay monitoring metrics and I noticed that the test I was doing (sysbench --test=oltp prepare) to generate replication data was far outstripping the slave.  The SQL thread was caught up to the IO thread, but the IO thread was way behind the master.
 
    Replicating from:           a2.db.bcp.re1.yahoo.com
    Master:                     a2_db_bcp_re1.000166/138395515
    Slave I/O:          Yes     a2_db_bcp_re1.000165/802640907  ???
    Slave Relay:        Yes     a2_db_bcp_re1.000165/802030586  596K
  198 secs
 

 

  In this case, the I/O thread was getting further and further behind as sysbench did bulk inserts into my master.  My theory is that a lot of relatively small binary log records simply don't transfer efficiently.  That leaves the SQL thread idle some of the time waiting for the IO thread, and leads it inefficient replication.
 
   I poked around the replication options manual page, looking for something to help and found this:  slave_compressed_protocol
 

Re: Eventually Consistent Relational Database?

In response to Eric Day's post on "Eventually Consistent Relational Database?"... I started posting a comment there, but I realized I have my own blog for this sort of thing. :)

I've been thinking the same thing, it's nice to hear I'm not the only one. This is a neglected area of "cloud" development, mostly because it's a big scary problem. Everyone says "use NoSQL", but if we had strategies/systems to give us EC RDBMS solutions, nobody would use key/value storage (except where it actually made sense). NoSQL is a big golden hammer nowadays. It works, but it sure takes a lot of effort to code stuff the storage layer should be able to handle (joins, etc.).

About Me

Jay Janssen
Yahoo!, Inc.
jayj at yahoo dash inc dot com
MySQL
High Availability
Global Load Balancing
Failover
View Jay Janssen on Twitter  View Jay Janssen's LinkedIn profile View Jay Janssen's Facebook profile