K Benton CMDBA

A basic shared-nothing data sharding system

2012-02-12T00:12:00.000-07:00

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

2012-01-15T18:44:00.000-07:00

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

2011-11-28T09:20:00.001-07:00

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

2011-11-27T14:58:00.000-07:00

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

2011-10-20T00:54:00.001-06:00

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

2011-06-20T11:22:00.001-06:00

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

2011-05-28T11:41:00.001-06:00

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...

2011-05-07T01:18:00.001-06:00

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)

Dude's Law: Meaningful Thinking

2010-09-14T14:11:00.001-06:00

I had never really thought much about how coaches get to be really good at what they do. I (like many, I assume) think that being a really good player makes someone a good coach of players. Then I thought about the adage - "Practice doesn't make perfect, practice makes permanent. Right practice makes perfect." The problem is - without the right feedback, there is little chance of knowing that practice is right.

The article I'm pointing to here inspired me to think more about "why" I do things. Why do I go to work each day besides earning a paycheck? Why do I blog? Why do I play disc golf? Why did I get married? Why do I stay married? Why did I make so many of the decisions I've made in my life?

The more I ask why, the more I have to answer "so that." By being conscious of the "so that's," I think living life can become much easier.

Timestamps in tables please

2010-05-14T14:10:00.002-06:00

As MySQL DBA's, it helps us to understand when rows were added in a table and when those rows were last updated. As a result, we're requesting that all tables have the following two columns in them to help us track those changes:

, created TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

This should help when it comes time to maintain rows in all your tables because the database knows how to handle these two columns by itself given the right scenario:

mysql> CREATE TABLE foo (
->     foo_id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY
->   , bar varchar(24) NOT NULL DEFAULT ''
->   , created TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
->   , updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> ) ;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT foo ( bar, created, updated )
-> VALUES ( 'hello', NULL, NULL )
->      , ( 'world', NULL, NULL )
->      ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Notice here that we intentionally insert NULL values into the created and updated columns. These will be rows 1 and 2 below.

mysql> INSERT foo ( bar ) VALUES ( 'hello again' ) ;
Query OK, 1 row affected (0.00 sec)

This time, I'm not setting the created field at all so you can see what happens when we skip it. I'm not crazy about this inconsistency, but it's what we have to work with for now.

mysql> UPDATE foo SET bar = 'from here' WHERE foo_id = 2 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM foo ;
+--------+-------------+---------------------+---------------------+
| foo_id | bar         | created             | updated             |
+--------+-------------+---------------------+---------------------+
|      1 | hello       | 2010-05-13 15:23:01 | 2010-05-13 15:23:01 |
|      2 | from here   | 2010-05-13 15:23:01 | 2010-05-13 15:23:19 |
|      3 | hello again | 0000-00-00 00:00:00 | 2010-05-13 15:23:03 |
+--------+-------------+---------------------+---------------------+
3 rows in set (0.00 sec)

Notice that row 3's created column has the 0000-... default value in it. Rows 1 and 2 don't because we actually inserted the NULL value into the NOT NULL column. That demonstrates that inserting NULL into a TIMESTAMP column created with NOT NULL will cause the column to use the current time when an action is performed (INSERT/UPDATE/REPLACE). This does not work with DATE, TIME, and DATETIME fields.

Want to learn more? Check out this link.

Two of my favorite "toys"

2010-03-28T14:13:00.001-06:00

Doing work all day long can get rather boring. So, here, I am showing off one of my hobbies. The magazine is there for perspective in size. The small helicopter is a Blade CP+ and the larger is a Bergen Gasser though not fully assembled. As you can see, the Gasser is about 2.5 times the size of the Blade even with the training gear on.

Automatically Generate Configuration Files (Part 1)

2010-02-08T18:31:00.027-07:00

This article begins a series on using m4 to generate configurations for MySQL but applies to other configurations as well. Why would I want to generate configurations versus just managing each configuration separately? How often do we feel we need to make a change that applies to several instances all at once? Wouldn't it be nice if there was an easy way to change something in one place so that wold be reflected in all the configuration files under our care? What about when we want to see the same change in all the files except one or two? What about when we want to see that change happen in only one or two files?

There are a plethora of templating, macro, and text processing systems available that can help administrators deal with this type of challenge. I am using m4 for my own needs because it is very simple to understand and maintain while retaining the flexibility I need to go far beyond standard search and replace. I use m4 to help me generate configurations for mysql, httpd, bind(DNS), and other software. Generating configurations makes it much easier for me to maintain, test, and version my changes across several systems.

Enough of the lead-in, let's get started using m4 in a meaningful way.

m4 is available on most Unix-based systems automatically. If it's not available on yours, check your repositories. On Windows, m4 is available in Cygwin. If you don't already have Cygwin, it is a way to get many of the tools that run in Unix-based environments without having to run Unix free of charge.

m4 is a macro processor that has its roots in the C language processors. It takes a stream of text, looks for certain keywords and uses those as macros that are expanded based on the needs presented to it. m4 can handle loops, conditionals, and other common programming constructs. m4 is a tool that helps users by keeping repetition down to a minimum.

m4 uses processing instructions to determine when it needs to do something. I've told it only to read processing instructions that start with m4_ because I don't want it confusing my instructions with real information that needs to be left alone. It's just a precaution, but a good one and I encourage you to use it as well. To get m4 to require m4_, simply add a -P to your m4 command (as shown below).

