Tuesday, September 30, 2008

Get Your Calendars!

Version 2.0 of the 2009 letterboxing calendar arrived this afternoon, and it is good! =) The colors looked good, the credits are legible, and the calendar is READY to order in mass quantities!

I'll continue taking orders for them through Friday then put in the bulk order for them all. So if you want the lowest prices possible on the calendar, put your order in by Friday. After that, calendars will still be available for purchase, but only through the Lulu.com website which will cost more than if you order it through me.

I've even ordered new address labels that will go with the calendars I'll ship this year. Okay, not particularly exciting, I'm sure, but hey--I can stop writing the return address by hand so I'm excited about it. =)

Happy trails!

Tuesday, September 23, 2008

Letterboxing Calendars!

I hold in my hands, or rather, I was holding in my hands (I'm typing now), a copy of the Official Atlas Quest 2009 Letterboxing Calendar. It's a beautiful thing, but as always, there's room for improvement. It was hard to read where the photo was taken and who took it on two of the months, so I tweaked that a bit. And I didn't like how the background color in one of the months turned out on paper, so I tweaked that too.

But despite those minor irritations, I like the calendar. =)

I've put in an order for another copy of the calendar--just one--to make sure the changes look good on the final product. I doubt I'll have any issue with it, so I'm going to open up the AQ Marketplace and officially put the calendars up for sale. I still need to tweak and test a couple of things and make sure nothing has broken since last year, but it should be ready by morning tomorrow when most of you read this message. =)

You can also order the 2009 Thru-Hiking Calendar. All photos taken while on my thru-hike from Key West to Springer Mountain. I don't actually expect to sell enough of these to get a bulk discount, but I don't have to pay for and give out 13 calendars for free either so it kind of evens out. =)

You can also order AQ patches at the same time you order calendars, but please note that I do not intend to ship the patches until the calendars that go with them arrive. (If you order just patches, I'll ship those immediately since I have those right now, but in that case, you're better off using the patch page to order them. Shipping is cheaper there since the shopping cart assumes I'll have to ship a calendar which would be more expensive.)

A couple of things to note: The calendars won't be in your hands until mid-October at best. Hopefully by the end of the October at worst. Seems like every year people put in orders and a couple of people later contact me asking what happened to them. I collect the orders up front ahead of time so I can buy them all in bulk. The calendars must then be printed and shipped to me--a process that typically takes two or three weeks. Once I get the calendars in my own hands, I mail them out as quickly as possible to you. The advantage for you--by buying the calendars in bulk, it's cheaper than being able to buy it directly off of Lulu.com (which is where I have them printed). And it's good for me since I have to give away 13 calendars for free, I end up saving money myself since those free calendars are included in the order and also get the bulk discount. (To be perfectly honest, that's the main reason I even handle the calendars myself--it ultimately saves me money in the long run too!)

So when you put in your order, keep in mind that the calendars likely won't reach you until mid to late October. And that's just an estimate--if the calendars take unusually long to reach me, it could even be later.

For those of you who had a photo used in the calendar--JBBK, Funhog, Music Woman, Ukusa, Benetti's Defense, firefighterfamily, Sandibox, Eye Wanitt, TulsaGal, Cabin Clan, ArtGekko, roseislandfans, and yachtygirl--congrats! =) The photos are beautiful! If you want more than the free calendar you'll be receiving, go ahead and put in an order just like anyone else. Do NOT include your free calendar in that order, however. AQ isn't smart enough to know who is getting a free calendar and will charge you for anything you run through the ordering process. I'll handle the free calendars separately.

And.... if you missed out on submitting photos for this year, I'm already accepting submissions for the 2010 letterboxing calendar. Keep your camera and your eyes ready!

For all things about the calendar, check out the Project X link under the Toolbox menubar option.

Wednesday, September 17, 2008

FullText Searches

Good news, folks! But before I get to that.... you're probably wondering, "What the heck is a full text search?" Good question!

It involves a special type of index in the database that will take a long string of text, figure out each and every word that's used it in, then create a massive index so when you try to do a search for all posts with the words "deep dark secrets," the database can look up each of those words in the index and figure out--practically instantly--exactly which posts match the search. There's a lot of sophisticated stuff going on there.

