BBS using MySQL has millions of posts, and the performance of the
forum has dropped significantly. How should it be optimized?
reddit / bbs

----------------------------------------------------------------------
Ok-Role5775 · 2023-10-17 14:35

Please tell me, a BBS using MySQL has millions of posts, and the
performance of the forum has dropped significantly. How should it be
optimized?

Because it is a private forum, non-profit, and does not have a
professional team, we hope to have the lowest cost solution possible.

thank you all!

----------------------------------------------------------------------
ahandle · 2023-10-17 14:51

Check and monitor storage space on the disk.

----------------------------------------------------------------------
Call_Me_Mauve_Bib · 2023-10-17 15:47

Is your code making too many connections? Are your data cached
reasonably?

You may have to enable slow query logging, then look for slow queries
that correspond to poor user experiences. Are your queries resulting
in a lot of cache misses?

Then investigate those one by one.

----------------------------------------------------------------------
Call_Me_Mauve_Bib · 2023-10-17 15:47

Is the disk okay? If its a 5 MB/s SCSI disk from 1992 it might be time
to upgrade.

----------------------------------------------------------------------
m00dawg · 2023-10-17 17:41

I'm a MySQL DBA by day but to avoid getting too deep into the weeds,
here's some initial thoughts:

MySQL heavily favors RAM if using the modern default storage engine
(InnoDB). Hop on the 'mysql' console and run "SHOW ENGINE INNODB
STATUS\G" and then take a look at your hit rates (the ####/1000
things). If they are say below 990/1000 it can indicate your dataset
is beyond your current innodb_buffer_pool_size which should be as
large as possible up to your data size (and secondarily up to your
index size, which can be found via information_schema or
performance_schema queries).

As /u/Call_Me_Mauve_Bib mentioned check the slow query log (if not
enabled, enable it) and see if perhaps your are running frequent
queries which are not using indexes or might be doing other things
(like LIMIT x,y which can cause poor performance when users might be
going deep into pagination).

If your writes are slow, that's where the disk I/O comes into play. By
default MySQL is fully ACID compliant, meaning it has protections from
powerloss. You can relax these (at some risk) to lower the I/O.

----------------------------------------------------------------------
temotodochi · 2023-10-17 17:41

Do you have indexes in your database? How long does one query take?
There are some tools available that can measure the performance of
mysql database and give out some ideas what to use as index. Also ask
GPT, it knows sql quite well.