Development

I just did a new install of 10.1.32 and innodb_empty_free_list_algorithm defaults to LEGACY. This appears to be an issue with upgrading only.

As serg found out, XtraDB modifies the value without telling the server, and it does so conditionally. So, for big buffer pools it's still BACKOFF, while for small ones it's LEGACY, and we have a discrepancy in SYSTEM_VARIABLES:

*************************** 1. row ***************************

        VARIABLE_NAME: INNODB_EMPTY_FREE_LIST_ALGORITHM

        SESSION_VALUE: NULL

         GLOBAL_VALUE: LEGACY

  GLOBAL_VALUE_ORIGIN: COMPILE-TIME

        DEFAULT_VALUE: BACKOFF

       VARIABLE_SCOPE: GLOBAL

        VARIABLE_TYPE: ENUM

     VARIABLE_COMMENT: The algorithm to use for empty free list handling.  Allowed values: LEGACY: Original Oracle MySQL 5.6 handling with single page flushes; BACKOFF: (default) Wait until cleaner produces a free page.

    NUMERIC_MIN_VALUE: NULL

    NUMERIC_MAX_VALUE: NULL

   NUMERIC_BLOCK_SIZE: NULL

      ENUM_VALUE_LIST: LEGACY,BACKOFF

            READ_ONLY: NO

COMMAND_LINE_ARGUMENT: OPTIONAL

1 row in set (0.01 sec)

(Global value originated from compile-time is LEGACY, while default value is BACKOFF).

Below is the query result of an upgraded 10.1.32 database with the default setting for innodb_empty_free_list_algorithm

MariaDB [(none)]> select * from information_schema.system_variables where variable_name = 'innodb_empty_free_list_algorithm'\G

VARIABLE_NAME: INNODB_EMPTY_FREE_LIST_ALGORITHM
SESSION_VALUE: NULL
GLOBAL_VALUE: BACKOFF
GLOBAL_VALUE_ORIGIN: COMPILE-TIME
DEFAULT_VALUE: BACKOFF
VARIABLE_SCOPE: GLOBAL
VARIABLE_TYPE: ENUM
VARIABLE_COMMENT: The algorithm to use for empty free list handling. Allowed values: LEGACY: Original Oracle MySQL 5.6 handling with single page flushes; BACKOFF: (default) Wait until cleaner produces a free page.
NUMERIC_MIN_VALUE: NULL
NUMERIC_MAX_VALUE: NULL
NUMERIC_BLOCK_SIZE: NULL
ENUM_VALUE_LIST: LEGACY,BACKOFF
READ_ONLY: NO
COMMAND_LINE_ARGUMENT: OPTIONAL
1 row in set (0.00 sec)

As Sergei Golubchik found out, XtraDB modifies the value without telling the server, and it does so conditionally. So, for big buffer pools it's still BACKOFF, while for small ones it's LEGACY, and we have a discrepancy in SYSTEM_VARIABLES:

At what size of a buffer pool will it choose LEGACY?

When we manually set innodb_empty_free_list_algorithm=LEGACY we no longer get the '[Warning] InnoDB: Difficult to find free blocks in the buffer' in the mysql error log. We changed the parameter to LEGACY on more than 10 databases and the warning went away. Can you explain the cause of this warning, its significance and how to make it go away?

For the record, a message was changed in MDEV-14776. As noted in MDEV-16617, the message used to be 3 lines when it was introduced in MySQL 3.23.44:

InnoDB: Warning: difficult to find free blocks from

InnoDB: the buffer pool (%lu search iterations)! Consider

InnoDB: increasing the buffer pool size.

The XtraDB parameter innodb_empty_free_list_algorithm=BACKOFF looks like it would cause buf_LRU_get_free_block() to sleep, relying on page cleaners to free up more blocks, and emitting the message if it fails to find free blocks due to that modification. XtraDB is not supported by MariaDB 10.2 or later, and the parameter never existed in InnoDB.

The default value of the parameter is BACKOFF since its introduction in MariaDB Server 10.0.10.

Given that MariaDB 10.0 has already reached and 10.1 will soon be at its end of life, I am reluctant to change the default value of this parameter.

Note: Before MDEV-19176 was fixed, InnoDB recovery could hang, emitting this message in an infinite loop.

MariaDB Server 10.1 reached its end of life in October 2020. The last release was 10.1.48.