Hello,
My database, used as a backend for a web application, gets an average of 5-10 selects per
second, as well as inserts every few seconds, and updates even less often. With the
queries being fairly well optimized, and the most commonly used table having no empty
blocks in the data file (concurrent select/inserts), I would expect the dual
PIII/700mhz/512mb to stand up pretty well. I use the LOW_PRIORITY keyword with most of
the update statements to speed up the flow of select statements.
And the server does stand up very well, with load averages seldom over 0.50, and using
show processlist I've been unable to detect any table lock conflicts at all. It stands up
well, that is, only until I run my nightly log analyzer cron job. I run it with a niceness
of +20, and it takes up less than 5% cpu and less than 2% memory on average (from what
I've seen in top). But ten seconds after I've started it, Mysql has completely buckled,
showing a processlist full of locked tables and hanging selects. Even if I abort the
analyzing job after half a minute, it's too late. Mysql is hanging and doesn't recover
until I stop the web server and wait a minute or two for it to process the hanging
queries.
I realize I could solve the problem easily by doing the analyzing on a different machine,
but I'm interested in why this fairly simple program should have such an impact on Mysql.
Is the problem lack of memory? Lack of cpu power? Are there any configuration changes I
could do that would help?
I'm guessing from how little it takes to screw things up that my application is on the
brink of buckling under the load anyway, and will do so if the traffic increases any
more. What should I concentrate on when I go through the queries to optimize them
further? Should I go for execution speed only, or are temporary tables an issue? (Some of
the queries use temporary tables, would eliminating those make a big difference?)
Any help/advice/explanations would be appreciated.
Thank you,
Jon Valvatne
