MySQL: Long Running Active Idle Transactions (Doc ID 1312358.1)

Last updated on SEPTEMBER 27, 2016

Applies to:

MySQL Server - Version 4.0 and later
Information in this document applies to any platform.

Symptoms

MySQL is increasingly slow . CPU is high. Some client connections time out.

The error log contains  aborted connection warnings :

[Warning] Aborted connection 139845 to db: 'xxx' user: 'yyyy' host: 'zzz' (Got an error reading communication packets)


In the SHOW ENGINE INNODB STATUS output, you can see :

History list length very high > 1K.

History list length 87585


The history list length of each InnoDB status is increasingly large.

Transaction history is increasing.

History list length 11239


....

History list length 11631



The bottom of the transaction list displays long running transactions doing nothing.

---TRANSACTION 7DAF6, ACTIVE 28786 sec, process no 25278, OS thread
id 140588225751376
MySQL thread id 3148, query id 1347630
<server name> <ip> <user>
Trx read view will not see trx with id >= 7DAF7, sees < 7DAF5
--------
FILE I/O
--------


The longest running active transaction does not run any SQL statement.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms