Most of them were on overloaded shared hosting boxes. I know this because I worked for hosting providers in that era.
When I spun up YAF.Net on an AWS IIS server, with a same-machine backend of SQLEXPRESS, it ran like a champ for years with hardly a blip. 16 bucks a month.
It wasn't ever really snappy, per se, but at least it was consistent and didn't bog down.
I don't remember which software it was but one of them would show, in the footer, a count of MySQL queries run. My memory tells me it would usually be in the 30-something range, maybe 50-something. It could have been 1!
It might have been Simple Machine Forum (SMF). The footer has the number of queries and number of seconds it took to generate the page (in my case, their community forum index page used 9 queries and took ~0.3 seconds to generate).