SQL Server Log Shipping – Testing the impact of large operations

Background

In a project we have here at Oasis Digital, our customer relies on the log shipping feature of Microsoft SQL Server 2000 to keep some secondary databases, reporting databases, up to date in almost-real-time: every few minutes a transaction log backup runs, then at a slightly longer interval, every 5-15 minutes, a restore of those logs runs on secondary reporting databases.  We’ve developed some code so that the reports can automatically run against any available reporting database; thus as the reporting database occasionally go offline for log shipping restores, the end users never notice that there’s more than one different database being used for their reports.

Most of the time, this works very well. The users get reports quickly, and those reports (no matter how large and painful the queries are) never have any impact on the production OLTP databas. But we have been stung a few times by changes we make in the production database having an unexpectedly large impact on that log shipping process.

We have two theories on what happens:

  1. Certain kinds of changes in the production database, such as adding a field, changing a field type, changing an index, can affect a very large number of pages of the database – since SQL Server log shipping occurs a page at a time, an operation that makes a small change to 100,000 pages of the database can result in an extremely large file being shipped between the production database and the reporting databases.
  2. There are performance characteristics of that restore process which we don’t fully understand – sometimes a log restore takes considerably longer than its side would suggest.  Sometimes a log-shipped change triggers such an occurrence.

The result of those two issues together is that sometimes we intentionally make a seemingly minor change in the database and it has a large, negative impact on that log shipping process: hours of reporting database downtime!  Occasionally it’s taken more than a day for the log restore target DBs to catch up.

This affects the users severely, and is therefore a Very Bad Thing.

Therefore, we’ve been looking for a way to assess the impact of such schema and index changes, on the log ship process, before making them in production.

Testing / Measuring

Unfortunately the only reasonable answer appears to be the rather large hammer of recreate a copy of the production system, including log shipping.  The databases in question are quite large, so this means two servers, or one server with a bunch of hard drives (to run the log ship source and destination on the same machine).

The process is:

  • Install a lot of hard drive space, OS, SQL Server
  • Restore a copy of the production DB, call this “test1”
  • Set up log shipping to one or two reporting DBs, test2 and test3

Then for each test run:

  • Run log shipping (so its all up to date)
  • Run test queries (like adding a field, changing a field type, changing a large index, etc)
  • Run log shipping again, and observe how big of a file is log shipped between the two DBs.

The main metric we’ll get out of this is for change X (i.e. for a certain SQL DDL change or DML change), we get a log ship of size N megabytes or N gigabytes.  I suspect that with this kind of data in hand, we will soon discover the underlying “rules” and understand which changes result in very large logs, so most of the time we can tell which things are going to have a big impact, and schedule them around appropriately.

We can even automate the test process: feed in a piece of SQL to a program runs those steps.  It might take hours of (cheap) computer time to run, but little (expensive) human time.

The Plot Thickens

What I’ve described above is the simple case.  There’s a more complex case: we’ve observed that the worst delays tend to happen if a log backup occurs in the middle of a certain operation – even operations that are usually harmless, can perhaps result in a huge log ship if the log backup happens mid-operation.  These also appear to be the logs that take especially long to restore on the recording on the destination database.

To test this, we can take a piece of DML that we think is going to take several minutes to run, start it, wait 30 seconds or so, and while it’s still running start a log backup.  Then wait until the DML completes and start another log backup.  We would gain several data points: the size of the mid-operation ship and the final data ship, the how long each takes to restore.  I suspect we will learn that it’s a really bad idea to let a log backup start while running any potentially large operation.

To prevent that, perhaps we can automate these operations like so:

  • Run a snippet of SQL to disable log backups (log shipping)
  • Wait for any running backup to finish
  • Run the target SQL
  • Wait for it to finish
  • Reenable log backups (log shipping)

That’s as much detail as I have time to post; hopefully this will help someout out there with SQL Server log shipping problems.  It would be great to hear from others out there who have experienced similar log shipping issues.

Refactoring to Patterns? No, learn the primitives.

