innodb存储引擎之innodb_io_capacity, innodb_max_dirty_pages_pct以及innodb_adaptive_flushing参数研究与源码分析（未完待续）

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

参考：