undo truncate 導致qps下降分析

5.7 有了undo truncate功能，防止undo無限增長，這個需要配置2個undotablespace 才能生效，在truncate期間，系統的qps會drop，分析下原因

在debug 環境重現undo truncate後，在日誌中會看到下面的輸出資訊

2022-07-15T03:41:26.353673Z 0 [Note] InnoDB: UNDO tablespace with space identifier 1 marked for truncate
2022-07-15T03:46:04.976877Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 1
2022-07-15T03:46:05.004033Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 1
2022-07-15T03:46:05.004075Z 0 [Note] InnoDB: UNDO tablespace with space identifier 2 marked for truncate
2022-07-15T03:47:59.244989Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 2
2022-07-15T03:48:00.104841Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 2
2022-07-15T03:56:57.371996Z 0 [Note] InnoDB: UNDO tablespace with space identifier 2 marked for truncate
2022-07-15T03:57:38.693458Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 2
2022-07-15T03:58:08.944582Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 23155ms. The settings might not be optimal. (flushed=103 and evicted=0, during the time.)

debug 可以看到下面的堆疊

mysqld!buf_LRU_drop_page_hash_for_tablespace(buf_pool_t*, unsigned long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/buf/buf0lru.cc:294)
mysqld!buf_LRU_flush_or_remove_pages(unsigned long, buf_remove_t, trx_t const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/buf/buf0lru.cc:946)
mysqld!fil_truncate_tablespace(unsigned long, unsigned long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/fil/fil0fil.cc:2920)
mysqld!trx_undo_truncate_tablespace(undo::Truncate*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0undo.cc:2102)
mysqld!trx_purge_initiate_truncate(purge_iter_t*, undo::Truncate*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1156)
mysqld!trx_purge_truncate_history(purge_iter_t*, ReadView const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1252)
mysqld!trx_purge_truncate() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1816)
mysqld!trx_purge(unsigned long, unsigned long, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1914)
mysqld!srv_do_purge(unsigned long, unsigned long*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/srv/srv0srv.cc:2636)
mysqld!::srv_purge_coordinator_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/srv/srv0srv.cc:2809)
libsystem_pthread.dylib!_pthread_start (Unknown Source:0)
libsystem_pthread.dylib!thread_start (Unknown Source:0)

在buf_LRU_drop_page_hash_for_tablespace這個函式中，我們可以看到是對buffer pool加了mutex鎖的

 buf_pool_mutex_enter(buf_pool);

然後開始遍歷lru列表，對每個page設定mutex,

buf_block_t*  block = reinterpret_cast<buf_block_t*>(bpage);
mutex_enter(&block->mutex);

遍歷完後，釋放mutex,然後批次drop page

buf_LRU_drop_page_hash_batch

在truncate的過程中會有執行一個全量的檢查點

mysqld!trx_purge_initiate_truncate(purge_iter_t*, undo::Truncate*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1134)
mysqld!trx_purge_truncate_history(purge_iter_t*, ReadView const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1252)
mysqld!trx_purge_truncate() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1816)
mysqld!trx_purge(unsigned long, unsigned long, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1914)
mysqld!srv_do_purge(unsigned long, unsigned long*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/srv/srv0srv.cc:2636)
mysqld!::srv_purge_coordinator_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/srv/srv0srv.cc:2809)
libsystem_pthread.dylib!_pthread_start (Unknown Source:0)
libsystem_pthread.dylib!thread_start (Unknown Source:0)

log_make_checkpoint_at(
lsn_t           lsn,
bool            write_always)
{
    /* Preflush pages synchronously */
while (!log_preflush_pool_modified_pages(lsn)) {
        /* Flush as much as we can */
}
while (!log_checkpoint(true, write_always)) {
        /* Force a checkpoint */
}
}

上面的堆疊不全，沒有buffer pool相關部分，下面是buffer pool相關的

