I think usually of MySQL as a simpler alternative to more feature rich RDBMS’s like Postgres. However recently I listened to an interview with Brian Moon, the author of Phorum which is the oldest PHP and MySQL based forum software. The interview was very cool and demonstrated that you can do a lot if you know your software. I would recommend to everyone who uses MySQL to listen to the interview. The slides are available from Brian Moon’s blog, or below, embedded via SlideShare for your convenience.
As a side-note: in his talk he uses yet an other name for the dogpile effect: cache stampede. From the slides it seems that they are using the solution detailed in the first point of my post (separating out the update in a separate process and running it on a schedule).
Update: In the first presentation, instead of SQL_COUNT_FOUND_ROWS, the correct syntax is SQL_CALC_FOUND_ROWS.