I am not a smart man...

CC was recently migrated to a new database engine. It went pretty well all things considered.

Jon Bjarnason
Share:

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

9 Comments

M. J. Ascot

Thanks for all the hard work. It's not easy, managing a migration by yourself and in a limited period of time. I'm glad the biggest problem was minor (not minor in performance impact, but minor in work required to fix). From my perspective, it's humming along nicely.

Jan-02 at 11:04   3

Vijay Kumar Kerji

Congratulations on finding the solution to the problem. I am happy to say that the site is working faster than the previous version and your decision to migrate the database is well thought!

Jan-04 at 05:21

Batimamsel

Wow. I actually understood some of that. Thanks so much for being this transparent. I’m on some creative social networks where sometimes all we get is a single email notification of server migration between the hours of Monday and Thursday :). This is nice!

Jan-05 at 02:08   1


Post was deleted by moderators

Jan-09 at 19:23

Lexlily

Wow! Great story of uh oh to amazing! Yes, the graph shows it all. Thank you for your fab work on a fab site! Your reward can be a case of spiedie sauce although I don't know if I in New York State can ship it to Iceland. Let me know. On know, do you know the writer Jonas Knutsson? Back to spiedie sauce. If we can't send it, please read the following:

Upstate New York American BBQ:

Our special, small-town Binghamton BBQ is spiedies, steamed clams, corn-on-the-cob, city chicken, beer, and perhaps a swig of moonshine. And potato salad! Spiedies are a local specialty, spiedie recipes are available online. Recipes for city chicken, a Northeastern Polish favorite are also available and that's a winner, too. And you can order Salamida Spiedie Sauce or Lupo’s Spiedie Sauce on the net. This gourmet prefers Salamida Sauce, but Lupo’s Sauce also is special and great for this one when the need to break up routine occurs. And spiedies are in Wikipedia.

Spiedies–Traditional Style!!!!!

Cube lamb into 1-inch cubes. Cover lamb with marinade ingredients (spiedie sauce) and refrigerate on top shelf for one day. Use marinade as needed until it covers top of meat. Stir thrice daily. Pull out and put on counter when you get up in the morning for an additional burst of marinade charm. Grill on skewers and serve spiedies on Italian bread. Some add a touch of marinade to the sandwich for another final burst. Some go shish-kabob with it. Spiedies with fresh pineapple on the skewer is sweet. Pork, chicken, venison, and sharkmeat also make great spiedies! Northeasterners prefer them some Genessee brand beer with their BBQ. And okay, it’s not really BBQ, it’s spiedies!

Ingredients

o 3 lbs lamb cubed bite size
o Marinade ingredients:
o 1 cup olive oil
o 1/2 cup lemon juice
o 1/2 cup dry red wine
o 5 cloves garlic, minced
o 2 tsp celery salt
o 2 T fresh basil, chopped
o 2 T fresh mint, chopped
o 2 T fresh oregano, chopped
o 2 T fresh rosemary, chopped
o 2 T fresh parsley, chopped
o 1/4 cup vinegar
o 1 bay leaf

Preparation

Combine marinade ingredients. Add meat and marinate 24-72 hours, turning occasionally Skewer and grill lamb, basting with marinade

Jan-11 at 21:50

Jon Bjarnason

Mm, that sounds delicious. It's very expensive to ship to Iceland but you can send me a private message if you really want to. :) I don't know Jónas personally I'm afraid but we have some wonderful authors here.

Jan-12 at 08:28

Ruth

Thank you. I understood none of that because I have zero tech ability or knowledge, but I am so grateful there are people like you who know how to do these things. You are a treasure. Thanks!

Jan-14 at 01:42

Ovid

Thanks for all of your hard work on this.

It sounds like a common issue I've seen with other companies: ad-hoc database change management. You might want to look at sqitch for something that would guarantee you don't miss issues like this. It's both free and open source.

If you are interested, but having trouble, you can always send me an email and I'll help.

Jan-26 at 16:48

Zebracat

Just wanted to say thanks for all the hard work. The site is impressive.

Feb-07 at 01:42
Click here to reply
Member submitted content is © individual members.
Other material is ©2003-2020 critiquecircle.com