Had a problem with a server that was running alot of traffic and doing a lot of logging and updating mysql tables. The CPU usage was through the roof and it’s an 8 core server with 30GB of RAM. The strange thing was that mysqld was using all the processing power but hardly any memory was being used.
You can check what is using all the CPU/Memory by running “top” from the command line. Ctrl-C to exit.
A quick fix to balance out the load was to add the following line to /etc/mysql/my.cnf (might be /etc/my.cnf on alot of servers)
innodb_buffer_pool_size=20G
20GB stands for 20 gigs of RAM so adjust this to suit your hardware specs. A rule of thumb I read was to use 70% of the total RAM but I would suggest using 70% of the free RAM on the system just to be safe, especially if your server isn’t only being used as a database server. So if you have a 4GB VPS with 2GB of currently free RAM use 1.5GB for the innodb buffer pool.
I would imagine this would work the other way round too so if you find RAM stats getting low, lower the pool size to free up memory.
