kentcowgill.org/blog

Wow.

Never stop looking for ways to improve existing code.

I had noticed that after I added tagging capability to my photo gallery that:

1. Page loads seemed sluggish.
2. Tagging 300 or so photos produced a large list of tags.
3. Drawing the tags seemed the slowest, as I could watch them appear in the tag cloud.

I actually started going through about 150-200 of my photos and re-tagged them to remove silly and/or superfluous tags.

But then on Sunday I was browsing my del.icio.us network and saw an article that caught my eye about optimizing MySQL. Since MySQL is the database that powers much of what you see here, I figured I'd take a look to see if I could help shave off any execution time for my database access, in case it mentioned anything that I didn't already know.

One of the items near the end mentioned a tool called mytop. I downloaded and installed it and quickly learned two things.

1. My database is pretty low traffic.
2. Unless I'm hitting my photo gallery.

I used the qps(Queries per Second) mode and saw that when I hit the first page of the main gallery (which only shows 10 thumbnails with their captions in the href title by default), I had an astouding 500+ queries run in a single second! Meaning that every time through a page load, I was querying my database for captions for all (currently) 500+ images.

That's insane.

I already had some code in place that conditionally added the pictures I've looped through to my Template Toolkit $vars variable, but I was performing the caption lookup outside that condition - in essence running it for each and every picture, even though I'm only displaying 10.

I moved the caption query inside the condition, and noticed an immediate improvement - page generation time went from a half a second to a tenth of a second. Really, a fairly simple fix for an 80% speed increase.

You've probably already noticed, but I've added some additional organizational capabilities in the form of categories that each post is filed under. To the right, you'll see a little "cloud" of topics I've posted about - and the more often I post about something, the bigger the topic appears in the cloud :)

Also, I've gone ahead and tagged all my articles. Thank goodness I'm doing this now, because I'd hate to have to go back and do it after hundreds of posts :)

I've also done a lot of tweaking and refactoring of the Catalyst code that powers this thing - I started noticing a lot of repeated code for doing things like formatting posts, obfuscating email addresses, etc. So I'm doing what I can to get rid of that duplication. The only visible effect you'll see from this is that from time to time, you'll probably get an error message - that just means I'm right in the middle of fixing something :)

(Maybe I should have a prettier error message?)