And that's where the good news comes in--I finally broke down and figured out how to personalize the index specifically to Atlas Quest. You see, all this time, I've just been using the defaults. Those defaults include the minimum length a word must be in order to be indexed (four) and the list of stopwords (common words that aren't worth indexing at all such as "which" or "that").

I hadn't been messing around with the defaults since I thought it would require tweaking the code for the database itself and recompile--something I so did not want to do and could seriously screw things up in a very big way.

Turns out, that's not the case. (Well, actually, certain settings do require this, but the minimum word length, maximum word length, and stopword list does not.) So this evening, I changed the minimum word length to 3 and the maximum word length to 10. I took the already existing stopword list and added a few commonly searched for terms that don't seem to be particularly useful but can bog the database down with especially common words.

Then I rebuilt the indexes and presto! Most three letter words are now searchable. You can search for them in the message boards, in clues, and wherever word-based searches are allowed. Very nice. I still need to tweak some of the code on Atlas Quest before the change is complete, but already the searches should be much more useful and powerful. You can complete full searches on three letter words such as pie, box (though I might add that one to the stoplist), toe, etc. Very nice. *nodding*

In other news.... just before I started doing these tweaks, I happened to noticed that the database grew to 1001.3 megabytes. The big One Gig! Must have happened some time this afternoon.

After I changed the full text index settings and rebuilt the indexes, the database size dropped to 986.76 megabytes. Hmm.... I actually expected it to grow even larger since I'm allowing three letter words to be indexed which wasn't happening before, but I guess the words longer than 10 letters that are not being indexed anymore more than compensated for the difference. *shrug* Plus a handful of words in the stopword list that are no longer being indexed.

In any case, it looks like I shrank the database by several megabytes with my change, so it's under the 1G size now. Give it a week, though, and I bet it'll be above one gigabyte again. The database certainly grows fast enough! I still thought it rather exciting to see the database being over 1G. I've never worked with a 1G database before. =)

So anyhow.... That's the update for tonight. Hope you like it! =)

-- Ryan

Saturday, September 13, 2008

More Message Boards!

For some time now, I've been meaning to add more regional divisions for the message boards. The folks in the southwest requested one almost immediately as soon as the message boards were added and it thrived briefly, but largely lays forgotten for the most part nowadays. But at long last, I've added a bunch of new regions.

There are a couple of reasons for this. First, to help cut down on having people cross-post to multiple states that they're traveling through or all of the neighboring states that an event is being held in. Especially with those little states in the northeast. It allows one to better target the specific audience they want to see their post without having to cross-post.

Second, for some time now, I've been wondering when creating "city-based" boards might be useful. Or at least regions that are smaller than individual states. So there's a place where one can discuss boxes or happenings in Seattle, or Los Angeles, or other large metro areas without having to annoy folks who might live in the state but nowhere near the area being discussed. (A potential issue for folks who live in large states such as California or Texas that span the better part of a thousand miles.)

I haven't created any city-based boards (not yet, at least), but I renamed the existing boards to specify what sort of area the individual boards cover: regional or states. (Or provinces, in the case of Canada.) So later, if or when city-based boards would be useful, I can call the boards "City: Seattle" (or whatever the case may be). It's rather useful to prepend the area being described with what type of area it is since the boards are sorted alphabetically. It lumps all of the regional boards together, and all of the state boards together, and (in the future) all of the city boards together so there isn't a big mash-up of regions, states, and cities randomly thrown together.

So anyhow.... to make a long story short, you'll likely want to check your board settings to make sure you've marked your region as a favorite or ignore those boards that have no interest to you. There are several new regions for the United States, mostly following the lead of the existing regional Yahoo Groups list since that's what everyone's used to already. I also added a few regions in the World Letterboxing category to cover Africa, Central America, South America, Western Europe, and Eastern Europe.

Happy trails!

Friday, September 05, 2008

That was Fast, Part Duo

Now that the message boards are running so incredibly fast, I've started taking a look at some other regularly recorded slow queries and putting some effort into fixing those up. The speed improvements can be just as dramatic, but you won't notice them nearly as much since they tend to be on rarely visited pages or when you run a rarely used command. (Relatively speaking, of course.)

Out of curiosity, I pulled up the stats from last month. In August, the message.html page (used to read or scan multiple messages at a time) was hit 379,824 times while the message.html page (used to read messages one at a time) registered one at a time got 282,833 hits for a total of 662,657 hits.

Almost every one of these page hits would have required at least 1,000 milliseconds to run at any given time, sometimes considerably longer during heavy loads, but lets assume 1,000 milliseconds is a good average. That's one full second.

Which means for the database to generate 662,657 message board pages, it would have required 662,657 seconds to do so. Seeing as there are 60 seconds in a minute, that's equivalent to 11044 minutes. And since there are 60 minutes in an hour, that would be equivalent to 184 hours. And since everyone knows there are 24 hours in a day, that amounts to 7.7 days.

So last month, if the sever on Atlas Quest did nothing but generate those 662,657 pages one at a time, non-stop, it would have taken 7.7 days. There were only 31 days last month. That's a heck of a lot of server time going to the message boards when you consider AQ also needed to serve up clues, profiles, AQ mail, yadda, yadda, yadda. Not surprising really that things were starting to come to a boiling point.

Now the message board pages are disgustingly fast. They seem to be averaging about 10 milliseconds per page, or about 1/100th of a second. So to process 662,657 message board pages would now require about 1.8 hours of processing time.

From 7.7 days to 1.8 hours. Yeah, I'd say that's an improvement. =)

