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
