Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16339

Upgrading to 10.1.32 shows innodb_empty_free_list_algorithm=BACKOFF as default when it should be 'LEGACY'

Details

    Description

      Upgrading from 10.0 to 10.1.32 the default value for innodb_empty_free_list_algorithm is set to BACKOFF when documentation shows >=10.1.24 it should default to LEGACY.

      May be contributing to error log entries
      [Warning] InnoDB: Difficult to find free blocks in the buffer pool

      Attachments

        Issue Links

          Activity

            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?

            joe.oreste@xpressbet.com Joseph Oreste (Inactive) added a comment - 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?
            joe.oreste@xpressbet.com Joseph Oreste (Inactive) added a comment - - edited

            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?

            joe.oreste@xpressbet.com Joseph Oreste (Inactive) added a comment - - edited 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.
            

            marko Marko Mäkelä added a comment - 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.

            marko Marko Mäkelä added a comment - 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.

            marko Marko Mäkelä added a comment - MariaDB Server 10.1 reached its end of life in October 2020. The last release was 10.1.48.

            People

              serg Sergei Golubchik
              joe.oreste@xpressbet.com Joseph Oreste (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.