First, I ran into a neat little issue on FreshBooks’ production servers last week involving the table cache and an O(n) algorithm for selecting a table to close. I wrote up a little explanation over on the FreshBooks blog that you might find interesting if you find any of this interesting. The short version is that if you’re going to be running with a full table cache and still opening tables regularly, you’ll be better off with a much smaller table cache, because finding the least-recently-used table to close is big-O of the size of the table cache. Smaller table cache = fewer tables to determine the LRU.

And then last night, out of the blue, a web forum about tinwhistles that I host hit a wall. About 8:30, my mostly-idle Linode went heavily IO-bound — as in one of the four CPUs spinning in diskwait all the time. What had originally been complex but fast (and common) queries were suddenly taking minutes and minutes to run: things like “get a list of topics in a forum”, and especially “get a list of posts for a forum’s RSS feed”.

There’s a lot of EXPLAIN output here, so I’d better put this behind a cut.

I took a look at the RSS feed query, which seemed the worst not only because it took a long time — 600+ seconds — but because it read-locked every important table in the database. Here’s the query, a big inner join:

SELECT t.topic_title, t.topic_last_post_id, t.forum_id,
    f.forum_name, p.post_time, pt.post_text, pt.bbcode_uid,
    u.username, u.user_id
FROM phpbb_topics t, phpbb_posts p, phpbb_posts_text pt,
    phpbb_users u, phpbb_forums f
WHERE t.topic_status != 1
  AND p.post_id = t.topic_last_post_id
  AND pt.post_id = p.post_id
  AND u.user_id = p.poster_id
  AND t.forum_id = f.forum_id
ORDER BY t.topic_last_post_id DESC
LIMIT 0, 15;

Here’s the mk-visual-explain output. I’ve replaced the table aliases with readable things:

Filesort
+- TEMPORARY
   table          temporary(forums,topics,posttext,posts,users)
   +- JOIN
      +- Bookmark lookup
      |  +- Table
      |  |  table          users
      |  |  possible_keys  PRIMARY
      |  +- Unique index lookup
      |     key            users->PRIMARY
      |     possible_keys  PRIMARY
      |     key_len        3
      |     ref            chiffbb.posts.poster_id
      |     rows           1
      +- JOIN
         +- Bookmark lookup
         |  +- Table
         |  |  table          posts
         |  |  possible_keys  PRIMARY,poster_id
         |  +- Unique index lookup
         |     key            posts->PRIMARY
         |     possible_keys  PRIMARY,poster_id
         |     key_len        3
         |     ref            chiffbb.topics.topic_last_post_id
         |     rows           1
         +- JOIN
            +- Bookmark lookup
            |  +- Table
            |  |  table          posttext
            |  |  possible_keys  PRIMARY
            |  +- Unique index lookup
            |     key            posttext->PRIMARY
            |     possible_keys  PRIMARY
            |     key_len        3
            |     ref            chiffbb.topics.topic_last_post_id
            |     rows           1
            +- JOIN
               +- Filter with WHERE
               |  +- Bookmark lookup
               |     +- Table
               |     |  table          topics
               |     |  possible_keys  forum_id,topic_status,topic_last_post_id
               |     +- Index lookup
               |        key            topics->forum_id
               |        possible_keys  forum_id,topic_status,topic_last_post_id
               |        key_len        2
               |        ref            chiffbb.forums.forum_id
               |        rows           2579
               +- Table scan
                  rows           23
                  +- Table
                     table          forums
                     possible_keys  PRIMARY

See that temporary table at the top that gets used in a filesort? Well…

The whole thing was multiple joins which were then ORDERed and LIMITed. So that meant that it had to find all posts to the forum, ever, and shove them in a temporary table, sort that, and take the 15 most recent posts.

“All posts to the forum, ever” is about 500MB of data. That made the temporary table big enough to go to disk. So every time this query ran and couldn’t be answered from the query cache, it had to write that 500MB file. And the cached query was invalidated whenever someone posted to the forum, which is pretty often.

The problem in this case wasn’t (entirely) the SQL. MySQL was optimizing the query poorly because the key distribution statistics were off. An ANALYZE TABLE on the affected tables fixed that, and gave us:

