No, it is not disk space.. it is not table corruption either..
it is server confusion, with no diagnostic information output.
Symptom again: hang on straight forward queries involving a total
index scan, with either count(*) or distinct(*), usually
stuck with 100% cpu use of the thread in 'sending data' phase,
temporarily cured by server restart and/or table reorganization.
-Justin
On Thu, Mar 08, 2001 at 07:44:51AM -0600, Gerald L. Clark wrote:
> Justin wrote:
> >
> > I have a puzzle..
> >
> > Frequently, about, weekly? our data server gets into a state
> > where
> > select distinct(keyfield) from table
> > hangs in the 'sending data' phase, or the 'sorting result' phase,
> > the client is left waiting for the result, no rows are returned,
> > no errors are in the sql server error log.
> >
> > I've had to write a monitor to kill these off as they show up.
> > otherwise they accumulate, use all CPU and never die.
> >
> > I used to think the fix was to take the server offline and isamchk
> > them, or run 'optimize table' from mysql> but today I've found
> > out that that is NOT the problem..
> >
> > I found this because after it happened, I took a file copy of the
> > problem table, to table_new
> > then did an isamchk on it -- no errors!
> > So then I did the same query on the new copy
> > select distinct(keyfield) form table_new
> > instant success!
> > select distinct(keyfield) form table
> > still hangs consistently!
> >
> > On disk,
> > cmp table.ISD table_new.ISD
> > cmp table.ISM table_new.ISM
> > shows they are bit for bit identical!
> >
> > So the server has itself is confused, and the way to fix it
> > is to restart mysqld (although that does not always work), the
> > problem might occur with the same query on another table, or not
> > go away until the table is 'alterd' in some no-op type way...
> >
> > file descriptor use is heavy but below 1024 limit:
> > ls /proc/NNN/fd | wc
> > 491
> >
> > This is version 3.22.32 - the binary FROM MYSQL.COM ..
> >
> > max_connections 100
> > key_buffer=300m
> > max_allowed_packet=1m
> > table_cache=300
> > sort_buffer=4m
> > record_buffer=2m
> > tmp_table_size=2m
> > thread_stack=128k
> >
> > -Justin
> >
> Next time it hangs, check your disk space.
>
> ---------------------------------------------------------------------
> 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
--
Justin Beech http://www.dslreports.com
Phone:212-269-7052 x252 FAX inbox: 212-937-3800
mailto:[EMAIL PROTECTED] --- http://dslreports.com/contacts
---------------------------------------------------------------------
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