And I should apologize.... That stolen one or two seconds every time you tried to load a new message board page added up to a wasted 7.7 days for you guys. Sitting there waiting for the database to do its thing and figure out which message you were trying to view. So collectively, you all now have an extra 7.6 or so days to FIND MORE LETTERBOXES this month! =) And every month from here on out. You're welcome. ;o)

Yowzers, that's fast!

Those folks with webmaster accounts on Atlas Quest get quite a bit of technical information included with every page they view on the site. It tells us if AQ detected any errors, any cookies that were sent, POST and GET data, the headers from each page, session variables, and--relevant to today's post--a record of all queries that AQ runs on the database that take more than five seconds to run. There's nothing special about five seconds. Originally I started it at ten seconds, then waited around for a bunch of slow queries to be recorded, then started figuring out ways to make them run faster. They were the worst of the lot and slowing down Atlas Quest.

Eventually, a day came along, and Atlas Quest did not record a single slow query. So then I cut the definition of a "slow" query to five seconds, and started fixing those up. And eventually, I knocked all of those out too.

I never cut the definition of a slow query after that--Atlas Quest was running fast and smooth and there seemed little reason for it. Five seconds is actually very slow, but an occasional slow query wasn't an issue and to be expected. During the nightly updates, for instance, certain chores such as backing up the database would cause queries to stop for the better part of a minute, but backups are very important and necessary and I wasn't going to stop doing them so queries at 3:00 in the morning ran quickly. So five seconds seemed like a reasonable limit--cutting out most of the temporary slow queries but still catching most of the problem queries.

A few weeks ago, I noticed that one particular query which runs on My Page was getting pegged as slow more and more often. My Page is one of the most commonly hit pages on Atlas Quest, and it has a slow query to boot? Not a good combination....

So I took a look at the queries on that page. Atlas Quest will display for webmasters every query that is run to generate a page along with how many milliseconds the query took to run. The vast majority of queries will run in less than 5 milliseconds. A few of the more complex queries may take as long as 10 or 20 milliseconds. And a small handful, under the right conditions, may take as long as 100 milliseconds. That's still 1/10th of a second, however, and pretty darned fast.

