in reply to OT: Scalable web application architecture
If you've got a "large" database on a "small" machine, you'll be wasting your time attempting to optomise it. Get another machine!
memory, is an order of magnitude faster than IO, so, if you can, cache your db in memory! Also indexes, create indexes for the most commonly hit items, or at least revise your indexing strategy
You say its an online reservation system, then say you cache your results. This prima facie sounds like a bad design IMO. I've got in my mind something like a hotel reservation system, which is an OLTP system. It doesnt make sense to me to cache results, as they have the chance of being immediately out of date.
That being said, if you're caching your result set, it is probably ok :-).
Have you thought of prerunning the most expensive queries periodically (and even perhaps on an offline copy) and using Cache::Cache ? its super quick and adds only a couple of lines to each module.
Its a work around for sure to a performance based problem, but its something you could get a good lot of milage from.