MySQL Bugs: #45062: Stuck threads needing a "killall -9 mysqld"

Bug #45062	Stuck threads needing a "killall -9 mysqld"
Submitted:	25 May 2009 9:33	Modified:	29 Dec 2009 19:50
Reporter:	Anton Dischner	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.1.34	OS:	Linux (SLES10 x86 64bit SP2)
Assigned to:		CPU Architecture:	Any
Tags:	hanging, innodb, mysqld, stuck thread

Description:
we try to port our system 
from 2*dual CPU SLES10 64 SP1 MySQL 5.0.51a 64bit, 16 GB RAM
to 2*quad CPU SLES10 64 SP2 5.1.34 64bit, 48 GB RAM

There where several threads sticking without producing load. 
'/etc/mysql stop' didn't stop the database. 
I had to do a kill -9 on mysqld. 
Startup rolled back several transactions. 

Problem repeats about once a day on light load.
No problem on weekend with very low load.

So there where about 1 to 5 commands which where severely stuck. 
There where no table locks. (checked with innotop) 

There was -no- change of innodb_commit_concurrency (Bug #42101)

I did some stress testing with sql_bench without errors.

Same systems runs against MySQL InnoDB 5.0.x under heavy load for years without problems. 
So this bug only exists in 5.1.x

How to repeat:
update xxx set xxx1_date = '2009-05-22', xxx1_lnr = 63523, zst = now()

also see: MySQL Forums :: InnoDB :: Stuck threads and thread_concurrency

highly cuncurrent and repetitive.

Table has one row no indexes.

Possible to make a dump while hanging?

connector is C API for MySQL (mysqlclient) 
libmysqlclient.so.16

MySQL-server-community-5.1.34-0.sles10
MySQL-client-community-5.1.34-0.sles10
MySQL-shared-community-5.1.34-0.sles10

Thank you for the report.

Please provide full error log file, output of SHOW PROCESSLIST in time when problem occurs and real query and output of SHOW CREATE TABLES  for all underlying tables,

Link to the forum with more description about the problem:
http://forums.mysql.com/read.php?22,263084,263084#msg-263084

In my first post i wrote: There where no table locks. (checked with innotop) 

I have to corret this. There -are- table locks as seen in file locks.txt

This time the locks timed out and the process continued.

Have to wait for another appearence,

regards,

Toni

I changed init variables to reflect our # of tables, old was 512

inspired by MySQL 5.4 changes the default of them.

Would love to test 5.4 here, are there SuSE 64 RPMs in the pipeline?

Current table_open_cache = 1024 tables
Current table_definition_cache = 1024 tables
You have a total of 832 tables
You have 862 open tables.

No stuck threads so far. Still testing.

Tuning of parameters didn't help.

Had same issue this morning.

Stuck process which blocks others until they time out.

Blocking process itself clears after about another 1200 s.
 
We reviewed the problem and found no error on our side.

We have to go back to MySQL 5.0.51a if we find no solution in this bugreport.

kind regards,

A. Dischner

We decided to go back to MySQL 5.0.51a

Please close this bugreport,

regards,

A. Dischner

Thank you for the feedback.

We'd like to not close the report until it is proved this is not MySQL bug or fixed, although I can not repeat described behavior with generic tests. Please provide your configuration file also: probably some variables matter.

Our MySQL 5.1 installation is no longer available.

Thank you for the feedback.

You have option innodb_lock_wait_timeout = 1200 in the my.cnf. In this case query will wait lock for 1200 seconds before it fails with "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction". Having query locked is expected, because you update the table in parallel.