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.

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!

I can’t participate in the opening pancake breakfast at Nathan Phillips Square because I commute the other way. Instead, I’m going to bike to work tomorrow morning even though they’re calling for thunderstorms in the afternoon, and either leave the bike there overnight or pick it up later in the evening.

Thursday’s a staff meeting at FreshBooks which is followed by a (decidedly un-bike-related) rock climbing excursion at Joe Rockhead’s. I’ve never climbed before but I’ve been meaning to for a long time (and it’s on my severely out-of-date 101 in 1001 list). But Thursday night brings back the bike content with a Bike Month screening of Pee-Wee Herman’s Big Adventure, with live action featuring various local personalities. Think Pee-Wee meets Rocky Horror. I should drag some FreshBooks people along.

Friday’s the last Friday of the month, which means Toronto’s monthly Critical Mass ride. I figure Bike Month is as good a time as ever for my first Mass. I know there’s a lot of opinions out there on Mass and I haven’t entirely formed mine yet, but I figure I ought to participate before I do. I know there’s been some issues with Mass in Toronto in the past but I’ve also heard that it’s cleaned up a bit (although I’ve also heard that a detour through the Eaton Centre is not unheard of).

Right after that is the first Toronto Criterium in the St. Lawrence Market area. The senior divisions’ race starts at 7:30 and lasts an hour and a half plus three laps. Crits are a blast to watch — especially compared to road races — because they’re laps, which means you get to see the peloton more than once, and because they’re short, which means they’re fast and aggressive.

And that’s just this week! I haven’t dug deeply into the rest of the month’s schedule, although dobbs on MetaFilter mentioned recently that the 2008 Cycle Messenger World Championships are being held in Toronto mid-June, so I’ll probably catch some of that too (and maybe catch up with dobbs there, for that matter).

(The Tour de Dufflet sure is tempting, though. And I need to remember to register for the Community Bicycle Network’s repair classes. But I might pass on the World Naked Bike Ride on the 14th.)

Post-and-ring photo at top by flickr user urbanmkr.

feet elev.

miles

Unfortunate, especially since there are no showers at work. I might end up taking the bus up the steep part with the bike on the bus’s bike rack some days, or just figuring something out to clean up and change at work anyhow. I feel kind of silly because shorts and a T-shirt are fine at FreshBooks, but I don’t really like wearing shorts day-to-day, even in summer.

I do want to get something to carry stuff on the bike instead of my back, though. Even though I’ve got a nearly-new Timbuk2 messenger bag, I’d like to avoid the damp back that comes from cordura-on-cotton. Current plan is to pick up a CETMA 5-rail front rack:

They’re handmade by Lane Cagay in Eugene, OR of cromoly tubing, with a weight limit of over 40 lbs (although the racks can handle a lot more, that’s when steering starts to get a bit wonky, apparently — and here’s why the front). On a lark I checked Craigslist yesterday and it turns out there’s someone with an unused 5-rail for sale just a few blocks north of here, so that’s awfully serendipitous. I figure even if I don’t ride to work daily, just doing all my errands at this end of town on the bike will be a plus compared to walking or bussing it everywhere.

I’ll post photos once I’ve got the rack!

]]> https://www.lafferty.ca/2008/04/28/nice-rack/feed/ 2 https://www.lafferty.ca/2008/03/19/fun-with-dhcp/ https://www.lafferty.ca/2008/03/19/fun-with-dhcp/#comments Wed, 19 Mar 2008 19:50:40 +0000 http://www.lafferty.ca/2008/03/19/fun-with-dhcp/ I rolled out a new firewall/DNS server/DHCP server at FreshBooks today. Went well except for one problem: occasionally people would lose DNS resolution. Well, that’s not good.

Checking out their machines showed that their DNS server addresses were being changed to an address on the wrong subnet, and their domain being changed to “mshome.net”. That last part’s a red flag: the thing that does that is Windows’ Internet Connection Sharing, which means someone had that enabled on an interface and we basically had a rogue DHCP server.

Rogue DHCP servers are a pain to track down because without a monitoring port on the switch, all you have to go by is broadcast traffic, and then all you get is the address the DHCP server thinks it’s at — which, we know, is on the wrong subnet anyhow — and its MAC address. And we’re a small shop but we still don’t have a handy list of MAC addresses lying around. I did know that the MAC address’s vendor ID was Dell.

So the first thing I did when I found the problem was to check the MAC addresses of all of the wired and wireless interfaces of the Dell computers in the office, and none of them matched! I puzzled over this for a while, had people double-check, and eventually something clicked and Saul remembered that Sunir had enabled ICS during their road trip.

I took a second look at Saul’s laptop, and there was the MAC address — on a disabled wireless broadband interface. Turns out that if you have ICS on, the DHCP server keeps running even when the shared network interface is down. Disable it, problem went away.

But the strange part was that Saul’s been back for a week and the problem just came up today.

I scratched my head about that for a bit and then it hit me: before today, the switch in the wiring closet was in the Linksys router that also served DHCP:

