3

I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list. So I looked at SHOW ENGINE INNODB STATUS, which showed that when the transactions in question are waiting on locks, the only other tansactions present that have started (there are many in thenot started state) are ones that have been running for 30 seconds or more, and are in the cleaning up state.

Why would a transaction get stuck in the cleaning up state? Is there any way to tell at that point, what any of the original queries were for that transaction? (The transaction does not appear to be present in the information_schema.innodb_trx table).

Example output:

---TRANSACTION 2670738352, ACTIVE 25 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158609, OS thread handle 0x2ae9a6bcf700, query id 487681086 10.0.1.246 ownlocal cleaning up
Trx read view will not see trx with id >= 2670738353, sees < 2670727358
---TRANSACTION 2670736929, ACTIVE 40 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158413, OS thread handle 0x2ae9a5842700, query id 487683091 10.0.1.203 ownlocal cleaning up
Trx read view will not see trx with id >= 2670737972, sees < 2670727358
---TRANSACTION 2670736875, ACTIVE 40 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158415, OS thread handle 0x2ae9a64b3700, query id 487678962 10.0.1.203 ownlocal cleaning up
Trx read view will not see trx with id >= 2670736876, sees < 2670724054
---TRANSACTION 2670727358, ACTIVE 136 sec
3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 1
MySQL thread id 158216, OS thread handle 0x2ae9a7679700, query id 487676960 10.0.1.110 ownlocal cleaning up
Trx read view will not see trx with id >= 2670735437, sees < 2670724054

Additional Info:

  • MySQL Version: 5.6
  • Storage Engine: InnoDB
  • what is %sys cpu load on the system? – akuzminsky Dec 16 '14 at 0:18
  • The state describes the following as per MySQL Documentation • cleaning up The thread has processed one command and is preparing to free memory and reset certain state variables. You may need to fine tune some memory related parameters if you see this often in your instance. – Nawaz Sohail Dec 16 '14 at 7:27
  • @akuzminsky unfortunately, I can't see specific CPU stats, as it's an AWS RDS instance, but the overall CPU usage (averaged per minute) hasn't gone over 50% (usually around 10-30%). – mltsy Dec 16 '14 at 14:32
  • @NawazSohail Thanks, I'll look into memory settings... any recommendations? Freeable memory has remained above 1GB for the past day, so it doesn't seem like we're running out of physical memory, but perhaps we need to increase specific allotments in MySQL. I know very little about MySQL tuning and configuration, so any recommendations could be helpful. – mltsy Dec 16 '14 at 15:13
  • 1
    @mitsy, "cleaning up" state should be instant. If that's not the case then CPU is extremely busy (RDS graphs show averages across all cores, so it may confuse). A side note - time has come for you to migrate out of RDS. I would collect slow queries (long_query_time=0) and check the queries digest. Check if there are unusual queries, if any of them have wrong execution plan. A suspect here is a query reading a lot of rows. – akuzminsky Dec 16 '14 at 16:01
0

I have the same issue on RDS. Some of "cleaning up" transactions blocked my ALTER TABLE query. And as I understood in MySQL 5.6 there is no way to find out which transaction should be killed.

So, my solution was to kill all "cleaning up" transactions. And it helped me. And more thing: according to AWS doc on RDS you should use CALL mysql.rds_kill(pid) instead of standard KILL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.