JOIN
+- Bookmark lookup
|  +- Table
|  |  table          forums
|  |  possible_keys  PRIMARY
|  +- Unique index lookup
|     key            forum->PRIMARY
|     possible_keys  PRIMARY
|     key_len        2
|     ref            chiffbb.topics.forum_id
|     rows           1
+- JOIN
   +- Bookmark lookup
   |  +- Table
   |  |  table          users
   |  |  possible_keys  PRIMARY
   |  +- Unique index lookup
   |     key            users->PRIMARY
   |     possible_keys  PRIMARY
   |     key_len        3
   |     ref            chiffbb.posts.poster_id
   |     rows           1
   +- JOIN
      +- Bookmark lookup
      |  +- Table
      |  |  table          posttext
      |  |  possible_keys  PRIMARY
      |  +- Unique index lookup
      |     key            posttext->PRIMARY
      |     possible_keys  PRIMARY
      |     key_len        3
      |     ref            chiffbb.posttext.post_id
      |     rows           1
      +- JOIN
         +- Bookmark lookup
         |  +- Table
         |  |  table          posts
         |  |  possible_keys  PRIMARY,poster_id
         |  +- Unique index lookup
         |     key            posts->PRIMARY
         |     possible_keys  PRIMARY,poster_id
         |     key_len        3
         |     ref            chiffbb.topics.topic_last_post_id
         |     rows           1
         +- Filesort
            +- Filter with WHERE
               +- Bookmark lookup
                  +- Table
                  |  table          topics
                  |  possible_keys  forum_id,topic_status,topic_last_post_id
                  +- Index range scan
                     key            topics->topic_status
                     possible_keys  forum_id,topic_status,topic_last_post_id
                     key_len        1
                     rows           57912

There’s still a filesort, but it’s now a filesort of a single 57k-row table that’s already been filtered. That table is about 5MB, and fits in tmp_table_size, so doesn’t go to disk. The joins all stack, and the ORDER BY just follows that one-table filesort. The query takes about 0.15s now, or about 4000x as fast.

Incidentally, it can still be improved: that “filter with WHERE” is because of the “WHERE t.topic_status != 1” in the query, and that means “where the topic is not locked”. The idea was that locked topics aren’t going to appear in the last-15-posts anyhow, so may as well exclude them. But if they’re not going to appear because of the sorting, and since you’re sorting anyhow, unless there are a LOT of locked posts that doesn’t matter. Taking out that restriction gets us:

JOIN
+- Bookmark lookup
|  +- Table
|  |  table          users
|  |  possible_keys  PRIMARY
|  +- Unique index lookup
|     key            users->PRIMARY
|     possible_keys  PRIMARY
|     key_len        3
|     ref            chiffbb.posts.poster_id
|     rows           1
+- JOIN
   +- Bookmark lookup
   |  +- Table
   |  |  table          posts
   |  |  possible_keys  PRIMARY,poster_id
   |  +- Unique index lookup
   |     key            posts->PRIMARY
   |     possible_keys  PRIMARY,poster_id
   |     key_len        3
   |     ref            chiffbb.topics.topic_last_post_id
   |     rows           1
   +- JOIN
      +- Bookmark lookup
      |  +- Table
      |  |  table          posttext
      |  |  possible_keys  PRIMARY
      |  +- Unique index lookup
      |     key            posttext->PRIMARY
      |     possible_keys  PRIMARY
      |     key_len        3
      |     ref            chiffbb.topics.topic_last_post_id
      |     rows           1
      +- JOIN
         +- Bookmark lookup
         |  +- Table
         |  |  table          forums
         |  |  possible_keys  PRIMARY
         |  +- Unique index lookup
         |     key            forums->PRIMARY
         |     possible_keys  PRIMARY
         |     key_len        2
         |     ref            chiffbb.t.forum_id
         |     rows           1
         +- Bookmark lookup
            +- Table
            |  table          topics
            |  possible_keys  forum_id,topic_last_post_id
            +- Index scan
               key            topics->topic_last_post_id
               possible_keys  forum_id,topic_last_post_id
               key_len        3
               rows           59900

And with that there isn’t even a filesort and the query finishes in <0.01 seconds, 60000x as fast as the original problem and 15x as fast as the post-ANALYZE optimization. Nice.

At the time, I never posted friends-only to my LiveJournal, because I was “blogging”, not “journaling”, and because my parents and coworkers weren’t the type to find either, and because I didn’t feel like I had a whole lot personal to say. But these days I’m finding more and more that there’s stuff I’d like to write about to people who are close to me, and not necessarily the whole Internet.

So I’ve turned off crossposting, and both my LJ and blog posts will have comments turned on in each place.

And I want you to keep reading! Here’s how:

