heavy MySQL use on linode128

@mnl:

hey,

i've seen lots of talk about tweaking your mysql settings for low memory usage.. but all of them are for sites that don't use the database that much.

is there anything i can do to make mysql run more efficiently for a server that uses the database all the time? right now i have a few fairly large tables that are accessed frequently. they are all properly indexed and optimized.. but when i make a query on them my load average spikes to 15 which seems crazy.

according to top during one of these database requests: my cpu usage peaks at about 20% user and 50% system, load average ranges between 5 and 15 and my memory usage is at a constant 120mb out of 128mb.

would more memory and a faster (maybe dedicated) processer solve this? or is it more of a configuration issue?

the app that seems to be causing all the problems is phplist .. any tips for running phplist?

thanks,

matt.

You are probably hitting the io limiter. You should check your io tokens while running your SQL query, by doing 'cat /proc/io_status'. If your tokens go to zero during the query, you're hitting the io limit.

As far as how to solve this, I think someone with mysql expertise would have to chime in. I don't know how to go about breaking up mysql tables into more manageable sizes or what settings might be tuned to reduce the I/O usage when running your query.

make sure log-bin is commented out in your /etc/my.cnf file.

uncomment/add: skip-innodb (provided you dont use InnoDB tables, I dont)

Stop MySQL then backup your MySQL data directory. After this go into the data directory and delete all the IB log file eg ibdata0 ibdata1…

Now Restart MySQL.

I bet your load-avg will be a lot better now.