A basic shared-nothing data sharding system

There's a lot of buzz about sharding data. Today, I'll provide a very brief overview of how sharding helps systems I manage run more efficiently and how we're addressing keeping individual shards balanced.

The goal of sharding data in our environment involves: 1) make the structure of the data consistent across all the shards, 2) dividing data up so it can be found easily, 3) automatically and continuously re-balance the shards, and 4) allow for changes in scale (like adding a new shard or different shard sizes).

Item 1 is a snap - all we do there is to deploy the same data structures in each of the shards with all the supporting data required to answer questions related to a user. Some of this data is user-specific, some is globally replicated. In any case, this goal makes it easy to use one set of code to access data in any of the shards without having to cross to another shard or database to get the answer for a question. This reduces workload in the application and on other database servers.

Item 2 is done by hashing our key data. Let's say that we have a set of widgets that users are concerned with. Some users have a few widgets, some have a lot, but each user is very different from another. Widgets are pretty common and well defined. Each user has a user ID and any question we ask the system always involves a specific user ID. So - our key data we hash against in this case would be the user ID. Data about the widgets is replicated to all the shards, but data about each user is only kept on the shard where that user's data lives.

Item 3 is handled by a separate process that utilizes the same API the application uses. Balancing the data between shards is simple - the balancer asks the API if there are any users that need to move. If yes, the balancer lets the API know to lock that user temporarily, moves the data, then unlocks the users for use on the new shard. What this means for applications is each time a location is returned for a specific user, that location is only guaranteed for a given window of time (30 seconds for example). So - when the balancer tells the API it's moving a user's records, any requests for that user's records are held up until the user's data is moved. The API is smart enough to only let the balancer move data that has not been accessed recently. This doesn't prevent all lock collisions, but it handles most of them.

Item 4 is handled through the configuration of the API. Because we use an API to tell the application where the data is for a given user, we've abstracted away where data actually lives. This makes it easy to add and remove servers from the sharding pool. We've extended this to include allowing a shard to be marked as in a draining state. When a shard is draining, the API will ask the balancer to move rows from the draining shard and redistribute that information onto other members of the sharding pool. This makes it possible to take a shard out of rotation for routine maintenance without the loss of data.

Notice that I didn't mention any specific software here. I didn't tell you what language the application is written in, what language the API is written in, or what the actual data store was. The technique of sharding data is pretty simple and can be done with nearly any persistence layer using any programming language.

The beauty of this system is that once the API is written, the balancer can be a complete "black box" to the application. This type of system could be implemented with a data store when just starting out and be expanded to multiple stores as the need expands. Also - sharding key needs to change, again, the application doesn't need to change - just the API and the balancer.

One other big benefit to sharding data like this - it's often a lot cheaper to buy several smaller systems than to buy and maintain one very large system. If one of the systems in the sharding pool goes off-line, the worst possible exposure in a shared-nothing sharding system is the data stored on the member that went down. In a monolithic system, you stand to lose a lot more.

While I wouldn't suggest trying to do this type of work on top of every data set out there, I do see that there is a lot of benefit when the types of questions being asked of a data set can be divided up easily while still making it relatively easy to answer the "question at hand" from a single source. The secret in the sauce is making sure that any common data is shared among all the systems in the pool.

Managing incoming emails

Reading emails all day long tends to be very counter-productive for me. I usually end up responding faster than anyone else which generally gets me a lot more work than I need. At the same time, I have a responsibility during my times as primary and secondary on-call to respond within our service level agreement. So - how do I find balance? My team and I use mailing lists to help us manage those truly urgent issues versus those issues that can be handled as time allows. We have three lists:

group_primary@foo.com
group_secondary@foo.com
group_admin@foo.com

We've published these three lists to our operations center. Everyone else just gets the admin list. We don't tell others about the primary and secondary lists because anything we'd get on primary or secondary would need to come via the operations center anyway. We also don't want our over 600 co-workers (not on our team and not in the NOC) to email us willy-nilly using our on-call emails.