And on My Page, Atlas Quest typically runs about 10 queries to generate all the data you see on that page. And one of the queries--the query that kept getting tagged as slow--took longer to run than all the other queries combined! Most of the time I looked, it required about 200 to 300 milliseconds to run, but when the server had a particularly heavy load, that time would often extend beyond 5,000 milliseconds (that's five full seconds!) and AQ would record the slow query for my examination. For one of the most commonly viewed pages on Atlas Quest, that query was becoming a major problem, and a problem that continued to grow worse as the number of members on Atlas Quest and the number of messages to the message boards grew.

What did this query do? It's the one that calculated how many new posts were on your favorite boards. Somehow, I didn't think most of you would like it if I just took out that feature. I needed to think of a way to make that query faster.

And for weeks now, I pondered ways about doing that. I delved into the database documention, tried to implement a couple of solutions, but kept hitting a block wall. That query was a darn good query already--it had to be to have kept running so well for so long--and further optimizations were growing a lot harder to find. There were other problem queries on Atlas Quest, but I considered the one on My Page my number one priority since it was one of the most commonly viewed pages on Atlas Quest.

I don't know exactly how many hours I worked on figuring out a solution to this problem query. Probably at least 50 hours. Probably not more than 100, but I don't have a timesheet I check in with, so I don't really know, but it was long enough that I was getting very frustrated. After weeks of effort and dozens of hours of research and tests, and I was getting absolutely nowhere.

And the problem query just kept becoming a bigger and bigger problem, getting tagged as slow more and more often. Would the madness ever end?

Finally, more-or-less out of desperation, I had one last idea that required me to duplicate some data in the database in two different places. Actually, technically speaking, the data was duplicated--on average--about 7 times. I only duplicated one column, but for those of you wise in database design, duplication of data is best avoided. But I thought one particular index might help, and to create it, I needed to duplicate some data from one table in the database to another, then create an index on it. An index is just what it sounds like--something like out of a technical book you might read. It allows finding specific data in a table very fast.

So I copied over this data, created an index on it, and what an improvement! That problem query on My Page went from 200-300 milliseconds (most of the time) down to 20-30 milliseconds (most of the time). It's a huge improvement, running 10 times faster than before. The last time I viewed My Page, the queries it ran took the follow milliseconds to run: 1, 19, 6, 2, 6, 5, 0, and 3 for a total of 42 millseconds. That's pretty normal, and is a huge improvement over the 300+ milliseconds the page used to take to run. So if My Page seems like it's running faster today, it's because it is. AQ is generating it nearly 10 times faster than it was just yesterday.

Turns out, however, that was just the beginning. There was another problem query I was running into when someone viewed a page of message board posts. Each time you view a post, Atlas Quest needs to calculate where the previous and next buttons should point to. I put in a couple of ugly hacks many moons ago to help keep the message boards running fast, but they were starting to filter. The calculation for the previous message in particular was often the slowest of all, rarely finishing in less than 1000 milliseconds (one full second). And that's a pretty busy page to have a query that takes at least one full second to generate.

With this duplicated data now in the database, on a hunch, I added another index and checked out the results. The previous message query took 1 millisecond to run. One. That friggin' query took 1/1000th of a second to run--a thousand times faster than before. HOLY COW! I was floored. I clicked more messages to see if it was a fluke, and the query consistently came back within one or two milliseconds. It also make the query for calcuation the next post equally fast (improving from about 100-200 milliseconds to 1 or 2 milliseconds), and the query for the messages that were being viewed also improved from about 100-200 milliseconds to 1 or 2 milliseconds. With queries running that fast, I no longer needed a couple of the hacks I put in the code to improve the query speeds, so I took them out which saved another few hundred milliseconds off the overhead to generate a page.

All told, viewing a page of 30 posts was taking nearly 2000 milliseconds to create on average. Even during the lightest of server loads, the page was taking at least 1000 milliseconds to generate. During heavy server loads, it might take 10,000 milliseconds. (Ten full seconds!)

The last page of messages I just looked at included the following query times (in milliseconds): 1, 2, 0, 0, 1, 1, 0, and 1 for a total of 6 milliseconds--an improvement over one hundred times faster than before. Another page of messages took the database 15 milliseconds to generate--and that was one of the slower ones!

So to make a long story short.... if the message boards seem zippier today than they have in a long time, there's a good reason for that. They're running over 100 times faster today than they were yesterday. =)

Monday, September 01, 2008

Calendar Photos

The submission deadline for calendar photos has come and gone, with a total of nearly 600 photos submitted! I actually started weeding through them last week and have now narrowed down the choices to 28 pictures. The more I narrow the photos down, the harder it gets. The first ones are easy--I don't even read anything about the photos. Date in the corner? Delete. Telephone wires running through the middle of the photo? Delete. Blurry photo? Delete. Strange photo of a person wearing a lobster hat? Delete. (Though that one did make me laugh.)

Now I find myself looking at four photos of beautiful waterfalls, and I think, "Okay, I can't use them all. But which one?" Then I start looking at where the photo was taken. If there are a lot of nice photos in a certain part of the country, I'll lean towards the photos taken somewhere else. Or if I've already picked a photo by one person, I'll try to pick another one by somewhere else. And one in a national park I ditched--beautiful photo--but all else being equal, I had to go with the waterfall not in a national park.

And I still have four waterfall photos left to choose from. Hmm.... At the very least, I still need to narrow it down to no more than two. It might boil down to which photos I can find the best quotes to go with them. I haven't even started looking for quotes as of it. Or maybe which months I need a photo to fill with. If I have an empty slot for January, I'd probably go with the waterfall in the snow. If I have an empty slot in October, I'd go with the waterfall surrounded by the fall leaves. Decisions, decisions.....

But I'm now down to 28 choices to choose from. There won't be a silly calendar this year--I actually lost money on those last year due to the lack of interest in that one. I'm kind of leaning towards not doing a "sunset edition" this year either. There are enough photos for that, but this year a large number of them seem surprisingly similiar.

But that still leaves me with 28 photos to choose from, and the choices just keep getting tougher....