techblog 

Any organization using databases needs many copies of the live database for development, training, beta and performance testing. As a DBA a considerable amount of time can be spent on provisioning these databases. For the organization it often means that large parts of a SAN or NAS storage array are taken up by storing all the copies. Traditionally a DBA might make backups of the live database to tape and when asked for a test database restore the backup from tape to a different host.

However the current snapshot technology in many storage arrays offers an effective way to save a lot of space and time in this provisioning process.  A snapshot can be described as a virtual copy of a logical source volume. Behind the scenes the storage array lets the snapshot point to the same disk blocks as the source volume for reading of the blocks. Only when either the source volume or the snapshot data changes the storage array writes the values that the snapshot needs to see to a separate place on disk. This way the storage array maintains a consistent view of a whole volume using up much less disk space. The amount of disk space saved depends of course on the amount of changes applications make against the source volume and snapshot. For databases it is on average true that the changes aren’t many relative to the size of the database.

Saving disk space

The advantage here is clear. We can save a lot of disk space by using snapshots rather than full copies of a database. Suppose you have a Live database of 200 Gb and need ten copies of that live database. You could spend nearly  2 TeraByte on that (10* 200 Gb). If you use snapshots and reserve 20% of diskspace for each copy to allow for database changes you will use:

(200Gb + ((0.2 * 200Gb) *10)) = 600 Gb

That means a 1.4 Terabyte saving.

Saving time

The other advantage of using snapshots is in saved time. Creating a snapshot is usually a matter of seconds or perhaps a few manual actions in the GUI to the storage array. Restoring a backup of 200 Gb in the traditional way from tape can take up to half a day or more. And you’ll have to repeat that for every copy needed again.

In summary, I think snapshots put less pressure on your disk space management and allows you to provision databases faster so that developers and testers can get on with their jobs faster.

The JUnit 4.5 release includes a new standard test runner, BlockJUnit4ClassRunner, which is part of the published API and is designed to be an extension point for building your own custom test runners.

The flow for executing a particular test method is built up by chaining a sequence of Statements, as seen by inspecting the source code for BlockJUnit4ClassRunner:

protected Statement methodBlock(FrameworkMethod method) {
  Object test;
  try {
    test= new ReflectiveCallable() {
      @Override
      protected Object runReflectiveCall() throws Throwable {
        return createTest();
      }
    }.run();
  } catch (Throwable e) {
    return new Fail(e);
  }
 
  Statement statement= methodInvoker(method, test);
  statement= possiblyExpectingExceptions(method, test, statement);
  statement= withPotentialTimeout(method, test, statement);
  statement= withBefores(method, test, statement);
  statement= withAfters(method, test, statement);
  return statement;
}

The statement returned by methodBlock is evaluated for each test method in the test class. The statement base class is straightforward, with a single evaluate method to implement:

public abstract class Statement {
  public abstract void evaluate() throws Throwable;
}

This makes it relatively easy to hook into a particular point in the execution flow to introduce custom behaviour. As part of our upgrade to 4.5, I needed to port a test runner which executed each test method within a transaction which is rolled back on completion. By overriding withAfters, I can obtain the statement which represents the standard JUnit execution flow:

protected Statement withAfters(FrameworkMethod method,
  Object test, Statement statement) {
  statement = super.withAfters(method, test, statement);
  // Evaluate the standard junit statement within a spring transaction
  statement = withinTransaction(test, statement);
  // Evaluate before and after transaction annotations outside transaction
  statement = withBeforeTransactions(method, test, statement);
  statement = withAfterTransactions(method, test, statement);
  return statement;
}

I then create a new Statement which wraps the standard flow in a Spring managed transaction:

