MySQL/vserver optimization

DON'T post new tutorials here! Please use the "Pending Submissions" board so the staff can review them first.
Post Reply
User avatar
bad_brain
Site Owner
Site Owner
Posts: 11636
Joined: 06 Apr 2005, 16:00
19
Location: In your eye floaters.
Contact:

MySQL/vserver optimization

Post by bad_brain »

I've had some problems on one of my servers lately, it was pretty tricky to figure it out so I thought I will give some tips. the problems started when I got notifications that several cronjobs couldn't be started because of a lack of available memory...which was kinda weird because the RAM usage was always within the proper parameters, so in theory enough resources should have been available.
on many vservers there is a nifty script available named "user_beancounters", it can be found in the /proc directory. this script reads different logfiles and logs the resource usage of the vserver:
http://code.suck-o.com/156

the "failcnt" row is the most interesting one because it shows how often the available resources have been exceeded...as you can see there are plenty of failcounts in "dcachesize". the job of the "dcache" is to make sure that important vserver processes have enough RAM available, but if the cache size is exceeded the system has to swap on HDD which is very slow and so causes failures.

I have been working on the MySQL config in the last time a lot, so this was the candidate I suspected to be the troublemaker, there is a really nice script availabe to check your MySQL server configuration name "tuning-primer.sh", you cn get it here:
http://www.day32.com/MySQL/
it will give you an output like this:
http://code.suck-o.com/157
(this is only an example output and not from my server).

in my case I had set the table_cache value too high so MySQL had an over-allocation of memory.
feel free to submit your output and I can recommend optimizations...of course this works on every MySQL server, not just on vservers... :wink:

Post Reply