Last night at XPSTL, John Sextro gave a talk on the “Move Embellishment to Decorator” refactoring as described in Joshua Kerievsky’s Refactoring to Patterns book. I greatly enjoyed and benefitted from the original Design Patterns book (from the Gang of Four) which was already old (published 1994) when I heard about it and bought it in 1998. (By the way, when I looked it up on Amazon to put in the link above, Amazon reminded me that: “You purchased this item on February 18, 1998”.)

I enjoyed John’s talk, and I hope he does more of them.  He hit a few rough spots along the way (the usually excellent IntelliJ IDEA IDE failed mysteriously, for example), but worked through it and reached the target of composable decorators. The rough spots led to some interesting diversions also.
I’m not sold on the “refactoring to patterns” idea though; it seems like a distraction from a more important goal: to gain deep experience and understanding of how to use the underlying “primitives” (encapsulation, abstraction, polymorpism, low coupling, high cohesion, etc.). Once you grasp the primitives, the design patterns are useful mainly as a tool for talking about how something works – in other words, write good code, then perhaps notice that it follows one of the “patterns”, if you find that helpful in explaining how the code works.

Several times at XPSTL, we’ve had lengthy conversations about how to choose whether to use “Strategy” or “Command” or “Decorator” or …. I’m not convinced that these conversations are helpful. My answer is that it is silly to look for a list of rules in choosing which pattern to use. Read the patterns, use them to learn good ways to use and combine the primitives. Then do that in your code:

  • Notice that you can benefit from polymorphism, and use it.
  • Notice that you can split a class in to two separately cohesive parts, and do so.
  • Notice that you could get composability by replacing inheritance with aggregation, and so so.

You’ll end up with the right “Pattern” – and you probably won’t care.

To Wrap, or Not To Wrap (Jemmy)

Yesterday I mentioned a talk by Mike Feathers about API design.  One of the topic of API wrapping, which we do frequently here at Oasis Digital, for a variety of reasons.

By coincidence, today the question came up of whether we should wrap the API of Jemmy, a Swing GUI testing tool.  Our natural inclination is to wrap.  But there are oppossing forces as well.  Here’s where I ended up:

  • The Jemmy API is large, and thus tedious to wrap.  (Which might be a good reason to wrap it…)
  • We haven’t used Jemmy much yet, so we don’t have any real idea what subset of its API we will use.
  • We haven’t done much GUI test automation yet, so we have little reason to think we know much about API design for that.
  • There are developers “out there” who know how to use Jemmy. Perhaps we will hire one, and benefit from them already knowing how it works.
  • Thus, we should start out using Jemmy as-is.
  • Once we have a moderate body of code (enough to understand out use, but not so much that revamping it would be burdensome), review this decision and decide whether to wrap it.

Michael Feathers at XPSTL

This evening at XPSTL, Michael Feathers (blog) (book) was in from out of town (and from around the world) and gave a talk on API design. He’s been thinking a lot about API design recently, driven by issues that come up with working with legacy code, which talks to lots of APIs, to cajole it in to a more testable state. I think there is a lot to say (maybe a book’s worth?), and a lot of what has been said elsewhere turns out to yield APIs that are unduly difficult to build testable code with.

What we end up doing here, and a thing that Michael says is not at all unusual, is to “wrap” most APIs with some application code, to enable:

  • a simplified way to call the external component / API, more suited to our needs
  • easy testing, as we can design our wrapper to make it trivial to substitute a test/mock implementation
  • a buffer from future changes in the external component / API
  • easier migration to alternate implementation of the same underlying services

Our wrappers tend to be “flatter” and simpler than the underlying APIs we wrap. For example, most of our use of Hibernate is behind a class we call DataSession, which represents the connection/session, transactions (it encodes our policies on how to use transactions) and many named methods for query operations (thus we avoid scattering HQL or SQL around the project).

Also, we had a big crowd at XPSTL – the room was packed.

More Bowling

In my last post, I presented an enhancement to a "bowling score calculator" problem being discussed on the Extreme Programming Mailing List. My solution extended a not-very-OO
solution presented here; though not object oriented, it was short and clear. I generally write intensively OO code, so I found this interesting.

