I've made a bit of progress since this last e-mail.
Put back in the debian default my.cnf. mysql processes are now about 6-8% of memory, apache is around 3.5% per thread. However, the server is still swapping out, crashing etc. seemingly at random every couple of days. We're _only_ running drupal on it, postfix is installed but only doing sendmail sends from contact.module, not receiving anything, and it's otherwise a fairly vanilla xen/vds/debian sarge installation.
I also installed the blockcache.module to see if that would help - this has definitely reduced queries per page by about 20 (still roughly 200-250 queries per page but that's 10% so not bad). There really doesn't seem to be any obvious trigger to the crashing out though - it'll be fine at high traffic levels (for us), so I'm wondering if something is going into a loop etc.
As always, any help much appreciated.
Nat
Im running only Drupal 4.7.3 on a 512mb ram VDS debian sarge mysql 4.1 apache2 php4
Memory usage is generally fine overall, cpu can be a bit high sometimes which I think is down to a lot of 404 errors from the old site. We want to eliminate some of those 404s first, then switch off drupals handling of 404s altogether, Im not sure this is our mysql memory problem though.
Running top u mysql just after /etc/init.d/mysql restart mysql processes are between 4.4 and 5% of ram, then I can watch them creep upwards all the way to 20% each. Eventually this slows things down, the server starts swapping out, then both apache and mysql crash and require a restart, then it starts over again.
This may not be drupals fault, but Id appreciate any advice you can offer with it.
Basic site information:
Around 8000 nodes and 110,000 comments, private message table is around 10mb. Average traffic is 10 logged in users and 20 guests on line rarely more than double that, often less. Forums index takes about 300 queries due to lots of subforums (c. 45) but not noticed any other pages that are significant resource hogs by themselves.
Were using cck, panels, imagecache, contemplate and views a lot. This has been a persistent problem for months now, which I originally put down to cache table overhead (sometimes megabytes of overhead within a few minutes of flushing, have dbmaintenance optimising it every hour now).
Im using a fair amount of contrib modules, so am wondering if it could be down to one of those notwithstanding my mysql settings. Everything is pretty much up-to-date by the way using almost entirely 4.7 versions except for a couple running CVS where theres not an official 4.7 branch.
Full list of contrib. modules below, devel module is generally off. Also making use of free tagging, forums, tracker and other core modules. Forum_get_forums runs pretty slow judging by devel modules output, and administering taxonomy access requires a php memory limit of around 60mb due to free tagging, so Im wondering if its something to do with one of those.
bbcode
tagadelic
cck
flatforum
pathauto
taxonomy_access
comment_mover
image
privatemsg
contemplate
imagecache
quicktags
views
content_moderator
imagefield
quote
db_maintenance
nodewords
scheduler
devel
panels
smileys
For completeness, heres my my.cnf settings:
skip-locking
key_buffer_size = 20M
max_allowed_packet = 1M
table_cache = 384
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 4M
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 4M
max_connections = 55
max_user_connections = 55
Any help much appreciated. This problem started happening when I had very few modules installed (mainly flatforum/smileys/cck/views/taxonomy access/privmsg/quicktags/bbcode).
Thanks!
Nathaniel