From: Benjamin Pflugmann
Date: March 17 2001 8:27pm
Subject: Re: causes for slowdowns/lockups?
List-Archive: http://lists.mysql.com/internals/618
Message-Id: <20010317212730.C24204@spin.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hello.

Please note that this kind of question doesn't belong to this list, it
is about developing MySQL itself. mysql@stripped is a more
appropriate forum for your question.

From what you tell, the problem is disk usage. Your log analyzer keeps
the disks busy and therefore messes MySQL up. And you are right,
regarding disk usage, your system is not too far from the edge, I
would say.

That doesn't necessarily mean that you have to upgrade your disks,
often you can circumvent that with other measures.

You did not say how large your database and your logs are. If more
memory can help to keep most stuff in memory, I would go this
way. Which also may help is to optimize tables with (my)isamchk:
ordering the indexes (-S) and the data according to the most used sort
(-R#) help reduce disk seeks and can result in a speed increase of
factor 3 or more (_can_, your mileage may vary).

Bye,

        Benjamin.


PS: Reply-To set accordingly

On Sat, Mar 17, 2001 at 08:04:55PM +0100, jonva@stripped wrote:
> 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.
>