关键词:innodb 存储引擎 innodb_io_capacity innodb_max_dirty_pages_pct innodb_adaptive_flushing
摘要:个人学习记录,仅供参考。
innodb_io_capacity,innodb_max_dirty_pages_pct,
innodb_adaptive_flushing这三个参数是为了解决SSD等大容量存储设备的出现而导致innoDB不能很好利用这类设备性能的困境而出现的,本文会结合mysql-5.5.34的源代码进行这三个参数的介绍。
正文:
master 线程位于srv/srv0srv.c,线程名称srv_master_thread。
第一步:测试数据创建
请参见《导入 Mysql 示例数据库
employees》
第二步:innodb_io_capacity,innodb_max_dirty_pages_pct,
innodb_adaptive_flushing参数分析
innodb_io_capacity 在代码中就是srv_io_capacity,位于srv/srv0srv.c,算法如下
#define PCT_IO(p) ((ulong) (srv_io_capacity * ((double) p / 100.0)))
返回每秒IO读写百分比。
MasterThread 中关于innodb_io_capacity伪代码标记
/* If i/os during one second sleep were less than 5% of
capacity, we assume that there is free disk i/o capacity
available, and it makes sense to do an insert buffer merge. */
buf_get_total_stat(&buf_stat);
n_pend_ios = buf_get_n_pending_ios()
+ log_sys->n_pending_writes;
n_ios = log_sys->n_log_ios + buf_stat.n_pages_read
+ buf_stat.n_pages_written;
if (n_pend_ios < SRV_PEND_IO_THRESHOLD
&& (n_ios - n_ios_old < SRV_RECENT_IO_ACTIVITY)) {
srv_main_thread_op_info = "doing insert buffer merge";
ibuf_contract_for_n_pages(FALSE, PCT_IO(5));
/* Flush logs if needed */
srv_sync_log_buffer_in_background();
}
if (UNIV_UNLIKELY(buf_get_modified_ratio_pct()
> srv_max_buf_pool_modified_pct)) {
/* Try to keep the number of modified pages in the
buffer pool under the limit wished by the user */
srv_main_thread_op_info =
"flushing buffer pool pages";
n_pages_flushed = buf_flush_list(
PCT_IO(100), IB_ULONGLONG_MAX);
} else if (srv_adaptive_flushing) {
/* Try to keep the rate of flushing of dirty
pages such that redo log generation does not
produce bursts of IO at checkpoint time. */
ulint n_flush = buf_flush_get_desired_flush_rate();
if (n_flush) {
srv_main_thread_op_info =
"flushing buffer pool pages";
n_flush = ut_min(PCT_IO(100), n_flush);
n_pages_flushed =
buf_flush_list(
n_flush,
IB_ULONGLONG_MAX);
}
}
if (srv_activity_count == old_activity_count) {
/* There is no user activity at the moment, go to
the background loop */
goto background_loop;
}
}
1、if (LastOneSecondIOs < 5% innodb_io_capacity) then do merge 5% innodb_io_capacity insert buffer endif
code注解:
2、if (BufGetModifiedRatIOPCT > InnodbMaxDirtyPagesPCT) then do buffer pool flush 100% innodb_io_capacity dirty page endif
code注解:
3、if (LastTenSecondIOs < innodb_io_capacity) then do buffer pool flush 100% innodb_io_capacity insert buffer endif
code注解:
参考: