[MDEV-12683] Threads are always in 'cleaning up' state in innodb status Created: 2017-05-04  Updated: 2018-07-13  Resolved: 2018-07-13

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.0.30, 10.1.23
Fix Version/s: 10.0.36, 10.1.34, 10.2.16, 10.3.8

Type: Bug Priority: Major
Reporter: Nilnandan Joshi Assignee: Sergei Golubchik
Resolution: Duplicate Votes: 1
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-15359 Thread stay in "cleaning up" status a... Closed

 Description   

Threads are always in "cleaning up" state in innodb status while in show processlist we can see "sleep". There was already bug opened and seem fixed but newer versions still have this issue.
MDEV-9747

MariaDB [test]> show processlist;
 
+----+------+-----------+-------+---------+------+-------+------------------+----------+
 
| Id | User | Host      | db    | Command | Time | State | Info             | Progress |
 
+----+------+-----------+-------+---------+------+-------+------------------+----------+
 
|  2 | root | localhost | test  | Query   |    0 | init  | show processlist |    0.000 |
 
|  3 | root | localhost | mysql | Sleep   |    4 |       | NULL             |    0.000 |
 
|  4 | root | localhost | test  | Sleep   |   10 |       | NULL             |    0.000 |
 
+----+------+-----------+-------+---------+------+-------+------------------+----------+


------------
 
TRANSACTIONS
 
------------
 
Trx id counter 2822
 
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
 
History list length 0
 
LIST OF TRANSACTIONS FOR EACH SESSION:
 
---TRANSACTION 0, not started
 
MySQL thread id 3, OS thread handle 0x7f5a704ca700, query id 56 localhost root cleaning up
 
---TRANSACTION 0, not started
 
MySQL thread id 4, OS thread handle 0x7f5a70481700, query id 22 localhost root cleaning up
 
---TRANSACTION 2821, not started
 
MySQL thread id 2, OS thread handle 0x7f5a70513700, query id 58 localhost root init
 
show engine innodb status



 Comments   
Comment by Elena Stepanova [ 2017-05-04 ]

Copying marko's comment from MDEV-9747:

The output lines "MySQL thread id" are emitted by thd_get_error_context_description(), which is called by innobase_mysql_print_thd(), called by trx_print_low() which displays the line "TRANSACTION 0, not started".
From the InnoDB point of view, the 2 transactions are not active. The trx objects should be cached objects that only exist in the handler interface and in trx_sys->mysql_trx_list. The objects should be freed when the connection is closed. Maybe these connections did access InnoDB in the past, but no transaction is active any more?
There only is one transaction allocated for the SHOW ENGINE INNODB STATUS.
Do the "cleaning up" entries go away when the two connections are disconnected?
I do not know where the "cleaning up" message is coming from. One possibility is PSI_stage_info stage_cleaning_up defined in mysqld.cc.

Comment by Elena Stepanova [ 2017-05-04 ]

marko,

Yes, it happens when connections did access InnoDB in the past. It's not always obvious, e.g. if a connection is established via MySQL client with auto-rehashing (default), it reads structures of tables in the default database, and after that 'cleaning up' occurs. If rehashing is turned off, or there is no default database, or there are no InnoDB tables in it, then no 'cleaning up' until you access InnoDB explicitly.

So, given the above, do you consider it normal and harmless?
I see the same behavior in InnoDB 5.6, but not in InnoDB 5.5 or 5.7 (both in MariaDB and in MySQL).

Comment by Marko Mäkelä [ 2017-05-05 ]

I do not see any evidence of anything being wrong on the InnoDB side.
That said, perhaps the status string of the THD object should be changed to something like "cached for reuse" instead of "cleaning up". That change would be outside InnoDB.

Comment by Elena Stepanova [ 2017-05-12 ]

serg, what do you think about it from the server point of view?

Generated at Thu Feb 08 07:59:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.