Wednesday, December 14, 2011

Upcoming event rating and size searches are back

Hello tournament seekers-

Over a year ago, I had to disable the "event size" and "expected event rating" search criteria in FRED's upcoming event list. At long last, they are back. These are one of the more valuable features of FRED's event search, so I'm very happy to have them back, and I'm sure you will be too.

You'd be surprised how much work it was. Admittedly, most of it was "under the hood" work that will be useful for lots of other features, so it's not just this one feature that caused all the headache. It's kind of like building a car just to drive to the store for milk, but we'll be able to use that car for so much other stuff, I swear!

Anyhow, thanks everyone for your patience while these features were "on vacation".


Warning: extreme geek-ness follows:

The problem:
These two filters were implemented in SQL, using some big joins and subselects on the preregistration table, and (brace yourself...) the USFA event classification chart expressed as an SQL view. Yeah, I admit, I did that one just to prove it could be done. This all worked fine while there were only a few tens of thousands of preregs in the db. But there are now over half a million. Whenever someone used one of these two criteria (esp the rating search), the database server would slow to a crawl and web page loads would time out, bringing the whole site to its knees for all users, not just the one searcher.


The Solution:
The current prereg count and predicted event rating are precalculated and saved in the event table so those filters are now just a simple where clause. But that's the simple and obvious part. The hard part is keeping them in sync in near-real-time as people preregister for the tournament. One way to do this would be to recalculate these values and update them as part of the same transaction as the user's preregistration. This is less than ideal because that could slow the response time to the user's prereg submission, just so we can accomplish some housekeeping tasks. Not cool.

Instead, FRED now has a task queue based publish-and-subscribe system for deferring such processing to a background worker process. Each time someone preregisters for a tournament, a small message is sent to the queue in fire-and-forget style. A worker process is continually pulling messages from the queue and acting on them, in this case updating the event table's prereg count and rating prediction fields. The whole process takes about 5-10 seconds, so the search criteria are correct very soon after the preregistration happens.

This pub-sub system will be super-useful for decoupling cause and effect in FRED's processes, and for deferring costly processing to the background, to preserve front end performance.