mysqld!buf_do_flush_list_batch(buf_pool_t*, unsigned long, unsigned long long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/buf/buf0flu.cc:1761)
mysqld!buf_flush_batch(buf_pool_t*, buf_flush_t, unsigned long, unsigned long long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/buf/buf0flu.cc:1866)
mysqld!buf_flush_do_batch(buf_pool_t*, buf_flush_t, unsigned long, unsigned long long, unsigned long*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/buf/buf0flu.cc:2026)
mysqld!buf_flush_lists(unsigned long, unsigned long long, unsigned long*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/buf/buf0flu.cc:2132)
mysqld!log_preflush_pool_modified_pages(unsigned long long) (Unknown Source:0)
mysqld!log_make_checkpoint_at(unsigned long long, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/log/log0log.cc:1910)
mysqld!trx_purge_initiate_truncate(purge_iter_t*, undo::Truncate*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1134)
mysqld!trx_purge_truncate_history(purge_iter_t*, ReadView const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1252)
mysqld!trx_purge_truncate() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1816)
mysqld!trx_purge(unsigned long, unsigned long, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0purge.cc:1914)
mysqld!srv_do_purge(unsigned long, unsigned long*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/srv/srv0srv.cc:2636)
mysqld!::srv_purge_coordinator_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/srv/srv0srv.cc:2809)
libsystem_pthread.dylib!_pthread_start (Unknown Source:0)
libsystem_pthread.dylib!thread_start (Unknown Source:0)

在buf_do_flush_list_batch的函式中我們可以看到，是獲取了flush_list_mutex，下面我們可以看到在update中，update也會獲取這個mutex

buf_do_flush_list_batch(
buf_pool_t*     buf_pool,
ulint           min_n,
lsn_t           lsn_limit)
{
ulint       count = 0;
ulint       scanned = 0;
ut_ad(buf_pool_mutex_own(buf_pool));
    /* Start from the end of the list looking for a suitable
    block to be flushed. */
buf_flush_list_mutex_enter(buf_pool);
ulint len = UT_LIST_GET_LEN(buf_pool->flush_list);

-----------------------------------下面mutex的情況是社群版本的，percona版本已經把buf pool mutex消除掉了

上面我們可以看到是對buf_pool以及lru上的塊加了mutex，下面看下update的mutex 情況，透過debug我們看到了update的堆疊

mysqld!lock_trx_has_rec_x_lock(trx_t const*, dict_table_t const*, buf_block_t const*, unsigned long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/lock/lock0lock.cc:7197)
mysqld!row_upd_clust_step(upd_node_t*, que_thr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0upd.cc:2926)
mysqld!row_upd(upd_node_t*, que_thr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0upd.cc:3054)
mysqld!row_upd_step(que_thr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0upd.cc:3200)
mysqld!row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0mysql.cc:2584)
mysqld!row_update_for_mysql(unsigned char const*, row_prebuilt_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0mysql.cc:2672)
mysqld!ha_innobase::update_row(unsigned char const*, unsigned char*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:8257)
mysqld!handler::ha_update_row(unsigned char const*, unsigned char*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:8134)
mysqld!mysql_update(THD*, List<Item>&, List<Item>&, unsigned long long, enum_duplicates, unsigned long long*, unsigned long long*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:894)
mysqld!Sql_cmd_update::try_single_table_update(THD*, bool*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:2906)
mysqld!Sql_cmd_update::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:3037)
mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:3616)
mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584)
mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491)
mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032)
mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313)
mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197)
libsystem_pthread.dylib!_pthread_start (Unknown Source:0)
libsystem_pthread.dylib!thread_start (Unknown Source:0)

在lock_trx_has_rec_x_lock的方法中，我們可以看到

bool
lock_trx_has_rec_x_lock(
/*====================*/
const trx_t*        trx,    /*!< in: transaction to check */
const dict_table_t* table,  /*!< in: table to check */
const buf_block_t*  block,  /*!< in: buffer block of the record */
ulint           heap_no)/*!< in: record heap number */
{
ut_ad(heap_no > PAGE_HEAP_NO_SUPREMUM);
lock_mutex_enter();
ut_a(lock_table_has(trx, table, LOCK_IX)
|| dict_table_is_temporary(table));
ut_a(lock_rec_has_expl(LOCK_X | LOCK_REC_NOT_GAP,
block, heap_no, trx)
|| dict_table_is_temporary(table));
lock_mutex_exit();
return(true);
}

buf_pool_mutex_enter 是加的buffer pool的mutex

這個是獲取buffer pool的mutex
/** Acquire a buffer pool mutex. */
#define buf_pool_mutex_enter(b) do {        \
    ut_ad(!(b)->zip_mutex.is_owned());  \
    mutex_enter(&(b)->mutex);       \
} while (0)