Next, on each of our team's smart phones, we've set them up to recognize emails going specifically to the primary and secondary emails so our phones will either go off like a pager or (in my case) read the sender and destination email (think "Inbound Primary email from the NOC"). That prevents me from having to look at my phone every time a new message comes in but lets me know when there's something that requires my attention.

The other thing we do is to make it easy to change the destination for the primary address easily so that only primary gets notified. Secondary is notified in the same way but on my two-man team, there are only two of us so secondary always goes to the whole team (for now).

Finally, to help us have reasonable sanity, I do what I can to only check the "other" emails twice a day.

The  net result of this process is I am able to focus on getting project work done between routine email readings and it lets others figure things out for themselves or wait a bit for an answer. If it was truly urgent, the sender could simply ask the NOC to reach out to the on-call person to get a faster response.

How do you deal with your on-call processes and email?

Watch this video for instructions on how to use indexes better

This is the first video I've ever seen that visually represents how indexing works. I've seen good stuff before but this ... wow. Yes - it has some stuff about Tokutek in it, but that's not why I suggest watching it - it's because it makes you re-think how to define good indexes.


http://www.youtube.com/watch?v=AVNjqgf7zNw

Flashback to summer riding

With all the recent crummy weather, I thought I'd resurrect a summer fun photo (from around July 4th, 2011) :-) If you like wind farms and/or motorcycling, you'll like this pic of my 2003 Honda Silverwing:

For those that are wondering, it gets about 52 MPG normally during the summer and around 45 MPG in the winter and will do over 100MPH.

On-Call Silliness

Are you on-call? Do you get called up for stuff that just makes no sense for you to attend to? Check out this video...

THE FROBNICATOR WON'T FROBNICATE!
by: kbcmdba

The trick to handling on-call is to make sure that you're getting called when your expertise is really needed. If you're getting called for things you can automate, automate them. If you're getting called for stuff that doesn't apply to you, show them this video. Just because the frobnicator won't frobnicate for someone else, doesn't mean it's your responsibility to fix it or even tell the right person about it.

mysqldumpslow and Rows Examined

I have found that watching the ratio of rows sent to rows examined helps me get a much better idea of how hard the server has to work to get a result set. Unfortunately, mysqldumpslow in MySQL 5.1 doesn't provide that capability. Rather than keeping it to myself, I'm sharing my fix with the world in hopes that it gets included in all the current and future versions of MySQL. :-)

This patch is officially entered into the public domain.

--- mysqldumpslow 2010-05-07 10:17:19.000000000 -0500
+++ mysqldumpslow 2011-06-20 11:46:44.000000000 -0500
@@ -8,8 +8,8 @@
 use strict;
 use Getopt::Long;

