I've done some experimenting with the config variables based on the disk usage theory,
and after increasing the table cache, Mysql handles the log analyzer being run much
better. It still buckles under after a while, but now after minutes instead of
seconds, and it's able to recover after I stop the log analyzer.
Many of the queries are complex, made with application convenience in mind rather than
performance (the traffic was never expected to rise to this level). When I can find
the time, I'll redesign the application and optimize the queries, and things should be
fine. Right now I feel quite a bit safer though, knowing more exactly what the problem
is. I'll move the log analyzer to a different machine and try the myisamchk
optimizations you suggested, and the server should survive for another month.
Thanks for all the help,
Jon
----- Original Message -----
From: "Benjamin Pflugmann" <[EMAIL PROTECTED]>
To: "Jon Valvatne" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, March 17, 2001 10:27 PM
Subject: Re: causes for slowdowns/lockups?
> Hello.
>
> On Sat, Mar 17, 2001 at 10:05:25PM +0100, [EMAIL PROTECTED] wrote:
> > 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.
>
> Well, in this case 1GB wouldn't hurt. 512MB is not enough to keep the
> main stuff (plus indexes) in memory and let enough space for the OS to
> do effective caching. But I agree, I would avoid upgrading a
> production system if it is avoidable.
>
> [...]
> > 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.
>
> Sorting the data file resp. indexes doesn't only speed up sorting. It
> speeds up retrievel, too. Best with 'range' selections, because the
> next needed value was maybe in the same block and is already in
> memory. Also disk usage in smaller (because less 'holes' are left in
> the files), but that you get with isamchk alone (without sorting),
> too. There are some other side-effects, I don't want to go in detail
> to now.
>
> If you have a spare test system, just try my suggestion and see with
> vmstat or a similar tool if it helps with your application. It is
> certainly worth a try.
>
> > 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 don't know exactly from mind. IIRC, they are in-memory except if
> they exceed a certain size. There should be a config variable for that
> limit, but I am not positive about that.
>
> > 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?
>
> I remember the same thing. Why don't you just have a look in the manual?
>
>
>http://www.mysql.com/doc/manual.php?search_query=SQL_SMALL_RESULT&submit=Search&depth=0
> (and follow the link to the SELECT section)
>
> Some other pointers:
>
> - If you are not using latest 3.23.x you may want to consider
> upgrading. Between 3.22.x and early 3.23.x and nowadays were a lot
> changes improving speed and concurrency.
>
> - 5-10 queries per second is not that much, except if they are
> complex. You said you think they are optimized well, but could you
> nevertheless post the EXPLAINs for some of the common ones?
>
> - I am especially wondering about that 0.5 load, I would expect it to
> be far lower. Do you have any explanation for that (but maybe I am
> too used to dual processor systems meanwhile).
>
> Bye,
>
> Benjamin.
>
>
> [...]
> > > 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).
> [...]
> > > 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
