Matt BaerAug 11, 2023

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!

Show threadericAug 11, 2023
Which database do you use?
Show threadMatt Baer
@tweets MySQL with innoDB
Aug 11, 2023 at 8:45pmWeb
Show threadericAug 11, 2023
I recently dealed with a more than 8 million row full text innoDB db. A good starting point is MylSQL tuner:
https://github.com/major/MySQLTuner-perl

Most import settings in context of scaling issues are index and innodb_buffer_pool_size. First is a question of reading/writing ratio, second a question of hardware. Maybe these are some starting points. :)
GitHub - major/MySQLTuner-perl: MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. - major/MySQLTuner-perl

GitHub