线上一套MySQL环境的innodb_io_capacity设置为500。
root@localhost [(none)]> show global variables like '%capacity%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 500 |
| innodb_io_capacity_max | 6000 |
+------------------------+-------+
2 rows in set (0.00 sec)
压力测试使用64个并发线程,对20张1000万行的表执行OLTP读写操作。
sysbench /opt/sysbench-1.0.13/src/lua/oltp_read_write.lua --db-driver=mysql --mysql-db=sbtest
--mysql-user=root --mysql-password --mysql-socket=/data/mysql/mysql.sock --tables=20
--table-size=10000000 --threads=64 --time=600 --report-interval=10 run
QPS性能曲线
InnoDB后台插入性能曲线
将参数innodb_io_capacity修改为2000。
root@localhost [(none)]> show global variables like '%capacity%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 2000 |
| innodb_io_capacity_max | 6000 |
+------------------------+-------+
2 rows in set (0.00 sec)
QPS性能曲线
性能比innodb_io_capacity设为500更平稳,之前性能的波谷抖动情况减少。
InnoDB后台插入性能曲线
源码中关于 innodb_io_capacity、innodb_io_capacity_max 的调用
/* buf0flu.cc */
/* 计算脏页的刷新百分比阀值 */
/*********************************************************************//**
Calculates if flushing is required based on number of dirty pages in
the buffer pool.
@return percent of io_capacity to flush to manage dirty page ratio */
static
ulint
af_get_pct_for_dirty()
/*==================*/
{
ulint dirty_pct = buf_get_modified_ratio_pct();
if (dirty_pct > 0 && srv_max_buf_pool_modified_pct == 0) {
return(100);
}
ut_a(srv_max_dirty_pages_pct_lwm
<= srv_max_buf_pool_modified_pct);
if (srv_max_dirty_pages_pct_lwm == 0) {
/* The user has not set the option to preflush dirty
pages as we approach the high water mark. */
if (dirty_pct > srv_max_buf_pool_modified_pct) {
/* We have crossed the high water mark of dirty
pages In this case we start flushing at 100% of
innodb_io_capacity. */
return(100);
}
} else if (dirty_pct > srv_max_dirty_pages_pct_lwm) {
/* We should start flushing pages gradually. */
return((dirty_pct * 100)
/ (srv_max_buf_pool_modified_pct + 1));
}
return(0);
}
/* 根据redo日志的生成频率,计算是否需要刷新脏页到磁盘 */
/*********************************************************************//**
Calculates if flushing is required based on redo generation rate.
@return percent of io_capacity to flush to manage redo space */
static
ulint
af_get_pct_for_lsn(
/*===============*/
lsn_t age) /*!< in: current age of LSN. */
{
lsn_t max_async_age;
lsn_t lsn_age_factor;
lsn_t af_lwm = (srv_adaptive_flushing_lwm
* log_get_capacity()) / 100;
if (age < af_lwm) {
/* No adaptive flushing. */
return(0);
}
max_async_age = log_get_max_modified_age_async();
if (age < max_async_age && !srv_adaptive_flushing) {
/* We have still not reached the max_async point and
the user has disabled adaptive flushing. */
return(0);
}
/* If we are here then we know that either:
1) User has enabled adaptive flushing
2) User may have disabled adaptive flushing but we have reached
max_async_age. */
lsn_age_factor = (age * 100) / max_async_age;
ut_ad(srv_max_io_capacity >= srv_io_capacity);
return(static_cast<ulint>(
((srv_max_io_capacity / srv_io_capacity)
* (lsn_age_factor * sqrt((double)lsn_age_factor)))
/ 7.5));
}
/* 刷新脏页 */
/*********************************************************************//**
This function is called approximately once every second by the
page_cleaner thread. Based on various factors it decides if there is a
need to do flushing. If flushing is needed it is performed and the
number of pages flushed is returned.
@return number of pages flushed */
static
ulint
page_cleaner_flush_pages_if_needed(void)
...
/* Cap the maximum IO capacity that we are going to use by
max_io_capacity. */
n_pages = (PCT_IO(pct_total) + avg_page_rate) / 2;
if (n_pages > srv_max_io_capacity) {
n_pages = srv_max_io_capacity;
}
if (last_pages && cur_lsn - last_lsn > lsn_avg_rate / 2) {
age_factor = static_cast<int>(prev_pages / last_pages);
}
MONITOR_SET(MONITOR_FLUSH_N_TO_FLUSH_REQUESTED, n_pages);
prev_pages = n_pages;
n_pages = page_cleaner_do_flush_batch(
n_pages, oldest_lsn + lsn_avg_rate * (age_factor + 1));
last_lsn= cur_lsn;
last_pages= n_pages + 1;
MONITOR_SET(MONITOR_FLUSH_AVG_PAGE_RATE, avg_page_rate);
MONITOR_SET(MONITOR_FLUSH_LSN_AVG_RATE, lsn_avg_rate);
MONITOR_SET(MONITOR_FLUSH_PCT_FOR_DIRTY, pct_for_dirty);
MONITOR_SET(MONITOR_FLUSH_PCT_FOR_LSN, pct_for_lsn);
if (n_pages) {
MONITOR_INC_VALUE_CUMULATIVE(
MONITOR_FLUSH_ADAPTIVE_TOTAL_PAGE,
MONITOR_FLUSH_ADAPTIVE_COUNT,
MONITOR_FLUSH_ADAPTIVE_PAGES,
n_pages);
sum_pages += n_pages;
}
return(n_pages);
}
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26506993/viewspace-2214703/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26506993/viewspace-2214703/