mutex_enter(&block->mutex) 這個是設定的block mutex
#define mutex_enter(M)          (M)->enter(         \
                    srv_n_spin_wait_rounds,     \
                    srv_spin_wait_delay,        \
                    __FILE__, __LINE__)

這個是獲取的lock_sys->mutex
/** Acquire the lock_sys->mutex. */
#define lock_mutex_enter() do {         \
    mutex_enter(&lock_sys->mutex);      \
} while (0)

下面的是update對flush list的操作，堆疊

mysqld!buf_flush_insert_into_flush_list(buf_pool_t*, buf_block_t*, unsigned long long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/buf/buf0flu.cc:439)
mysqld!buf_flush_note_modification(buf_block_t*, unsigned long long, unsigned long long, FlushObserver*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/include/buf0flu.ic:105)
mysqld!ReleaseBlocks::add_dirty_page_to_flush_list(mtr_memo_slot_t*) const (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/mtr/mtr0mtr.cc:356)
mysqld!ReleaseBlocks::operator()(mtr_memo_slot_t*) const (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/mtr/mtr0mtr.cc:368)
mysqld!Iterate<ReleaseBlocks>::operator()(dyn_buf_t<512ul>::block_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/mtr/mtr0mtr.cc:77)
mysqld!bool dyn_buf_t<512ul>::for_each_block_in_reverse<Iterate<ReleaseBlocks> >(Iterate<ReleaseBlocks>&) const (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/include/dyn0buf.h:375)
mysqld!mtr_t::Command::release_blocks() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/mtr/mtr0mtr.cc:956)
mysqld!mtr_t::Command::execute() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/mtr/mtr0mtr.cc:981)
mysqld!mtr_t::commit() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/mtr/mtr0mtr.cc:584)
mysqld!trx_undo_assign_undo(trx_t*, trx_undo_ptr_t*, unsigned long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0undo.cc:1839)
mysqld!trx_undo_report_row_operation(unsigned long, unsigned long, que_thr_t*, dict_index_t*, dtuple_t const*, upd_t const*, unsigned long, unsigned char const*, unsigned long const*, unsigned long long*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0rec.cc:1958)
mysqld!btr_cur_upd_lock_and_undo(unsigned long, btr_cur_t*, unsigned long const*, upd_t const*, unsigned long, que_thr_t*, mtr_t*, unsigned long long*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/btr/btr0cur.cc:3566)
mysqld!btr_cur_update_in_place(unsigned long, btr_cur_t*, unsigned long*, upd_t const*, unsigned long, que_thr_t*, unsigned long long, mtr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/btr/btr0cur.cc:3866)
mysqld!btr_cur_optimistic_update(unsigned long, btr_cur_t*, unsigned long**, mem_block_info_t**, upd_t const*, unsigned long, que_thr_t*, unsigned long long, mtr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/btr/btr0cur.cc:4026)
mysqld!row_upd_clust_rec(unsigned long, upd_node_t*, dict_index_t*, unsigned long*, mem_block_info_t**, que_thr_t*, mtr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0upd.cc:2652)
mysqld!row_upd_clust_step(upd_node_t*, que_thr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0upd.cc:2957)
mysqld!row_upd(upd_node_t*, que_thr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0upd.cc:3054)
mysqld!row_upd_step(que_thr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0upd.cc:3200)
mysqld!row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0mysql.cc:2582)
mysqld!row_update_for_mysql(unsigned char const*, row_prebuilt_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0mysql.cc:2672)

在buf_flush_insert_into_flush_list中，會獲取flush_list_mutex

Inserts a modified block into the flush list. */
void
buf_flush_insert_into_flush_list(
/*=============================*/
buf_pool_t* buf_pool,   /*!< buffer pool instance */
buf_block_t*    block,      /*!< in/out: block which is modified */
lsn_t       lsn)        /*!< in: oldest modification */
{
ut_ad(!buf_pool_mutex_own(buf_pool));
ut_ad(log_flush_order_mutex_own());
ut_ad(buf_page_mutex_own(block));
buf_flush_list_mutex_enter(buf_pool);
ut_ad((UT_LIST_GET_FIRST(buf_pool->flush_list) == NULL)
|| (UT_LIST_GET_FIRST(buf_pool->flush_list)->oldest_modification
<= lsn));

所以在執行truncate undo的時候，由於flush_list mutex的衝突，導致dml 的qps下降

有興趣學習原始碼的加群一起學習啊 QQ：        700072075