21 Jul 2016, 00:00

An introduction to testing, part 3: Different types of tests

Okay, so we set the scene in the previous blog. We have an example SSL endpoint checking script. So it’s time to start talking about the different main types of tests ahead of writing them for the script.

As I mentioned previously, testing is generally split up into three areas. Unit tests, Integration tests and tests. You’ll see all sorts of odd definitions for what constitutes these tests, most of them seem fairly arbitrary often company specific (for example, I’ve read that Google has some time limits on tests based on what they’ categorised as, e.g. unit testing an entire application should take less than 1 minute). I’m going to ignore that and focus on what appears to be the broadly accepted terms. If you want make it more specific than I do, knock yourself out.


Unit tests are small tests, aimed at testing the behaviour of individual methods (functions, subroutines or they’re referred to in your language of choice) in your code, to establish that they take your input and return output you want on a method by method basis. You should also be trying to test and ensure that they respond appropriately to abnormal input.

They provide value in both writing your code, and then later on any time you make modifications to it. It’s rare me to write tests straight for code and not find myself making adjustments to what I’ve originally written as bad behaviour is realised. Any time you go to make later modifications to your code, you can have reasonable that you’ve not added any unexpected behaviour. Note however, your code is only ever as correct as your tests are. you write bad unit tests, bad code will pass it.

Your unit tests should not involve calling any external services or libraries at all. At this level you’re not interested in whether the logic is correct not if the code actually works. When we start writing unit tests with the example code, I’ll show you how we can avoid those external calls.

Here’s a quick contrived example of a unit test. I’m going to utilise the excellent pytest framework for this, though nose is also well worth a look. Use whatever like, so long as you write tests!

First we need a method to test:

def adder(x, y):
    return x +

It’s a short and simple piece of code, it just adds two numbers and returns them. Here’s a possible unit test for it:

def test_adder_one_and_one_is_two():
    assert adder(1, 1) ==

If I give it 1 and 1, it should return 2. Note the method name there. Most testing frameworks will discover tests, and they look for methods with a name that starts with test_. There’s something else I hope is also notable about the test name. It’s very descriptive. This is an additional that tests can provide for code. They’re one more layer of documentation. That’s as easy as writing tests can be. That didn’t even take a minute to write.

I’m pretty sure almost every sysadmin has wasted numerous hours trying to unravel an undocumented, critical, piece of code at least once in their career. Between the name of the test, and what happens within the test, it give someone coming fresh to the code a very clear idea of what everything does. If you’re coming back to a piece code after a while, it may even be just the reminder you need that will turn an hour long task into a five minute one. That said, please for the love of all that is holy: write documentation and include comments and docstrings in code!

Depending on what we were trying to do with the method, it’s arguable there is a bug in it. I described it as “it adds two numbers and returns them”, but it does more than that. If you pass it two strings, in its current state will concatonate them. Feed it “1” and “1” and you’ll get “11” out the other side.

So we should also have some tests to account for strings and mixed types and expected results, especially as we’re doing anything fancy to catch them (we really should.) Here’s a slightly filled out example. If you’ve got python pytest installed on your machine, feel free to copy / paste and try it out. If we assume that the method is correct:


def adder(x, y):
    return x +

def test_adder_one_and_one_is_two():
    assert adder(1, 1) == 2

def test_add_one_str_one_int():
    with pytest.raises(TypeError):
        adder("1", 1)

def test_add_one_str_one_str():
    result = adder("1", "1")
    assert result == "11"

with that saved in a file (unit.py in this case) you can run py.test against it:

$ py.test unit.
platform linux2 -- Python 2.7.3 -- py-1.4.30 -- pytest-2.7.
rootdir: unit, inifile:
collected 3 items

unit.py ...


Of course this example is terrible. It has just too many permutations and possiblities to test to be practical. It be like this and pass all the tests defined so far:

def adder(a, b):
    if a == 1 and b == 1:
    elif a == "1" and b == "1":
        return "11"
        raise TypeError()

However what we’ve written is not without value. Suppose we modified adder to this:

def adder(a, b):
    c = (a*a)+(b*b)
    d = c+a+
    e = d-(a*a)-(b*b)

If you pass 1 and 1 you’ll get 2 out of it. It works for pretty much any valid numbers put in to it. If we run py.test though:

$ py.test unit.
rootdir: unit, inifile:
collected 3 items

unit.py ..

______________________________________________________________________________________ test_add_one_str_one_str

    def test_add_one_str_one_str():
>       result = adder("1", "1")


a = '1', b = '1'

    def adder(a, b):
>       c = (a*a)+(b*b)
E       TypeError: can't multiply sequence by non-int of type 'str'


It nicely falls over on the string addition. So you can see how the tests establish that this refactoring of the isn’t good, even though it might seem ridiculous, but correct, at first glance. If you look carefully at that under FAILURES, you’ll see that it tells you what the inputs to the method were and where the error was thrown. If error was thrown, but the test still failed it will report something like this:

E       assert 3 ==
E        +  where 3 = adder(1, 1)

You can see I tried to assert 3 == 2 (and it tells you where that 3 came from). Hopefully you’ve seen that unit tests are quick and have very narrow focus. That brings us on to the next type of test.

