sipsorcery's blog

Big step towards SimpleDB

November 8, 2009 by sipsorcery

On Saturday (two days ago) I decided to upgrade the sipsorcery service to the latest version of my code. Prior to that apart from a few minor updates around dialplan processing the sipsorcery software hadn’t been updated for nearly two months. The impetus for Saturday’s update was to take another step closer to getting the service ready to migrate from MySQL to Amazon’s SimpleDB.

There were two major parts to the upgrade:

1. The data access layer code has been rewritten to add SimpleDB as a persistence option in addition to an SQL RDBMS and XML. As part of that rewrite the DbLinq library was removed and a custom very simplified Linq-to-SQL library was written. The main reason to replace DbLinq was performance. I’d already had to revert to a mechanism of using raw SQL for certain high volume queries in the live sipsorcery site and given that the sipsorcery service only requires extermely simple SQL queries to be used I decided to see if I could come up with a smaller, simpler and hopefully faster Linq-to-SQL implementation.

2. The timestamp fields in the MySQL database needed to be converted to varchar so that when the time comes the data can be migrated to SimpleDB. With SimpleDB select queries are all string based and as sipsorcery relies heavily on timestamp fields for a lot of its operations the format they are stored in had to be changed.

I was a little bit worriead about the update as anytime database schemas need to change on a running system it can be a bit hairy and replacing the data access layer software in-situ is also not for the faint hearted. However everything went suprising well. There were no complications and the only noitceable effect on the system was that calls were not processed for about a minute.

Subsequent to the upgrade a few minor bugs cropped up. One that appeared to cause a few ATAs issues was that the datetime format in the SIP header fields was accidentally changed to a round trip format, which is what the database now uses and looks like 2009-06-15T13:45:30.0900000, instead of the format mandated by the SIP standard which looks like Sun, 8 Nov 2009 12:12:21 GMT. Apparently that can cause some ATAs to reject responses and have other weird and inexplicable consequences.

A good side effect to come from the upgrade was that the new simplified Linq-to-SQL implementation has reduced CPU consumption by nearly half over what the system was getting using DbLinq. That’s good news because the sipsorcery server was starting to creep back up over 80% average utilisation as more people have started using it. Now it’s back down to an average utilisation in the low 40’s.

SIP Sorcery CPU utilisation with new Linq-to-SQL implementation

So a big step has been taken towards using SimpleDB as the data store for sipsorcery which will result in the ability to run two redundant sipsorcery servers sharing the same data. The next step is to get the SSH and HTTP duplex monitoring sorted out, add in encryption for sensitive databse fields and then do a bit more testing to make sure the extra latency with SimpleDB requests is being handled appropriately.