Hi All,
This is a repost of my letter which I sent a couple of days ago to mysql
mailing list with some additional info. Any advice is much appreciated,
and I do need this problem solved soon. Please help.
I am experiencing repeated hangs of mysqld 3.22.32 on FreeBSD 4.1-RELEASE,
mysqld is compiled from the ports with native threads. It starts ok, works
for some time and then hangs. Hangs are caused by htdig web indexing software
accessing mysql thru apache php3.0.16 module. They are absolutely repeatable,
it just takes mysql different time to hang.
Release: mysql-3.22.32 (Source distribution)
Environment:
<machine, os, target, libraries (multiple lines)>
System: FreeBSD hub.all.yans.ru 4.1-RELEASE FreeBSD 4.1-RELEASE #2: Mon Oct 9 14:22:57
MSD 2000 root@stripped:/usr/src/sys/compile/HUB i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS=''
Configure command: ./configure --localstatedir=/var/db/mysql --without-perl
--without-debug --without-readline --without-bench --enable-assembler
--with-mit-threads=no --prefix=/usr/local i386--freebsd4.1
The symptoms are as follows:
bash-2.04# mysql -p -S /heap/mysql/tmp/mysql.sock
Enter password:
ERROR 2002: Can't connect to local MySQL server through socket
'/heap/mysql/tmp/mysql.sock' (61)
bash-2.04# mysql -p -h127.0.0.1
Enter password:
Then just hangs.
Clients that were left connected to mysql both thru UNIX socket and TCP/IP
when trying to get status info (\s at the 'mysql>' prompt) also hang.
This is the output of mysqladmin -i60 -h127.0.0.1 ping status-extended
which also hung.
mysqld is alive
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Created_tmp_tables | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 0 |
| Handler_read_first | 12211 |
| Handler_read_key | 12373 |
| Handler_read_next | 5336 |
| Handler_read_rnd | 153646252 |
| Handler_update | 288 |
| Handler_write | 5 |
| Key_blocks_used | 6 |
| Key_read_requests | 15940 |
| Key_reads | 6 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 4 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 38 |
| Open_files | 76 |
| Open_streams | 1 |
| Opened_tables | 42 |
| Questions | 417090 |
| Running_threads | 4 |
| Slow_queries | 0 |
| Uptime | 12594 |
+--------------------------+-----------+
mysqld is alive
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0
This is the end of mysql log file:
001010 18:47:09 11746 Init DB id23
11746 Query select group_id,category_id,sub_cat_id from price1 where
flag<>0 AND group_id=950542922 and category_id=950543023 and sklad=0 GROUP BY
sub_cat_id
11746 Init DB id23
11746 Query select cdate from up_date1 ORDER BY cdate DESC
11746 Quit
The time before that the last query in the log was different:
129020 Init DB id23
129020 Query select usr from visitor1 where utime=0x39d6cbd998ff9 and
usr=0x39d6cbd994210 and TO_DAYS(NOW()) - TO_DAYS(cdate) <= 5
129020 Init DB id23
129020 Query select id,count from cart1 where id_usr=1017515293098512 ORDER BY id
DESC
129020 Quit
No limits are imposed on mysqld process, ps shows it as idle, it does not
react to SIGTERM, only SIGKILL kills it.
When trying to connect thru TCP/IP truss shows the following for mysqld:
SIGNAL 14
SIGNAL 14
SIGNAL 14
(null)() ERR#4 'Interrupted system call'
write(4,0x816fc7f,1) = 1 (0x1)
sigreturn(0x816fc9c) ERR#4 'Interrupted system call'
read(0x3,0x816fdac,0x80) = 1 (0x1)
read(0x3,0x816fdac,0x80) ERR#35 'Resource temporarily unavailable'
gettimeofday(0x816fec4,0x0) = 0 (0x0)
poll(0x8153000,0x1,0x0) = 0 (0x0)
setitimer(0x2,0x816fecc,0x0) = 0 (0x0)
sigprocmask(0x3,0x8150e30,0x0) = 0 (0x0)
gettimeofday(0x816ff34,0x0) = 0 (0x0)
setitimer(0x0,0x816ff2c,0x816ff1c) = 0 (0x0)
sigaction(SIGTERM,0x816ff44,0x0) = 0 (0x0)
sigaction(SIGTSTP,0x816ff44,0x0) = 0 (0x0)
sigprocmask(0x1,0x0,0x8150e30) = 0 (0x0)
gettimeofday(0x816fec4,0x0) = 0 (0x0)
poll(0x8153000,0x1,0x0) = 0 (0x0)
gettimeofday(0x816fe74,0x0) = 0 (0x0)
SIGNAL 14
SIGNAL 14
SIGNAL 14
poll(0x8153000,0x2,0xffffffff) ERR#4 'Interrupted system call'
write(4,0x816fc7f,1) = 1 (0x1)
sigreturn(0x816fc9c) ERR#4 'Interrupted system call'
read(0x3,0x816fdac,0x80) = 1 (0x1)
read(0x3,0x816fdac,0x80) ERR#35 'Resource temporarily unavailable'
gettimeofday(0x816fec4,0x0) = 0 (0x0)
poll(0x8153000,0x1,0x0) = 0 (0x0)
setitimer(0x2,0x816fecc,0x0) = 0 (0x0)
sigprocmask(0x3,0x8150e30,0x0) = 0 (0x0)
gettimeofday(0x816ff34,0x0) = 0 (0x0)
setitimer(0x0,0x816ff2c,0x816ff1c) = 0 (0x0)
sigaction(SIGTERM,0x816ff44,0x0) = 0 (0x0)
sigaction(SIGTSTP,0x816ff44,0x0) = 0 (0x0)
sigprocmask(0x1,0x0,0x8150e30) = 0 (0x0)
gettimeofday(0x816fec4,0x0) = 0 (0x0)
poll(0x8153000,0x1,0x0) = 0 (0x0)
gettimeofday(0x816fe74,0x0) = 0 (0x0)
Thanx in advance.
Ekaterina Ivannikova