If you’re reading this on LiveJournal, please add richtext to your friends list, so you can see both my Rich Text blog posts and my mendel posts. (You might also like my LiveJournal too. But you won’t see my protected posts that way, so you might want to read me on a LiveJournal friends list instead.

(And I’m looking forward to diving back in to LiveJournal-style LiveJournaling, too — I’m sorry I’ve been so quiet on the comments for the last little while, but reading LiveJournal friends via RSS did lose something in translantion, and I’m back to reading on a friends list again for my LJ peepz.)

Unfortunately we didn’t get a picture of the five of us because we’re kind of dumb, but here’s a couple that show everyone from Carrie’s Flickr set of the trip (because I didn’t bring our camera along!):

(Lushes.) The top picture is from Future Bakery, and the bottom from the Victory Cafe. It was a pleasant surprise to me how easy it was to find places to flop on a patio that were kid-friendly, and with the heat and all the walking we did a lot of flopping.

It was a fun weekend on the face of it, but it was also great because this is the first time the five of us have all been together. Candice and I met Carrie for the first time at Trevor and Jenny’s wedding back last September. What happened next is complicated, but Carrie and I learned we had had an old mutual crush on each other, helped each other through some hard times in the month or so after that wedding (Carrie was a big help in my decision to withdraw from the MBA, for instance), ended up pretty close, and we had been struggling since to try to find the best way to remain a part of each other’s lives in a way that made our families feel safe and respected (and through a lot of change on Candice’s and my part, with me leaving school and us moving to Toronto!).

That hasn’t been easy, but this weekend things clicked. Misunderstandings cleared up; Jason and I finally met and got along well (and we learned we share an awful sense of humour, to the point where we were often racing to make the same bad pun first); Amie took to me so well that Candice and Carrie were calling me her boyfriend; and the whole thing was just comfortable. It felt like family, which was sort of a goal that Carrie and I had talked about months ago but which I sometimes thought was impossible.

What better way to finish off than this:

(And for those keeping track, now you know #23.)

From one of my new favorite webcomics, Piled Higher and Deeper, which pokes fun at grad school and grad students. I’m probably all “old meme” on this one since the comic’s been published since 1997 (originally in a Stanford student paper), but I only found it recently and I’m loving it.

The details are all at the link, but basically we’re at the point where there’s enough to do that stuff needs doing in parallel. It’ll be a two-sysadmin shop after this, so there’s lots to do from PCI compliance and new architectures down to maintaining desktops in a casual but busy startup-ish environment with a lot of fun people.

If you’re interested, or if you know someone who might be, drop us a line. The position’s been filled!

On Father’s Day we drove back to Belleville to go out to dinner with my father, the first long drive we’ve done in the caar since my trips back and forth to Ottawa while Candice was still living there and me here. About halfway home: grindy noise! It came and went but performance, temperature and so on didn’t seem affected, and I noticed a wobbly pulley, so I figured it’d get us back to Toronto fine (and it did) and then I could take it in.

A couple days later, I had a new A/C compressor, and the wobbly pulley (and the serpentine belt) replaced too. And parts are not cheap for this old Swede.

Combine that with how often we drive — I filled up the tank for that trip, and before that the previous fill-up was March 25 — and the crazy cost of car insurance living in downtown TO, and the conclusion jumps out at you: We really don’t need a car here.

I take the TTC or my bike to work every day. Candice walks a few blocks to work. Grocery stores are even closer. Whenever we head downtown or to the Annex or Queen West we just take the TTC or walk. Hell, we don’t even have parking — the car is on the street all the time. And for the odd errand that does require a car, we can just use Zipcar or Autoshare in town and a regular car rental for road trips, and come in way, way under what we’re paying to have a car sit on the street, even though the car’s fully paid for.

I was car-free in Montreal from 1994 to 2001, but then as soon as I moved to Ottawa I bought the Saab, partly because Ottawa (and the neighbourhood I was living in) was pretty car-heavy, and partly because I had the disposable income and an excuse. But for someone who’s read car mags since childhood, I didn’t find car ownership all that fun. Too much maintaining and not enough spirited driving, maybe? Or maybe too sensible a car, or maybe too sensible a place to drive.

(The same thing happened with the motorcycle, I think. I’m not sure why, specifically.)

So the car’s going for sale later this year. Not sure when yet. Not looking forward to the process one bit, either! But I’ll be back to car-free soon, and I’m pretty happy with that, and especially happy living somewhere where I can, and not be too inconvenienced or treated like a freak for relying on transit.

I want one.

Update: Another coworker, Taavi, points out that it’s also available as desktop wallpaper.

But it seems that aside from reminding myself of what I’d like to be doing, there’s always these tiny little things that motivate me right then. That shouldn’t be a huge surprise, I suppose, since the whole point of the book is motivation and Covey is a motivational speaker, but it’s nice that it happens. I never post about those little things because they’re usually really obvious, and sometimes the impact of really obvious things doesn’t come across well online.

This time around it was this:

What one thing could you do (you aren’t doing now) that if you did on a regular basis, would make a tremendous positive difference in your life?

The first implication, of course, is “Then why aren’t you doing it?” — but the central thesis of the entire book is that there are many usual reasons that you’re not, and that it’s a lot of work to develop such that you can get past those. The second implication is that once you’re doing that, you’ll find another One Thing. And Covey’s explanation for why you’re not doing it is that it’s always a Quadrant II activity — “important but not urgent” — and the Quadrant I and III activities (“important and urgent” and “urgent but not important”) crowd out Quadrant II without effort to the contrary — but that’s detail. Just the seed of the idea is enough for now. If I’m not working towards finding and doing it, what am I waiting for?

(And while digging up the exact quote I thought of another three or four ideas I want to write about, but I know people can only take so much of Seven Habits, so you’re safe for now.)

(From a Flash presentation about Gomez‘s web monitoring service.)