So my websites are getting quite a bit more traffic and it’s been maxing out my 4vCPU cloud server. Getting pegged at 100% sometimes for an hour straight. Before deciding to pay for a bigger server, I decided to see if I could make some performance tweaks. (already using caching, memcached, redis, etc.). MariaDB was clearly the culpret often maxing the CPU by itself.
I found that by adding this one line to MariaDB config, my CPU usage dropped from 100% down to about 65%
performance_schema = ON
Please add this line under [mysql] inside of /etc/mysql/mariadb.conf.d/50-server.cnf as a default. Massive difference in performance. I did some other tweaks as well, but this one was the game changer.
strange, because I’ve read this for example when to turn it off
While Performance Schema offers numerous advantages, there are situations where you might consider disabling it:
Minimal Resource Usage: In some cases, you may be operating a MySQL server with extremely limited resources, and the small overhead introduced by Performance Schema might be noticeable. Disabling it can free up some resources, but this should be weighed against the loss of valuable performance insights.
It is hungrier for RAM with this turned on. I had to cut back how many Gb I allocated to innodb_buffer_pool_size as it was hitting max limit (before never more than 70-80% ram usage. So I guess it’s a bit of a trade-off, much better CPU performance for some extra RAM usage.
Enabling performance_schema = ON does not improve database performance. This directive is used for monitoring and diagnosing performance issues, helping you optimize queries, configuration, etc., but it does not provide any performance improvement on its own.
Maybe your CPU performance gain is related to the restart required to apply the changes.
After further research, it seems you may be correct. Perhaps I didn’t previously see the benefits of whatever I had previously done until some spam attack stopped (typical daily on several of my sites).
However, this is what chatGPT says about it:
Why Enable performance_schema?
Better Query Analysis: Helps track slow queries, memory usage, and performance bottlenecks. Lower CPU Load Overhead: Unlike general_log, it provides detailed insights without significant performance impact. Detect Inefficient Queries & Index Issues: Works well with MySQLTuner and Query Profiling Tools.
So perhaps the fact that I’m using it instead of the general_log was the benefit… hard to be sure.
Edit: After some more research, it seems that general_log logs every SQL request to ther server, which performance_schema is more performance oriented, tailored to high traffic logging, and doesn’t log everything. When I set up my server, general_log must have been what it was using and turning on performance_schema turned it off (at least the way I enabled it, it did). My disk activity dropped massively too.
general_log is more detailed, but becomes too cumbersome when the traffic grows into millions of SQL hits a day. Now I’m only logging things like slow hits that take longer than 2s, and the significant events I might want to address.
Better Query Analysis: Helps track slow queries, memory usage, and performance bottlenecks.