MySQL truncate慢影響系統qps分析

aoerqileng發表於2022-06-14
MySql

bug:

文中提到在8.0修復，下面看下5.7版本的呼叫堆疊

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:938)
mysqld!fil_reinit_space_header_for_table(dict_table_t*, unsigned long, trx_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/fil/fil0fil.cc:3016)
mysqld!row_truncate_table_for_mysql(dict_table_t*, trx_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0trunc.cc:2085)
mysqld!ha_innobase::truncate() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:12477)
mysqld!handler::ha_truncate() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:4730)
mysqld!Sql_cmd_truncate_table::handler_truncate(THD*, TABLE_LIST*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_truncate.cc:251)
mysqld!Sql_cmd_truncate_table::truncate_table(THD*, TABLE_LIST*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_truncate.cc:509)
mysqld!Sql_cmd_truncate_table::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_truncate.cc:565)
mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:4845)
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)


fil_reinit_space_header_for_table 是使用相同的space id 重新初始化表空間頭

在函式中會有 

/* Lock the search latch in shared mode to prevent user
    from disabling AHI during the scan */
btr_search_s_lock_all();
DEBUG_SYNC_C("simulate_buffer_pool_scan");
buf_LRU_flush_or_remove_pages(id, BUF_REMOVE_ALL_NO_WRITE, 0);
btr_search_s_unlock_all();


會將btr_search_latch 設定s鎖，這樣會影響dml

/** Lock all search latches in shared mode. */
UNIV_INLINE
void
btr_search_s_lock_all()
{
for (ulint i = 0; i < btr_ahi_parts; ++i) {
rw_lock_s_lock(btr_search_latches[i]);
}
}


buf_LRU_flush_or_remove_pages(id, BUF_REMOVE_ALL_NO_WRITE, 0);

在buf_LRU_flush_or_remove_pages中會遍歷每個buffer pool instance，去remove 髒頁


所以在執行truncate的時候，儘可能用drop+recreate的方式



drop 的時候會遍歷buffer pool 加buffer pool mutex 刪除屬於這個表的buffer堆疊

mysqld!buf_flush_or_remove_pages(buf_pool_t*, unsigned long, FlushObserver*, bool, trx_t const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/buf/buf0lru.cc:599)
mysqld!buf_flush_dirty_pages(buf_pool_t*, unsigned long, FlushObserver*, bool, trx_t const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/buf/buf0lru.cc:700)
mysqld!buf_LRU_remove_pages(buf_pool_t*, unsigned long, buf_remove_t, trx_t const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/buf/buf0lru.cc:902)
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:938)
mysqld!fil_delete_tablespace(unsigned long, buf_remove_t) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/fil/fil0fil.cc:2810)
mysqld!row_drop_single_table_tablespace(unsigned long, char const*, char const*, bool, bool, trx_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0mysql.cc:4249)
mysqld!row_drop_table_for_mysql(char const*, trx_t*, bool, bool, dict_table_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0mysql.cc:4800)
mysqld!ha_innobase::delete_table(char const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:12595)
mysqld!handler::ha_delete_table(char const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:4972)
mysqld!ha_delete_table(THD*, handlerton*, char const*, char const*, char const*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:2601)
mysqld!mysql_rm_table_no_locks(THD*, TABLE_LIST*, bool, bool, bool, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:2552)
mysqld!mysql_rm_table(THD*, TABLE_LIST*, char, char) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:2202)
mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:3628)
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)

drop刪除的是dirty page,discarding tablespace 會刪除lru 中的buffer,lru 中的buffer會用冷熱演算法自動淘汰，在flush 1024個頁後會釋放flush mutex,share pool mutex

所以在drop的時候，我們關心是否會有問題，可以檢視下這個表的最後一次update的時間，感覺這個不太準，還有別的方法？


percona 的lazy drop，是做了標記，刪除的lru list

The main function that is responsible for cleaning the bufferpool in the event of drop table is  buf_LRU_mark_space_was_deleted(space_id), here  space_id is the id of the tablespace corresponding to the table being dropped. Following are the steps performed in this function:

  • Take a mutex on the LRU list of the buffer pool
  • Scan through the LRU list and for each page in the LRU list:
    • If the page belongs to the tablespace being dropped, then set a flag in the page structure marking the page as belonging to a tablespace being dropped
  • Exit the mutex on the LRU list
  • Take a reader-writer shared lock on the latch protecting the Adaptive Hash Index (AHI)
  • Scan the buffer pool and for each page in the buffer pool
  • If the page has a hash index built on it, (meaning the AHI has an entry for this page):
    • Release the shared lock on the latch protecting the AHI
    • Lock the page in exclusive mode
    • Remove all entries in the AHI pointing to the page
    • Release the lock on the page
    • Take the reader-writer shared lock on the latch protecting the AHI again
  • Release the shared lock on the latch protecting the AHI


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


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25719946/viewspace-2900431/，如需轉載，請註明出處，否則將追究法律責任。

相關文章