Thanks for your quick reply. My database is nearly 700 megs alltogether, 320 of those
megs are the main table which also receives the most queries.
The log is currently around 700 megs as well, which very obviously is a bad idea to
analyze every night... but as I said, I was looking more for an explanation than a
solution. I will move the log analyzing to another machine, which should clear the
problem up temporarily.
The problem being disk usage sounds very reasonable, and explains alot, but I would
like to avoid hardware upgrades. Since the little sorting being done in my queries is
not done on indices, I would guess your suggestion of optimizing the indices is
useless in my case. Is there anything else that can be done? I have noticed a couple
of the most common queries use temporary tables.. are these temporary tables on disk
or in memory? I seem to remember having read somewhere that using SQL_SMALL_RESULT
makes temporary tables go in memory. Is that correct, and will it help me?
Jon
PS: I apologize for sending to the wrong list. I guess I let the name change from
"developer" to "internals" confuse my conception of which topics are appropriate.
Thanks for setting me straight.
----- Original Message -----
From: "Benjamin Pflugmann" <[EMAIL PROTECTED]>
To: "Jon Valvatne" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, March 17, 2001 9:27 PM
Subject: Re: causes for slowdowns/lockups?
> Hello.
>
> Please note that this kind of question doesn't belong to this list, it
> is about developing MySQL itself. [EMAIL PROTECTED] 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, [EMAIL PROTECTED] 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.
> >
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
