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

Truncate hangs InnoDB when innodb_safe_truncate is off

    XMLWordPrintable

    Details

      Description

      InnoDB truncate table command could hang the InnoDB server when innodb_safe_truncate is disabled.

      innodb_zip.wl6501_scale_1 'innodb' w51 [ fail ] timeout after 900 seconds

      10.2 f3160ee44f8f3ae4e5eeea768e289ec40253f35e

      Thread 2:
      #5  0x00000000016c77a5 in os_event_wait_low (event=0x60b000074878, reset_sig_count=128) at /mariadb/10.2o/storage/innobase/os/os0event.cc:507
      #6  0x0000000001932646 in sync_array_wait_event (arr=<optimized out>, cell=<optimized out>) at /mariadb/10.2o/storage/innobase/sync/sync0arr.cc:471
      #7  0x000000000193d153 in rw_lock_x_lock_wait_func (lock=0x610000000040, pass=<optimized out>, threshold=<optimized out>, file_name=<optimized out>, line=<optimized out>)
          at /mariadb/10.2o/storage/innobase/sync/sync0rw.cc:455
      #8  0x000000000193a748 in rw_lock_x_lock_low (lock=0x610000000040, pass=0, file_name=0x285f5e0 <str> "/mariadb/10.2o/storage/innobase/btr/btr0sea.cc", line=1218)
          at /mariadb/10.2o/storage/innobase/sync/sync0rw.cc:511
      #9  0x0000000001939bfa in rw_lock_x_lock_func (lock=0x610000000040, pass=<optimized out>, file_name=<optimized out>, line=<optimized out>) at /mariadb/10.2o/storage/innobase/sync/sync0rw.cc:660
      #10 0x0000000001b0056e in pfs_rw_lock_x_lock_func (lock=0x610000000040, pass=0, file_name=<optimized out>, line=1218) at /mariadb/10.2o/storage/innobase/include/sync0rw.ic:537
      #11 btr_search_drop_page_hash_index (block=<optimized out>) at /mariadb/10.2o/storage/innobase/btr/btr0sea.cc:1218
      #12 0x0000000001b99c77 in buf_LRU_free_page (bpage=<optimized out>, zip=<optimized out>) at /mariadb/10.2o/storage/innobase/buf/buf0lru.cc:1615
      #13 0x0000000001b50a68 in buf_page_io_complete (bpage=<optimized out>, dblwr=<optimized out>, evict=<optimized out>) at /mariadb/10.2o/storage/innobase/buf/buf0buf.cc:6222
      #14 0x0000000001cbcf65 in fil_aio_wait (segment=<optimized out>) at /mariadb/10.2o/storage/innobase/fil/fil0fil.cc:5144
      #15 0x00000000019154f8 in io_handler_thread (arg=<optimized out>) at /mariadb/10.2o/storage/innobase/srv/srv0start.cc:332
      #16 0x00007fa941c99ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
      #17 0x00007fa9412eaeaf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
       
      Thread 15:
      #7  0x000000000193d153 in rw_lock_x_lock_wait_func (lock=0x610000000740, pass=<optimized out>, threshold=<optimized out>, file_name=<optimized out>, line=<optimized out>) at /mariadb/10.2o/storage/innobase/sync/sync0rw.cc:455
      #8  0x000000000193a748 in rw_lock_x_lock_low (lock=0x610000000740, pass=0, file_name=0x285f5e0 <str> "/mariadb/10.2o/storage/innobase/btr/btr0sea.cc", line=1218) at /mariadb/10.2o/storage/innobase/sync/sync0rw.cc:511
      #9  0x0000000001939bfa in rw_lock_x_lock_func (lock=0x610000000740, pass=<optimized out>, file_name=<optimized out>, line=<optimized out>) at /mariadb/10.2o/storage/innobase/sync/sync0rw.cc:660
      #10 0x0000000001b0056e in pfs_rw_lock_x_lock_func (lock=0x610000000740, pass=0, file_name=<optimized out>, line=1218) at /mariadb/10.2o/storage/innobase/include/sync0rw.ic:537
      #11 btr_search_drop_page_hash_index (block=<optimized out>) at /mariadb/10.2o/storage/innobase/btr/btr0sea.cc:1218
      #12 0x0000000001b99c77 in buf_LRU_free_page (bpage=<optimized out>, zip=<optimized out>) at /mariadb/10.2o/storage/innobase/buf/buf0lru.cc:1615
      #13 0x0000000001b50a68 in buf_page_io_complete (bpage=<optimized out>, dblwr=<optimized out>, evict=<optimized out>) at /mariadb/10.2o/storage/innobase/buf/buf0buf.cc:6222
      

      Above both threads are waiting for ahi latch to remove ahi entries. The ahi latch held by the following
      truncate thread. innodb_safe_truncate=OFF

      #0  0x00007fa9412b2d21 in __GI___clock_nanosleep (clock_id=clock_id@entry=0, flags=flags@entry=0, req=0x7fa92967ccc0, rem=0x0) at ../sysdeps/unix/sysv/linux/clock_nanosleep.c:48
      #1  0x00007fa9412b8503 in __GI___nanosleep (requested_time=<optimized out>, remaining=<optimized out>) at nanosleep.c:27
      #2  0x00000000016c82c0 in os_thread_sleep (tm=<optimized out>) at /mariadb/10.2o/storage/innobase/os/os0thread.cc:231
      #3  0x0000000001b912b1 in buf_flush_dirty_pages (buf_pool=0x61b000003180, id=47, observer=0x0, first=0) at /mariadb/10.2o/storage/innobase/buf/buf0lru.cc:543
      #4  buf_LRU_flush_or_remove_pages (id=47, observer=<optimized out>, first=<optimized out>) at /mariadb/10.2o/storage/innobase/buf/buf0lru.cc:564
      #5  0x0000000001caa2eb in fil_reinit_space_header_for_table (table=<optimized out>, size=<optimized out>, trx=<optimized out>) at /mariadb/10.2o/storage/innobase/fil/fil0fil.cc:3110
      #6  0x000000000188f0c9 in row_truncate_table_for_mysql (table=<optimized out>, trx=<optimized out>) at /mariadb/10.2o/storage/innobase/row/row0trunc.cc:1991
      #7  0x0000000001501f60 in ha_innobase::truncate (this=<optimized out>) at /mariadb/10.2o/storage/innobase/handler/ha_innodb.cc:13670
      #7  0x000000000188f0c9 in row_truncate_table_for_mysql (table=<optimized out>, trx=<optimized out>) at /mariadb/10.2o/storage/innobase/row/row0trunc.cc:1991
      #8  0x0000000001501f60 in ha_innobase::truncate (this=<optimized out>) at /mariadb/10.2o/storage/innobase/handler/ha_innodb.cc:13670
      #9  0x00000000014337af in Sql_cmd_truncate_table::handler_truncate (this=<optimized out>, thd=<optimized out>, table_ref=<optimized out>, is_tmp_table=<optimized out>) at /mariadb/10.2o/sql/sql_truncate.cc:241
      #10 0x0000000001435439 in Sql_cmd_truncate_table::truncate_table (this=<optimized out>, thd=<optimized out>, table_ref=<optimized out>) at /mariadb/10.2o/sql/sql_truncate.cc:440
      #11 0x000000000143591a in Sql_cmd_truncate_table::execute (this=<optimized out>, thd=0x62a0000ba208) at /mariadb/10.2o/sql/sql_truncate.cc:497
      #12 0x00000000008cd581 in mysql_execute_command (thd=0x62a0000ba208) at /mariadb/10.2o/sql/sql_parse.cc:5964
      

      By disabling innodb_safe_truncate, InnoDB uses different approach to do truncation (retains the same tablespace id).

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              thiru Thirunarayanan Balathandayuthapani
              Reporter:
              thiru Thirunarayanan Balathandayuthapani
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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