List:General Discussion« Previous MessageNext Message »
From:Scott Olson Date:July 8 2002 4:19pm
Subject:mysql hanging
View as plain text  
 I've been having some problems with MySQL-4.0.1-2 on RedHat 7.2
 
 rpms
 ----
 MySQL-Max-4.0.1-2
 MySQL-4.0.1-2
 MySQL-shared-4.0.1-2
 MySQL-devel-4.0.1-2
 
 Occasionally the server enters into a strange state where it
 will refuse to do much of anything.
 
 Commands hang in the mysql client. Commands such as drop database,
 flush tables etc.  show processlist, show variables still work. 
 
 Output from the binary log typically shows the drop database command
 as the last item and then all further output to the binary log
 ceases.
 
 Binary error log snipit
 
 # at 299584
 #020704 15:52:30 server id  1
 	Query	thread_id=18874	exec_time=0	error_code=0
 SET TIMESTAMP=1025819550;
 INSERT INTO edrsystem(edrsystemid,fieldname,value) VALUES
 (15,'country','Canada'),(16,'metricsystem','yes'),(17,'timezone','MST'),(18,'daylightsavings','yes'),(19,'editpipetally','no'),(20,'viewfillstrokes','no'),(21,'viewholefill','no'),(22,'depthdatainc','.2'),(23,'nominaldepthinc','1.0'),(24,'pushtimes','000001000000010000000000'),(25,'rigsize','single'),(26,'lang','en'),(27,'padrig','no');
 # at 300013
 #020704 15:52:30 server id  1
 	Query	thread_id=18876	exec_time=0	error_code=0
 SET TIMESTAMP=1025819550;
 INSERT INTO hub(hubid,address,name) VALUES (2,'hub@stripped','Canadian
 Data hub');
 # at 300129
 #020704 15:52:31 server id  1
 	Query	thread_id=18887	exec_time=0	error_code=0
 SET TIMESTAMP=1025819551;
 DROP DATABASE IF EXISTS edr;
 # at 300187
 #020704 18:05:27 server id  1 	Rotate to mysql-bin.002pos=4
 # at 300227
 #020704 18:05:27 server id  1 	Stop
 

 A show processlist shows 3 instances of the drop database
 
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 18893 to server version: 4.0.1-alpha-Max-log
 
 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
 mysql> show processlist;
 +-------+------+-----------+------+---------+------+-------+-----------------------------+
 | Id    | User | Host      | db   | Command | Time | State |
 Info                        |
 +-------+------+-----------+------+---------+------+-------+-----------------------------+
 | 18887 | root | localhost | NULL | Query   | 3564 | NULL  | DROP
 DATABASE IF EXISTS edr |
 | 18888 | root | localhost | NULL | Query   | 2444 | NULL  | DROP
 DATABASE IF EXISTS edr |
 | 18890 | root | localhost | NULL | Query   | 2387 | NULL  | DROP
 DATABASE IF EXISTS edr |
 | 18893 | root | localhost | NULL | Query   | 0    | NULL  | show
 processlist            |
 +-------+------+-----------+------+---------+------+-------+-----------------------------+
 4 rows in set (0.00 sec)
 
 mysql> 
 
 Typically this hanging of the drop database command happens on the order
 of several times/day.  It is extremely repeatable. Annoying in the
 extreme. 
 
 Included in this email is output from lsof (in case of some file
 resource conflict) and output from show variables.
 
 Lately we had an interesting error message that may or may not be a
 clue:
 
 I tried to 'mysqladmin shutdown', but it blocked.  When I
 hit CTRL-C, it printed the following:
 
 dmurray@dmurray2:/home/dmurray> mysqladmin shutdown -u root
 Warning;  Aborted waiting on pid file:
 '/opt/pason/data/mysqldb/mysqld-master.pid' after 13 seconds
 

 I sent an email regarding a similar problem in May but I don't think any
 one had any thoughts back then either.  I've included the text of the
 original email below:
 
 May 29:
 
 ---------------------------------------
 
 I have an application database that I'm unable to drop after a period of
 running several applications against that are using the MySQL++ api.
 
 Wondering if anyone has seen this, if it's a known bug and/or if there
 is a fix.
 
 mysqladmin -uroot -p drop <db> and rm -rf /var/lib/mysql/<dbname> do
 succesfully remove the database it's just the mysql client has the
 problem.  drop database returns ok but the database is still there (both
 show databases and ls -al /var/lib/mysql/<dbname> say so).  
 
 Logging in to mysql with mysql -uroot -p to do the drop so it shouldn't
 be a user permission issue.  The directory permissions and file
 permissions within seem fine too
 -rw-------    1 mysql    mysql <dirname>
 
 The database is largely unpopulated (<10Mb) with about 50 tables (most
 of which are MyISAM with 2 or 3 HEAP tables).   
 
 Linux redhat 7.2
 
 rpms 
 ----
 MySQL-Max-4.0.1-2
 MySQL-4.0.1-2
 MySQL-shared-4.0.1-2
 MySQL-devel-4.0.1-2
 MySQL-client-4.0.1-2
 
lsof output
--------------
> mysqld_sa  3837    root  cwd    DIR        3,2    4096          2 /
> mysqld_sa  3837    root  rtd    DIR        3,2    4096          2 /
> mysqld_sa  3837    root  txt    REG        3,2  519964      81746 /bin/bash
> mysqld_sa  3837    root  mem    REG        3,2  485171     635289 /lib/ld-2.2.4.so
> mysqld_sa  3837    root  mem    REG        3,2     340     342057
> /usr/lib/locale/en_CA/LC_IDENTIFICATION
> mysqld_sa  3837    root  mem    REG        3,2      28     619030
> /usr/lib/locale/en_CA/LC_MEASUREMENT
> mysqld_sa  3837    root  mem    REG        3,2      53     863337
> /usr/lib/locale/en_CA/LC_TELEPHONE
> mysqld_sa  3837    root  mem    REG        3,2     132     863341
> /usr/lib/locale/en_CA/LC_ADDRESS
> mysqld_sa  3837    root  mem    REG        3,2      67     863340
> /usr/lib/locale/en_CA/LC_NAME
> mysqld_sa  3837    root  mem    REG        3,2      39     863338
> /usr/lib/locale/en_CA/LC_PAPER
> mysqld_sa  3837    root  mem    REG        3,2      59     537513
> /usr/lib/locale/en_CA/LC_MESSAGES/SYS_LC_MESSAGES
> mysqld_sa  3837    root  mem    REG        3,2     291     342058
> /usr/lib/locale/en_CA/LC_MONETARY
> mysqld_sa  3837    root  mem    REG        3,2   21407     863339
> /usr/lib/locale/en_CA/LC_COLLATE
> mysqld_sa  3837    root  mem    REG        3,2    2456     342059
> /usr/lib/locale/en_CA/LC_TIME
> mysqld_sa  3837    root  mem    REG        3,2      59     619024
> /usr/lib/locale/en_CA/LC_NUMERIC
> mysqld_sa  3837    root  mem    REG        3,2   11832     635475
> /lib/libtermcap.so.2.0.8
> mysqld_sa  3837    root  mem    REG        3,2   65997     635302
> /lib/libdl-2.2.4.so
> mysqld_sa  3837    root  mem    REG        3,2 5772268     847024
> /lib/i686/libc-2.2.4.so
> mysqld_sa  3837    root  mem    REG        3,2  173408     619031
> /usr/lib/locale/en_CA/LC_CTYPE
> mysqld_sa  3837    root    0r   CHR        1,3              66352 /dev/null
> mysqld_sa  3837    root    1u   CHR     136,11                 13 /dev/pts/11
> (deleted)
> mysqld_sa  3837    root    2u   CHR     136,11                 13 /dev/pts/11
> (deleted)
> mysqld_sa  3837    root  255r   REG        3,2    9634     361030
> /usr/bin/mysqld_safe
> mysqld-ma  3886    root  cwd    DIR        3,6    4096      31937
> /opt/pason/data/mysqldb
> mysqld-ma  3886    root  rtd    DIR        3,2    4096          2 /
> mysqld-ma  3886    root  txt    REG        3,2 5654541     425727
> /usr/sbin/mysqld-max
> mysqld-ma  3886    root  mem    REG        3,2  261460     635323
> /lib/libnss_files-2.2.4.so
> mysqld-ma  3886    root  mem    REG        3,2 1282588     635298 /lib/libc-2.2.4.so
> mysqld-ma  3886    root  mem    REG        3,2  485171     635289 /lib/ld-2.2.4.so
> mysqld-ma  3886    root  mem    REG        3,2  355236     635331
> /lib/libnss_nisplus-2.2.4.so
> mysqld-ma  3886    root  mem    REG        3,2  436784     635307
> /lib/libnsl-2.2.4.so
> mysqld-ma  3886    root    0r   CHR        1,3              66352 /dev/null
> mysqld-ma  3886    root    1w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma  3886    root    2w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma  3886    root    3u  IPv4    1281535                TCP *:mysql (LISTEN)
> mysqld-ma  3886    root    4u  unix 0xcfe6d540            1282205
> /var/lib/mysql/mysql.sock
> mysqld-ma  3886    root    5w   REG        3,6  300187      31939
> /opt/pason/data/mysqldb/mysql-bin.001
> mysqld-ma  3886    root    6u   REG        3,6      38      31940
> /opt/pason/data/mysqldb/mysql-bin.index
> mysqld-ma  3886    root    7w   REG        3,6    8745      31941
> /opt/pason/data/mysqldb/mysql-slow.log
> mysqld-ma  3886    root    8r   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma  3886    root    9u   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma  3886    root   10r  FIFO        0,0            1282210 pipe
> mysqld-ma  3886    root   11w  FIFO        0,0            1282210 pipe
> mysqld-ma  3886    root   12u  unix 0xc0e47560            1580579
> /var/lib/mysql/mysql.sock
> mysqld-ma  3886    root   13u  unix 0xc767f580            1580668
> /var/lib/mysql/mysql.sock
> mysqld-ma  3886    root   48u  unix 0xc8f70580            1579236
> /var/lib/mysql/mysql.sock
> mysqld-ma  3913    root  cwd    DIR        3,6    4096      31937
> /opt/pason/data/mysqldb
> mysqld-ma  3913    root  rtd    DIR        3,2    4096          2 /
> mysqld-ma  3913    root  txt    REG        3,2 5654541     425727
> /usr/sbin/mysqld-max
> mysqld-ma  3913    root  mem    REG        3,2  261460     635323
> /lib/libnss_files-2.2.4.so
> mysqld-ma  3913    root  mem    REG        3,2 1282588     635298 /lib/libc-2.2.4.so
> mysqld-ma  3913    root  mem    REG        3,2  485171     635289 /lib/ld-2.2.4.so
> mysqld-ma  3913    root  mem    REG        3,2  355236     635331
> /lib/libnss_nisplus-2.2.4.so
> mysqld-ma  3913    root  mem    REG        3,2  436784     635307
> /lib/libnsl-2.2.4.so
> mysqld-ma  3913    root    0r   CHR        1,3              66352 /dev/null
> mysqld-ma  3913    root    1w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma  3913    root    2w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma  3913    root    3u  IPv4    1281535                TCP *:mysql (LISTEN)
> mysqld-ma  3913    root    4u  unix 0xcfe6d540            1282205
> /var/lib/mysql/mysql.sock
> mysqld-ma  3913    root    5w   REG        3,6  300187      31939
> /opt/pason/data/mysqldb/mysql-bin.001
> mysqld-ma  3913    root    6u   REG        3,6      38      31940
> /opt/pason/data/mysqldb/mysql-bin.index
> mysqld-ma  3913    root    7w   REG        3,6    8745      31941
> /opt/pason/data/mysqldb/mysql-slow.log
> mysqld-ma  3913    root    8r   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma  3913    root    9u   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma  3913    root   10r  FIFO        0,0            1282210 pipe
> mysqld-ma  3913    root   11w  FIFO        0,0            1282210 pipe
> mysqld-ma  3913    root   12u  unix 0xc0e47560            1580579
> /var/lib/mysql/mysql.sock
> mysqld-ma  3913    root   13u  unix 0xc767f580            1580668
> /var/lib/mysql/mysql.sock
> mysqld-ma  3913    root   48u  unix 0xc8f70580            1579236
> /var/lib/mysql/mysql.sock
> mysqld-ma  3914    root  cwd    DIR        3,6    4096      31937
> /opt/pason/data/mysqldb
> mysqld-ma  3914    root  rtd    DIR        3,2    4096          2 /
> mysqld-ma  3914    root  txt    REG        3,2 5654541     425727
> /usr/sbin/mysqld-max
> mysqld-ma  3914    root  mem    REG        3,2  261460     635323
> /lib/libnss_files-2.2.4.so
> mysqld-ma  3914    root  mem    REG        3,2 1282588     635298 /lib/libc-2.2.4.so
> mysqld-ma  3914    root  mem    REG        3,2  485171     635289 /lib/ld-2.2.4.so
> mysqld-ma  3914    root  mem    REG        3,2  355236     635331
> /lib/libnss_nisplus-2.2.4.so
> mysqld-ma  3914    root  mem    REG        3,2  436784     635307
> /lib/libnsl-2.2.4.so
> mysqld-ma  3914    root    0r   CHR        1,3              66352 /dev/null
> mysqld-ma  3914    root    1w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma  3914    root    2w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma  3914    root    3u  IPv4    1281535                TCP *:mysql (LISTEN)
> mysqld-ma  3914    root    4u  unix 0xcfe6d540            1282205
> /var/lib/mysql/mysql.sock
> mysqld-ma  3914    root    5w   REG        3,6  300187      31939
> /opt/pason/data/mysqldb/mysql-bin.001
> mysqld-ma  3914    root    6u   REG        3,6      38      31940
> /opt/pason/data/mysqldb/mysql-bin.index
> mysqld-ma  3914    root    7w   REG        3,6    8745      31941
> /opt/pason/data/mysqldb/mysql-slow.log
> mysqld-ma  3914    root    8r   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma  3914    root    9u   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma  3914    root   10r  FIFO        0,0            1282210 pipe
> mysqld-ma  3914    root   11w  FIFO        0,0            1282210 pipe
> mysqld-ma  3914    root   12u  unix 0xc0e47560            1580579
> /var/lib/mysql/mysql.sock
> mysqld-ma  3914    root   13u  unix 0xc767f580            1580668
> /var/lib/mysql/mysql.sock
> mysqld-ma  3914    root   48u  unix 0xc8f70580            1579236
> /var/lib/mysql/mysql.sock
> mysqld-ma  3915    root  cwd    DIR        3,6    4096      31937
> /opt/pason/data/mysqldb
> mysqld-ma  3915    root  rtd    DIR        3,2    4096          2 /
> mysqld-ma  3915    root  txt    REG        3,2 5654541     425727
> /usr/sbin/mysqld-max
> mysqld-ma  3915    root  mem    REG        3,2  261460     635323
> /lib/libnss_files-2.2.4.so
> mysqld-ma  3915    root  mem    REG        3,2 1282588     635298 /lib/libc-2.2.4.so
> mysqld-ma  3915    root  mem    REG        3,2  485171     635289 /lib/ld-2.2.4.so
> mysqld-ma  3915    root  mem    REG        3,2  355236     635331
> /lib/libnss_nisplus-2.2.4.so
> mysqld-ma  3915    root  mem    REG        3,2  436784     635307
> /lib/libnsl-2.2.4.so
> mysqld-ma  3915    root    0r   CHR        1,3              66352 /dev/null
> mysqld-ma  3915    root    1w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma  3915    root    2w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma  3915    root    3u  IPv4    1281535                TCP *:mysql (LISTEN)
> mysqld-ma  3915    root    4u  unix 0xcfe6d540            1282205
> /var/lib/mysql/mysql.sock
> mysqld-ma  3915    root    5w   REG        3,6  300187      31939
> /opt/pason/data/mysqldb/mysql-bin.001
> mysqld-ma  3915    root    6u   REG        3,6      38      31940
> /opt/pason/data/mysqldb/mysql-bin.index
> mysqld-ma  3915    root    7w   REG        3,6    8745      31941
> /opt/pason/data/mysqldb/mysql-slow.log
> mysqld-ma  3915    root    8r   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma  3915    root    9u   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma  3915    root   10r  FIFO        0,0            1282210 pipe
> mysqld-ma  3915    root   11w  FIFO        0,0            1282210 pipe
> mysqld-ma  3915    root   12u  unix 0xc0e47560            1580579
> /var/lib/mysql/mysql.sock
> mysqld-ma  3915    root   13u  unix 0xc767f580            1580668
> /var/lib/mysql/mysql.sock
> mysqld-ma  3915    root   48u  unix 0xc8f70580            1579236
> /var/lib/mysql/mysql.sock
> mysqld-ma 23276    root  cwd    DIR        3,6    4096      31937
> /opt/pason/data/mysqldb
> mysqld-ma 23276    root  rtd    DIR        3,2    4096          2 /
> mysqld-ma 23276    root  txt    REG        3,2 5654541     425727
> /usr/sbin/mysqld-max
> mysqld-ma 23276    root  mem    REG        3,2  261460     635323
> /lib/libnss_files-2.2.4.so
> mysqld-ma 23276    root  mem    REG        3,2 1282588     635298 /lib/libc-2.2.4.so
> mysqld-ma 23276    root  mem    REG        3,2  485171     635289 /lib/ld-2.2.4.so
> mysqld-ma 23276    root  mem    REG        3,2  355236     635331
> /lib/libnss_nisplus-2.2.4.so
> mysqld-ma 23276    root  mem    REG        3,2  436784     635307
> /lib/libnsl-2.2.4.so
> mysqld-ma 23276    root    0r   CHR        1,3              66352 /dev/null
> mysqld-ma 23276    root    1w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma 23276    root    2w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma 23276    root    3u  IPv4    1281535                TCP *:mysql (LISTEN)
> mysqld-ma 23276    root    4u  unix 0xcfe6d540            1282205
> /var/lib/mysql/mysql.sock
> mysqld-ma 23276    root    5w   REG        3,6  300187      31939
> /opt/pason/data/mysqldb/mysql-bin.001
> mysqld-ma 23276    root    6u   REG        3,6      38      31940
> /opt/pason/data/mysqldb/mysql-bin.index
> mysqld-ma 23276    root    7w   REG        3,6    8745      31941
> /opt/pason/data/mysqldb/mysql-slow.log
> mysqld-ma 23276    root    8r   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma 23276    root    9u   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma 23276    root   10r  FIFO        0,0            1282210 pipe
> mysqld-ma 23276    root   11w  FIFO        0,0            1282210 pipe
> mysqld-ma 23276    root   12u  unix 0xc0e47560            1580579
> /var/lib/mysql/mysql.sock
> mysqld-ma 23276    root   13u  unix 0xc767f580            1580668
> /var/lib/mysql/mysql.sock
> mysqld-ma 23276    root   48u  unix 0xc8f70580            1579236
> /var/lib/mysql/mysql.sock
> mysqld-ma 23359    root  cwd    DIR        3,6    4096      31937
> /opt/pason/data/mysqldb
> mysqld-ma 23359    root  rtd    DIR        3,2    4096          2 /
> mysqld-ma 23359    root  txt    REG        3,2 5654541     425727
> /usr/sbin/mysqld-max
> mysqld-ma 23359    root  mem    REG        3,2  261460     635323
> /lib/libnss_files-2.2.4.so
> mysqld-ma 23359    root  mem    REG        3,2 1282588     635298 /lib/libc-2.2.4.so
> mysqld-ma 23359    root  mem    REG        3,2  485171     635289 /lib/ld-2.2.4.so
> mysqld-ma 23359    root  mem    REG        3,2  355236     635331
> /lib/libnss_nisplus-2.2.4.so
> mysqld-ma 23359    root  mem    REG        3,2  436784     635307
> /lib/libnsl-2.2.4.so
> mysqld-ma 23359    root    0r   CHR        1,3              66352 /dev/null
> mysqld-ma 23359    root    1w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma 23359    root    2w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma 23359    root    3u  IPv4    1281535                TCP *:mysql (LISTEN)
> mysqld-ma 23359    root    4u  unix 0xcfe6d540            1282205
> /var/lib/mysql/mysql.sock
> mysqld-ma 23359    root    5w   REG        3,6  300187      31939
> /opt/pason/data/mysqldb/mysql-bin.001
> mysqld-ma 23359    root    6u   REG        3,6      38      31940
> /opt/pason/data/mysqldb/mysql-bin.index
> mysqld-ma 23359    root    7w   REG        3,6    8745      31941
> /opt/pason/data/mysqldb/mysql-slow.log
> mysqld-ma 23359    root    8r   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma 23359    root    9u   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma 23359    root   10r  FIFO        0,0            1282210 pipe
> mysqld-ma 23359    root   11w  FIFO        0,0            1282210 pipe
> mysqld-ma 23359    root   12u  unix 0xc0e47560            1580579
> /var/lib/mysql/mysql.sock
> mysqld-ma 23359    root   13u  unix 0xc767f580            1580668
> /var/lib/mysql/mysql.sock
> mysqld-ma 23359    root   48u  unix 0xc8f70580            1579236
> /var/lib/mysql/mysql.sock
> mysql     23383 dmurray  cwd    DIR        3,8    4096    1406300
> /home/dmurray/sandbox/EDR/software/source/apps/initialdataload
> mysql     23383 dmurray  rtd    DIR        3,2    4096          2 /
> mysql     23383 dmurray  txt    REG        3,2  673752     361039 /usr/bin/mysql
> mysql     23383 dmurray  mem    REG        3,2  261460     635323
> /lib/libnss_files-2.2.4.so
> mysql     23383 dmurray  mem    REG        3,2 1282588     635298 /lib/libc-2.2.4.so
> mysql     23383 dmurray  mem    REG        3,2  485171     635289 /lib/ld-2.2.4.so
> mysql     23383 dmurray    0r   REG        3,8   19059    1569793
> /home/dmurray/sandbox/EDR/software/source/database/sql/edr.sql
> mysql     23383 dmurray    1w   REG        3,8      91    1406282
> /home/dmurray/sandbox/EDR/software/source/apps/initialdataload/TEST-stdout.log
> mysql     23383 dmurray    2w   REG        3,8       0    1406299
> /home/dmurray/sandbox/EDR/software/source/apps/initialdataload/TEST-stderr.log
> mysql     23383 dmurray    3u  unix 0xc767fa80            1580667 socket
> mysql     23383 dmurray    5r  FIFO        0,0               1811 pipe
> mysql     23383 dmurray    6w  FIFO        0,0               1811 pipe
> mysql     23383 dmurray    7r  FIFO        0,0            1304527 pipe
> mysql     23383 dmurray    8r   REG        3,5  506904      47906
> /tmp/kde-dmurray/ksycoca
> mysql     23383 dmurray    9r  FIFO        0,0               2073 pipe
> mysql     23383 dmurray   10w  FIFO        0,0               2073 pipe
> mysql     23383 dmurray   11w  FIFO        0,0            1304527 pipe
> mysql     23383 dmurray   13r   REG        3,5  506904      47906
> /tmp/kde-dmurray/ksycoca
> mysql     23383 dmurray   14r  FIFO        0,0            1323248 pipe
> mysql     23383 dmurray   15w  FIFO        0,0            1323248 pipe
> mysql     23383 dmurray   16r   CHR        1,3              66352 /dev/null
> mysql     23383 dmurray   17w   CHR        1,3              66352 /dev/null
> mysql     23383 dmurray   18w   CHR        1,3              66352 /dev/null
> mysqld-ma 23384    root  cwd    DIR        3,6    4096      31937
> /opt/pason/data/mysqldb
> mysqld-ma 23384    root  rtd    DIR        3,2    4096          2 /
> mysqld-ma 23384    root  txt    REG        3,2 5654541     425727
> /usr/sbin/mysqld-max
> mysqld-ma 23384    root  mem    REG        3,2  261460     635323
> /lib/libnss_files-2.2.4.so
> mysqld-ma 23384    root  mem    REG        3,2 1282588     635298 /lib/libc-2.2.4.so
> mysqld-ma 23384    root  mem    REG        3,2  485171     635289 /lib/ld-2.2.4.so
> mysqld-ma 23384    root  mem    REG        3,2  355236     635331
> /lib/libnss_nisplus-2.2.4.so
> mysqld-ma 23384    root  mem    REG        3,2  436784     635307
> /lib/libnsl-2.2.4.so
> mysqld-ma 23384    root    0r   CHR        1,3              66352 /dev/null
> mysqld-ma 23384    root    1w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma 23384    root    2w   REG        3,6    5753      31938
> /opt/pason/data/mysqldb/mysql-master.err
> mysqld-ma 23384    root    3u  IPv4    1281535                TCP *:mysql (LISTEN)
> mysqld-ma 23384    root    4u  unix 0xcfe6d540            1282205
> /var/lib/mysql/mysql.sock
> mysqld-ma 23384    root    5w   REG        3,6  300187      31939
> /opt/pason/data/mysqldb/mysql-bin.001
> mysqld-ma 23384    root    6u   REG        3,6      38      31940
> /opt/pason/data/mysqldb/mysql-bin.index
> mysqld-ma 23384    root    7w   REG        3,6    8745      31941
> /opt/pason/data/mysqldb/mysql-slow.log
> mysqld-ma 23384    root    8r   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma 23384    root    9u   REG        3,6   29585      31942
> /opt/pason/data/mysqldb/log.0000000001
> mysqld-ma 23384    root   10r  FIFO        0,0            1282210 pipe
> mysqld-ma 23384    root   11w  FIFO        0,0            1282210 pipe
> mysqld-ma 23384    root   12u  unix 0xc0e47560            1580579
> /var/lib/mysql/mysql.sock
> mysqld-ma 23384    root   13u  unix 0xc767f580            1580668
> /var/lib/mysql/mysql.sock
> mysqld-ma 23384    root   48u  unix 0xc8f70580            1579236
> /var/lib/mysql/mysql.sock
> grep      23721    root    1w   REG        3,2       0     638475
> /root/lsof_deadlocked_mysql.txt
> ----
> 

show variables output
> Variable_name	Value
> back_log	50
> basedir	/
> bdb_cache_size	8388600
> bdb_log_buffer_size	32768
> bdb_home	/opt/pason/data/mysqldb/
> bdb_max_lock	10000
> bdb_logdir	
> bdb_shared_data	OFF
> bdb_tmpdir	/tmp/
> bdb_version	Sleepycat Software: Berkeley DB 3.2.9a: (December 23, 2001)
> binlog_cache_size	32768
> character_set	latin1
> character_sets	latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8
> dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian
> koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
> concurrent_insert	ON
> connect_timeout	5
> datadir	/opt/pason/data/mysqldb/
> delay_key_write	ON
> delayed_insert_limit	100
> delayed_insert_timeout	300
> delayed_queue_size	1000
> flush	OFF
> flush_time	0
> ft_min_word_len	4
> ft_max_word_len	254
> ft_max_word_len_for_sort	20
> ft_boolean_syntax	+ -><()~*:""&|
> have_bdb	YES
> have_innodb	DISABLED
> have_isam	YES
> have_raid	NO
> have_symlink	YES
> have_openssl	NO
> init_file	
> innodb_additional_mem_pool_size	1048576
> innodb_buffer_pool_size	8388608
> innodb_data_file_path	ibdata1:64M
> innodb_data_home_dir	
> innodb_file_io_threads	9
> innodb_force_recovery	0
> innodb_thread_concurrency	8
> innodb_flush_log_at_trx_commit	OFF
> innodb_fast_shutdown	OFF
> innodb_flush_method	
> innodb_lock_wait_timeout	1073741824
> innodb_log_arch_dir	
> innodb_log_archive	OFF
> innodb_log_buffer_size	1048576
> innodb_log_file_size	5242880
> innodb_log_files_in_group	2
> innodb_log_group_home_dir	
> innodb_mirrored_log_groups	1
> interactive_timeout	28800
> join_buffer_size	131072
> key_buffer_size	8388600
> language	/usr/share/mysql/english/
> large_files_support	ON
> locked_in_memory	OFF
> log	OFF
> log_update	OFF
> log_bin	ON
> log_slave_updates	OFF
> log_long_queries	ON
> long_query_time	10
> low_priority_updates	OFF
> lower_case_table_names	0
> max_allowed_packet	1048576
> max_binlog_cache_size	4294967295
> max_binlog_size	1073741824
> max_connections	100
> max_connect_errors	10
> max_delayed_threads	20
> max_heap_table_size	16777216
> max_join_size	4294967295
> max_sort_length	1024
> max_user_connections	0
> max_tmp_tables	32
> max_write_lock_count	4294967295
> myisam_bulk_insert_tree_size	8388608
> myisam_max_extra_sort_file_size	256
> myisam_max_sort_file_size	2047
> myisam_recover_options	OFF
> myisam_sort_buffer_size	8388608
> net_buffer_length	16384
> net_read_timeout	30
> net_retry_count	10
> net_write_timeout	60
> open_files_limit	0
> pid_file	/opt/pason/data/mysqldb/mysqld-master.pid
> port	3306
> protocol_version	10
> record_buffer	131072
> record_rnd_buffer	131072
> rpl_recovery_rank	0
> query_buffer_size	0
> query_cache_limit	1048576
> query_cache_size	0
> query_cache_startup_type	1
> safe_show_database	OFF
> server_id	1
> slave_net_timeout	3600
> skip_locking	ON
> skip_networking	OFF
> skip_show_database	OFF
> slow_launch_time	2
> socket	/var/lib/mysql/mysql.sock
> sort_buffer	2097144
> sql_mode	0
> table_cache	64
> table_type	MYISAM
> thread_cache_size	0
> thread_stack	65536
> transaction_isolation	READ-COMMITTED
> timezone	MDT
> tmp_table_size	33554432
> tmpdir	/tmp/
> version	4.0.1-alpha-Max-log
> wait_timeout	28800



Thread
mysql hangingScott Olson8 Jul