Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12683

Threads are always in 'cleaning up' state in innodb status

Details

    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

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15359 Thread stay in "cleaning up" status after finishing

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            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.

            elenst Elena Stepanova added a comment - 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.
            elenst Elena Stepanova added a comment -

            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).

            elenst Elena Stepanova added a comment - 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).
            marko Marko Mäkelä added a comment -

            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.

            marko Marko Mäkelä added a comment - 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.
            elenst Elena Stepanova added a comment -

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

            elenst Elena Stepanova added a comment - serg , what do you think about it from the server point of view?

            People

              serg Sergei Golubchik
              niljoshi Nilnandan Joshi
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.