Saturday, March 31
I've been scratching my head trying to work out why the main query that drives Minx blogs is so inefficient. I tweak indexes and I tweak selection criteria and I tweak sorting and nothing seems to improve the query time reported at the bottom of the page. Now, normally it's only 20ms or so, but if you catch it after an update - a new post or comment - it blows out to 400ms or so because the query cache just got flushed.
So I tweaked and I twiddled and I fiddled and fretted, and I took the query and plugged in some parameters and ran it outside of Minx, and it turned out to be, in fact, reasonably quick.
Then I tried disabling the sidebar to see what effect that had on the total query time.
19ms cached; 28ms uncached. Perfectly reasonable for selecting and sorting and joining multiple fairly complex (albeit small) subsets of 13,000 records.
It's those darn recent comments again!
Update: And now I remember why recent comments are slow. When you list the recent comments, you're listing the last 20 (or whatever) comments in a folder. But comments aren't in folders, they're in threads, so you have to look at the threads in the folder, and then find the most recent comments from among the comments in those threads. Big slow join before the sort.
There's a straightforward way to optimise the common case - use either multiple queries or subqueries to restrict MySQL to only inspect the 20 most recently updated threads. That gets messy for the general case, though, where you want page 17 of recent comments.
Need to spend a bit more time thinking about this one.
Update: Gargh! MySQL 5.0.27 doesn't support LIMIT in subqueries anyway.
Update: Well, it doesn't if you're using IN to restrict the main search to a subset of threads selected by the subquery, but if you use the subquery to return the update datetime of the nth most recently updated thread and then restrict the search to threads updated since then, you're swimming in gravy.
Which would be pretty unpleasant, come to think of it.
Categories. On the right. On the category icons. In the trees. They're everywhere! Eeeeee!!!
Fixed a couple of problems. The MT->Minx transfer marked posts as unpublished under their individual categories (sort of, it's complicated), so they showed on the main blog page, but not on the category pages. And a bug in the recent comments query meant that recent comments didn't show up at all on category pages.
Now, back to being driven crazy by this darn editor.
Friday, March 30
I was back at my old job today, preparing for what I hope is one of the last phone company bill runs I ever have to do, when suddenly, at about 5:45 on a Friday afternoon,* the billing server dropped dead.
My first thought was Do we have backups? Yes, we have backups.** Okay then, let's haul the server out, plug it into a monitor (the computer room is not terribly well organised), and see what we get.
After a certain amount of fiddling I managed to get a BIOS screen up, and select the boot menu, whereupon the server spontaneously rebooted. I tried this several times with identical results; in fact, leaving the server alone would result in a boot-crash-boot-crash cycle just like my unlamented Compaq notebook.
There are four lights on the front of the server that indicate a POST code, so I hit Dell's website for the user manual and looked it up. Running down the list: CPU failure, memory failure, hard drive failure.***
Swapped the keyboard to a different USB port and it worked perfectly.
* There's some sort of physical law that requires this.
** It would take a couple of days to rebuild the system and restore the backups, but at least we had backups. From the previous day, I should add.
*** This is the mission-critical server that was spec'd with only one disk drive, so my first thought was exactly that.
The music is good too.
70ms for the main page without comments.
150ms with comments.
96 comments displayed (including the Recent Comments thingy).
Hmm. A bit disappointing, considering this is the fastest hardware I'm likely to get this year.
I just hope that Ace, who has 200+ comments on individual posts, doesn't try to do inline comments.
Update: Well, it's not broken, just a bit poky at certain tasks. An individual entry page takes 5.7ms (elapsed time) if you remove the Recent Comments. 2.9ms of that is the SQL queries. The forum page sans sidebar takes 54ms to list 20 posts; 6ms for the queries. (The query times are for cached queries; uncached takes longer in elapsed time, but that doesn't come into CPU efficiency of the application code.)
If I tweak the forum listing to include the body of the post, it takes exactly the same amount of time to within the limits of precision of the built-in timer. Which shouldn't be surprising I guess; all the data processing is done whether the template needs it or not, and Python's string processing is very efficient. It does point to where I need to concentrate my attention, though.
Update: Adding the planned [topics] selector and the associated [topic] data tags reduces the time for a forum page sans sidebar to 13ms. The sidebar adds another 13ms. Extending it from 20 to 50 topics pushes the time out to 42ms: about 500μs per topic, compared to about 2ms per entry.
Wednesday, March 28
Simple, but neat:
[me] Pixy Misa
[me] picks up the author of the current post or comment. [you] gets your username if you're logged in, or the name you used on your most recent comment if it's still retained by the session code, or the text specified, or if all else fails, "you".
Windows Update just croaked both my desktop and my notebook. I've spent the past hour in the Windows XP Repair thingy.
That little yellow blippy can sit there forever as far as I'm concerned. In fact, hey, c'mere. Automatic Updates Off. Little red blippy Always Hide.
Let's give these ones a whirl:
Steven wrote:Context is key, in two ways.
Though it's good, it doesn't strike me as being that outstanding. I suspect that what I'm missing is context. What I suspect I'm missing is that the hooplah was from people who were comparing it to all the other shows that came out at the same time, and by comparison to the normal run-of-the-dreck series from the last year, it really did seem like a lightning strike.
First, as Steven says, 2006 was a pretty blah year for anime, and Haruhi is a good show, with a solid story, strong cast, and high production values.
But second, and specific to Haruhi Suzumiya, is the effect of the re-ordering of the episodes. It's effective when you watch the episodes one after the other, but it's crazy-making when you watch them a week apart.
BBCode update one for the morning: You can now add links to the attribution in [quote] tags. Just separate the author name and the link with a pipe character, |. So in this case, the code was [quote="Steven|http://denbeste.nu"], producing
So that's why my honeypot wasn't catching any flies. I hadn't properly updated the code, and it was still expecting the Page object to be a global - which hasn't been true since the Great Rewrite back in January.
Without the Page object, it didn't have access to... well, much of anything, really. Including the database. So it never actually created any records.
Let's see what it does now...
Hmm. Still empty, but it hasn't been fixed very long.
Guess I should pretend to be a spammer and actually test it.
58 queries taking 0.3984 seconds, 368 records returned.
Powered by Minx 1.1.6c-pink.