PlanetMysql.ru — информация о СУБД MySQL

When InnoDB appears hung, I know the natural reaction is to check SHOW ENGINE INNODB STATUS.

In fact, it’s the first thing I check when InnoDB tables are involved.

However, I just want to iterate how valuable SHOW FULL PROCESSLIST and/or mysqladmin debug outputs can be even when it seems mysqld is hung on on InnoDB table.

Two recent cases I’ve encountered illustrate why.

Case #1:

MySQL appeared hung on the following simple, single-row INSERT:

---TRANSACTION 0 2035648699, ACTIVE 76629 sec, process no 9047,
OS thread id 3069426592, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
...
INSERT INTO test (id, parent, text) VALUES (180370, 70122, 'test table')

At least that’s what it seemed per the INNODB STATUS, but unfortunately, there wasn’t any further information to go on.

The next time it occurred, SHOW FULL PROCESSLIST was captured at the time.

Turns out, there was a *very* long SELECT running, but not from the same table, and no foreign keys (FKs) either. Turned out it was some crazy, auto-generated query that self-joined itself 548 times. So there were no locks, per se. This query itself held up everything, and thus also the INSERT.

Case #2:

This was a table that was also hanging on a certain, simple UPDATE. The UPDATE was based on te PK, so only one row was to be updated.

Yet, it hung, and it hung, longer than wait_timeout, interactive_timeout, and innodb_lock_wait_timeout. And there were no other transactions running in the INNODB STATUS.

Turned out, another client had issued a LOCK TABLE command on the table. Since LOCK TABLE is handled outside of the InnoDB storage engine, the lock doesn’t appear in SHOW INNODB STATUS output.

Using mysqladmin debug output, coupled with SHOW PROCESSLIST helped catch this offender.

At any rate, hope this helps, and happy troubleshooting.

 
 

PlanetMySQL Voting: Vote UP / Vote DOWN

There are several parameters to set a timeout on MySQL :

But I would like to focus on wait_timeout (or interactive_timeout depending on how you connect)

This timeout allows MySQL to automatically close a connection in case of non-activity during the time defined by this parameter (default value is 28000 seconds).

The problem I wish to explain here may happen when this timeout is set to a low value (about 10 to 30 seconds).

Indeed, in this case, this timeout may have serious consequences, look at that :
[The wait_timeout parameter is set to 10 seconds in this case]

You are connected through the standard MySQL client and you need to run a delete query (bypass autocommit) :

• mysql> start transaction;
• [ You wait more than 10s because a friend calls your attention about the latest news on iphone 5 ! ]
• mysql> delete from most_valuable_table;

Your problem starts here because you forgot the where clause !
After the delete command, MySQL said :

ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...
Connection id:    98
Current database: master
Query OK, 33698541 rows affected (0.01 sec)

This simply means that the connection was lost and the last order re-submitted.
Then, you try to cancel your incomplete “delete” with a rollback command (your heart beats faster now…) :

• mysql> rollback;
• mysql> select * from most_valuable_table;

This select returns 0 rows, cruel !

It isn’t a mistake, the “start transaction” order was lost when MySQL has gone away.
The rollback is no longer possible. Bye bye to your 33.000.000 of rows !

It’s time to go to vacation…

PlanetMySQL Voting: Vote UP / Vote DOWN