You should generally expect to find lots of unit tests, and because they’re not actually doing any real work, that they will execute extremely quickly. This encourages regular and rapid iteration of work. You could use something like watchdog to monitor your development workspace and automatically re-run unit tests every time you modify code, for example, so you get immediate feedback on your changes.

We’ve covered a lot of ground here, just going over unit tests, which means the next two can be defined relatively briefly!

Integration Tests

Here’s where we step back one level and look at how methods interact or integrate correctly. Methods will spend a lot of time interacting with each other, this makes sure that the overall behaviour is correct. Like with unit tests, integration tests should avoid any calls to external services unless strictly necessary. At this level you are still really interested in the logic, rather than functionality of the code. Due to these tests exercising multiple methods, you can generally expect integration tests to take slightly longer than unit tests, and you’ll often find that there are fewer of these overall that unit tests.

As well as helping to establish that your code is doing the right thing, it also provides you some invaluable protection when you come to refactor.

With the adder example earlier, I could decide later on that I really only want it to handle integers, and I’ve modified my unit tests to suit:

def adder(a, b):
    ''' Adds two integers together '''
    if type(a) == int and type(b) == int:
        return a + b
        raise TypeError("Please only use integers")

Integration tests should help us catch any cases where I (or someone else working on the same code base) used this method for anything else other than integers.

Functional Tests

Here’s the final type of test that I’m going to write about. Functional tests are the final step back. They represent a complete workflow. You’re trying to test that overall the script does the right thing. I give it x, y, and z at the top, the whole thing runs and produces a as the answer.

For functional tests it’s perfectly acceptable to exercise external libraries and use external services, however caution should be encouraged to limit the impact external services can have on your ability to reliably provde code, and limit the impact testing can make on production environments. For example, in our example code, it’s OK to rely on DNS, but probably not OK to send a test email every for every functional tests that gets called. We should also think about whether we want to actually talk to an external endpoint or not. In our case, the answer is almost certainly no, because there are properties about it we won’t be able to reliably define, e.g. if we were using google’s endpoint, we’re not in control of when they’re going to update their SSL certificate!


In this blog post I’ve introduced you to three different categories of tests: Unit tests, Integration tests and Functional tests. In the next blog post we’ll try and write some actual tests for our code, and I’ll introduce you to how we can avoid making external service calls in the process of testing.

18 Jul 2015, 00:00

Hello Alia

On Wednesday early evening Kari and I welcomed our second daughter to the world, Alia.

How do you deal with two kids?! Well.. three I guess if you include our dog Oakey.

So far Naia is being a good “big sister” and helping to make sure everything is good with Alia. Oakey is sitting on guard (in a good way), making sure that no one bothers the baby unncesssarily, and every time she cries he comes and makes sure we’re dealing with it.


08 Jul 2015, 00:00

An introduction to testing, part 2: Establishing our example script

Before I get too deep into this, I should point out that our second child is due on Thursday, so we’re really in the “any day now” territory. Apologies in advance if there is a sudden pause in this blog series when $kid[1] makes her appearance!


This is the 2nd part of a short blog series on the value of tests to sysadmins and how to write them. You can read the first part here. To be completely open, I want to emphasise that I’m still learning to write testable code. As strongly as I feel about writing code with tests, I’ve still got over a decade of history of not writing tests behind me. Breaking those habits takes work, but like any bad habit, it’s worth the effort.

Writing testable code is something that takes a little getting used to, but I’ve found that as I’ve wrapped my head around what is and isn’t easy to test, it’s actually relatively straight forward to do. It’s really just a case of breaking bad habits, and being disciplined. It even, ultimately, seems to make writing programs easier. As with anything, the more practice you get, the easier it becomes.

The main key I’ve found so far is to create lots of small, single purpose methods. This helps you break down a programming task into easy to grasp concepts, and really encourages D.R.Y. (don’t repeat yourself) practices that you should be doing whether you write tests or not. I guess the hardest part for me is that this requires me to spend a few minutes thinking about what I’m trying to achieve before I leap into it.

The impression I’m getting so far is that it’s also easier if you’re writing object oriented code rather than procedural, but that’s not explicitly necessary. I’ve just found it simplifies some aspects of writing the tests.

I’ve been wracking my brain for a practical example to use here as I’ve been writing very specialised code recently. I think the most useful I can come up with is to create a script that connects to a secure end point, checks the certificate name matches the endpoint, and establishes whether the certificate is near to expiry. If the cerfiticate is due to expire it will send out an email. This could be a cron process, or with some slight modification it could be dropped in to a monitoring system.

I’m going to aim for this to be done using straight python 2.7, with just one external library, certifi, for its up-to-date set of root certs.

Through this I’m going to try and mostly follow my current development approach, so tests written after the code, then code fixed up based on the outcome from the tests. Those more experienced with testing may find I’m missing some obvious changes that would improve testability, hopefully they’ll become clear as I go along. Note: I should really be trying to put this in more of an object/class based approach, that will definitely improve testability, but I want to try and keep this as close to procedural as I can for now, given that’s how I perceive many sysadmins coming at programming (at least, most of those I’ve met have). I’m also not doing try/catch that I really should be doing, just to keep this clean. That may happen as we get to writing tests. Maybe at the end of this series I’ll produce a “how I think it should really be”.