-# t=time, l=lock time, r=rows
-# at, al, and ar are the corresponding averages
+# t=time, l=lock time, r=rows sent, e=rows examined
+# at, al, ar, and ae are the corresponding averages

 my %opt = (
     s => 'at',
@@ -83,8 +83,8 @@
     s/^#? Time: \d{6}\s+\d+:\d+:\d+.*\n//;
     my ($user,$host) = s/^#? User\@Host:\s+(\S+)\s+\@\s+(\S+).*\n// ? ($1,$2) : ('','');

-    s/^# Query_time: ([0-9.]+)\s+Lock_time: ([0-9.]+)\s+Rows_sent: ([0-9.]+).*\n//;
-    my ($t, $l, $r) = ($1, $2, $3);
+    s/^# Query_time: ([0-9.]+)\s+Lock_time: ([0-9.]+)\s+Rows_sent: ([0-9.]+)\s+Rows_examined: ([0-9.]+).*\n//;
+    my ($t, $l, $r, $e) = ($1, $2, $3, $4);
     $t -= $l unless $opt{l};

     # remove fluff that mysqld writes to log when it (re)starts:
@@ -121,6 +121,7 @@
     $s->{t} += $t;
     $s->{l} += $l;
     $s->{r} += $r;
+    $s->{e} += $e;
     $s->{users}->{$user}++ if $user;
     $s->{hosts}->{$host}++ if $host;

@@ -129,10 +130,11 @@

 foreach (keys %stmt) {
     my $v = $stmt{$_} || die;
-    my ($c, $t, $l, $r) = @{ $v }{qw(c t l r)};
+    my ($c, $t, $l, $r, $e) = @{ $v }{qw(c t l r e)};
     $v->{at} = $t / $c;
     $v->{al} = $l / $c;
     $v->{ar} = $r / $c;
+    $v->{ae} = $e / $c;
 }

 my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt;
@@ -141,13 +143,13 @@

 foreach (@sorted) {
     my $v = $stmt{$_} || die;
-    my ($c, $t,$at, $l,$al, $r,$ar) = @{ $v }{qw(c t at l al r ar)};
+    my ($c, $t,$at, $l,$al, $r,$ar, $e,$ae) = @{ $v }{qw(c t at l al r ar e ae)};
     my @users = keys %{$v->{users}};
     my $user  = (@users==1) ? $users[0] : sprintf "%dusers",scalar @users;
     my @hosts = keys %{$v->{hosts}};
     my $host  = (@hosts==1) ? $hosts[0] : sprintf "%dhosts",scalar @hosts;
-    printf "Count: %d  Time=%.2fs (%ds)  Lock=%.2fs (%ds)  Rows=%.1f (%d), $user\@$host\n%s\n\n",
-           $c, $at,$t, $al,$l, $ar,$r, $_;
+    printf "Count: %d  Time=%.2fs (%ds)  Lock=%.2fs (%ds)  Rows Sent=%.1f (%d) Rows Examined=%.1f (%d), $user\@$host\n%s\n\
n",
+           $c, $at,$t, $al,$l, $ar,$r, $ae,$e, $_;
 }

 sub usage {
@@ -163,11 +165,13 @@

   -v           verbose
   -d           debug
-  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
+  -s ORDER     what to sort by (ae, al, at, ar, c, e, l, r, t), 'at' is default
+                ae: average rows examined
                 al: average lock time
                 ar: average rows sent
                 at: average query time
                  c: count
+                 e: rows examined
                  l: lock time
                  r: rows sent
                  t: query time

Heavy improper connection termination creates disk full risk

Logging warnings, is generally a good thing for a DBA. Without them, we may not be able to understand when there are problems that are otherwise hard to find. One of the warnings mysqld logs looks a bit like this:

110528 11:35:44 [Warning] Aborted connection 4185247 to db: 'db_name' user 'user_name' host: 'a.b.c.com' (Got an error reading communication packets)

There are two primary candidates for this type of warning: a) the client application, or b) the network.

On the client side, this is often caused by programs that terminate the connection without calling mysql_close after completing use of the connection. This problem is so common, in fact, that it is documented in http://dev.mysql.com/doc/refman/5.5/en/communication-errors.html
I can't count how many times I've had to speak with developers about making sure they close connections properly in order to prevent this kind of warning from occurring.

At the root of this issue, mysqld expects to receive some sort of information back from each client every so many (interactive_timeout or wait_timeout) seconds. If that time elapses without receiving anything, mysqld assumes that the connection has died. Rather than letting the valuable information go unnoticed, it logs that as a warning. mysql_close() tells mysqld that the connection is closing normally and the server should do its normal cleanup (no need to log normal closures).

If your code or the library it utilizes on doesn't call mysql_close when your program exits, mysqld will eventually log an error like the one above. If you utilize persistent connections, you will need to make sure that your connection talks to mysqld on a regular basis to keep that connection alive. If your library handles that for you, great. Otherwise, you'll need to "ping" mysqld at least once every interactive_timeout-1 or wait_timeout-1 seconds depending on your connection type. Most people should use wait_timeout for programs and interactive_timeout for mysql command-line client connections.

In rare occasions, some developers may also be concerned with net_read_timout - the amount of time mysqld will wait for data being transmitted to it in order to complete an operation. If you're sending data to mysqld and the sending process gets interrupted for a long time, this may also trigger the error listed above.

On the network side, I've seen this type of error is when the network is having a problem between the application layer and the database. How do we know which is which? We do all we can to make sure that the application layer isn't causing these issues so that when this error pops up, it's far less likely to be a preventable software coding issue.