As some of you may know, I have been working hard on migrating the website from ancient VBScript ASP to new fancypants webservice arcitecture.
You can read my earlier posts on the subject here and here.
In the last update which was pushed out to the live server the morning of Monday the 21st I took a pretty big step towards the goal by completing a transition of the database layer from Microsoft SQL server to PostgreSQL.
The reason for this change is that on one hand Postgres plays better with the tech stack that I am moving to and on the other hand that MS SQL costs quite a sum in monthly licence costs whereas the postgres software is free.
I've been migrating database code and working out the kinks for a few months now off and on and building a migration plan. Some of the code and database objects, such as indices were a bit tricky since they originally came from the original MS Access database that CC first ran on (oh yes, computer nerds, CC started with an MS Access backend....). There was plenty of stuff that wouldn't play nice with postgres in the mountain of legacy in the database, so things had to be done by hand and copying and pasting.
This brings me to the topic of this post, a little fun tidbit I wanted to tell you about.
I originally anticipated that the postgres database would be as performant as the MS SQL one, if not better. The day of the migration was rather hectic and I managed to get the site open around noon but kept fixing small issues and monitoring errors throughout the day.
The site seemed to run pretty well, but I did notice that some pages seemed to load a bit slower than before.
The next day I started to do some performance profiling to see how we were doing and I found out to my astonishment that the database machine was running very hot; it was using a lot more cpu than the old ms sql database, despite the two being on identical hardware and running the same(ish) code.
Here you can see a CPU graph of the machine. The new site was opened up around 12:00 on the 21st and the cpu was hovering around 20% and spiking above 80%, where the old db had been idling most of the time.
This was a huge disappointment. I had expected very different behavior.
When I ran some stats procs on the database I noticed something peculiar. All of the requests were doing table scans and making a huge number of reads. Looking into it a bit further I found the culprit.
There was not one index in any of the tables beside the primary key. Not a one.
For those that don't work with databases, indices are very important and behave exactly like indices in a book. They allow you to query a large table quickly by a certain criteria. For example, on the critiques table we have an index on sender_id which is your user ID. If you want to see your crits the system automatically gets the data directly from the sender_id index and returns the rows. If no index is found, the entire table is scanned to collect the data.
This is what was happening now, with every query on every table.
I started to look at my migration scripts and found that I must have simply not run the one containing the index creation. Woops. I ran the script at noon on the 22nd and you can see the effect on the graph after that time.
All is well that ends well. I hope the site will be as snappy as it was before and please report any bugs that you find. If there is a problem (that didn't exist before the migration) chances are I don't know about it.
- Nonni