private Statement withinTransaction(final Object target, 
  final Statement statement) {
  return new Statement() {
    public void evaluate() throws Throwable {
      PlatformTransactionManager txManager;
 
      try {
        txManager = ((HasTransactionManager) target).getTxManager();
      } catch (ClassCastException e){
        throw new RuntimeException("Test class must implement HasTransactionManager", e);
      }
 
      new TransactionTemplate(txManager, new DefaultTransactionDefinition(
      TransactionDefinition.PROPAGATION_NESTED)).execute(
      new TransactionCallbackWithoutResult()
      {
        public void doInTransactionWithoutResult(TransactionStatus transactionStatus)
        {
          try {
            // evaluate the standard flow within a transaction
            statement.evaluate();
          } catch (Throwable throwable) {
            throw new RuntimeException(throwable);
          }
          finally {
            // Force a transaction rollback
            transactionStatus.setRollbackOnly();
          }
        }
      });
    }
  };
}

Finally I add in some hooks which mirror the @Before and @After annotations to run methods outside the transaction scope:

protected Statement withBeforeTransactions(FrameworkMethod method, 
  Object target, Statement statement) {
  return new RunBefores(statement, 
    getTestClass().getAnnotatedMethods(BeforeTransaction.class), target);
}
 
protected Statement withAfterTransactions(FrameworkMethod method, 
  Object target, Statement statement) {
  return new RunAfters(statement,
    getTestClass().getAnnotatedMethods(AfterTransaction.class), target);
}

It was quite easy to figure out how to write a custom test runner by examining the source code for the provided statements, which I think reflects well on the new design.

I’m currently in the process of adding some reports based on data stored in a database. With annotated configuration in Spring 2.5 and the flexibility of JFreeChart, I was surprised at what can be achieved with such little effort.

The new features added to spring-mvc provide a nice way to avoid having to manage large configuration files and meant that I could concentrate on writing the code for my charts. I’ve added a quick example after the jump

Continue Reading »

We have a Sun X4150 server on trial here so see that it performs as well as the equivalent HP server. The Sun servers claim to be more energy efficient and as we have a CSR policy at work then we really should be trying to reduce the power we use in our server room. The Sun server arrived with Solaris pre-configured, but we wanted to test running CentOS, so a quick PXE boot and kickstart later we had an OS installed. Puppet then configured the server to our standard build. All was going well so far - as you would expect as they are just standard servers inside.

Configuring the lights out management network interface proved a bit trickier. This is done from the bios but the LOM interface is not easy to spot. It is in the server menu and is called AST200 LAN Configuration - not obvious at all but once you know where it is then it’s not a problem. The eLOM can now be upgraded to iLOM which is good but why not just install iLOM on all new servers? Maybe this is an old loan server but you would think that they would make sure it had the latest version of LOM installed.

On our HP servers we use hardware raid, so our test server came with a StorageTek (adaptec) Raid controller so that we were doing a like for like comparison. Initial volume configuration was easy, but monitoring the array proved a bit trickier. I was initially pointed to a GUI to do this. Whilst this looks very good and has the ability to send email alerts it does not fit in very well with our monitoring system which uses scripts run on the remote server. The answer came the X4150 server downloads page. This download contains a linux.zip file which has a StorMan rpm inside it. Installing this gives access to the /usr/StorMan/arcconf command which can be used to get the array status (Initially this failed to run as it was looking for the file libstdc++.so.5. This was provided by the StorMan rpm in /usr/Storman but I just installed the compat-libstdc++-33 rpm instead). A wrapper script around this command means we can monitor the array remotely.

This server does everything that our current servers do and the hardware can be monitored correctly.

Following up from my previous post on using SQL on collections using the TABLE operator, I did a little test on my assertion that the TABLE operator will be faster. I removed the spooling to screen in the code to make sure that the actual gains are better illustrated by the elapsed time. Remember the original table X (see the previous post for the PL/SQL procedures):

 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ID                                                                         NUMBER
 NAME                                                                       VARCHAR2(30)

I filled the original table X with 100.000 records and made a collection filled with 50.000 ID’s. See the results below:

declare
   test_coll test_coll_type := test_coll_type();
begin
   test_coll.extend(50000);
   for i in 1..50000
   loop
      test_coll(i) := i;
   end loop;
   who_walks_the_dog1(test_coll);
end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:36.57

declare
   test_coll test_coll_type := test_coll_type();
begin
   test_coll.extend(50000);
   for i in 1..50000
   loop
      test_coll(i) := i;
   end loop;loop;
  9
   who_walks_the_dog2(test_coll);
