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

Aria_pagecache_blocks_unused and Aria_pagecache_blocks_used status variables are updated inconsistently

    XMLWordPrintable

Details

    Description

      --source include/have_sequence.inc
      		 
       
      		 
      select variable_name, variable_value from information_schema.global_status where variable_name in ('Aria_pagecache_blocks_unused','Aria_pagecache_blocks_used');
      		 
      create table t1 (pk int primary key, a int, b int, key(a)) engine=Aria;
      		 
      insert into t1 select seq, seq, seq from seq_1_to_10000;
      		 
      select variable_name, variable_value from information_schema.global_status where variable_name in ('Aria_pagecache_blocks_unused','Aria_pagecache_blocks_used');
      		 
      select * from t1 where a = 2;
      		 
      select variable_name, variable_value from information_schema.global_status where variable_name in ('Aria_pagecache_blocks_unused','Aria_pagecache_blocks_used');
      		 
      flush status;
      		 
      select variable_name, variable_value from information_schema.global_status where variable_name in ('Aria_pagecache_blocks_unused','Aria_pagecache_blocks_used');
      		 
      flush tables;
      		 
      select variable_name, variable_value from information_schema.global_status where variable_name in ('Aria_pagecache_blocks_unused','Aria_pagecache_blocks_used');
      		 
      drop table t1;

      main 4b79d7b8ee557d53a859aedec839b8673585b514

      		 
      select variable_name, variable_value from information_schema.global_status where variable_name in ('Aria_pagecache_blocks_unused','Aria_pagecache_blocks_used');
      		 
      variable_name	variable_value
      		 
      ARIA_PAGECACHE_BLOCKS_UNUSED	883
      		 
      ARIA_PAGECACHE_BLOCKS_USED	108
      		 
      create table t1 (pk int primary key, a int, b int, key(a)) engine=Aria;
      		 
      insert into t1 select seq, seq, seq from seq_1_to_10000;
      		 
      select variable_name, variable_value from information_schema.global_status where variable_name in ('Aria_pagecache_blocks_unused','Aria_pagecache_blocks_used');
      		 
      variable_name	variable_value
      		 
      ARIA_PAGECACHE_BLOCKS_UNUSED	883
      		 
      ARIA_PAGECACHE_BLOCKS_USED	159
      		 
      select * from t1 where a = 2;
      		 
      pk	a	b
      		 
      2	2	2
      		 
      select variable_name, variable_value from information_schema.global_status where variable_name in ('Aria_pagecache_blocks_unused','Aria_pagecache_blocks_used');
      		 
      variable_name	variable_value
      		 
      ARIA_PAGECACHE_BLOCKS_UNUSED	880
      		 
      ARIA_PAGECACHE_BLOCKS_USED	159
      		 
      flush status;
      		 
      select variable_name, variable_value from information_schema.global_status where variable_name in ('Aria_pagecache_blocks_unused','Aria_pagecache_blocks_used');
      		 
      variable_name	variable_value
      		 
      ARIA_PAGECACHE_BLOCKS_UNUSED	880
      		 
      ARIA_PAGECACHE_BLOCKS_USED	159
      		 
      flush tables;
      		 
      select variable_name, variable_value from information_schema.global_status where variable_name in ('Aria_pagecache_blocks_unused','Aria_pagecache_blocks_used');
      		 
      variable_name	variable_value
      		 
      ARIA_PAGECACHE_BLOCKS_UNUSED	975
      		 
      ARIA_PAGECACHE_BLOCKS_USED	159
      		 
      drop table t1;

      So, upon insert into the table, ARIA_PAGECACHE_BLOCKS_USED increased noticeably (108 => 153), but ARIA_PAGECACHE_BLOCKS_UNUSED didn't change at all.

      Upon select from the table, it's the other way round – ARIA_PAGECACHE_BLOCKS_UNUSED changed (883 => 880), but ARIA_PAGECACHE_BLOCKS_USED didn't. Same upon FLUSH TABLES.

      FLUSH STATUS doesn't play a role here, which is logical, but contradicts the documentation which, on some reason, say that FLUSH STATUS should affect these values.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25286 Docu: aria status in docu does not reflect reality

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36916 The number of pagecache segments affects counting of used/unused blocks

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            People

              monty Michael Widenius
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated: