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
