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

ANALYZE: r_engine_stats shows unrealistically low pages_read_count

    XMLWordPrintable

Details

    Description

      Consider an attached testcase mdev34190-test.diff

      It creates a table with 160K rows, each row being about 1K.
      This should take about 10K InnoDB disk pages (assuming page fill ratio=100%).

      Then, I restart the server, and do an ANALYZE for a full table scan.

      I take care to prevent any other activity:

      • Disable buffer pool save/load
      • Disable background statistics collection
      • Following Marko's advise, use innodb_fast_shutdown=0

      I get:

      show status like 'innodb_pages_read';
      Variable_name   Value
      Innodb_pages_read       143
      analyze format=json 
      select * from t1 force index (PRIMARY) 
      order by a asc, b asc, c asc, d asc;
      ANALYZE
      

      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 910.051,
          "table": {
            "table_name": "t1",
            "access_type": "index",
            "key": "PRIMARY",
            "key_length": "1028",
            "used_key_parts": ["a", "b", "c", "d"],
            "r_loops": 1,
            "rows": 1,
            "r_rows": 163840,
            "r_table_time_ms": 794.313,
            "r_other_time_ms": 115.711,
            "r_engine_stats": {
              "pages_accessed": 11708,
              "pages_read_count": 271,
              "pages_read_time_ms": 13.03
            },
            "filtered": 100,
            "r_filtered": 100,
            "using_index": true
          }
        }
      }
      

      only 271 pages were read? which took just 13 ms of 794 ms spent in the engine?

      show status like 'innodb_pages_read';
      Variable_name   Value
      Innodb_pages_read       12112
      

      Note that Innodb_pages_read shows a more reasonable number of 12K.

      If I get the same query to do a backward scan by using ORDER BY DESC, I get a more reasonable numbers:

      analyze format=json 
      select * from t1 force index (PRIMARY) 
      order by a desc, b desc, c desc, d desc;
      

      ANALYZE
      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 1129.6,
          "table": {
            "table_name": "t1",
            "access_type": "index",
            "key": "PRIMARY",
            "key_length": "1028",
            "used_key_parts": ["a", "b", "c", "d"],
            "r_loops": 1,
            "rows": 1,
            "r_rows": 163841,
            "r_table_time_ms": 1036.171,
            "r_other_time_ms": 93.424,
            "r_engine_stats": {
              "pages_accessed": 11708,
              "pages_read_count": 11707,
              "pages_read_time_ms": 315.503
            },
            "filtered": 100,
            "r_filtered": 100,
            "using_index": true
          }
        }
      }
      

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.