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!
@matt Unfortuantely I know nothing about your stack or your code (other than it being MySQL-backed). However, here's the general troubleshooting lists of things that make a database-backed web application slow:
1. Too many queries: app is pulling lots of things from the DB constantly that could be cached, or doing joins in the app code
2. Unoptimized requests: some DB requests need better queries/better data structure/indexes
...
@matt ... both of the above could be found via query logs and metrics.
3. Inadequate hardware: if your DB is 150GB and you have 1GB of RAM, you will always be trying to fight performance.
4. Locking: app is designed so that some requests block other requests.
5. Misconfiguration: DB, platform, or other parts of the stack are configured in a way that limits throughput.
...
Matt Baer
Berkubernetus