The first instruction I'll show you is m4_define - the root of all that's m4. What good would a macro processor be if we couldn't define macros to be expanded? Isn't that like having a sail boat without a sail? Anyway, I digress. The syntax for m4_define looks like this: m4_define(`defined_label', `macro expanded') with the defined_label being the text that m4 will substitue and macro expanded being what it will replace defined_label with. Notice that I've wrapped these items with a back-tick and a regular tick. This is important and I'll explain more about that in upcoming articles. For now, let's get with the program! ;-)

$ cat my.cnf.m4
m4_define(`DEF_SERVER_ID', 1)

[mysqld]
server-id = DEF_SERVER_ID
$

If we run this file through m4, we will get the following:

$ m4 -P < my.cnf.m4


[mysqld]
server-id = 1
$

Notice that the definition of the DEF_SERVER_ID variable seems to have disappeared. That's true - m4 "ate" the definition macro. The new line is still there but that's all that's left of the first line of the file. What's cool, however, is we now have one place where the server ID was specified and another where it was used. No big deal there - what value does that give me, you ask? None at all unless you really like repeating yourself a lot. Of course there is more. Let's make this a little more meaningful.

$ cat my.cnf.m4
m4_define(`DEF_MYSQLDIR', `/var/lib/mysql')
m4_define(`DEF_SERVER_ID', 1)

[mysqld]
server-id = DEF_SERVER_ID
log = DEF_MYSQLDIR/logs/mysql.log
socket = DEF_MYSQLDIR/mysql.sock
pid-file = DEF_MYSQLDIR/run/mysql.pid
datadir = DEF_MYSQLDIR/data
$

Did you think through what m4 will do for us? Think about your answer before you read further.

$ m4 -P < my.cnf.m4



[mysqld]
server-id = 1
log = /var/lib/mysql/logs/mysql.log
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/run/mysql.pid
datadir = /var/lib/mysql/data
$

Pretty cool, eh? Well - maybe not yet. Did you get it right? What if I decided I needed to move my MySQL instance to another directory like /opt/mysqlInstance1 ? Rather than having to search for and replace all the text of "/var/lib/mysql" changing it to "/opt/mysqlInstance1", all I need to do is change the definition of DEF_MYSQLDIR at the top of the m4 template, then re-run m4 on my template and I get the new ouptut file. That's what I call slick. :-)

$ cat my.cnf.m4
m4_define(`DEF_MYSQLDIR', `/opt/mysqlInstance1')
m4_define(`DEF_SERVER_ID', 1)

[mysqld]
server-id = DEF_SERVER_ID
log = DEF_MYSQLDIR/logs/mysql.log
socket = DEF_MYSQLDIR/mysql.sock
pid-file = DEF_MYSQLDIR/run/mysql.pid
datadir = DEF_MYSQLDIR/data
$ m4 -P < my.cnf.m4



[mysqld]
server-id = 1
log = /opt/mysqlInstance1/mysql.log
socket = /opt/mysqlInstance1/mysql.sockm4 
pid-file = /opt/mysqlInstance1/run/mysql.pid
datadir = /opt/mysqlInstance1/data
$

So - that's nice, but there's a lot more to m4 than just being able to define macros. One of the most powerful tools that m4 offers is the ability to pull in other files. I'll explain by example:

$ cat my.cnf.m4
[mysqld]
server-id = DEF_SERVER_ID
log = DEF_MYSQLDIR/logs/mysql.log
socket = DEF_MYSQLDIR/mysql.sock
pid-file = DEF_MYSQLDIR/run/mysql.pid
datadir = DEF_MYSQLDIR/data

$ cat instance1.cnf.m4
m4_define(`DEF_MYSQLDIR', `/opt/mysqlInstance1')m4_dnl
m4_define(`DEF_SERVER_ID', 1)m4_dnl
m4_include(`my.cnf.m4')
$ cat instance2.cnf.m4
m4_define(`DEF_MYSQLDIR', `/opt/mysqlInstance2')m4_dnl
m4_define(`DEF_SERVER_ID', 2)m4_dnl
m4_include(`my.cnf.m4')

$ m4 -P < instance1.cnf.m4
[mysqld]
server-id = 1
log = /opt/mysqlInstance1/mysql.log
socket = /opt/mysqlInstance1/mysql.sock
pid-file = /opt/mysqlInstance1/run/mysql.pid
datadir = /opt/mysqlInstance1/data

$ m4 -P < instance2.cnf.m4
[mysqld]
server-id = 2
log = /opt/mysqlInstance2/mysql.log
socket = /opt/mysqlInstance2/mysql.sock
pid-file = /opt/mysqlInstance2/run/mysql.pid
datadir = /opt/mysqlInstance2/data

$

So - I added two things: m4_include and m4_dnl. m4_include says basically go read this file and continue processing as if the text were part of this file. m4_dnl says discard to next line. It's a form of comment but I often use it to mean "don't new line."

You notice in the example above, I created two very small files with the m4_define statements I needed (defining my macros) and then included the main template where those definitions were expanded as explained in the smaller files. So - rather than creating one my.cnf file with the definitions for one instance then copying and hand-modifying that second file for the second instance, I am able to write one template and a couple of small definition files to get the same result. If I need ot make a change to both files at the same time, I can simply change my my.cnf.m4 then re-generate my my.cnf files for each instance.

In today's tutorial, you saw how to create and get m4 to expand basic macros, how to include other files into an m4 macro file, and how to create comments. In our next episode, I'll explain how to make m4 smarter by showing you how to make it ask questions and do things differently depending on the answer. I'll also explain how to get the make command to automatically handle creating all your configuration files for you as well as getting the files into source control.

Do you like this series? Please post a comment! :-)

How to COUNT in SQL

2010-02-08T10:32:00.001-07:00

Often, I see users attempting to count rows of a table with COUNT(*) as a part of their query. Regardless of what database you're using, this probably not an optimal way to count.

COUNT(*) is likely to cause the system to do a table scan to find out exactly how many rows are available. Table scans are a very bad thing because it causes the system to go to disk to read the table. COUNT(columnName) on a column that is indexed will allow the database to use the index to compute the count (meaning it reads less data and may even be able to do it entirely from RAM if the index is already loaded there). The difference between COUNT(*) and COUNT(columnName) is a simple one - COUNT(*) counts every row where COUNT(columnName) counts the rows where columnName has a non-NULL value.

My favorite way to do counts (when I don't already have a summary table telling me how many rows are in the result set I'm looking for) is to count the non-contextual primary key in the table since I make it a habit of putting a non-contextual primary key in every table I can. This takes advantage of every possible optimization I can and keeps the amount of data the system must read down to a minimum.

This is a great practice to be in because it helps not only in MySQL, but in other databases as well.

MySQL Styles

2010-01-07T20:41:00.002-07:00

There are nearly as many ways of writing SQL code as there are people writing it. Each person has his or her own style. While that may make it easy at one time or another for the writer, I suggest that using a well-recognized style makes it easier to read and maintain code (SQL or otherwise). Good style also makes it easier to spot problems with code. Whether you like the style I prefer or not, simply thinking about why others use one coding style over another can help us all communicate through our code better.

Before I launch into the methods I'm using, I suggest you check out http://sqlinform.com/ (I'm not associated with the site, however, I use the free on-line SQL formatting tool there rather frequently). When I'm trying to decipher someone else's code formatted in some other way (often just mashed together on a single or a few lines), this tool gives me a quick/easy way to re-format my code in a way that helps me read through the code. It clearly delineates the pieces of the code structure and helps guide my eye through it. Given the proper settings, I am able to read code so much easier that I am encouraging all the DBD's I work with to utilize this tool whether or not they're writing SQL for MySQL.

Imagine you were handed a file that looks like this:


USE DBFOO; START TRANSACTION;
DELIMITER ;;
// SP_GET_FOO_DATA CREATED BY SOME BEGINNER TO MAKE IT EASIER TO MAINTAIN HOW FOO DATA IS RETRIEVED
CREATE PROCEDURE SP_GET_FOO_DATA(V_FOO VARCHAR(2048)) SQL SECURITY INVOKER BEGIN SELECT SPLATTER.FOO, BLIP.BAR, BLOP.BAZ FROM BLIP LEFT JOIN SPLAT SPLATTER USING(SPLAT_ID), BLOP WHERE FOO = V_FOO AND BLIP.BLIP_ID = BLOP.BLIP_ID; END;; COMMIT;;
DELIMITER ;

Don't run for the hills... That's some funky code but let's see what we can do to clean it up:

1) Using START TRANSACTION and COMMIT are both unnecessary here because DDL statements (like CREATE PROCEDURE, ALTER TABLE, CREATE TABLE, etc) implicitly call COMMIT for you whether or not you're in an active transaction. MySQL silently ignores COMMITs when dealing with non-transactional storage engines but the point is, adding a COMMIT here doesn't add anything, so let's get rid of the excess transactional code.

