MySQL truncate慢影響系統qps分析
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- undo truncate 導致qps下降分析
- truncate 比 delete 慢delete
- truncate操作巨慢
- 測試truncate，delete 對rman 備份集大小的影響delete
- truncate 比 delete 慢的原因。delete
- mysql事務對效率的影響分析總結JILEMySql
- SAP系統執行慢的原因分析
- 分割槽表truncate慢處理
- MySQL資料庫的效能的影響分析及其優化MySql資料庫優化
- 修改系統時間對oracle的影響Oracle
- 7個方面影響OA系統穩定性
- 關於java檔名字影響系統配置Java
- Rollback&Truncate操作對高水位線影響之效能優化篇優化
- MySQL truncate原理MySql
- MYSQL sync_relay_log對I/O thread的影響分析MySqlthread
- 開源元件漏洞影響多個 CMS 系統元件
- CRM系統對企業的潛在影響
- 硬體環境對系統效能的影響
- 影響OLTP 系統效能的儲存因素解析
- 實驗總結分析報告 ——從系統的角度分析影響程式執行效能的因素
- 表的storage (MINEXTENTS 屬性對truncate後表大小的影響
- oam系統安裝，windows作業系統註冊列表影響系統安裝Windows作業系統
- Mysql慢SQL分析及優化MySql優化
- MySQL crash recovery恢復慢分析MySql
- 影響mysql效能的因素都有哪些MySql
- MySQL alter 新增列對dml影響MySql
- 影響MySQL效能的硬體因素MySql
- 影響MySQL效能的硬體因MySql
- mysql event對主從的影響MySql
- linux系統影響tcp連線數的因素LinuxTCP
- 一次資料庫響應慢分析資料庫
- 關於LOL成就係統對玩家影響的簡略分析
- Mysql 慢日誌分析工具MysqldumpslowMySql
- 關於mysql連線慢的分析.MySql
- mysqlsla 分析mysql慢查詢日誌MySql
- MySQL null值儲存，null效能影響MySqlNull
- 表資料量影響MySQL索引選擇MySql索引
- 產品資料管理對ERP系統的影響