MySQL tweak that reduced our page load times by 80%
We saw a sudden increase in traffic on Screener over the last few days. This made the whole website a little slow.
Yet, all the core vitals looked fine: - Our 32 gb ram was at 40% utilisation - Our 8 core CPU showed a system load of 4-5. - Disk space utilisation was 70%.
I increased the number of web workers from 8 to 12. But the website still appeared slow. I tried to look into how slow it was - and for how long. Google Analytics provided some details. The server response times had been increasing consistently There was a consistent and meaningful increase in the server-response time.
I reached out to Aniruddha for some help over screen sharing. Just sounding off the problem often provides some insights.
Aniruddha asked me to run htop command. It showed MySQL was using a lot of CPU. We ran SHOW PROCESSLIST on MySQL console to inspect the current query queue - nothing abnormal.
Aniruddha then guided me to check the innodb_buffer_pool_size value in MySQL. It turned out to be the exact bottleneck.
We used the default MySQL configuration. The defaults set the innodb_buffer_pool_size to 128 MB. I increased it to 16 GB - and wooo - the website was flying now. It was faster than ever before.
Technical details innodb_buffer_pool_size defines the cache size for tables and indexes. It is recommended to set it to 50% of the available RAM.
Epilogue This little fix reduced the page load times by 80%. It also reduced the runtime of various other scripts. Do let me know if there are other such magic settings :).