2) Adding a USE statement may or may not be appropriate. At a minimum it makes deploying the code to a different database that much more difficult because the person applying it will need to change the USE statement accordingly.

3) Am I the only one who finds it hard to read text in all caps all the time? Okay - don't answer that. Obviously, I'm not the only one who would rather see text mostly lower case and all caps only when it's time to draw the reader's attention to something special.

4) The comment that is placed right before the CREATE PROCEDURE statement makes it hard to identify where the comment begins and ends versus where the code begins and ends. These lines need to be a) split apart at a minimum. It makes sense to me that the code would look and read better if the comment became a part of the DDL so it will show up in the SHOW PROCEDURE STATUS statement when called.

5) Long lines are no fun to read when the line is longer than your screen width setting. To get the whole picture, one must either decrease the font size to a sometimes unreadable value, or manually edit the file to break things up. When I do reviews, I want to spend time reading code, not reformatting it.

6) As I mentioned above, all caps makes it harder to follow text. It's a very common standard used by SQL writers that SQL clauses (like SELECT, WHERE, AND, VARCHAR, etc) are typically expressed in capital letters while database, table, column and variable names are expressed in lower case. This makes it much easier to find the clause or parameters to a clause much easier when utilized in conjunction with the other methods below.

7) Breaking up lines into logical groups helps the reader get the big picture fast. When I am writing SQL code, I like to see my column listings in a group, each JOIN in a line group, the WHERE clause in a line group, etc.

8) Combining implicit and explicit joins in MySQL 5 with a sql_mode of STRICT will cause MySQL to complain loudly. Even if we hadn't used the explicit join in the middle, adding the implicit join after the explicit join throws many for a loop. By writing explicit joins, we lead the reader through the code.

9) When aliasing columns or tables, use the word AS even though it's not required. This helps the reader quickly see that the table or column is being aliased. Without it, the alias isn't as easy to find.

10) Combining JOIN USING with implicit joins is typically a problem for MySQL and will likely cause a syntax error. Consider being more explicit when using the USING clause.

11) Positional sensitivity creates fragile code. It's better to use order and group by with the names of the columns than their positional counterparts because the positional order can change without negatively impacting the functionality.