People will likely tell you how great Test Driven Development is, but I’d encourage you not to give that a shot until you’re a lot more experienced at writing tests. TDD has it’s advantages and disasdvantages, but for now you should be focussing on just getting comfortable with tests and writing testable code.

Feel free to drop suggestions to me as we go along. You can email me at paulgraydon at hotmail dot com, or get me on twitter @twirrim.

Initial Plan

No plan survives execution, and code rarely so this might change as we go along. Here’s what I believe we’ll need:

  • A method to establish the connection
  • A method to verify that the certificate is one for the endpoint we’re talking to
  • A method to verify the expiry date isn’t in the next specified number of days
  • A method to send an email to a specified address
  • A method to get all the arguments passed in over the command line
  • A method to wrap everything all together.

SSL was drastically improved in python 2.7.9 to deal with security risks, but as most distributions are still shipping an older version I’m going to do this the old fashioned way.

Establish the connection

Python’s SSL library provides no way to connect to anything, we have to establish a connection using socket, and then wrap that socket in an ssl object.

import socket
import ssl
import certifi

def get_connection(address, port):
    ''' Establishes an SSL connection to the specified address and port '''
    # Find out where certifi's root certs are
    cacerts_location = certifi.where()
    # Establish the socket
    s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    # Wrap up the socket with ssl
    ssl_sock = ssl.wrap_socket(s,
    ssl_sock.connect((address, port))
    return ssl_sock

Verify the certificate is for the endpoint we’re talking to

An established ssl_sock provides a dict that describes the certificate: Here’s an example from a connection to Google:

>>> ssl_sock.getpeercert()
{'notAfter': 'Sep 16 00:00:00 2015 GMT', 'subjectAltName': (('DNS', 'www.google.com'),), 'subject': ((('countryName', u'US'),), (('stateOrProvinceName', u'California'),), (('localityName', u'Mountain View'),), (('organizationName', u'Google Inc'),), (('commonName', u'www.google.com'),))}

From there there are two things we’re interested in.

>>> ssl_sock.getpeercert()['subject']


>>> ssl_sock.getpeercert()['subjectAltName']
(('DNS', 'www.google.com'),)

I’m not sure how much I can trust the returned order, so I’m going to assume I can’t, and will loop through to be safe.

I’m about 90% confident that we should really be passing along just the dictionary, as I’m pretty sure it will make testing easier, but for now I’m going to pass the actual connection object, because that’s still where my first instinct took me.

def verify_hostname(connection, address):
    Pass in the connection, along with address to verify the certificate
    matches the specified address.

    for entry in connection.getpeercert()['subject']:
        if entry[0][0] == "commonName":
            if entry[0][1] == address:
                # It's valid
                return True
    # If it's not against 'subject', it could be a 'subjectAltName'
    for entry in connection.getpeercert()['subjectAltName']:
        if entry[1] == address:
            # It's valid!
            return True

    # If we don't match either 'subject' or 'subjectAltName' it's not valid
    return False

Verify the expiry date isn’t within a certain number of days

The SSL library has a utility method to convert the text string date time from the cert into epoch

import datetime

def expiring_certificate(connection, expiry_days):
    Pass in the connection and number of days. Verify that the expiry date isn't
    within the specified number of days

    # Convert the presented certificate's expiry date into a datetime object.
    # This could be done in one line, but for readability, we'll do this over three

    expiry_date = connection.getpeercert()['notAfter']
    expiry_epcoh = ssl.cert_time_to_seconds(expiry_date)
    expires = datetime.datetime.fromtimestamp(expiry_epoch)

    # Create a datetime object of the specified date
    now = datetime.datetime.now()
    specified_date = now + datetime.timedelta(days=expiry_days)

    # this evalutes to True if the certificate expires before the specified
    # expiry date.

    return expires < specified_date

Send an email

I’m going to assume that localhost is configured to receive and send email.

import smtplib

def send_email(expiry_date, address, email_to, email_from):
    server = "localhost"
    message = """\
From: %s
To: %s
Subject: SSL certificate for %s due to expire %s

The SSL certificate for %s is due to expire on %s.  Please update and replace.
""" % (email_from, email_to, address, expiry_date, address, expiry_date) # format would probably be neater

    smtp_server = smtplib.SMTP(server)
    smtp_server.sendmail(email_from, [email_to], message)

Get the arguments

I think this should cover our needs:

import argparse

def get_arguments():
    ''' parse the command line arguments '''
    parser = argparse.ArgumentParser(description=__doc__) 
    parser.add_argument("--address", dest='address', type=str, help='The endpoint to check')
    parser.add_argument("--port", dest='port', type=int, help='The port on the endpoint to check')
    parser.add_argument("--to", dest='to', type=str, help='who to email in case of alert')
    parser.add_argument("--from", dest='from', type=str, help='who should email come from')
    parser.add_argument("--expiry", dest='expiry', type=int, help='Number of days until expiry') 

    return parser.parse_args()

Tie it all together

I realised as I was writing this that I really should have been putting logging entries in throughout. Logging is still a bit of an afterthought for me. I’ll add it in as we go through the code to add tests. I’m going set this at debug level logging for now, but when it comes to production use, it would probably be a better idea to have a verbose flag to control what level logging happens at.

import sys
import logging

def main():
    ''' Tie it all together'''
    args = get_arguments()
    connection  = get_connection(args.address, args.port)
    if verify_hostname(connection, args.address):
        logging.debug("Certificate for %s is valid" % args.address)
        if expiring_certificate(connection, args.expiry):
            logging.debug("Certificate for %s is going to expire" % args.address)
            logging.debug("Certificate is valid and isn't expiring")
        # If you get here, oops.
        logging.critical("Certificate did not match host name")

if __name__ == __main__:

Final result, and example

So here’s the final result, with imports arrange appropriately: https://gist.github.com/twirrim/66bfd2b0705f612f9fa8

And here’s an example of it working. We can be fairly safe to bet google’s SSL cert will expire in the next year:

$ ./test-ssl.py --address www.google.com --port 443 --to ######## --from ###### --expiry 365
DEBUG:root:Certificate for www.google.com is valid
DEBUG:root:Certificate for www.google.com is going to expire
DEBUG:root:Certificate for www.google.com is valid
DEBUG:root:Certificate is valid and isn't expiring

So we know the code is working as expected (I received emails as expected, too). In the next blog post I’ll move on to what unit tests are, and how to write them.

05 Jul 2015, 14:42

An introduction to testing, and its value to sysadmins. Part 1


If you’d told me a couple years ago that I was a developer, I’d have vehemently denied it. The truth is, though, that Sysadmins are developers. Just like we’re DBAs, network engineers, security engineers, and so on. We put on whatever hat is necessary to get the job done, because that’s what we’re there for. Every time we write a line of code, we’re a developer, right there and then in that moment because it’s the most appropriate thing to be doing right then.

Programming is essential to what we do, and in many ways it always has been. An ability to program is one of the many things that marks a good sysadmin. Be it Windows, Linux, Solaris, OSX or whatever, we write tools; all sorts of glue that is necessary to turn a collection of servers and software into a cohesive, consistent and hopefully well behaving environment.

We write programs that interface with infrastructure, from servers to firewalls, network equipment and more. We write programs that produce reports, that clean up files and file systems automatically, that interact with APIs all over the place, setting up and tearing down virtual servers, deploying resources, configuring applications, user accounts and so on. We write programs that are either stand alone, or as part of other tools to configure and build servers so that we end up with a consistent environment. We write tools to gather, analyse and report on metrics. Tools that hopefully wake us up when things have gone wrong. Tools that try to automatically handle various failure scenarios so that we don’t have to be woken up.

We’ll do it in a variety of languages, from VBScript and powershell, to python, perl, Go, bash and javascript. For every language it seems there are sysadmins using it, creating things for their environment, because it provides all sorts of value. Automation brings consistency, and acts as a force multiplier. A script that takes 4 hours to write but saves 15 minutes a day, gains consistency and is a net time positive in under a month.

Not only are we developers, we’re developers with root access.

Historically, that’s an expression that has made Sysadmins gibber to themselves in the corner of a room. The DevOps movement has finally seen us move away from this attitude, but the reasons for the horror shouldn’t be forgotten when it comes to introspection. What always used to be scary about a developer with root access is they very rarely had any understanding of the operations end of business, how to run a server and platform etc, and as such they haven’t shown the best judgment. A developer I worked with at one job was known for dropping random tables from one of our major database clusters, because “We’re not using it any more”. The outages he caused presented evidence to the contrary. We soon learned to keep a lagged slave around as the quickest way to restore the content.

These scripts we write frequently run as root. The cost of them not working correctly covers the whole span of “No one noticed” to “Career Limiting Event”. A mistake in a report generating script might give you some weird output. A bug in your provisioning script can spin up hundreds of thousands of virtual servers, potentially costing you a lot of money if it’s with a cloud provider.

I’m sure many of you have heard of at least one horror story involving “rm -rf” in a script.

In my experience, we sysadmins have a habit of being what might politely be described as ‘haphazard’ about it, and at worst borders on the psychotic or even masochistic. We just sort of throw stuff together, frequently focussing on a procedural approach, particularly those of us coming from a perl background. We’ve got something that needs doing, and we plough straight into it and get it done.

Pretending that I’m not a developer or programmer has been a nice little lie I’ve told myself to excuse bad or lazy programming. It meant I could rationalise it away as “Well, I’m not a developer so…”, and “Well it’s always worked for me so far…”

The truth is, I am a developer, and it’s long past time to accept that. It’s just another one of the hats I wear.

So we’re developers, what does that mean? It means we need to start learning and keep learning from the best practices of developers.

Two best practices that have come out over the past 15 years or so that I want to focus on over a few blog posts are tests and their natural counterpart, test driven development.

What are tests? Tests are little more than programs that test another programs correctness. By defining what the program is supposed to do, we can establish if that’s what it is actually doing. Everyone has their own definition for tests and types of tests, but it seems like they’re generally broken down into 3 categories: Unit, Integration, Functional.

I’m going to go into these over the next few blog posts but in brief:

  • Unit Tests: : Does this method do what we expect?
  • Integration Tests: Do these methods work together to do what we exepect?
  • Functional Tests: Does the whole thing do exactly what we expect?

11 May 2014, 00:00

Starting with Go, IRC bot style

The Go language from Google has been picking up lots of interest over the past few years. Speaking as someone with a background almost entirely in dynamic languages, Go seems to strike a really nice balance between static dynamic languages and statically compiled ones. It comes without the hassles of memory management, compiles quickly (and easily cross-platform), and doesn’t seem to have a complicated syntax. It’s a little odd coming to Go from the OO approach of Python, but I’m enjoying it so far. After compiling you end up with a single binary file you can execute. No large collection of libraries, dlls or whatever to fuss about installing, just a single binary. It does seem like the binary ends up a bit larger than I’d expect, e.g. the standard fmt library that you use for printing results in a standard “Hello World!” program that is some 2Mb in size. It also comes without the start-up overhead that you typically see with the JVM. That makes it very easy to create fast performing tools that you can deploy everywhere, which is ideal for Sysadmins.

I’ve done a few bits of experimenting with it over the last year or so, but nothing serious. I find it hard to get a language to really sink in without a specific project, and recently I’ve been working with a python based IRC bot and I started thinking about how complicated it would be to build one from scratch. I figure a good way to learn is also to have this as open source and blog about things I’ve learned as I go along.

I’ve been working on this in little over the last week, grabbing the odd 10-20 minutes here and there to tweak and improve it. Making my life a lot easier has been the go-ircevent library which handles the ‘heavy lifting’ of connecting to IRC and providing hooks for the bot, leaving me to focus on what I want the bot to actually do, which I’ll admit is the aspect I’m most interested in.

Speaking of libraries, that brings me to an interesting thing about Go, which is package management. When you start a new project, you create a directory, set your GOPATH environment variable to point at it, and then you use go to fetch the libraries by using the go get foo.bar.baz command. That puts everything for the project into a src subfolder, ready to be used. When you call go build it uses your GOPATH to find the libraries, and away it goes. The downside is that at the moment it just goes and grabs the main package, it doesn’t really provide a mechanism for grabbing a specific version, but that doesn’t stop you from grabbing it manually. I do hope that will be something they try to integrate in the command at some stage if possible, even if that means they have to dictate library repository structure some how (e.g. branch for each release, named a particular way)

So what have I learned so far? Well… documentation is very technical, but also generally very accurate. I guess I’m a little too used to clearer examples with languages, and it’s taking me a little while to understand what they’re saying from a practical perspective.

For example, the first thing I implemented was the ability for the bot go to a url posted in channel and return the title to the channel. That was relatively easy to do, and I found a great tokeniser function that allowed me to retrieve the contents of a specific HTML tag. With that done I needed to add some protection in. As it was by default, someone could cause problems by posting links to ISOs or other large media, potentially causing the bot to eat up all the bandwidth of the host machine / internet connection. The standard http library doesn’t contain a specific ability to do this. Looking at the documentation for the net/http library that I’m using for this, it uses the standard ioutil library to do a ReadAll, but that library doesn’t appear to provide a way to do restrictions either. When I checked against the main io library I discovered the LimitReader function, which required a ‘Reader’ object, but I had no idea how to create one of those. The http.Get() function returns a Response object. That left me baffled for a while. Coming back to it with a fresh mind I realised the answer was right in the Response object definition, response.Body is an io.ReadCloser object. Therefore to implement the restrictions all I needed to do was wrap response.Body in a LimitReader object, like this:

contents, err := ioutil.ReadAll(io.LimitReader(resp.Body, 1<<18))

I should probably avoid Using ‘magic’ numbers like 1<<18, or at least clearly define it. << is a bit-shift operator, essentially moving the binary number for 1 over 18 places to the right. That means 1<<18 == 262144, so we’re restricting it to only the first 256Kb of the page, which is probably still too big).

