Innodb truncate内存维护代价高于drop

Ty 2022-04-06

485

本文为随笔。留个接口后面好详细学习。

函数接口 buf_LRU_flush_or_remove_pages 用于确认是否维护 LRU list，其中有三种类型：

/** Algorithm to remove the pages for a tablespace from the buffer pool.
See buf_LRU_flush_or_remove_pages(). */
enum buf_remove_t {
    BUF_REMOVE_ALL_NO_WRITE,    /*!< Remove all pages from the buffer
                    pool, don't write or sync to disk */  
    BUF_REMOVE_FLUSH_NO_WRITE,  /*!< Remove only, from the flush list,
                    don't write or sync to disk */
    BUF_REMOVE_FLUSH_WRITE      /*!< Flush dirty pages to disk only
                    don't remove from the buffer pool */
};复制

drop为：BUF_REMOVE_FLUSH_NO_WRITE，需要维护flush list，不回写数据
trunacte为：BUF_REMOVE_ALL_NO_WRITE，需要维护flush list和lru list，不回写数据

下面是buf_LRU_flush_or_remove_pages函数这里的cascade判断：

    for (i = 0; i < srv_buf_pool_instances; i++) {
        buf_pool_t* buf_pool;

        buf_pool = buf_pool_from_array(i);

        switch (buf_remove) {
        case BUF_REMOVE_ALL_NO_WRITE:
            buf_LRU_drop_page_hash_for_tablespace(buf_pool, id);
            break; //维护LRU

        case BUF_REMOVE_FLUSH_NO_WRITE:
            /* It is a DROP TABLE for a single table
            tablespace. No AHI entries exist because
            we already dealt with them when freeing up
            extents. */
        case BUF_REMOVE_FLUSH_WRITE:
            /* We allow read-only queries against the
            table, there is no need to drop the AHI entries. */
            break;
        }复制

下面可以看到

DROP调用：

(gdb) bt
#0  buf_LRU_flush_or_remove_pages (id=145, buf_remove=BUF_REMOVE_FLUSH_NO_WRITE, trx=0x0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/buf/buf0lru.cc:986
#1  0x0000000001d10eff in fil_delete_tablespace (id=145, buf_remove=BUF_REMOVE_FLUSH_NO_WRITE)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/fil/fil0fil.cc:2825
#2  0x0000000001b40c8a in row_drop_single_table_tablespace (space_id=145, tablename=0x7ffe44071170 "test/tfl", filepath=0x7ffe4405c4f8 "./test/tfl.ibd", 
    is_temp=false, is_encrypted=false, trx=0x7fffd78045f0) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0mysql.cc:4742
#3  0x0000000001b421a3 in row_drop_table_for_mysql (name=0x7ffef07f9370 "test/tfl", trx=0x7fffd78045f0, drop_db=false, nonatomic=true, handler=0x0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0mysql.cc:5311
#4  0x00000000019dc60f in ha_innobase::delete_table (this=0x7ffe44006cf8, name=0x7ffef07fa7e0 "./test/tfl")
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:13545
#5  0x0000000000f8934c in handler::ha_delete_table (this=0x7ffe44006cf8, name=0x7ffef07fa7e0 "./test/tfl")
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:5240
#6  0x0000000000f82272 in ha_delete_table (thd=0x7ffe44000b70, table_type=0x2f2c420, path=0x7ffef07fa7e0 "./test/tfl", db=0x7ffe44006b00 "test", 
    alias=0x7ffe44006540 "tfl", generate_warning=true) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:2763
#7  0x000000000165b291 in mysql_rm_table_no_locks (thd=0x7ffe44000b70, tables=0x7ffe44006578, if_exists=false, drop_temporary=false, drop_view=false, 
    dont_log_query=false) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_table.cc:2680
#8  0x0000000001659f97 in mysql_rm_table (thd=0x7ffe44000b70, tables=0x7ffe44006578, if_exists=0 '\000', drop_temporary=0 '\000')
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_table.cc:2221
#9  0x00000000015cc9b4 in mysql_execute_command (thd=0x7ffe44000b70, first_level=true) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3769
#10 0x00000000015d30c6 in mysql_parse (thd=0x7ffe44000b70, parser_state=0x7ffef07fc600) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901
#11 0x00000000015c6c5a in dispatch_command (thd=0x7ffe44000b70, com_data=0x7ffef07fcd70, command=COM_QUERY)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490
#12 0x00000000015c59e7 in do_command (thd=0x7ffe44000b70) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021
#13 0x000000000170e660 in handle_connection (arg=0x5fa1fc0) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312
#14 0x0000000001945620 in pfs_spawn_thread (arg=0x5f9bd00) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190
#15 0x00007ffff7bc6aa1 in start_thread () from /lib64/libpthread.so.0
#16 0x00007ffff6719bcd in clone () from /lib64/libc.so.6
复制

TRUNACTE 调用：

