Hi,
Having a most odd problem with random crashes, that appear to be some
kind of deadlocking or blocked process/thread occuring within mysqld
(a wild guess, not any type of assertion).
mysql v4.0.24, ~350Mb DB, running in production for months, no
problems under fairly heavy daily use from multiple users.
Upgraded to 5.0.22 [1], re-importing the data from mysqldump files.
Periodically, a client process will lock up, while holding a lock on
the table. Separate clients can connect and issue queries, but not on
the table that is locked by the hung process. Accessing the locked
table in question hangs the clients, which never appear to return.
Just for the record, killing the client(s) doesn't release the lock.
First observed on our production machine [2]. We took a snapshot, by:
* stopping mysqld
* enabled the query log, copied the db files from /var/lib/mysql
* restarted mysqld, waited for the problem to re-occur,
* stopped mysqld, copied all the files to one side, then downgraded to v4.0.24.
On our dev box [3], we
* installed the v5 mysqld from the same deb package (and later from
MySQL AB binaries, see [4])
* copied the /var/lib/mysql files in that we took from our production box
* parsed the mysql query log with perl, and re-applied the statements
(1 DBI connection per user, as shown in query log, queries applied
in query log order.
DBI does set autocommit=1 on each connection)
Doing this:
* We can reproduce the crash
* It typically always crashes at the same point
(Doing a simple insert into one of a couple of a fairly simple MyISAM tables)
* Sometimes it doesn't crash at that point, but does eventually, while
doing an insert.
I can reduce it to about 40 queries that will crash it 90% of the
time. However, by running some innocent/any query in a separate
command line client immediately after restarting (with reset files in
/var/lib/mysql) and before running the query log commands, the crash
typically won't occur until a lot more queries have run.
Removing a couple of the earlier select queries (40) causes the
lockup not to happen until later on.
We recompiled with the --with-debug and have a couple of the trace
files from when it crashed (it appears to crash less often with the
debug-enabled mysqld). I'm not really up to reading the tracefiles
and making any sense out of them. The last few lines of both do
differ, but the last line is always:
process_alarm: info: sig: 14 active alarms: X
where X might be 1..4 typically.
When this occurs, in almost all cases:
mysql show processlist;
...
| 13 | wwwuser | localhost | our_db | Query | 6 | update | INSERT
INTO `` (`field_1`, `field_2`, `field_3`, `field_4`) VALUES
('', '1 |
...
Occasionally we instead see:
| 266 | wwwuser | localhost | our_db | Query | 29 | Locked | INSERT
INTO `` (`field_1`, `field_2`, `field_3`, `field_4`, `field_5`,
`field_6`, `da |
The time (6 and 29 in the above) would increase, but everything else
remained as shown. It would never complete. Any other client trying
to access that table would be blocked indefinitely. Any further
queries on the relevant table would show up in processlist as Locked
Next, we downloaded the MySQL AB binary [4]. Exactly the same behaviour.
I'm stumped, and wondering if anyone has some ideas on how to proceed?
Thanks,
Matt.
[1] We started from the Debian package for version 5.0.22-2bpo1 found here:
deb-src http://www.backports.org/debian sarge-backports main
and compiled it with --with-openssl
[2] Production : Linux production 2.6.8-11-amd64-generic #1 Wed Jun 1
00:42:47 CEST 2005 x86_64 GNU/Linux
AMD Opteron(tm) Processor 244, 4Gb RAM
Running Debian Sarge (stable), including libc6 2.3.2.ds1-22
[3] Development: Linux dev 2.6.15-1-amd64-k8-smp #2 SMP Tue Mar 7
21:00:29 UTC 2006 x86_64 GNU/Linux
AMD Athlon(tm) 64 X2 Dual Core Processor 4400+, 4Gb RAM
Running Debian Sarge (stable), including libc6 2.3.2.ds1-22
[4]
http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-x86_64-glibc23.tar.gz
We stopped the debian packaged version (/etc/init.d/mysql stop)
and ran this instead.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
