Mysql optimization on 2GB VPS

Portal Home > Knowledgebase > Articles Database > Mysql optimization on 2GB VPS

Posted by ashras99, 12-13-2010, 08:59 AM
My VPS plan allows me 2GB RAM on CENTOS 5.5 x86_64 and cpanel 11.28 I am encountering high CPU loads spikes but memory usage not gone over the 400MB. I am posting my current MYSQL settings, please suggest which setting to optimize, to reduce the server load. My total database size is approx 1.5GB Last edited by ashras99; 12-13-2010 at 09:06 AM.
Posted by Gunzour, 12-13-2010, 11:27 AM
Why do you think MySQL settings are the issue? Is your server running anything besides MySQL? Have you run vmstat during a CPU spike? Is anything in your slow query log? You will need to determine where the bottleneck is first.
Posted by ashras99, 12-13-2010, 11:35 AM
May be those are not the culprit, but i need to optimize those too. Seocndly vmstat report show high CPU usage of mysql at the time of spike. So, not a bad idea to optimize the mysql.
Posted by Gunzour, 12-13-2010, 12:28 PM
Can you post the vmstat output?
Posted by ashras99, 12-13-2010, 12:55 PM
this is recent output after some optimizations... Output from vmstat: procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 19 0 0 1612424 0 0 0 0 1925 187 0 33 5 2 86 7 0
Posted by Gunzour, 12-13-2010, 07:40 PM
vmstat should be run at an interval, for example 5 seconds, and the first line of output ignored. Here's an example from an idle server: What I am looking for are signs that you are swapping memory (high values for si/so), waiting on a slow/busy disk (high value for wa), or something else. The other thing that is useful for troubleshooting performance is running SHOW STATUS on your database, and seeing if you are hitting any of the limits that you can change in your config file. Are you using InnoDB or MyISAM? There isn't one right answer to your database config, you need to understand your use case and adjust your settings appropriately.
Posted by ashras99, 12-13-2010, 07:42 PM
I send the setting which i receive through mail.. when server load exceeds and get a warning... My database contains both InnoDB and MyISAM
Posted by ashras99, 12-15-2010, 02:51 PM
while doing high intensive memory hungry task... i got an error - MySQL server has gone away please suggest which value to change?
Posted by winlinuxadmins, 12-24-2010, 09:37 AM
You need to increase the buffer cache and timeout value in your my.cnf file and then restart the mysql
Posted by ashras99, 12-24-2010, 09:40 AM
you are talking about key_buffer and wait_timeout or some other?