I'm looking for a little help working through some database scaling issues with #WriteFreely.
On our Write.as instance, we're up to 4.25 million rows in our `posts` table, and it's causing problems for some individual blogs with 9,000+ posts.
I have an idea of what some bottleneck queries are. But wondering if there are easy db optimizations we might make (indexes, etc.), and if anyone can help identify what they might be.
Boosts appreciated!
It might not be a quick fix, but spinning off older entries into their own quasi-archive tables could help, especially if they're ones that are rarely accessed. You'd need a routing mechanism somewhere to detect when to jump from the main table to the archives.
And do you have something like Redis in place for caching common / recent queries? That could help for material that's frequently accessed but doesn't change often.
@info Yeah, definitely think splitting up the table could make sense, if I can figure out what lines to do that along.
I don't have Redis in place, but am using Varnish on the frontend, which has generally helped reduce database load across the system -- it's just that this blog in particular can still run slow.
Always the hardest part with DB design, segmenting records.
If you have traffic stats on what gets the most requests? It could be a scenario where the most recent material stays in the main or live table(s), for kind of obvious reasons.
The older but high activity segments get their own table(s) with some advance caching.
Low traffic / nobody ever looks at it? Archive. Not worth the bandwidth or overhead.
Matt Baer
Red Ogre Review