首页 > 数据库 > MySQL > innodb drop table的改进
innodb在drop table会2次锁定LRU list,分别进行清除adaptive hash index和LRU & flush list,期间会延阻其他操作,当buffer pool较大时问题尤为明显,innodb_file_per_table =0时由于不会discard tablespace故而会更快一些。
每个版本对此都有些许的改进,主要是减少持有锁的次数和粒度。
在Mysql 5.6中,客户端发出drop table命令,传至innodb存储引擎后,依次调用如下API
ha_innobase::delete_table -- row_drop_table_for_mysql -- fil_delete_tablespace(space_id,BUF_REMOVE_FLUSH_NO_WRITE) -- buf_LRU_flush_or_remove_pages(id, buf_remove, 0);
而buf_LRU_flush_or_remove_pages根据传入参数buf_remove调用不同API,或先删除hash index,或直接操作LRU & flush list;
其逻辑如下
1
buf_LRU_drop_page_hash_for_tablespace 清除hash index entry
遍历LRU list,待候选页凑足1024后调用buf_LRU_drop_page_hash_batch,执行此函数前后释放buf_pool mutex,避免长期阻塞其他操作
所谓批量删除hash index,其实是for循环调用btr_search_drop_page_hash_when_freed
2
buf_LRU_remove_pages 刷新LRU list & flush list
注: buf_remove取值范围如下,
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 */
};
下面是API细节
fil_delete_tablespace(ulint id, buf_remove_t buf_remove) {
--串行化drop tablespace操作
rw_lock_x_lock(&space->latch);
#ifndef UNIV_HOTBACKUP
buf_LRU_flush_or_remove_pages(id, buf_remove, 0);
#endif
--删除物理文件
--记录redo
}
buf_LRU_flush_or_remove_pages(ulint id, buf_remove_t buf_remove, const trx_t* trx)
{
--遍历buffer pool实例
for (i = 0; i < srv_buf_pool_instances; i++) {
--依据buf_remove决定是否删除hash index entry
switch (buf_remove) {
case BUF_REMOVE_ALL_NO_WRITE:
buf_LRU_drop_page_hash_for_tablespace(buf_pool, id);
break;
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;
}
buf_LRU_remove_pages(buf_pool, id, buf_remove, trx);
}
}
注:不管是删除hash index还是LRU & flush list,都需要buf_pool mutex
1 删除hash index
buf_LRU_flush_or_remove_pages -- buf_LRU_drop_page_hash_for_tablespace
buf_LRU_drop_page_hash_for_tablespace(buf_pool_t* buf_pool,ulint id)
{
--申请buf_pool mutex
buf_pool_mutex_enter(buf_pool);
--从后向前遍历LRU list,针对每个page检查其state & space & io_fix字段,如不符合要求跳过
scan_again:
bpage = UT_LIST_GET_LAST(buf_pool->LRU);
while (bpage != NULL) {
prev_bpage = UT_LIST_GET_PREV(LRU, bpage);
--待到候选页累计到 BUF_LRU_DROP_SEARCH_SIZE(1024),释放buf_pool mutex,调用buf_LRU_drop_page_hash_batch批量删除hash index
buf_pool_mutex_exit(buf_pool);
buf_LRU_drop_page_hash_batch(id, zip_size, page_arr, num_entries);
--再次申请,周期性是否buf_pool mutex避免长期阻塞其他操作
buf_pool_mutex_enter(buf_pool);
--由于执行buf_LRU_drop_page_hash_batch期间释放了buf_pool mutex,此刻bpage可能发生位移,甚至已经在free list中
--对于前者则继续while循环(可能漏掉部分hash entry),后者则跳转至scan_again重新开始扫描
if (bpage && buf_page_get_state(bpage) != BUF_BLOCK_FILE_PAGE) {
goto scan_again;
}
}
buf_pool_mutex_exit(buf_pool);
buf_LRU_drop_page_hash_batch(id, zip_size, page_arr, num_entries);
ut_free(page_arr);
}
buf_LRU_flush_or_remove_pages -- buf_LRU_drop_page_hash_for_tablespace -- buf_LRU_drop_page_hash_batch
buf_LRU_drop_page_hash_batch(ulint space_id,ulint zip_size,const ulint* arr,ulint count)
{
--批量删除hash index
for (i = 0; i < count; ++i) {
--调用btr_search_drop_page_hash_index(block)删除
btr_search_drop_page_hash_when_freed(space_id, zip_size, arr[i]);
}
}
2 清理LRU & flush list
buf_LRU_flush_or_remove_pages -- buf_LRU_remove_pages
buf_LRU_remove_pages(buf_pool_t* buf_pool,ulint id,buf_remove_t buf_remove,const trx_t* trx)
{
switch (buf_remove) {
case BUF_REMOVE_ALL_NO_WRITE:
buf_LRU_remove_all_pages(buf_pool, id);
break;
case BUF_REMOVE_FLUSH_NO_WRITE:
ut_a(trx == 0);
buf_flush_dirty_pages(buf_pool, id, false, NULL);
break;
case BUF_REMOVE_FLUSH_WRITE:
ut_a(trx != 0);
buf_flush_dirty_pages(buf_pool, id, true, trx);
/* Ensure that all asynchronous IO is completed. */
os_aio_wait_until_no_pending_writes();
fil_flush(id);
break;
}
}
2.1 同时清理LRU list和flush list
buf_LRU_flush_or_remove_pages -- buf_LRU_remove_pages -- buf_LRU_remove_all_pages
buf_LRU_remove_all_pages(buf_pool_t* buf_pool,ulint id)
{
scan_again:
buf_pool_mutex_enter(buf_pool);
--遍历LRU list
for (bpage = UT_LIST_GET_LAST(buf_pool->LRU); bpage != NULL;)
{
prev_bpage = UT_LIST_GET_PREV(LRU, bpage);
--申请hash_lock & block_mutex
hash_lock = buf_page_hash_lock_get(buf_pool, fold);
rw_lock_x_lock(hash_lock);
block_mutex = buf_page_get_mutex(bpage);
mutex_enter(block_mutex);
--如果bpage->index >0则释放申请到的buf_pool & hash_lock & block_mutex,然后删除相应的hash index entry
btr_search_drop_page_hash_when_freed(id, zip_size, page_no);
goto scan_again;
--刷新flush list
if (bpage->oldest_modification != 0) {
buf_flush_remove(bpage);
}
--调用buf_LRU_remove_block(bpage)清理LRU list,如果没有hash index entry则将其并放入free list
if (buf_LRU_block_remove_hashed(bpage, true)) {
buf_LRU_block_free_hashed_page((buf_block_t*) bpage);
} else {
ut_ad(block_mutex == &buf_pool->zip_mutex);
}
next_page:
bpage = prev_bpage;
}
buf_pool_mutex_exit(buf_pool);
}
2.2 只清理flush list,当flush=true时异步刷新至磁盘,否则仅将页从flust list移出
buf_LRU_flush_or_remove_pages -- buf_LRU_remove_pages -- buf_flush_dirty_pages
buf_flush_dirty_pages(buf_pool_t* buf_pool,ulint id,bool flush,const trx_t* trx)
{
--循环执行,直至err != DB_FAIL
buf_pool_mutex_enter(buf_pool);
err = buf_flush_or_remove_pages(buf_pool, id, flush, trx);
buf_pool_mutex_exit(buf_pool);
}
buf_LRU_flush_or_remove_pages -- buf_LRU_remove_pages -- buf_flush_dirty_pages -- buf_flush_or_remove_pages
buf_flush_or_remove_pages(buf_pool_t* buf_pool, ulint id,bool flush,const trx_t* trx)
{
buf_flush_list_mutex_enter(buf_pool);
rescan:
--遍历flush list,整个循环一直占有flush_list mutex
--依次对每个页调用buf_flush_or_remove_page(buf_pool, bpage, flush)
for (bpage = UT_LIST_GET_LAST(buf_pool->flush_list);bpage != NULL; bpage = prev)
{
prev = UT_LIST_GET_PREV(list, bpage);
if (!buf_flush_or_remove_page(buf_pool, bpage, flush))
{
all_freed = false;
} else if (flush) {
goto rescan;
}
++processed;
/* Yield if we have hogged the CPU and mutexes for too long. */
if (buf_flush_try_yield(buf_pool, prev, processed)) {
/* Reset the batch size counter if we had to yield. */
processed = 0;
}
/* The check for trx is interrupted is expensive, we want to check every N iterations. */
if (!processed && trx && trx_is_interrupted(trx)) {
buf_flush_list_mutex_exit(buf_pool);
return(DB_INTERRUPTED);
}
}
buf_flush_list_mutex_exit(buf_pool);
}
buf_LRU_flush_or_remove_pages -- buf_LRU_remove_pages -- buf_flush_dirty_pages -- buf_flush_or_remove_pages -- buf_flush_or_remove_page
--如果flush=false即不需要刷新至磁盘,则调用buf_flush_remove()将页从flush_list移出
--否则调用buf_flush_page--异步刷新至磁盘,采用simulated AIO机制
buf_flush_or_remove_page(buf_pool_t* buf_pool,buf_page_t* bpage,bool flush)
{
--释放flush list mutex,申请block_mutex
buf_flush_list_mutex_exit(buf_pool);
mutex_enter(block_mutex);
if (!flush) {
buf_flush_remove(bpage);
mutex_exit(block_mutex);
processed = true;
} else if (buf_flush_ready_for_flush(bpage,BUF_FLUSH_SINGLE_PAGE)) {
buf_flush_page(buf_pool, bpage, BUF_FLUSH_SINGLE_PAGE);
os_aio_simulated_wake_handler_threads();
buf_pool_mutex_enter(buf_pool);
processed = true;
} else {
mutex_exit(block_mutex);
}
buf_flush_list_mutex_enter(buf_pool);
return(processed);
}
小结:
由delete_table最终调用的buf_LRU_flush_or_remove_pages,传入参数为BUF_REMOVE_FLUSH_NO_WRITE,故跳过了hash index,只需遍历一次LRU list;
另外flush=false,只需将脏页从flush list移出,不需要刷新至磁盘
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/15480802/viewspace-1065024/ ,如需转载,请注明出处,否则将追究法律责任。