ITPub博客

首页 > 数据库 > MySQL > innodb drop table的改进

innodb drop table的改进

原创 MySQL 作者:myownstars 时间:2013-12-27 15:51:15 0 删除 编辑

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/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3320732