I have some other rules I use for my own code that make it easier for me to read SQL. Can you find them below?


      USE dbFoo ;
DELIMITER ;;
   CREATE
PROCEDURE sp_get_foo_data(
                           v_foo VARCHAR(2048)
                         )
      SQL SECURITY INVOKER
  COMMENT 'Make it easier to maintain how foo data is retrieved'
    BEGIN
          SELECT s.foo
               , b1.bar
               , b2.baz
            FROM blip
              AS b1

            LEFT
            JOIN splat
              AS s
              ON s.SPLAT_ID = b1.SPLAT_ID

           INNER
            JOIN blop
              AS b2
              ON b2.blip_id = b1.blip_id

           WHERE s.foo = v_foo
               ;
      END ;;
DELIMITER ;

You can see I like to keep operators and SQL clauses to the left of a visual vertical column (right justified) and indent according to the level of code (new blocks like the begin/end block are indented). I also prefer to be explicit with table aliases for each table in-use. I go through this extra step when tables are being joined even if those names don't conflict. This makes it easier to quickly indicate which table the column comes from. When I have long lists of comma-separated lists of items (columns in this case), I like to treat the comma as an operator and start a new line right before the comma then indent according to the list. I put the comma first because it makes adding and removing lines from the list much easier that way. When it makes sense to do so, I also like to vertically align arithmetic, comparison, and assignment operators.

I could go on, I think you might be able to see some of the why behind my own formatting preferences. What preferences do you have and why?

Motorola Droid versus Blackberry 8830 World Edition

2009-12-02T23:06:00.003-07:00

As a DBA, I often need to be available to perform work on databases I am responsible for when I am away from the office. I have two phones - a corporate Blackberry 8830 World Edition and my personal Motorola Droid. Both phones are fully featured and support a large number of applications. Both phones are impressive in their own right on capabilities, though the thing I feel I miss most on the Blackberry is the turn-by-turn verbally announced directions (versus VZ Navigator on my old Glyde). The ssh client in the Droid (ConnectBot) seems far more capable than the ssh client I use on my Blackberry. I had difficulty learning the Blackberry development environment and while I didn't try terribly hard, I was not able to find a simulator to help me test my applications before deploying them to a Blackberry. I did not have that problem at all with my Droid. The Droid simulator was very easy to find and works wonderfully for me. The integration into Eclipse also makes it very easy to utilize.

With what I know about the Droid versus the Blackberry after a few months of use, for my needs, the Droid beats my 8830 hands down.

Dave's Stuff: Cardinality

2009-11-03T12:49:00.000-07:00

Here's some good stuff on cardinality I read recently. It made me think to review my own understanding of the computation of cardinality in a table. Dave's Stuff: Cardinality

I didn't know that cardinality isn't a perfect computation of cardinality, rather a random sampling of values during an ANALYZE TABLE according to his description.

Dave - The way I have been explaining cardinality to users is to discuss uniqueness of a value. If a bin has n items in it, the highest cardinality possible is n if all the items in the bin are unique. The lowest possible cardinality is 1. The real value of cardinality is the relationship of the number of all items in the bin to the number of distinct items in the bin (key in this case). One formula for determining this is...

cardinality = total_items / distinct_items

Dynamic Memory Allocation in MySQL

2009-06-19T11:36:00.004-06:00

Did you know that MySQL can dynamically allocate memory for queries? I didn't till I found this web page... http://dev.mysql.com/doc/refman/5.0/en/memory-use.html

The connection buffer and result buffer both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement. While a statement is running, a copy of the current statement string is also allocated.

What's the big deal about this? mysqld needs enough memory in order to transmit the entire result of a query. If a query or result requires more than max_allowed_packet bytes, mysqld is unable to serve up the results. While the table structure is built to support very large amounts of data, it's possible to design a table in such a way that mysqld won't be able to respond by asking it to send back too much data in a single row.

I was surprised to learn that even if you can concat data into a text field, if the size of a retrieved data set exceeds max_allowed_packet bytes, the query will fail! That sucks. I was certain I had filed a bug on this years ago, but wasn't able to find it. At any rate, http://bugs.mysql.com/bug.php?id=45625 has been filed recently. :-)

What do you think? Test automation...

2009-05-31T01:40:00.000-06:00

What do you think? What are the advantages of how your approach works for you? Some say write tests after other code is done. Some prefer to write tests then code. Some want tests at the unit level. Some only care about testing API's. What are your thoughts?

What should I use - stored procedures or application code?

2009-04-25T12:19:00.004-06:00

I've often thought about using a stored procedure versus using application code to accomplish tasks. The rules I use are pretty simple:

Let the database generate data for itself (from data it already has).
Let the database maintain data security and integrity where appropriate.
Let the database help me administer it.

I've chosen to shy away from asking the database to handle business logic internally for a number of reasons, largely because databases are typically very expensive resources in comparison to application servers. It may also be difficult to revision business logic stored in the database, therefore making it more difficult to track how that code has changed.

I do understand the value of stored procedures reducing network traffic for certain types of queries as well as guaranteeing the effective use of the query cache, however, I find that applications can be designed to use libraries of SQL in such a way that the query cache is used effectively and network traffic is reduced through the proper use of joins, cursors and temporary tables.

Talking with other DBA's, some agree with me, some don't. What are your thoughts about when to use stored procedures versus application code?

Creating a MySQL reference database in 3 easy steps

2009-04-25T10:48:00.007-06:00

What is a reference database?