end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.21

That is an amazing difference. But of course, we haven’t indexed the ID column on the table yet. That causes 50.000 full table scans on a 100.000 record table.

explain plan for select name from x where id = 1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     8 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X    |     1 |     8 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Let’s make this comparison fairer and see what happens when we create an index on the ID column and analyze the table.

create index x_idx on x(id);
analyze table x compute statistics;
explain plan for select name from x where id = 1;
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    12 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| X     |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | X_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

We are using the index now for the statement that doesn’t use the TABLE operator. What happens now when we execute the procedures again ?

declare
   test_coll test_coll_type := test_coll_type();
begin
   test_coll.extend(50000);
   for i in 1..50000
   loop
      test_coll(i) := i;
   end loop;
   who_walks_the_dog1(test_coll);
end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.05
declare
   test_coll test_coll_type := test_coll_type();
begin
   test_coll.extend(50000);
   for i in 1..50000
   loop
      test_coll(i) := i;
   end loop;loop;
  9
   who_walks_the_dog2(test_coll);
end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17

Using the TABLE operator is still about ten times faster in this case. The more records you use the greater the gains. The converse is true as well.

You may have worked with collections in SQL and PL/SQL already. Collections are basically arrays holding columns or records of a user-defined data type. They come in various flavors. They can be defined as PL/SQL variables, but also as data types within the database. For example like this:

create type test_coll_type is table of number;
/

Imagine you have a procedure receiving such an array filled with id’s as an IN parameter. Within the procedure we need to check whether any of the values in this array exist within a table in the database. One’s first idea to solve this would probably be something like this:

create table x(id number, name varchar2(30));

ID NAME
---------- ------------------------------
1 James
2 Mark
3 John

create or replace procedure who_walks_the_dog1(p_coll   test_coll_type)
is
   l_name varchar2(30);
begin
   for i in p_coll.first..p_coll.last
   loop
     if p_coll(i) is not null
     then
      begin
      select name into l_name from x where id = p_coll(i);
      exception
      when no_data_found then null;
      end;

      dbms_output.put_line(l_name||' walks the dog');
     end if;
   end loop;
end who_walks_the_dog1;
/

Of course that is not very pretty code and it took me longer to write than I initially thought it would take. It also means a lot of calls to the database. That creates a lot of switches between the SQL and PL/SQL engine causing performance degradation.

It would be ideal if we could just take the list of ID’s and use it in a WHERE clause saying “WHERE id in (list of id’s)”.

Here is how you do it. You use the TABLE operator that basically converts a collection to a normal table so you can use the collection in SQL statements. That looks like this:

create or replace procedure who_walks_the_dog2(p_coll   test_coll_type)
is
type name_tabtyp is table of varchar2(30) index by binary_integer;
name_tab name_tabtyp;
begin
select name bulk collect into name_tab from x where id in (select column_value from table(p_coll));

if name_tab.count > 0
then
for i in name_tab.first..name_tab.last
loop
dbms_output.put_line(name_tab(i)||' walks the dog');
end loop;
end if;
end who_walks_the_dog2;
/

The TABLE operator allows you to write shorter, simpler code that’s more intuitive to read and it should be faster especially when you process a lot of rows. Test it like this.

declare
   test_coll test_coll_type := test_coll_type();
begin
   test_coll.extend(5);
   test_coll(1) := 1;
   test_coll(2) := 2;

   who_walks_the_dog2(test_coll);
end;
/

Collections and the TABLE operator have lots more interesting applications. Perhaps more in the next post.

We had some issues in getting an MS SCOM 2007 (formerly Microsoft Operations Manager) install up and running this week. This product has a fairly heavy set of pre-requisites before it will install and amongst these is asp.net. Try as we could myself and a fellow sysadmin could not get the install to recognise that asp.net (which is included in the .net framework) was installed. Luckily after a little search I stumbled upon MS KB article 934759 (http://support.microsoft.com/?kbid=934759) which states that this problem can occur if you install IIS after the .net framework as asp.net does not then get registered in the IIS metabase, it goes on to recommend that you run the ASPNet_RegIIS.exe command to register asp.net, however this command seems to be 32 bit only and would not run on our 64 bit installation of Windows, the only options were to modify the IIS metabase manually or re-install from scratch, in the interests of application stability we opted for the latter, when we then ensured that IIS was added before the .net framework the SCOM 2007 went through without any issues.

I had cause recently to search for domains that contained 2 and only 2 letters. One way of performing this is to use the relatively new Oracle support for querying with regular expressions - regexp_like. The Oracle implementation of regular expressions follow enhanced POSIX support.

First off how not to do it. If you attempt something like the following:

select *
from domains
where key like '__';

This will indeed return all domains containing 2 characters, but it also returns domains with numbers in them as well and this was not what we were interested in. It really had to be a regular expression:

select *
from domains
where regexp_like (key, '^[a-z]{2}$');

So how do we decode this expression?

^ In this position actually donates the beginning of the data. We need this as we want to find 2 occurances and only 2 occurances so we start counting from the beginning of the data, rather than at some arbitrary point.

[a-z] is the standard find me any alphabetic character, we know the data is lowercase.

{2} states we need to find 2 occurances of the previous expression, i.e. 2 alphabetic characters.

The $ signifies the end of the line. All domain names contain 2 or more characters we do not want to find occurrances that have more than 2 characters.

I’m sure there is more than one way of performing this little bit of sql, but certainly regular expressions make it easier, though they can take a bit of getting used to.

As a follow-up to my previous post about a race condition in dnsjnio, I thought I’d explain the issues that arose, and what has been done to fix them. These fixes are now in beta, so if you’d like to help test them, please let me know soon!

Basically, I found two bugs in the 0.9.9 release of dnsjnio; both were related to a race condition between the timer thread and the reception of data. The first bug was pretty much as reported in my previous post. The timer thread would fire a timeout for a query. In the meantime, data has been received, but the client has not yet been notified. The connection is being closed as the timer fires. When the timer thread comes to close the connection, it did not check that the connection was still good, so a NullPointerException was generated when it attemped to close the closed connection. This has been fixed by checking the status of the connection, and returning a boolean to the timer thread indicating the status. If the connection has been closed, then the timer thread takes no more action (otherwise, a timeout event would be sent!).

The other bug was similar, but the other way round - the data arrival occurs just after the timeout. This resulted in two client events; both a timeout, and a data receipt! This case was more difficult to fix - although I could make sure that the connection was properly closed, it was difficult to signal this back to the select thread (the Thread : void run() interface is used). In the end, I decided to put some logic in the query Transaction class itself, that enforces the condition that only one result must be generated for each query. In this case, whichever event fires first has the priority.

Note that both of these bugs would only show up if the query timeout value was very close to the normal query time. If a long timeout value was used (say, 10 seconds or more), these bugs would be unlikely to show up. In order to reproduce them (a requirement to prove a fix!), I had to write some pretty pathological test code (which took some time). In normal dnsjnio usage (as I imagine it), a single client thread makes many query requests to a single dnsjnio select thread (an additional timer thread is also present). This client thread uses the ResponseQueue interface to pick up query responses as they return. To reproduce the bug, I had to have hundreds of client threads, each performing sequential queries (using the original dnsjava thread-intensive callback pattern), with timeouts starting at 500ms. Still, even this case now works with no problems.

Please let me know if you have any issues with this latest beta release - the latest test version can be found at :

https://dnsjnio.svn.sourceforge.net/svnroot/dnsjnio/tags/dnsjnio-1.0.0-beta/dnsjnio-1.0.0-beta-1.jar

I’ve just released version 1.2 of dnsruby as a gem on Rubyforge. This release adds no major functionality, but fixes a few issues that have come up. The main reasons for a new release are some fixes to the dnsruby select thread. Previously, there was a nasty memory leak here, and performance was not totally reliable (see tales of woe here and here). This has now been fixed, and the select thread seems to work just fine (with 100 threads concurrently sending and receiving queries, for many hundreds of thousands of queries).

It’s worth upgrading to version 1.2!