You may be looking too far for your problem...
Your join_buffer and sort_buffer are probably a little large if you
have man concurrent users.
Must you use an old version of mysql?
You should analyize or post EXPLAIN of your query so that you/we
can see that it is properly indexed.. you should be able to
do many joins on very large tables and have results that respond
in fractions of seconds unless you are missing some indexes
or are trying to do some LIKE '%value%'.
if it is writing to temp that means it has to create temporary
tables to complete your query, this can be the case if your
are missing indexes and it is having to search all tables to
complete your join.
Neil Streeter wrote:
>
> Hi all,
>
> I've a bit of an issue with mysqld -- errr is it?
>
> Here's the configuration....
>
> Dell PowerEdge 6300
> - 1GB ram (actually 2GB but no bigmem support at the moment - I'll get to
> that)
> - RH linux 6.1 running 2.2.12-smp (needs updating, I know - but hard to find
> good 'down time')
> - 4 10gig UW-SCSI drives - in hardware raid 5 - AMI raid...
>
> Running:
> Apache -- 1.3.4 (Unix) W/ DBI and CGI.pm
>
> Mysql -- Ver 3.22.21 for pc-linux-gnu on i686 with options:
> wait_timeout=31536000
> join_buffer=10M
> key_buffer=64M
> sort_buffer=8M
> table_cache=81
> tmp_table_size=64M
> --big-tables
> -----------------------------------------
> Here's the problem / questions
>
> --- This 'snuck' up on us as usage increased, we never noticed it before....
>
> It seems that certian queries in mysql make the daemon write to /tmp
> table... This process takes a long, long, time.... and does not generate any
> errors in the log - actually, If you're patient enough, it will complete....
> The processlist plainly shows that it's writing to /tmp.... while running
> top, and generating one of these queries, I notice that a mysql thread (or
> two) jumps to the top taking up to 50% (or as much as is available) of the
> CPU - and quite a bit of mem... More than it seems it should.... What causes
> a querry to be sent to a tmp table?
>
> Realisticly, it seems that this querry should be able to run in no more than
> 5 seconds.... But we're talking minutes, here.... It's just a join querry,
> and the tables are really not 'that big'....
>
> It also seems that when one of these queries is running, quite a few other
> mysql processes become 'locked'... What might cause this?
>
> I've been doing quite a bit of reading on mysql and the linux kernel... I've
> found a couple of possible answers to my own question, but I'd really like
> to hear form others who may have experianced the same situation.
>
> One possible answer comes from the linux specific segment of the docs, and
> speaks of a:
> Version 2.2 kernel "feature" that penalizes a process for forking or cloning
> a child in an attempt to prevent a fork bomb attack. This will cause MySQL
> not to scale well as you increase the number of concurrent clients. On
> single CPU systems, we have seen this manifested in a very slow thread
> creation
>
> The other possiblity I found deals with Parameters in
> /proc/sys/vm/bdflush... I don't know a lot about these, so any help here is
> mucho appreciated.... It seems though, that modifying the ??nfract?? (is
> that right?) paramater may help (on systems with lots of RAM) - as disk IO
> will be buffered up to the limit specified??? thus causing 'slow' writes???
> Am I on here, or way off???
>
> Also, I'm considering VALinux 7.0.1 (modified redhat) which has built in
> bigmem support, and unbuffered diskIO (rawIO)... I would also immediatly
> upgrade to kernel 2.4.x as I understand that the "anti fork-bomb feature" is
> fixed in the 2.4 kernels... does anyone have any other suggestions, possible
> fixes (to current kernel)? or ideas?
>
> Please help, I've almost no hair left :-0
>
> ns
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
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