A reference database is nothing more than a set of data that developers and test systems use to mirror what production data looks like without actually exposing sensitive data from the production system. This is critical for systems that handle HIPPA and PCI/DSS data because developers need to know what production looks and performs like, but they aren't allowed to see protected data like credit card numbers or medical data about specific individuals.

There are a number of different goals a reference database typically covers:

Provide a realistic snapshot of what developers need in order to be effective when maintaining most of the production code (i.e. the DDL and realistic data).
Provide a way to test edge cases without affecting production.
Provide a data set that realistically models production for performance testing of queries.

How do I set up a basic reference database?

So, the process of creating the reference database is pretty simple using replication. I'll assume you're already using replication to provide back-up of your master data set (at minimum). I'm also going to assume you are on MySQL 5 or newer.

Create a replication slave that receives all data from your production master.
Create triggers and corresponding stored procedures on this slave that obfuscate sensitive data.
Utilize a cron or similar automated job to do a mysqldump of the resulting data set from time to time so that developers and testers can download it to their environments.

This accomplishes the goal of providing a production database snapshot in isolation without exposing sensitive data to others that aren't authorized to see it. It also accomplishes the goal of providing a basis for testing performance of the database as well as a method for verifying edge cases.

How do I cover edge cases?

The problem is, this method probably won't cover edge cases by itself, but as I said above, it does provide the basis for it. To cover edge cases that the production data doesn't cover, you'll need to write a script that will update a deployed copy of the reference database. What happens if certain data is missing? What if it's duplicated inappropriately? How does the application react when a transaction table is full - does it automatically add another transaction table to the merge? ... You know your application and the edge cases it needs. Creating scripts to simulate these conditions should be relatively straightforward.

Once you have a reference database and scripts to test performance and edge cases, it becomes terribly simple to add your database to your continuous integration system. Deciding on how often to run those tests largely depends on how often you can afford to run the tests with the resources you have available to you. Typically, I want to run a minimum set of tests for every code commit related to it, then run a full set of tests on a timed schedule so I don't overwhelm my build system.

Has this helped you? Do you have suggestions or comments that might help someone else? Please feel free to add your comments here. :-)

Letting RSS help you with searches

2009-04-25T01:57:00.004-06:00

Until recently, I never really thought much of using RSS to help me in this economy with job searches, but someone else did because sites like Dice, Monster, Indeed and Craigslist all provide ways to let your RSS reader keep you up-to-date with job search data. I know - I'm giving away one of my search secrets, but if I don't tell you, someone else will. Let's stick together and help each other out as we weather this recession.

When do I get a certification and why?

2009-01-20T22:45:00.005-07:00

I see questions like this on LinkedIn - http://www.linkedin.com/ very frequently.

Will it help me make more money? Will it help me get my foot in the door? Are certifications just a waste of time?

The answer to all these questions is: it depends. I've been on both sides of job searching (looking for work and looking for workers) and I can tell you that I've seen lots of people who had or were looking for certifications. My Certified MySQL 5 Database Administrator #5739 "ticket" has helped open doors for me. I have seen people come to me looking for work with certifications obtained by cramming for exams to pass certifications without attempting to understand what they were memorizing.

When I've interviewed (on both sides of the table), it's often been helpful to be able to explain how I used my certification in a real-world business scenario. For example, I've been able to say things like this (honestly, of course) to an interviewer. "I had been developing code against MySQL databases and administering those databases many years before I decided to obtain a MySQL certification of my own. The additional learning I got from studying for the certification helped me be better at my job and exposed me to new concepts that helped me think in new ways about how to use MySQL." Flipping that around, I've asked questions like - "Why did you get your certification?" and "What has the certification process done for you?"

Are you just out of college and deciding on getting certified to gain an edge? Go ahead, but better yet - if you can find an organization who can use those skills, even as a volunteer, it will likely have more credibility with an employer over someone who has a degree and certifications without experience.

Looking for ways to use your MySQL certification? Look for local clubs, non-profit's, and other similar groups that have a website, keep membership lists, or do other things where a database could help them. If they already have someone that's maintaining their database, you might ask if you could shadow that person to help build skills. If you end up getting some work out of it, great. If not, at least you'll build contacts that could potentially help you find work or provide a professional reference for you.

Testing MySQL Query Results

2009-01-20T15:53:00.010-07:00

Often times, developers talk about testing code, but few spend the time to test data changes made in SQL adequately. I've found that using code similar to this has been a real help to me in isolating potential bugs in how I've coded my own inserts, updates, and deletes. Many would argue that there are better ways to do this, often from an external language, but when all you have is plain old SQL, there are effective ways to perform tests.

Breaking it down, there are three basic steps I use:

Drop and re-create a table to store results in

Write inserts to that table from selects that verify key parts of expected results

Inspect the outcome of the tests

Drop and re-create a table to store results in

SQL Code:


DROP TABLE IF EXISTS test_resultset;
CREATE TABLE test_resultset (
    test_name    VARCHAR(255)         NOT NULL
  , created      TIMESTAMP            NOT NULL DEFAULT CURRENT_TIMESTAMP
  , passfail     ENUM('Pass', 'Fail') NOT NULL DEFAULT 'Fail'
  , result_count INT UNSIGNED         NOT NULL DEFAULT 0
);

Write inserts to that table from selects that verify key parts of expected results


INSERT INTO test_resultset ( test_name, passfail, result_count )
SELECT '01 first_test_table.first_test_column' AS test_name
     , IF( SOME CONDITION , 'Pass', 'Fail' )   AS passfail
     , COUNT(*)                                AS result_count
  ...
 GROUP
    BY passfail
     ;

Inspect the outcome of the tests


SELECT *
  FROM test_resultset
     ;

I've found that when I write my tests first, it helps me know when I've written "enough" code to be able to say a particular step is complete. When someone asks me what my code is supposed to do, I use my test cases to explain it to them.

Recently, I made modifications to reports that were very intricate. To make it easier on me, I wrote SQL code that verified my modifications before I made them. I used tests that looked something like this (the real tables are named descriptively, unlike these):


INSERT
  INTO test_resultset
SELECT '41 mlr.withheld_amount = SUM(mlrd.withheld_amount)' AS test_name
     , IF( ABS( mlr.withheld_amount - mlrdj.withheld_amount ) < 0.001
         , 'Pass'
         , 'Fail' ) AS passfail
     , COUNT(*)
  FROM mlr

  JOIN
     (
       SELECT id
            , SUM(withheld_amount) AS withheld_amount
         FROM mlrd
     )
    AS mlrdj
    ON mlr.mlrd_id = mlrdj.id

 GROUP
    BY passfail
     ;

When I looked at my result set, I saw my test passed, what the name of the test was, the number of records that matched, and the time when the test was last run. I know to look for test 41 in my result set so that if the test doesn't show up, I know it didn't return any rows so it's likely that either my test is not valid or there is no data to run my test against.

By storing the test results in a table, it also provides reference to help you know when you ran your last test set and gives you an easy way to find out what the results of each test were.

If you wanted to keep results from multiple runs of tests, you may want to create a separate table that will give you a test run number, then add that to your test_resultset table as a cross-reference. Then, removing data (like dropping the test_resultset table) between runs becomes not only unnecessary but undesirable.

While this is a very rudimentary test method, it can save lots of work by giving yourself an easy way to run a test set quickly. As you develop code, keeping a test set like this around (whether it's in a SQL file or driven by some other language), you'll find benefits from having test sets like this to rely on as your code becomes more robust and complex.

I could go on, but I'm sure you get the idea and can see how useful this could be when verifying SQL code works as expected. Want to learn more about writing tests before code? Check out "Test Driven Development" in your favorite search engine.

Using top, mytop, mysql command-line all inside screen

2009-01-16T21:22:00.005-07:00

This posting contains sample .screenrc and .mytop files.

If you've never used screen before and you like using command-line things, check it out. What I like most about screen is you can start a screen session, detach from it intentionally or otherwise, then reattach picking up right where you left off last time. The easiest way to explain what screen does is simply stating that screen allows you to open (within reason) as many independent terminal sessions as your system will allow. From each of these sessions, you can do pretty much anything you'd do from your regular terminal session. Some of these sessions can be displayed back to you at the same time, some may be left running in the background. In each session, however, screen tracks the session display just like a regular terminal would.

I use screen personally to help me be aware of what's happening critically on my system, and to provide a constant point of reference I can get back to where I left off when I'm able.

The top command is widely used by system administrators to see what processes are taking up the most resources on a given system. Like top, mytop also helps DBA's to see what threads are utilizing resources quickly. What many don't know is there are a number of things that can be done with mytop like killing active processes, turning off the header, setting up color, etc.

When you combine these features with screen, it can be a very nice tool when doing administration and development of a database host.

For example, over a telnet (very insecure) or ssh session, screen can present multiple session windows back to the user all, without needing a gui at all.

My own .screenrc automatically sets up my windows for me like this:

At the top, I automatically start top in a window that's 10 lines high. This lets me see the most process-intensive commands running at any given time.

The window below that is a mytop session to the database and shows me a list of the active queries at any given time (I filter out sleeping sessions and I don't generally display the headers giving me more space to see what queries are running. I also have it updating once per second.

In the third (bottom) window, I leave the rest of the lines to interact with MySQL, the shell, and other systems.

You'll probably find these files interesting:

$HOME/.mytop: (note: chmod 600 to protect your password from prying eyes)
----------------------------------------------------------------------------


host=dbdev:3306
user=me
db=sys_trunk_db
pass=my_pass
delay=1
port=3306
socket=
batchmode=0
header=0
color=1
idle=0

----------------------------------------------------------------------------

$HOME/.screenrc
----------------------------------------------------------------------------


# Make the escape key the backtick rather than ctrl-a since I use ctrl-a so much
escape ``
termcapinfo linux|xterm|rxvt|xterm-color ti@:te@:XT
autodetach on
autonuke on
defflow off
defscrollback 3000 # yes, that's lines
startup_message off
chdir
vbell on
vbell_msg "bell on %t (%n)"

caption splitonly "%{= bw}%-Lw%{= wb}%50> %n%f* %t %{-}%+Lw"

hardstatus alwayslastline "%< %= %{= bw} %H %{= rW} %l %{= bw} %m/%d %0c:%s %{-}"

activity "Activity in %t(%n)"

shelltitle "shell"

#
#  Toggle 'fullscreen' or not.
#
bind f eval "caption splitonly" "hardstatus ignore"
bind F eval "caption always"    "hardstatus alwayslastline"

# escape Control-R will reset my windows back to start-up sizes and locations.
# It's nice for when I disconnect from screen then reconnect because screen
# doesn't split windows by
# default.
bind ^R eval "only" "select 0" "split" "split" "focus top" "resize 10" "focus down" "select 1" "resize 12" "focus bottom" "select 2"

# If a window goes unresponsive, don't block the whole session waiting for it.
nonblock on

bufferfile /home/me/screenPasteBuffer.txt

msgwait 0
screen -t top 0
stuff "nice top \012"
screen -t mytop@dbdev 1
stuff "nice mytop \012"
screen -t webdev001 2
split
split
focus top
select 0
resize 10
focus down
select 1
resize 12
focus bottom
screen -t dbdev001 3
stuff "ssh dbdev001"
screen -t dbreport001 4
stuff "ssh dbdev001"
screen -t webdev001 5
stuff "ssh dbdev001"
screen -t webdev001 6
stuff "ssh dbdev001"
select 2
msgwait 2

pow_detach_msg "Screen session of \$LOGNAME \$:cr:\$:nl:ended."
shell -$SHELL

----------------------------------------------------------------------------

I can't take credit for all these enhancements. On the other hand, I have tweaked them to suit my own needs.

Enjoy!

Making the mysql command-line client work better

2009-01-09T21:35:00.002-07:00

Baron Schwartz on www.mysqlperformanceblog.com posted a number of very nice tips on how to use the mysql command-line client. Many of these pertain specifically to Unix-based clients, but some are rather useful in Windows as well. :-)

If you're doing MySQL solo, I've often found mysqlperformanceblog.com to be rather useful.

MySQL Clustering vs. Replication (single & multi-master)

2009-01-01T02:31:00.006-07:00

There is a really cool method of implementing replication in MySQL (5.0.24+, 5.1.12+) that allows you to have more than one replication master server for a data set without clustering. There are some really nice capabilities this gives you, however, there are some gotcha's with it too:

Pro's:

Having dual replication masters allows an administrator to take one of the masters off-line during an upgrade allowing the other master(s) to handle requests during that upgrade. This provides a cheap form of high availability (HA).

Hardware failures in a multi-master situation are less of an issue a s long as the software is configured to use more than one of the masters or a load balancer deals with sensing which host to use.

Given a large enough pipe, multi-mastering makes it possible to have a physically remote master. You can't do that reliably with MySQL cluster (not replicated) because it requires extremely fast connections between the nodes of the cluster.

No storage engine changes are required to implement for MyISAM (as opposed to Cluster requiring changing to NDB - a transactional storage engine).

Con's:

Locking/Updating Race: It's possible for a lock to succeed in multiple masters simultaneously then fail on the "other" master(s). For example, a user double-clicks a submit causing the application to start two different threads of the same type. It's possible that neither will know about the other until it's "too late" if each thread gets a different DB server connection.

Each DB still needs to have a full copy of the entire data set unlike cluster which can distribute copies of data across multiple servers.

Suffers from al the problems of replication and the fragility it introduces.

requires modifications to every table using auto_increment primary keys.

Tables that don't use an auto-increment field as the primary key or tables that have a UNIQUE key constraint on a column or set of columns may allow inserts to succeed on separate masters then fail in replication.

MySQL multi-master replication can be a cheap alternative to MySQL cluster without some of the issues introduced by changing storage engines and incurring the expense of setting up the number of servers (and associated RAM, disk, networking, etc.) required to make MySQL cluster work efficiently.

To learn more about MySQL Multi-Master Replication, see:

Using an effective_date column in MySQL

2008-12-30T22:59:00.000-07:00

There are times when I blog about things just because it's a nice place to share knowledge about how to get things done that aren't always obvious or straightforward. This is one of those things.

I have a table of "stuff" that uses an "effective date" to determine when a particular entry should be available. In this case, I want the largest effective date that's also not greater than the current moment. Sure - I could use a start and end date, but that would be a waste because it implies I know when I will stop using returned rows.

Let's assume I am showing users time-sensitive information from the following table (please ignore the lack of additional indexing):

CREATE TABLE user_notification (
   id             MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
 , category_id    MEDIUMINT UNSIGNED NOT NULL
 , effective_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
 , is_active      TINYINT(1) NOT NULL DEFAULT 1
 , message        MEDIUMTEXT NOT NULL
);

Next, let's assume that I only want to display the most up-to-date messages that are active for a given set of categories. There is one option that works for me no matter how many categories I use. It requires me to select and join from my user_notification table twice. When combining this data with other parts of a select, it will wind up being two joins.


  SELECT NOW() INTO @compare_date;
  SELECT un.*
    FROM user_notification
      AS un

    LEFT
    JOIN user_notification
      AS un2
      ON un2.id = un.id
     AND un2.effective_date > un.effective_date

   WHERE un.effective_date <= @compare_date
     AND un2.effective_date IS NULL

This might look a little strange, but here's how it works. Notice that the left join simply re-joins the same table back against the select, but, it's attempting to find the largest possible effective_date. The select's where clause limits us down to rows that are smaller than the compare date (NOW()).

This is one of those queries that may not come easily at first, but once you've done it a few times, you'll be able to crank out similar queries with ease.

Giving credit where credit is due - Thanks to my co-worker Brian P. for turning me on to this particular method of solving a similar problem.

Review: Samsung Glyde phone

2008-12-30T16:20:00.000-07:00

Rating: 2 (0=toss in trash, 10=give as nice gifts) in a DBA/IT operational environment.

This phone sucks. It has the worst user interface I've seen in any phone since I started using them more than ten years ago. What's wrong with this phone? Plenty.

The screen is heat sensitive (not touch sensitive) so accidentally allowing your face to touch the phone at one of the button points will activate the pad and likely send the person on the other end (and your ear, of course) a really annoying tone. Also, because it's heat sensitive, if you're into doing things outdoors when it's cold (I bike year-round), the phone may not be able to sense your keypresses in the event your fingers get cold enough. I suppose I could learn to live with these "features" if it weren't for my next major gripe.

Often, when I touch the pad, the phone thinks I'm pressing somewhere on the phone that's nowhere near where I'm pressing. For example, when in portrait mode, I unlock the pad (so I don't annoy the person on the other hand when the phone bumps my face), then want to click the end button. If I've typed any keys accidentially or intentionally, it isn't there, so I have to click on the "back" button. Assuming I can press that button (I often can't for the same reason that will become very apparent in a moment), often when the phone is cold, I press the end button on the pad and I can watch it display a depressed button two buttons to the left of the end button. When that happens, I am forced to either wait for the person on the other end to hang up, or power the phone off using a button on the side.

If you thought this phone had a standard USB-sized port on the side for charging, forget it. I thought I would be able to use this phone more like a PDA and transfer appointments to/from it, synchronize my settings with my laptop, and other handy similar things you can do with almost any PDA. I guess Samsung didn't think that was very important so (at least on my Verizon version), the only way I can sync things is through their web-based service and even that is only limited to contacts. :-(

The web browser sort-of supports Javascript, so using sites like Blogger.com are difficult at best (when you're turned off Javascript support in the phone). Of course, when you do that, you loose the ability to use the nice WYSIWYG editor we're so used to. Even if you turn off Javascript, you're limited to 2,048 keystrokes and you can't send the ENTER key like you can on a regular computer. How lame.

Signal with the Glyde on the Verizon network in and around Denver has been "okay" but definitely not great. My Qwest phone had fewer dropped calls and low signal issues than this phone. I am not sure if it's the phone, the network, or the locations I'm operating the phone from, but I'm not impressed at this point.

The Glyde also has some pretty tight restrictions on memory available. Just to copy a single CD-worth of 56Kbps .wmv files onto the phone, I had to add a MicroSD card to the phone. Want to listen to music privately? You'll need an adapter to bring your headphone size down if you're used to using the same headphones you use on your laptop or desktop computer.

The camera operation is "okay" as well, however, it often gets confused and enters recording mode when I just want a snapshot.

Having talked to a number of Verizon reps, I've learned that this phone is Verizons most problematic offering today.

Okay - having said all that "bad stuff" I think it's only fair to talk about some of the good (though difficult to find) things. I do like that it comes with both a wall-based charger and a USB cable to transfer music to the phone that can also act as a charger for the phone as well. I like the operation of the sliding mechanism and how it protects the keyboard. I also like that when viewing web pages, I can zoom in or out.

All in all, if I had known then (when I bought the phone) what I know now, I would never have even looked at the Glyde.

MySQL CMDBA Certification Tips

2008-12-29T15:01:00.000-07:00

Taking (and passing, of course) both the MySQL 5 CMDBA (Certified MySQL Database Administrator) examinations are all that's required to become a MySQL 5 CMDBA.

What did I do to prepare to take the exams? It helped that I've been using MySQL for a number of years, however, that isn't a prerequisite. MySQL offers a certification class that does a fair job of preparing attendees for the exam. However, even my class instructor suggested attendees go back to the book to continue practicing the exercises given and reading materials from the book. The book he was referring to is the very same book you can get at your local book store - MySQL 5 Certification Study Guide.

Knowing what I know now, with my experience, I could probably have passed the exams without the class, though taking the class exposed me to information I wouldn't have easily learned otherwise. That additional knowledge helped me become a better CMDBA.

If I had it to do over again, I would likely have used this approach:

Get the MySQL 5 Certification Study Guide
Read each DBA chapter carefully
Work through each of the exercises given in the study guide.
Find someone who is already certified to help me verify my answers.
Print a copy of the Q&A section on the included CD's
Answer each question without looking at the included answers first.
Study materials related to any questions I missed, then go back and take the sample questions again until I was able to pass easily.
When a particular topic proved especially difficult, I would have made up my own multiple choice questions right from the details given in the study guide.

Remember that the exams are based on a specific verison of MySQL 5 that is documented in the study guide. Some of the capabilities of MySQL have changed since the book and corresponding exams were written. The answer the exam is looking for is based on expectations at that time, not based on current performance and capabilities.

Good news - when I took my MySQL 5 CMDBA examinations, I was allowed to review answers I had already given (or not given) before submitting the answers back through the system. If in doubt about a question, don't let it bog you down. Make a note on the question and come back to it with the whiteboard or paper the proctor will provide. That way, you can get as many questions answered as possible. If early questions take big chunks of time to answer, that can hurt you if you don't finish some of the questions later in the exam.

One other note, if you have difficulty with a particular question (i.e. it seems ambiguous), make sure you comment on the question explaining why you answered the way you did. If the question really is ambiguous, then when your exam results go for review, the reviewers will see your comments explaining your thinking. This can help improve the test.

Why did I take the exams? Besides gaining the certification, I wanted to improve my knowledge of MySQL 5 specifically. I also wanted something I could put on my resume that helped show that not only had I done work on MySQL 5 systems, but I became certified on them in the process. That has helped me with credibility in interviews.

Oh - one other thing I almost forgot - on the day of each exam - I actually chose to take off early from work, then spend an hour in the coffee shop near the exam site just to make sure I knew the answers to the questions I had previously missed. It proved helpful. I also took the night prior to the exams off just to make sure my mind could relax. I also made it easier on myself by scheduling my exams for the afternoon, and I stayed away from certain foods I knew would make me tired that day (like turkey sandwiches at lunch) and other foods that would make me nervous or jumpy (caffine drinks for example).

Good luck on your exams :-)

KB