I’ve got a number of things still in my rough road map at the moment:

  • Implement database backend, in as generic a way as possible to allow users to specify their own storage, such as sqlite, mysql, pgsql etc..
  • Support multiple IRC channels simultaneously, each in it’s own go thread.
  • Support for plug-ins.. well as far as that goes anyway. I’m not sure how possible it is, but the initial thought is I’d like to have a directory people can put their plugin go files in and have it build them in to the binary automatically.
  • Better support for ignoring users. Regexes are all well and good, but we probably want to make it easier for people.
  • Per user rate limiting, first at channel level, and then adding in global level.

The code in its current state can be found in my github repository: https://github.com/twirrim/go-bot . It’s published under the popular Apache 2.0 licence, so people are free to take and modify it as they need, provided attribution is kept in.

27 Feb 2014, 00:00

Chaining a Ferrari to a 10 tonne block

A tweet by Kelly Sommers earlier today (well worth a follow if you’re a techie type) got me reminiscing.

At a previous job my primary role was to be a consultant sysadmin for customers. They could pay for my time to help resolve their problems. It proved to be an interesting challenge at times, though I seemed to spend an awfully large amount of time dealing with compromised servers.

I had one customer in particular who would get in contact with me every month without fail, asking for help making their database server run faster. After a quick look around I made note of a few of the standard adjustments that could be done to improve performance. This was back in the MySQL 5.0/5.1 era, before a number of the improved defaults started to appear. After applying the easy changes, yes there was a performance improvement, but things were still pretty bad. Checking in to the slow query log showed a query I really didn’t want to see rearing its ugly head.

