MySQL Bugs: #48614: inconsistent enforcement of max value for innodb_old_blocks

Bug #48614	inconsistent enforcement of max value for innodb_old_blocks_pct
Submitted:	7 Nov 2009 19:43	Modified:	11 Nov 2009 20:54
Reporter:	Mark Callaghan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.1.41	OS:	Any
Assigned to:	Inaam Rana	CPU Architecture:	Any
Tags:	innodb_old_blocks_pct

Description:
The max for innodb_old_blocks_pct with the option parser is 95

static MYSQL_SYSVAR_UINT(old_blocks_pct, innobase_old_blocks_pct,
  PLUGIN_VAR_RQCMDARG,
  "Percentage of the buffer pool to reserve for 'old' blocks.",
  NULL, innodb_old_blocks_pct_update, 100 * 3 / 8, 5, 95, 0);

But the max enforced at runtime via set commands is 100 because of this code:

#define BUF_LRU_OLD_RATIO_DIV   1024
/** Maximum value of buf_LRU_old_ratio.
@see buf_LRU_old_adjust_len
@see buf_LRU_old_ratio_update */
#define BUF_LRU_OLD_RATIO_MAX   BUF_LRU_OLD_RATIO_DIV

uint
buf_LRU_old_ratio_update(
/*=====================*/
        uint    old_pct,/*!< in: Reserve this percentage of
                        the buffer pool for "old" blocks. */
        ibool   adjust) /*!< in: TRUE=adjust the LRU list;
                        FALSE=just assign buf_LRU_old_ratio
                        during the initialization of InnoDB */
{
        uint    ratio;

        ratio = old_pct * BUF_LRU_OLD_RATIO_DIV / 100;
        if (ratio < BUF_LRU_OLD_RATIO_MIN) {
                ratio = BUF_LRU_OLD_RATIO_MIN;
        } else if (ratio > BUF_LRU_OLD_RATIO_MAX) {
                ratio = BUF_LRU_OLD_RATIO_MAX;
        }

How to repeat:
When using the plugin add this in my.cnf:
innodb_old_blocks_pct=96

and then run:
SHOW VARIABLES LIKE 'innodb_old_blocks_pct'

it will be 95

try this at runtime:
set global innodb_old_blocks_pct=96
SHOW VARIABLES LIKE 'innodb_old_blocks_pct'

Suggested fix:
Make enforcement of the max consistent -- either 95 or 100

Thank you for the bug report.

miguel@quetzal ~/dbs/5.1 $ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE 'innodb_old_blocks_pct'
    -> ;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 95    |
+-----------------------+-------+
1 row in set (0.01 sec)

miguel@quetzal ~/dbs $ 5.1/libexec/mysqld --ignore-builtin-innodb --plugin-load=innodb=ha_innodb_plugin.so --innodb_old_blocks_pct=96
091107 20:15:10 [Note] Plugin 'FEDERATED' is disabled.
091107 20:15:10 [Note] Plugin 'ndbcluster' is disabled.
091107 20:15:10 [Warning] option 'innodb-old-blocks-pct': unsigned value 96 adjusted to 95
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
091107 20:15:10  InnoDB: highest supported file format is Barracuda.
091107 20:15:10 InnoDB Plugin 1.0.5 started; log sequence number 44244
091107 20:15:10 [Note] Event Scheduler: Loaded 0 events
091107 20:15:10 [Note] 5.1/libexec/mysqld: ready for connections.
Version: '5.1.42-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

I tried to set the value at runtime to 98 and it was adjusted to 95 with a warning:

inaam@dabba:~/install/log$ msql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.41-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.01 sec)

mysql> set global innodb_old_blocks_pct = 95;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 95    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_old_blocks_pct = 98;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1292 | Truncated incorrect old_blocks_pct value: '98' |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 95    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> 

Am I missing something here?

Miguel,

From what you have shown where does it proves that the max value of 95 is not being enforced?

I hope I didn't report a bug that only exists in my backport of this feature to 5.0.

I have verified that the plugin behaves correctly. Change to "Not a Bug".