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.

Just for fun...

There are moments in every life when we ask ourselves - why do I do this?

One of the reasons I enjoy doing what I do is to get away at times and go ride... In my case, a Honda Silver Wing (FSC600). I enjoy the "SWing" so much that I've made it a point to take it with me when I can. :-)

Here are some of the places I've ridden so far... (updated 30May11)