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
