Ekaterina Ivannikova wrote:
>
> 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)
My guess is that the problem has to do with some bug in the thread library or in
libc itself. Can you try to repeat the problem with a script that we can run on
our systems?
Tim, who is our FreeBSD guy, might be able to offer some additional words of
advice - any comments, Tim?
--
Sasha Pachev
+------------------------------------------------------------------+
| ____ __ _____ _____ ___ http://www.mysql.com |
| /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sasha Pachev |
| /*/ /*/ /*/ \*\_ |*| |*||*| sasha@stripped |
| /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Provo, Utah, USA |
| /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ |
| ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ |
| /*/ \*\ Developers Team |
+------------------------------------------------------------------+