[client]----[switch + dhcp server]----[saul's PC]

After today, both Saul’s network segment and the new DHCP server were both connected to a separate switch:

[client]-----------[switch]-----------[saul's PC]

                       |

                       |

                 [dhcp server]

DHCP is designed to handle multiple (cooperating) DHCP servers on a segment; when a client sends a request, any DHCP servers can respond, and the client chooses one of the responses and informs the DHCP server that sent it that it will use that one. The usual client implementation is to accept the first response.

So before today, a client on one segment would make a DHCP request, but the legitimate DHCP server (at the switch) would be located one Ethernet segment closer to the client than the rogue DHCP server, so it would always win. As of today, the legitimate DHCP server was now the same distance from the client as the rogue one, so part of the time it’d lose, which is exactly what was happening — not every DHCP lease was broken, just the occasional one.

Sometimes it’s easy to forget that actual electrons need to move around for this stuff to work — which in turn reminded me of Trey Harris’s 500-mile email.

But a single ADSL link is a little bit fragile. If it’s down for a day, that’s a day without access to our servers at Rackspace, our tech support email, our marketing reports, everything. Everyone might as well go home save for one person to answer the phones, not that that person can do much when people call.

So in case the DSL line goes down, we’ve got a backup connection using Rogers’ WiMax^? network which they call “Portable Internet“. It’s portable in the sense of the old “portable computers”, in that the router/radio we have is a wall-powered RSU that acts as an Ethernet bridge; there’s also a PC card^? version which would provide a real mobile everywhere-in-the-city network.

Unfortunately it’s a bit slow, especially for the price: $49.95/mo gets you 1.5Mbps down and 256kbps up, or $24.95/mo for 256kbps down and 64kbps (!?) up. I’m not quite sure why it’s as slow as it is, since the real-world maximums for WiMax are in the 10Mbps range, but speed tests confirm that it’s right where they say it is.

But it works! I wanted to have some idea of how it worked before we needed to use it, so I brought the RSU home tonight, and I’m connected to it now. In the window I get five bars of signal, although five feet inside our plaster walls that’s down to one or two bars, although a weak signal seems to affect latency more than speed. And it couldn’t be much easier — plug the laptop into the RSU’s ethernet jack and it grabs a DHCP lease and it’s online.

The speed will make it a bit painful if we ever have to rely on it to get the office connected, but it’ll be a lot less painful than falling off the net completely, and a lot more straightforward than provisioning some sort of permanent redundancy to our little office. (And with a new box arriving early next week to be put to use as a Linux-based firewall and router, I think I might look into automating failover, too. Incidentally, on that subject, I’m torn between trying IPCop and just going with a straight CentOS install and managing iptables and so on by hand. Thoughts?)

I forgot that I wouldn’t have a router right away, so I ended up getting a Speedtouch 516 DSL modem configured as a bridge, so I’m using Windows’ own PPPOE client until the move.

Next up is VOIP. I’m leaning towards Unlimitel because Babytel never replied to my email sent to their support address (pilot error: sent to .com instead of .ca), but there’s still a little shopping to do there. New blog theme/approach coming soon too, although that’s just fitting in the quiet moments of which there have been few lately.

Work is going awesome. I’m moving support over to RT any day now, have a huge set of projects and to-dos to drill through, and have really been given pretty much full ownership over my areas of responsibility already. (And everyone’s so great to work with!)

Off to Dan’s tonight for pizza, beer and conversation. (We tried this last week but I made it a block before I decided it was time to take a rain check and get the car in for a brake job right away. I lucked out in that a great garage happens to be exactly one block away from me, and Frank at Master Mechanic on Dupont and Concord took it in on no notice and did good work at a good price, which was nice considering I needed a garage on short notice in a strange city.)

Movers are scheduled: pack on the 25th, load on the 28th, unload a couple of days later. Final stretch!

Perhaps I was onto something when I concluded that there was little to no Internet industry left in Ottawa, and that it was concentrated in Toronto.

Perhaps.

(Of course, if you have DSL advice for me and I forgot to email you, I’m accepting it here too!)

Work continues to go well. Tomorrow’s the release of a new version of FreshBooks. It’s really too early for me to be involved hands-on in a new release, but I’ll at least get to see the procedure. I also took a few photos of the office. A bit of a difference from before, eh?

Other than actual work time, I’m pretty much ready for these two “advance weeks” to end. I need to get Christmas over with so I can concentrate on the move. Things have been fine here, but I’m just ready to head home for a bit, and then to head over to new-home here. Driving has been a real pain with the big snowfall, since I still don’t entirely know my way around, roads are about half a lane narrower, and parking has been up at the air both at the apartment and at work because of snow everywhere. Once I get into the new apartment and start doing public transportation pretty much everywhere I think that’ll improve a bit.

Thursday night’s the office Christmas party, which should be fun. That forces my hand on shopping: I must pick up something silly for the gift “exchange” by tomorrow night, so I’ll brave heading downtown then. (Downtown should be easy — walk over to Yonge and Eglinton, subway down to Queen. But everything’s just a little disorienting because of the snow and the busy-ness.)

Four (maybe three!) sleeps until I see Candice again, though! Yay!