Bug #74009 Buffer Pool graph: used > total
Submitted: 22 Sep 2014 10:50 Modified: 22 Sep 2014 13:24
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Enterprise Monitor: Web Severity:S3 (Non-critical)
Version:3.0.14 OS:Any
Assigned to: CPU Architecture:Any

[22 Sep 2014 10:50] Daniël van Eeden
Description:
In the InnoDB Buffer Pool graph for the instance running the MEM repository the amount used is larger than the total amount.

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 824180736; in additional pool allocated 0
Dictionary memory allocated 2105005
Buffer pool size   49151
Free buffers       1024
Database pages     43692
Old database pages 16108
Modified db pages  4745
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 558522, not young 585852
2.00 youngs/s, 0.00 non-youngs/s
Pages read 40057, created 3635, written 273419
0.00 reads/s, 1.00 creates/s, 99.95 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 43692, unzip_LRU len: 21133
I/O sum[4231]:cur[0], unzip sum[287]:cur[0]

How to repeat:
Go to the buffer pool graph.
[22 Sep 2014 10:52] Daniël van Eeden
Graph

Attachment: mem_bp_used_gt_total.png (image/png, text), 50.97 KiB.

[22 Sep 2014 10:52] Daniël van Eeden
I expect this to be due to compression being activated.
[22 Sep 2014 13:24] MySQL Verification Team
Hello Daniël,

Thank you for the report.
Verified as described.

You are right compression triggers this issue.

Thanks,
Umesh
[22 Sep 2014 13:26] MySQL Verification Team
How to repeat:

Run something like below, and observe the buffer pool graph:

SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
 
drop table if exists keyvalue10;
CREATE TABLE `keyvalue10` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250),
  PRIMARY KEY (`id`)
) ENGINE=innodb ROW_FORMAT=COMPRESSED 
 KEY_BLOCK_SIZE=8;;

set @id:=0;

 insert into `keyvalue10` values (@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000)),(@id:=@id+1,md5(rand()*1000000));

 insert into `keyvalue10`(`id`,`name`)
   select @id:=@id+1,md5(rand()*1000000) from
    `keyvalue10` k1, `keyvalue10` k2, `keyvalue10` k3, `keyvalue10` k4,`keyvalue10` k5,`keyvalue10` k6, `keyvalue10` k7, `keyvalue10` k8, `keyvalue10` k9,
`keyvalue10` k0,`keyvalue10` ka, `keyvalue10` kb, `keyvalue10` kc, `keyvalue10` kd limit 10000000;
[22 Sep 2014 13:27] MySQL Verification Team
Screenshot..

Attachment: 74009.png (image/png, text), 55.19 KiB.