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

Setting innodb_buffer_pool_size to the maximum value can cause drastic performance degradation

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.32, 10.5.23, 10.6.16, 10.11.5
    • 10.5, 10.6, 10.11
    • None
    • docker mariadb:10.11

    Description

      docker run --restart=always --name mariadb1011enginevalidate -e MYSQL_ROOT_PASSWORD=123456 -d mariadb:10.11

      > create database test;
      > use test;
      > source init_tables.sql;
      > source select.sql;

      When executing source select.sql normally, you will get that the empty set takes about 0.002s.
      But when SET GLOBAL innodb_buffer_pool_size = 9223372036854775807; this value is the legal maximum value described in the document, and the execution time of source select.sql can reach about 50 seconds.
      Performance drops greatly. Why is this?

      https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size

      Attachments

        1. 0.002s.png
          33 kB
          fuying
        2. 50s.png
          31 kB
          fuying
        3. backup.sql
          7.43 MB
          fuying
        4. init_tables.sql
          9.01 MB
          fuying
        5. select.sql
          18 kB
          fuying

        Issue Links

          Activity

            People

              danblack Daniel Black
              ying fuying
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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