We have been struggling with high loads and slow responsiveness on our forum. The number of bots accessing our site has increased exponentially over the past twelve months. I have sought for ways to resolve the issue unsuccessfully. With the help of Grok, I finally found the solution. We had not set innodb_buffer_pool_size in my.cnf, so it defaulted to 128 MB. After checking to see how much free memory we had (11 G), I increased it to 8 GB. Our site is now very responsive and even with the increase of bots, responds rapidly.
Previously, we had engaged support at our hosting company, and they told us the vendor needed to improve their queries or we needed to find a different software to use.
Making this one change, changed performance dramatically.
I may be the only one who was unaware of this setting, but I'm posting it here for others who may be struggling with similar issues. If you give the db room to breathe, it will function much better. Obviously, the number you set it to needs to be reasonable for the amount of free memory that you have.
