mysql innodbmaxdirtypagespct_innodb存储引擎之innodb_io_capacity, innodb_max_dirty_pages_pct以及innodb_adapt...

关键词：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。

第一步：测试数据创建

第二步：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注解：

参考：