A contention on the list, though, was that the procedural solution could not be extended to support more features easily. Today I’d added even more features, just to see if this is true:

  • Know how many pins are standing, and prevent the caller from knocking down
    more pins than are up.
  • Know which rolls occurred in which frame; be able to answer "what were
    the rolls in frame N?"
  • Present an HTML representation of the state of the game after each roll.

As usual, I added this features a test at a time a bit at a time. It turned out to be easy to keep track of which rolls go in which frame. The updated source code can be downloaded: bowling-java-3c.tgz and is also on github. As before the download includes the tests also. I’ve renamed a few things for greater clarity. (I’ve updated the file a couple of times since posting it, to fix a problem in the final output, and separate some tests I had combined.)

I was surprised to find that adding these features didn’t add much complexity to the code. When I look at this code, it cries out to have a class of some kind extracted – but I’ve tried extracting, for example, the idea of a Frame and been unsatisfied. Perhaps I’ll explore that more another day. These variable:

  private int[] frameScores = new int[NUM_FRAMES];
  private int[] firstRollInFrame = new int[NUM_FRAMES + 1];
  private int scoredFrame;
  private int finishedFrame;

could form the starting point for that, like so:

class Frame {
    private int score;
    private int firstRoll;
    private boolean scored;
    private boolean finished;
}

The Game class is in its entirety is
available here (syntax highlighted HTML) or in the download above.

I implemented the HTML rendering of frames without aid of test cases. The code is included
in the download above, and produces output like the sample run below. The output looks
much better when not included inside a WordPress post – the sample below is
partially mangled.

The main
loop of the demo program biases the random numbers toward high scores:

        while (!game.gameOver()) {
          // Bias our guy toward good rolls:
          int n = rnd.nextInt(game.pinsStanding() + 4);
          int pins = Math.min(n, game.pinsStanding());
          renderer.tellAboutPins(pins);
          game.roll(pins);
          renderer.printGame(game);
        }

Rolling… 7 pins

7

Rolling… 3 pins

7 3

Rolling… 10 pins

7 3

20

10

Rolling… 4 pins

7 3

20

10
4

Rolling… 2 pins

7 3

20

10

36

4 2

42

Rolling… 10 pins

7 3

20

10

36

4 2

42

10

Rolling… 0 pins

7 3

20

10

36

4 2

42

10
0

Rolling… 3 pins

7 3

20

10

36

4 2

42

10

55

0 3

58

Rolling… 10 pins

7 3

20

10

36

4 2

42

10

55

0 3

58

10

Rolling… 3 pins

7 3

20

10

36

4 2

42

10

55

0 3

58

10
3

Rolling… 5 pins

7 3

20

10

36

4 2

42

10

55

0 3

58

10

76

3 5

84

Rolling… 10 pins

7 3

20

10

36

4 2

42

10

55

0 3

58

10

76

3 5

84

10

Rolling… 4 pins

7 3

20

10

36

4 2

42

10

55

0 3

58

10

76

3 5

84

10
4

Rolling… 0 pins

7 3

20

10

36

4 2

42

10

55

0 3

58

10

76

3 5

84

10

98

4 0

102

Rolling… 1 pins

7 3

20

10

36

4 2

42

10

55

0 3

58

10

76

3 5

84

10

98

4 0

102

1

Rolling… 1 pins

7 3

20

10

36

4 2

42

10

55

0 3

58

10

76

3 5

84

10

98

4 0

102

1 1

104


Comments welcome, via email (address below).

How Many (Java) Classes Do You Need To Go (XP) Bowling?

An object-oriented developer searches for a reason to add more of them.

Ron Jeffries recently posted a couple of articles on simple design, with the
specific example of code to score a bowling game:

In a thread on the subject on the XP Mailing List, various posters expressed
a preference for one solution or the other.

With the caveat that I’m normally a very object-oriented guy, I prefer the
second (procedural) solution, in that I find has approriately simple design:
it work, expresses the programmer’s intentions, has (almost) no duplication
of ideas, and has the minimal amount of code needed to do all those things.

The point was made, though, that perhaps the procedural solution would fall
apart once the complexity of the problem increased. To test this idea, I decided
to extend it with the following features:

  • Know what frame the bowler is current done with / working one
  • Know the score of each frame
  • Know if the game is over
  • Reject erroneous input data

My first step was to convert the example C# code to Java. (I like C# at least
as much as Java, but for the moment I’m happier with the code-editing features
of Eclipse compared to VS.NET.) Here’s the Java code, the starting point before
adding any features:

/*
 * Trivial port of Ron Jeffries' Bowling to Java
 */
package bowling;

import java.util.ArrayList;

public class BowlingGame {

  ArrayList rolls = new ArrayList();

  public void roll(int roll) {
    rolls.add(new Integer(roll));
  }

  public int score() {
    int rollIndex = 0;
    int total = 0;
    for (int frame = 0; frame < 10; frame++) {
      if (strike(rollIndex)) {
        total += 10 + pinCount(rollIndex + 1) + pinCount(rollIndex + 2);
        rollIndex++;
      } else if (spare(rollIndex)) {
        total += 10 + pinCount(rollIndex + 2);
        rollIndex += 2;
      } else {
        total += pinCount(rollIndex) + pinCount(rollIndex + 1);
        rollIndex += 2;
      }
    }
    return total;
  }

  private boolean strike(int rollIndex) {
    return pinCount(rollIndex) == 10;
  }

  private boolean spare(int rollIndex) {
    return pinCount(rollIndex) + pinCount(rollIndex + 1) == 10;
  }

  private int pinCount(int pinPosition) {
    return ((Integer) rolls.get(pinPosition)).intValue();
  }
}

I also converted the NUnit tests to JUnit. (Formerly this was available for download,
but I’ve misplaced the file since then.)

With this in hand, I started adding tests for my new features. An example test:

  public void testStrikeWithFrameCounting() {
    game.roll(10);
    assertEquals(1, game.finishedFrames());
    assertEquals(0, game.scoredFrames());
    game.roll(5);
    assertEquals(1, game.finishedFrames());
    assertEquals(0, game.scoredFrames());
    game.roll(3);
    assertEquals(2, game.finishedFrames());
    assertEquals(2, game.scoredFrames());
    game.roll(2);
    assertEquals(2, game.finishedFrames());
    game.roll(1);
    assertEquals(3, game.finishedFrames());
    assertEquals(3, game.scoredFrames());
    rollMany(14, 0);
    assertEquals(10, game.finishedFrames());
    assertEquals(10, game.scoredFrames());
    assertEquals(29, game.score());
  }