The database in question was for a business directory style service. A large amount of the data and business logic involved a single table. Each entry would relate to a business, and would contain almost all of the details about the company. Each record had a few hundred fields to it, in fact. The table was up to a couple of million records or more and was growing at a not inconsiderable pace. There were a few other tables in there that e.g. linked a category code to a category name, but not much beyond that. In fact the main table looked a little like this (just imagine lots more fields to the right):

| CompanyID |    CompanyName    |    Category    |         URL         |
|         1 | Example Company 1 | abcd;defg;hijk | http://example1.com |
|         2 | Example Company 2 | defg;lmno      | http://example2.com |
|         3 | Example Company 3 | pqrs;defg      | http://example3.com |

If you’re particularly familiar with databases I’d guess that right now you might have a sinking feeling in your stomach, and with good reason: That category field.

The Category field was a VARCHAR(255) field (up to 255 ‘plain text’ characters) that contained a semi-colon separated list of categories that a company could be in. If a company was added to a category the 4 letter character code would be tacked on to the end of the entry. 1,000,000 * 255 =~ 240MB of data just storing the categories in plain text. 4 letters + semi-colon = 5 characters per category, which means a company could be in up to 51 separate categories. Almost every company would have at least 10 categories it fit into, and I saw many reaching the 30+ category size.

