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

Small buffer pool size causes XtraDB buffer flush thread to spin at 100% cpu

    XMLWordPrintable

Details

    Description

      Small innodb_buffer_pool_size causes mysqld to spin at 100% CPU.

      Here is how I could repeat:

      Start server with:

      sql/mysqld --no-defaults --language=$(pwd)/sql/share/english --basedir=$(pwd) --datadir=$X --socket=$(pwd)/$X/mysql.sock --port=3310 --innodb-buffer-pool-size=5M

      Create some dirty pages to trigger flush thread:

      create table t1 (a int primary key auto_increment, b varchar (1000));
      insert into t1 (b) values (repeat("a", 999));
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;
      insert into t1 (b) select b from t1;

      This causes the mysqld process to appear at 100% CPU in `top`.

      The problem seems to be related to heuristics with hard-coded constants that
      assume a certain minimum pool size. In page_cleaner_adapt_lru_sleep_time(),
      if the number of free pages is less than @@innodb_lru_scan_depth / 20 (which
      works out as 51 by default), it will never sleep between trying to flush
      pages. Probably the page flusher has another heuristics that doesn't try to
      flush out more than some percentage of total pages or something.

      Se with small buffer pool, we end up with two heuristics fighting one another,
      and 100% cpu spinning results.

      Attachments

        Activity

          People

            jplindst Jan Lindström (Inactive)
            knielsen Kristian Nielsen
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.