MySQL Bugs: #68497: innodb_io_capacity and innodb_lru_scan

Bug #68497	innodb_io_capacity and innodb_lru_scan_depth
Submitted:	26 Feb 2013 16:41	Modified:	31 Oct 2013 18:47
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any

Description:
I think a few InnoDB options need more details for MySQL 5.6:
* the meaning of innodb_io_capacity has changed from 5.5 to 5.6 given that "furious flushing" is not done in 5.6. 
* from browsing code, innodb_io_capacity is the rate limit per buffer pool instance. So the global limit is: innodb_io_capacity X innodb_buffer_pool_instances.
* innodb_io_capacity_max is also the limit per buffer pool instance
* innodb_lru_scan_depth is also the limit per buffer pool instance

People might be surprised that the limits are per buffer pool instance and not global especially given the change in 5.6 to respect the limits now that "furious flushing" has been removed.

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_lru_scan_depth

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_io_capacity

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_io_capacity_ma...

See http://mysqlha.blogspot.com/2013/02/mysql-56-io-bound-update-only-workloads.html

How to repeat:
read the docs, read the source (or ask InnoDB people about the source)

Suggested fix:
update the docs

Thank you for the bug report.

Updated the descriptions for innodb_io_capacity, innodb_iocapacity_max, and
innodb_lru_scan_depth to indicate that limits are per buffer pool instance,
as per the defect. Fixed applied to 5.6 and 5.7 reference manual.

Maybe I misread the code or maybe the code changed, but in 5.6.13:
* innodb_io_capacity/innodb_io_capacity_max are global limits, not per buf pool instance
* innodb_lru_scan_depth is per buf pool instance

Might be too late to change, but I think it is confusing for io_capacity to be global while lru_scan_depth is per buf pool instance.

The docs state that all are per buf pool instance:
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_lru_scan_depth 

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_io_capacity_ma...

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_io_capacity

See this code in buf_flush_list where the max pages to flush count is divided by srv_buf_pool_instances:

bool
buf_flush_list(
/*===========*/
        ulint           min_n,          /*!< in: wished minimum mumber of blocks
                                        flushed (it is not guaranteed that the
                                        actual number is that big, though) */
        lsn_t           lsn_limit,      /*!< in the case BUF_FLUSH_LIST all
                                        blocks whose oldest_modification is
                                        smaller than this should be flushed
                                        (if their number does not exceed
                                        min_n), otherwise ignored */
        ulint*          n_processed)    /*!< out: the number of pages
                                        which were processed is passed
                                        back to caller. Ignored if NULL */

{
        ulint           i;
        bool            success = true;

        if (n_processed) {
                *n_processed = 0;
        }

        if (min_n != ULINT_MAX) {
                /* Ensure that flushing is spread evenly amongst the
                buffer pool instances. When min_n is ULINT_MAX
                we need to flush everything up to the lsn limit
                so no limit here. */
                min_n = (min_n + srv_buf_pool_instances - 1)
                         / srv_buf_pool_instances;
        }

I think the docs should be updated.

Descriptions for innodb_io_capacity and innodb_io_capacity_max have been revised to indicate that the limit for both parameters is a total limit for all buffer pool instances.

Thank you for the bug report.