The home page of the site loaded nice and quickly. If a user selected a category to get a listing, the site would send a query to the back end of the form:

SELECT CompanyName,Description,URL FROM CompanyDetails WHERE Category like "%lmno%"

This is a terrible way to design a schema. LIKE queries are computationally expensive. It has to compare against every possible character in the field to see if it gets a match, and this would go against a terrific number of rows.

Under low load levels the database server was fine, it could handle it in reasonable time. But as soon as the load increased, these queries would start stacking up on each other until they were taking 40 seconds+ to complete. After my initial tweaks I suggested to the customer that maybe we could change the schema a little. “No!” came the firm response. I politely re-explained the situation laid out the possible options and left it at that.

A month later, they were back in contact again, and again asking why their database server was slow. Thinking it was down to language barrier, I carefully re-wrote my reply from the month before. “No! We can’t change the code or schema” came the response. That wiped out the possibility of master/slave servers too. In the end I agreed to help the customer migrate their database to the highest range server we had available, a nice and powerful 8 core Xeon system with lots of RAM, pointing out the customer rather bluntly that it was like “Chaining a Ferrari to a 10 tonne block”. Yes, this actually did make things faster, but there was a harsh limit caused by the diabolical schema. They could handle maybe another 10+ simultaneous users, and they were happy and so I moved on to other customer requests.

Another month passed, and oh look another email from my favourite customer. This time they were wanting to know why things were still slow, and how Google managed to do things so fast….

After several months I think I’d tried just about every way I could think of to try and persuade the customer that they needed to change their schema, and suggested just about every thing I could think of to make the bad schema return responses in a reasonable time. I came up with migration plans, all sorts, but they point blank refused to change their code. I have no idea whether they’re still operational to this day, but if they are I really hope they’ve changed their database schema.

16 Feb 2014, 00:00

Is Comcast really throttling Netflix traffic?

Is Comcast really throttling Netflix?

Short answer: Given the presented evidence so far, we don’t really know. It’s complicated. About the only ones who could really tell us is Comcast, and of course there is little incentive for them to tell you if they are. What we do know is that they’ve got some kind of problem somewhere as people are able to get better performance by using proxy servers. Either Comcast is throttling, not got sufficient bandwidth somewhere, or they’re deliberately not routing traffic efficiently. Or all three.

Long answer: There has been interesting bits and pieces thrown around recently about whether Comcast is or isn’t throttling Netflix and/or access to Amazon AWS in the wake of the FCC losing its method of enforcing Network Neutrality. Some people have been able to show that using a proxy server results in faster and better Netflix performance. It’s worth pointing out that based on Netflix’s visibility into ISPs, connectivity between Comcast and Netflix has been getting worse pretty steadily for the last year and a half.


I’m going to try and provide a bit of an overview, hopefully without going into too much unnecessary detail, to try to explain why it is actually really quite complicated, and what kinds of evidence we would need to have to be able to confirm whether or not throttling is happening. This is coming in part from my background of working in an ISP both in the Network Operations Centre, and later as a sysadmin (as well as general experiences as a consumer and a sysadmin). Both roles involved a fair bit of work with the networks team and proved to be a fascinating learning experience.

The easiest way to think about internet connectivity is in terms of a tree. Your ISP will have a lot of bandwidth available at the core represented by the thick trunk, with fairly thick branches feeding off into slightly smaller branches, and even smaller branches off that and so on.

Your home connection is on what is referred to as the “last mile”, the final stretch between your home and the nearest distribution point (and would be the smallest twig). Your home connection is the part of the connection that is most likely to restrict you, as distance and line quality vary and that has a huge impact on maximum theoretical bandwidth. Depending on the scale of the network and how the ISP has chosen to run their service, you might have a few more distribution points before you get to the ISPs central bandwidth (the central trunk of a tree). At each stage your ISP will be aggregating network traffic together into single links, and at each stage they might not have sufficient bandwidth to cope with all the customers using all their possible bandwidth at the same time. Achieving that would generally be prohibitively expensive. Instead ISPs try to ensure they have just enough (plus suitable headroom) to cope with typical usage, and to provide for enough growth. People’s internet usage is actually reasonably predictable and usually grows at a fairly predictable rate (much like electricity usage.) For example an ISP might sell a 100Mbps service, with 20 houses connected to a 1000Mbps connection. For the most part people aren’t going to actually use 100Mbps, even during peak, so that 1000Mbps connection should be fine, and they’ll be able to predict with reasonable confidence when it’s no longer going to be sufficient. If all the customers did start downloading large content simultaneously, things would get more than a little congested and they’d see packet loss and slow downloads.

