Tuesday, February 3, 2009

Thoughts on Replication from 2 Feb SFO MeetUp (slides too)

Last night I gave a presentation on Tungsten Replicator to the SFO MySQL MeetUp. It was really fun--lots of excellent questions and ideas from an audience that knows MySQL in and out. Here are slides from the talk from our trusty S3 document repository.

There were many things to think about on the BART ride home to Berkeley but here are a couple that really struck me, in part because I think we can do something about them.

First, database replication needs to expose as much monitoring data as possible. The kind of data you get or can infer from SHOW SLAVE STATUS is just the beginning. This came through really strongly from people like Erin who are using load-balancers and other automated techniques for distributing load to replicas using decision criteria like availability and latency of slaves. We have heard this from people like Peter Zaitsev as well--it's starting to sink in. :)

Second, it's critical to think through the database failover problem fully. There seem to be two axes to this problem. The first is what you might call call a vertical axis where you think about a single component in the system--here the database. You have to cover not just swapping replication flow but also enabling/disabling writes, triggers, and batch jobs, as well as application specific tasks. (Lots of good comments from the audience here as well.)

The other failover "axis" is horizontal where you think about the ensemble of databases, proxies, applications, and utilities that make up the cluster. The issues to cover here including sending commands in parallel, ensuring everyone receives them, and dealing with a wide range of possible failure conditions ranging from network partitions to failure of individual OS-level commands. We plan to unveil a solution shortly in the form of the Tungsten Manager, which uses group communications to attack this problem. I can't wait to get feedback on that.

p.s., Baron Schwartz yesterday posted a very interesting article on cache issues with failover on InnoDB. Just another example of how broad the failover problem really is.


Anonymous said...

Hi Robert,

I like the whole idea of advanced MySQL replication and think the LA MySQL meetup group would enjoy a similar presentation.

Are you are anyone else from Continuent ever in the LA area?


Robert Hodges said...

Hi Scott,

I can get to LA pretty easily. I'll send email directly to your address. Thanks for tuning in!

Cheers, Robert

db geek chick said...

Thanks for such a thought provoking talk. It was really interesting & raised a lot of issues about the how, what, where of replication. I do love how our group gets into the discussion too.

I did worry it would be a sales talk but it was sooo geeky one of the recruiters told Michael that most of it went over his head. hehe


Robert Hodges said...

Hi Erin,

You are most welcome. I have to do a lot of business stuff in the other half of my job, so it's fun to revel in true geekiness.

Seriously, I was thinking about expanding this and some other recent talks into a general tutorial on MySQL replication (i.e., not just our stuff). I did something similar for PostgreSQL a while back and it seemed to be pretty well received.

Cheers, Robert

db geek chick said...

Well that sounds like a MySQL conf talk or better yet maybe mysqlCamp (Sheeri's putting that on)!

I think that replication has the flexibility to do all sorts of neat tricks with it. And its interesting to hear how folks are using it.

Oh! I was asking about prefetching. It's part of maatkit now. I found it on some blog about performance & wrote up a script to do it. Seemed to help.


Andy Cohen said...

Robert, this is completely off topic, but I don't have your latest email address - As a history buff, here's something you might enjoy seeing:


Have fun,


Robert Hodges said...

Hi Andy,

Off-topic for sure, but also very cool--like seeing the potsherds used to ostracize Cimon and Aristides from Athens.

Thanks!! Robert

Scaling Databases Using Commodity Hardware and Shared-Nothing Design