(gdb) bt
#0  buf_LRU_flush_or_remove_pages (id=577, buf_remove=BUF_REMOVE_ALL_NO_WRITE, trx=0x0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/buf/buf0lru.cc:986
#1  0x0000000001d117b1 in fil_reinit_space_header_for_table (table=0x7ffe80b5a920, size=6, trx=0x7fffd7804080)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/fil/fil0fil.cc:3030
#2  0x0000000001b859a0 in row_truncate_table_for_mysql (table=0x7ffe80b5a920, trx=0x7fffd7804080)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0trunc.cc:2071
#3  0x00000000019dc12e in ha_innobase::truncate (this=0x7ffe440cab50) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:13420
#4  0x0000000000f88bc5 in handler::ha_truncate (this=0x7ffe440cab50) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:4997
#5  0x000000000181aa78 in Sql_cmd_truncate_table::handler_truncate (this=0x7ffe44006b18, thd=0x7ffe44000b70, table_ref=0x7ffe44006588, is_tmp_table=false)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_truncate.cc:244
#6  0x000000000181b348 in Sql_cmd_truncate_table::truncate_table (this=0x7ffe44006b18, thd=0x7ffe44000b70, table_ref=0x7ffe44006588)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_truncate.cc:510
#7  0x000000000181b4b3 in Sql_cmd_truncate_table::execute (this=0x7ffe44006b18, thd=0x7ffe44000b70)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_truncate.cc:566
#8  0x00000000015d0de6 in mysql_execute_command (thd=0x7ffe44000b70, first_level=true) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5117
#9  0x00000000015d30c6 in mysql_parse (thd=0x7ffe44000b70, parser_state=0x7ffef07fc600) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901
#10 0x00000000015c6c5a in dispatch_command (thd=0x7ffe44000b70, com_data=0x7ffef07fcd70, command=COM_QUERY)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490
#11 0x00000000015c59e7 in do_command (thd=0x7ffe44000b70) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021
#12 0x000000000170e660 in handle_connection (arg=0x5fa1fc0) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312
#13 0x0000000001945620 in pfs_spawn_thread (arg=0x5f9bd00) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190
#14 0x00007ffff7bc6aa1 in start_thread () from /lib64/libpthread.so.0
#15 0x00007ffff6719bcd in clone () from /lib64/libc.so.6
复制

下面是源码truncate流程：

/* Understanding the truncate flow.

    Step-1: Perform intiial sanity check to ensure table can be truncated.
    This would include check for tablespace discard status, ibd file
    missing, etc ....

    Step-2: Start transaction (only for non-temp table as temp-table don't
    modify any data on disk doesn't need transaction object).

    Step-3: Validate ownership of needed locks (Exclusive lock).
    Ownership will also ensure there is no active SQL queries, INSERT,
    SELECT, .....

    Step-4: Stop all the background process associated with table.

    Step-5: There are few foreign key related constraint under which
    we can't truncate table (due to referential integrity unless it is
    turned off). Ensure this condition is satisfied.

    Step-6: Truncate operation can be rolled back in case of error
    till some point. Associate rollback segment to record undo log.

    Step-7: Generate new table-id.
    Why we need new table-id ?
    Purge and rollback case: we assign a new table id for the table.
    Since purge and rollback look for the table based on the table id,
    they see the table as 'dropped' and discard their operations.

    Step-8: Log information about tablespace which includes
    table and index information. If there is a crash in the next step
    then during recovery we will attempt to fixup the operation.

    Step-9: Drop all indexes (this include freeing of the pages
    associated with them).

    Step-10: Re-create new indexes.

    Step-11: Update new table-id to in-memory cache (dictionary),
    on-disk (INNODB_SYS_TABLES). INNODB_SYS_INDEXES also needs to
    be updated to reflect updated root-page-no of new index created
    and updated table-id.

    Step-12: Cleanup Stage. Reset auto-inc value to 1.
    Release all the locks.
    Commit the transaction. Update trx operation state.

    Notes:
    - On error, log checkpoint is done followed writing of magic number to
    truncate log file. If servers crashes after truncate, fix-up action
    will not be applied.

    - log checkpoint is done before starting truncate table to ensure
    that previous REDO log entries are not applied if current truncate
    crashes. Consider following use-case:
     - create table .... insert/load table .... truncate table (crash)
     - on restart table is restored .... truncate table (crash)
     - on restart (assuming default log checkpoint is not done) will have
       2 REDO log entries for same table. (Note 2 REDO log entries
       for different table is not an issue).
    For system-tablespace we can't truncate the tablespace so we need
    to initiate a local cleanup that involves dropping of indexes and
    re-creating them. If we apply stale entry we might end-up issuing
    drop on wrong indexes.

    - Insert buffer: TRUNCATE TABLE is analogous to DROP TABLE,
    so we do not have to remove insert buffer records, as the
    insert buffer works at a low level. If a freed page is later
    reallocated, the allocator will remove the ibuf entries for
    it. When we prepare to truncate *.ibd files, we remove all entries
    for the table in the insert buffer tree. This is not strictly
    necessary, but we can free up some space in the system tablespace.

    - Linear readahead and random readahead: we use the same
    method as in 3) to discard ongoing operations. (This is only
    relevant for TRUNCATE TABLE by TRUNCATE TABLESPACE.)
    Ensure that the table will be dropped by trx_rollback_active() in
    case of a crash.
    */复制

下面两篇阿里内核月报，后期详细学习可以参考：