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
