Last week I was contacted by my hosting partner that our website is exceeding server limits for Input/Output Usage.
I was told to optimize our website or they would shut it down. This website is our SMF forum.
Unfortunately the forum is already optimezed to the best of my knowledge, following these guidelines:
https://wiki.simplemachines.org/smf/Performance_enhancements
We are using SMF 2.0.18
PHP version 7.4
The host provides REDIS caching, so to use that I have implemented this mod:
https://custom.simplemachines.org/mods/index.php?mod=4119
The forum is not extremely big:
290245 messages in 12600 Topics by 1097 members.
What I need help with is this:
I see that the server I/O Usage goes wild for a period of time, but it also slows down but for reasons I cannot find: mind you there have been NO changes in mods or settings in the forum at the time. I also see no increases in visitors at the time.
See attached images for illustration.
I have checked the REDIS caching, to find that the caching is working, but the average cache used is around 1MB, which (according to my host) is VERY low and I have not been able to use more of it (tried by updating the ttl setting from 120 seconds to as much as 7200, but no difference)
I also checked my various MOD's, but they have been there for quite a while and I am not directly seeing any issues there...
(List below).
Since there are also some test forums on the same server I did close those down to make sure they are not causing it, but no difference.
So now I do not know what to check next. I could use some hints or pointers if possible...
Mods currently installed
View Only Boards 1.2c
Ignore Topics 2.3
AJAX Recent Topics 1.1
Auto Lock Old Topics 2.0
Custom Board Sort 1.0.2
Alternate User Posting 1.0.1
Group Attachment Limits 1.1.0
EU Cookie FJR 1.3fjr
Remove "Last edit" mod 0.2.5
Float BBCODE 1.0.2
Users Online Today 2.1
Board Index Unread Icon Fix 1.0
Spiders Don't Increase Topic Views 1.1.1
Search Focus Dropdown 1.6.1
Image Processing Memory Limit 0.1.2
Sortable Packages (and Installed Time) 1.3.5
Tidy Child Boards 2.0
Google Analytics Code 1.5.1
Simple .htaccess Cache Mod 2.7
Responsive Curve 1.1.0 (Unofficial Rev2)
Global Topics 2.2
More Spiders 1.2
PECL Redis Support 0.1FJR
SMF Optimus 2.6.6 [06.08.20]
Generic Avatars 1.11
Previous and Next Links for Page Index 2.1.204 FJR
Simple Audio Video Embedder 6.0
Voter Visibility 2.1(2.0.18)
Voter Visibility 2.1 FJR
YandexGarland 1.3.3
Downloads System 3.0.8b
TinyPortal 2.0.1
Image Attachment Thumbnail View 1.0
Post and PM Inline Attachments 7.01
Automatic Attachment Rotation (and Resize) 6.02
SMF-20-fix_birthdate (uninstall before 2.0.19) 1.0
Sadly, it sounds like your best course of action might actually be to search for a better host.
You can try to cut down DB usage by limiting access for bots and spiders, lowering logging settings (for example, don't log spiders), and so on - but at the core of it all, SMF is a UI for a database.
So SMF will cause DB traffic. ( Any and all AJAX stuff is likely to cause unnecessary calls though, so you may want to reconsider that AJAX recent topics mod for example.. )
I was just looking at the AJAX recent topics. Already set the update interval to every 60 seconds (was set to 10)
Logging of spiders was set to very high, set that to minimal now...
I also noticed that the reported value under load balancing in SMF is pretty High: seems to fluctuate around a value of 20 to 25...
anything AJAX will be "expensive"
Also, if you do not have full text or custom search index enabled, that will cause database spikes.
Ajax update is set to 60 secs, index is Custom index . Still the load does fluctuate. Today it is very low again, no changes on my end though...
Hard to tell without knowing which queries are the problems.
My first suggestion would be to enable & use MySQL's slow query log to ID long-running queries.
Ok, that's something. Not sure how to activated that though?
well, I asked my host and they pin pointed it for me within 5 mins.... :P
That does not help, my host keeps telling me it's something I should fix... but maybe with this specific question I can get a better answer.
I would first try to do it via the command line. Something obvious might pop up quickly.
You can also enable it by default by changing your config. Over time there are utilities that help you understand the log better. Worth being careful here, because you'll forget about it & then your log will just grow indefinitely. But good for a broader analysis of all long running queries.
To enable it via the command line for temporary usage:
https://www.a2hosting.com/kb/developer-corner/mysql/enabling-the-slow-query-log-in-mysql
To enable it in your config:
https://docs.cpanel.net/knowledge-base/sql/how-to-enable-the-slow-query-log-in-mysql-or-mariadb/
Helpful overviews - including using the log analyzer:
https://blog.toadworld.com/2017/08/09/logging-and-analyzing-slow-queries-in-mysql
https://mariadb.com/kb/en/slow-query-log-overview/
Even if your host refuses to diagnose the issue, they should at least be able to start the logging for you.
Once you have some queries in hand, you need to run explain plans on them & see what's going on...