Mysql – Sleep Processes are Running Longer Than Allowed

MySQLperformancesleep

From time to time my mysql server will run into a "too many connections" error. I believe part of the problem is large amount of sleep processes that are running, some over 1000 seconds.

When looking into the issue, I saw this and investigated. I checked my timeout values with

show variables like "%timeout";

And both interactive_timeout and wait_timeout are set to 600.

How/Why would these sleep processes be running so long, if they're supposed to stop at 600?

Best Answer

There are two possibilities the come to mind:

the values of interactive_timeout and wait_timeout were not 600 when those threads first connected, so they are using the values that existed when they first connected, or
the application is changing the timeout values for its sessions after connecting.

If you're running out of available connections, though, why not just raise max_connections? If the threads are sleeping, you're not talking about a significant resource cost.

Related Solutions

MySQL: Lock wait timeout exceeded

This is definitely a head-scratcher, but here's one possibility, especially if you're running MySQL 5.1 or earlier. MySQL Bug #37346 addresses an issue where there are locks at two different layers that are (at least partially) unaware of each other's existence... an uncommitted transaction is holding locks inside the storage engine (invisible to MySQL's core), while the ALTER TABLE is holding a different kind of lock, outside the storage engine (invisible to InnoDB).

In the bug report, when the ALTER TABLE is almost done, it bumps into the inner locks and ultimately times out with the message you see.

This is referenced as "fixed" in the release notes for 5.5.3 but I didn't find it mentioned in 5.1.x, which suggests to me that it might still exist there. It seems like a long shot but it did seem worth mentioning.

Until I found the above, my initial thoughts had to do with the table that is referenced by fk_foo_1... I was going to speculate that InnoDB might be trying something unnecessary related to validation of the foreign key constraint (unnecessary, since the constraint is about to disappear)... or that an uncommitted update or delete on the referenced table might be setting locks on the table you're trying to alter... an update or delete "over there," needing to be validated or trying to cascade "over here," while the ALTER TABLE is underway, and not waiting as it should for the lock held by the ALTER TABLE.

Mysql – do anything about a very long running KILL command

First of all, thank you posting the DELETE query and the table layout

With regard to your question, there is nothing you can really do because a rollback is being done via the UNDO tablespace inside ibdata1 (it should have grown immensely). If you kill the mysqld process and restart it will just pickup where it left off as part of the crash recovery cycle.

Here is how you can handle large and small deletes in the future:

LARGE DELETEs

Instead of deleting 70% of the table and create lots of rollback info, try copying the tables you want to keep.

CREATE TABLE adc_fetched_documents_new LIKE adc_fetched_documents;
INSERT INTO adc_fetched_documents_new
    SELECT * FROM adc_fetched_documents
    WHERE id > 73097292
;
ALTER TABLE adc_fetched_documents RENAME adc_fetched_documents_zap;
ALTER TABLE adc_fetched_documents_new RENAME adc_fetched_documents;
TRUNCATE TABLE adc_fetched_documents_zap;

Whenever, you delete 70% or more of the table, do it like this.

SMALL DELETEs

If you are deleting smaller chunks, perhaps you can do it with a DELETE JOIN:

CREATE TABLE deletion_ids (id INT NOT NULL PRIMARY KEY (id)) ENGINE=InnoDB;
INSERT INTO deletion_ids
    SELECT id FROM adc_fetched_documents
    WHERE id <= 73097292
;
DELETE B.* FROM deletion_ids A INNER JOIN adc_fetched_documents B USING (id);

Give it a Try !!!