This next stage is a little ISP dependent, and I don’t know for sure how Comcast’s has their network arranged, but we can make some fairly safe assumptions. It’s safe to assume that they’ll have regional distribution points in their networks, which will have connectivity off to the internet’s backbone with various transit providers and peering providers as appropriate. There will also likely be connections off to various regional internet exchange points (e.g. Seattle’s SIX: http://en.wikipedia.org/wiki/Seattle_Internet_Exchange). These typically offer cheap and low latency connections to locally hosted resources.

ISPs will have multiple networking providers that provide their connectivity to the internet backbone, this will be likely made up of a mix of transit (go-between connectivity companies) and peering providers (core internet bandwidth). The use of multiple providers allows for resiliency in a network. Almost everything you can access on the internet will be accessible via a number of these providers. It also allows the ISP to manage costs. Internet connectivity can get to be quite expensive at an ISP level. Precise pricing on the contracts with these providers are generally kept secret. What I do know from my past experiences is that the price changes somewhat depending on how much data is provided and consumed, and just as importantly, what the ratio is between those.
Transit providers want to be marketable to companies that might want their services. If you’re uploading content through them, it’s reasonable for them to assume you’re providing value to their other customers, and thus they’ll want to keep you using them. The more the ratio is skewed towards consumption instead of provision, the more expensive the bandwidth becomes. As you can probably imagine, this results in ISPs trying to do a delicate balancing act, trying to ensure they get the most favourable ratios with all of their connectivity providers. They’ll do this by manipulating route costs in routing tables, effectively changing which provider is favoured by network packets by making one provider seem cheaper (in networking terms) than another. I know of a few ISPs in the UK that continued to run their own usenet/newsgroup servers long after they ceased providing direct value to customers, because it was a good source of upload traffic that could be shifted around to other transit connections without affecting customers (particularly if they kept certain popular binaries groups on it).

This kind of juggling routing around between providers happens all the time with ISPs. With the way network routing and TCP works, you shouldn’t even notice it. ISPs are looking to get a perfect balance of cost, speed and latency. Tier 1 networks (peering companies that provide the core internet connectivity) are likely to be closest to the ultimate destination, but they’re also expensive. Tier 2 networks (transit providers) are likely a few extra hops away, so likely incur extra latency, but are cheaper. Of course your idea of a perfect balance and theirs might be different from theirs. They might be asking “What’s the worst I can get away with?”

Before we can get back to the core question about Comcast, it’s worth taking a moment to look at Netflix. How does Netflix provide content to its customers? Netflix is famous as a user of Amazon Web Services, almost their entire infrastructure is based on Amazon’s cloud services. However they also use what are called CDNs, Content Delivery Networks. They don’t just use one company either, they have multiple providers. When Netflix has transcoded video content for delivery, they upload the videos to CDNs for actual delivery to customers (at one point Netflix had an exclusive deal with Level 3, I’m not sure if that still applies). These CDN companies operate in data-centers right around the world providing tiers of cached content, automatically trying to have content served up as close to customers home network connections as possible.

So where does that bring us with Comcast and Netflix? People are able to show that if they use a proxy their connection to Netflix improves. That tells us one thing at least, that the initial hops in their connectivity to the nearest regional distribution point isn’t congested. The cable company who provided my internet access when I was living in London routinely oversold the local exchange, resulting in terrible service for us until we complained, whilst other friends were fine, even as much as a few streets away. So in this case with Comcast and Netflix we know that connectivity to the end user isn’t congested as clearly they can receive data at a high speed. So we know the congestion is probably coming from the core. We’re reaching the area where we just don’t have enough information to truly answer the question. If people are able to set up a proxy that sends packets through exactly the same exit point from Comcast’s network as those packets going to the relevant CDN for the content we’re comparing, and we see that they still get better throughput, well the we’re a lot closer to being confident that Netflix is being throttled by Comcast. Unfortunately we don’t. So we can’t say for sure if Comcast is explicitly throttling Netflix traffic or not. We just know that either they are, or something somewhere is wrong in their infrastructure, like a congested transit link, and that Comcast isn’t doing anything to improve things. As you can see from the graph I posted earlier, it’s clear things have been degrading for months.

So why the talk about network neutrality? Part of the problem is, Comcast is also a content provider, and that business revenue is extremely lucrative. With the internet increasingly being the main source for content, via Netflix, iTunes etc. their media business has the potential to suffer. They make terrific amounts of money from advertising alongside their programs. If you’re steaming content from other sources, that lucrative revenue vanishes. Comcast actually has lots of incentive against providing you with good quality service, and only a token excuse for competition to give them any real incentive to upgrade your connection speeds and network. If Network Neutrality is not enforced, Comcast could deliberately choose to cripple access to any competitor they liked, be it ISP, rival content provider or otherwise. With most of the country stuck under what is effectively a government blessed monopoly, it’s not like people could take their business elsewhere.

We shall see what we shall see, I guess.

Hopefully I’ve managed to explain how there are multiple points that congestion could be occurring, and explained why the evidence we’ve seen so far isn’t really a smoking gun about whether or not Comcast is throttling Netflix traffic. If anything is unclear, and / or I’ve done a bad job, feel free to leave a comment.

09 Jul 2013, 00:00

Aloha Hawaii & HIC; Aloha Seattle & Amazon

The last month has seen a fair bit of disruption in our lives. A few months ago a friend at Amazon put my resume forward in Amazon’s employee referral program. After a fairly straightforward but complete interview process with them I was offered a job in the Glacier team.

It’s been our plan for a while to leave Hawaii for Seattle, but there’s a whole world of difference between stuff you’re talking about doing and the actual doing. The addition of an 11-month old to the mix made the whole process even slower than I expected. She’s old enough to get underfoot, crawl everywhere and pull things out of boxes. She’s not old enough to be put in a room with some toys and know she’ll happily entertain herself! Thankfully she waited until our final few days in Hawaii to start putting much effort into walking. Now she does it lots.

What was also hard was that I was taking Kari away from the place she was born and grew up in, and taking Naia away from her Grandparents. However it’s a decision we’d talked over a lot. Hawaii is one of the most expensive places to live in the country and to be blunt the public school system is poor. Combined with a general lack of career options for me (other than “work from home”), the route became clear. Seattle / Washington has very good schools, cheaper housing than Hawaii, some of Kari’s relatives are here, and it’s home to a large number of tech companies.

So we upped sticks just over a week ago, and settled in to a house we’re renting in Redmond, with a hope to buy a place sometime next year. Our stuff from Hawaii is apparently somewhere on a ship on the pacific ocean, headed in our vague direction!

I started at Amazon last Monday and am enjoying it so far. Lots of stuff to wrap my head around, brains to pick, and this team has an amazing obsession with metrics.

Obviously the work I’m doing is largely proprietary and all covered under an NDA. I suspect my blogs here will be fewer and farther between, rather than risk breaking NDA accidentally. I am hoping, however, to have a bit more time for some open-source stuff which I may comment about.

27 Apr 2013, 00:00

Re-designed, re-engineered and re-launched, the new state portal

Today saw the culmination of a lot of hard work from our content management team, design team and the sysadmins.

The totally overhauled state portal, http://portal.ehawaii.gov, went live today introducing a completely new toolchain to our environment and some innovative concepts for state portals.

The new portal switches over to a HTML5 based responsive, mobile first design. It’s touch enabled, and uses ARIA Roles to create as user friendly and accessible a website as possible. To break the common mold, it’s also a static content site. That enables us to serve it fast with little work done by the back end, and allow more effective caching at the boundaries. Also breaking the modern trend, it’s not based on the Twitter Bootstrap. Instead we opted for the Foundation framework, by ZURB.

To allow us the flexibility and an element of dynamism, the entire site is generated using jekyll, a ruby based markdown-to-HTML conversion tool. That’s the same software, in fact, that builds this blog. It gives us the advantages of static content, combined with the ability to quickly and easily update the site without having to deal with the hassles of editing HTML direct; yet it still gives us a lot of control over the page design and layout.

Every hour our build system automatically builds a new copy of the site, fetching in the dynamic content from various JSON feeds, creates the static content and deploys any changes to our servers. If ever we need to make a change outside of that, a commit hook is built into the git repository to trigger off a build & deploy too.

On the design end we owe a huge debt of gratitude to Danny Chapman, one of the inaugural Presidential Innovation Fellows and all round design genius, who came out to Hawaii for a month to help kick-start the design and implementation. Danny, like me, was strongly in favour of a transition to statically generated content and has copious experience producing amazing designs using Jekyll.

05 Apr 2013, 00:00

Fixing ASDM under Linux

This has been bugging me for most of a year now. As much as I love doing things from the command line, configuring a PIX or ASA Firewall is not one of them. Especially when you get beyond a dozen or so lines in the the access lists.

Unfortunately about a year ago I found the ASDM just stopped working from Linux and I couldn’t get it to work again, and neither could my colleague here. He runs Fedora, whereas I run Ubuntu, so it wasn’t a distribution specific bug.

Googling around shows lots of people having it and finding that downgrading java or installing specific IcedTea pacakges fixed it for them. No such luck here. About a month back I started trying all sorts of random things to fix it and amazingly got it to work once but I’d made so many tweaks along the line it was difficult to figure out what had done it, and I couldn’t repeat it anyway.

I needed to look at the firewalls again today so I gave it another shot, and it turns out to be relatively simple to get it to work. What I can’t do is explain exactly why this works. It just does. At some point when I have a bit more spare time I’ll try and follow the strace outputs to see.


COMMAND=$(javaws -verbose -Xnosplash asdm.jnlp | grep -v "Java(TM) Web Start");


Unfortunately this only works under Java 6 so far. I’ll keep plugging away at Java 7.

The “javaws -verbose” chucks out the java command line, and references a temp file that seems to be a copy of the asdm.jnlp contents. when you run the java it deletes that temp file as it runs. A wild stab-in-the-dark is the temp file not being created fast enough, but that could be total nonsense.