This test, and others like it, verify that after each roll, the game object
know what frame has been completed, what frame is scorable, and when the game
is over. (I also added the feature to report the score of each frame, but didn’t
get around to testing it explicitly. I found that to get things right, I needed
a variety of tests for things happening in the last frame, since there are special
rules there.

Without further ado, here is working code to score the game, with the new features:

/*
 * Bowling Scorer, converted to Java, extended to know what
 * frame we are on, what frame has been scored, whether the
 * game is over, and the score of each frame
 */
package bowling;

public class BowlingGame {

  private static final int NUM_FRAMES = 10;
  private static final int NUM_PINS = 10;
  private static final int MAX_ROLLS_IN_GAME = NUM_FRAMES*2 + 1;

  // Input state:
  private int[] rolls = new int[MAX_ROLLS_IN_GAME];
  private int rollSoFar = 0;

  // Output state:
  private int[] frameScores = new int[10];
  private int scoredFrame;
  private int finishedFrame;

  // Processing variables; these would be locals, but
  // this class essentially is a "method object", so we
  // use instance variables instead of param passing
  private int scoringFrame;
  private int scoringRoll;

  public void roll(int roll) {
    if(roll<0 || roll>NUM_PINS)
      throw new RuntimeException("Roll out of range");

    if(gameOver())
      throw new RuntimeException("The game is over, no more rolls allowed.");

    rolls[rollSoFar++] = roll;
    calculate();
  }

  private void calculate() {
    scoredFrame = 0;
    finishedFrame = 0;
    scoringRoll = 0;
    for (scoringFrame = 1; scoringFrame <= NUM_FRAMES; scoringFrame++) {
      if (isStrike()) {
        scoreStrike();
      } else if (isSpare()) {
        scoreSpare();
      } else {
        scoreNormal();
      }
    }
  }

  private boolean isStrike() {
    return rolls[scoringRoll] == NUM_PINS;
  }

  private void scoreStrike() {
    storeFrameScore(NUM_PINS + rolls[scoringRoll + 1] + rolls[scoringRoll + 2]);
    frameIsScoredIfWeHaveRollOffset(2);

    if(scoringTheLastFrame())
      frameIsDoneIfWeHaveRollOffset(2);
    else
      frameIsDoneIfWeHaveRollOffset(0);
  }

  private boolean isSpare() {
    return rolls[scoringRoll] + rolls[scoringRoll + 1] == NUM_PINS;
  }

  private void scoreSpare() {
    storeFrameScore(NUM_PINS + rolls[scoringRoll + 2]);
    frameIsScoredIfWeHaveRollOffset(2);

    if(scoringTheLastFrame())
      frameIsDoneIfWeHaveRollOffset(2);
    else
      frameIsDoneIfWeHaveRollOffset(1);
  }

  private void scoreNormal() {
    storeFrameScore(rolls[scoringRoll] + rolls[scoringRoll + 1]);
    frameIsScoredIfWeHaveRollOffset(1);
    frameIsDoneIfWeHaveRollOffset(1);
  }

  private boolean scoringTheLastFrame() {
    return scoringFrame == NUM_FRAMES;
  }

  private void storeFrameScore(int frameScore) {
    frameScores[scoringFrame - 1] = frameScore;
  }

  private void frameIsDoneIfWeHaveRollOffset(int rollOffset) {
    if(scoringRoll + rollOffset < rollSoFar) {
      finishedFrame = scoringFrame;
    }
    // Continue scoring at the roll after the last one
    // on this frame:
    scoringRoll += rollOffset + 1;
  }

  private void frameIsScoredIfWeHaveRollOffset(int rollOffset) {
    if(scoringRoll + rollOffset < rollSoFar) {
      scoredFrame = scoringFrame;
    }
  }

  // The public interface has a few more methods for the new features:

  public int score() {
    int totalScore = 0;
    for(int i=0; i<frameScores.length; i++)
    totalScore += frameScores[i];
    return totalScore;
  }

  public int scoredFrames() {
    return scoredFrame;
  }

  public int finishedFrames() {
    return finishedFrame;
  }

  public int scoreForFrame(int frame) {
    return frameScores[frame-1];
  }

  public boolean gameOver() {
    return finishedFrame == NUM_FRAMES;
  }
}

Here is what I like about this solution:

  • There are tests to show that it actually works
  • The method names are, at least to me, intention-revealing
  • The methods are short and straightforward
  • The main scoring loop, which still happens in one straight-line pass, is
    simply this:

        for (scoringFrame = 1; scoringFrame <= NUM_FRAMES; scoringFrame++) {
          if (isStrike()) {
            scoreStrike();
          } else if (isSpare()) {
            scoreSpare();
          } else {
            scoreNormal();
          }
        }
  • There’s not much duplication left in there; perhaps someone can suggest
    a way to get rid of the bits that remain

The real question, of course, is whether this is too much for one class to
do – whether there are any parts of this code that should be a separate class.
The obvious candidates are the pairs of methods: should isStrike() and scoreStrike()
somehow be in a Strike class? They have an obvious parallel structure, a form
of duplication that I might be able to get rid of by adding classes.

I thought these new features would push me there – but they didn’t. Perhaps
a future story would do so. This implementation is still not quite “finished”,
in that I know there are more kinds of error conditions to consider, test for,
and implement. I don’t see any reason to think that adding those now would add
much to the conversation, so I didn’t add them.

One lesson of this exercrise, to me, is a common one: the problem space is
not the solution space
. Just because we have a thing called a Foo in the problem
domain, doesn’t mean we need a class Foo in the solution. We might, we might
not; with test-driven design, the need for a Foo should eventually become obvious.

Comments welcome, via email (address below) or on the XP mailing list.