InnoDB has a transaction running, but "SHOW PROCESSLIST;" shows nothing

Question

I have a replicated database setup, mostly Innodb databases/tables. The query that I believe caused the issue ran in about 20 minutes on the master database. This is where it gets weird. Show processlist has nothing listed, except for my connection and the replicant connection. Innodb has a transaction running according to show innodb status. The transaction has been running for 7+ hours.

Replication seems to have stopped, despite the slave_io and slave_sql both running. The Exec_Master_Log_Pos hasn't budged and cpu usage is at 100% (1 core).

Is there any way to see what query it's actually running as well as maybe a way to figure out how long the transaction may take? I'm kind of lost here and about to blow away the slave and resetup replication if I can't get anywhere soon.

Answer 1

Is it possible that your InnoDB settings are way too small ?

The default innodb_buffer_pool_size is 8M. Could you increase it ?

The default innodb_log_file_size is 5M. Could you increase it ?

You should resize them as follows:

Goto this link to compute the proper size for the innodb_buffer_pool_size based on the amount of InnoDB you have or 75% of installed RAM, whichever is smaller.

Once you have this number, set innodb_log_file_size to 25% of the computed innodb_buffer_pool_size. In order to resize them, you will have to shutdown mysql, delete the innodb log files, and start mysql.

Give it a Try !!!

CAVEAT

IMHO The reason for this conjecture is that lots of MVCC activity + default InnoDB configs spells a lot of internal InnoDB maintenance we may never know is going on.

Here are other possibilities :

• Could the .ibd file be corrupt ?
• Is the .ibd on a degraded array ?
• Is there an insufficient number for the innodb_open_files option ?
• Is tmpdir folder too full (tmp tables log jamming in the tmp folder) ???
• Are the my.cnf files of the two instances identical (less server_id) ???