Bug #83084 sys.memory_global_% tables reduced usability due to max BP being returned
Submitted: 21 Sep 2016 16:18 Modified: 22 Sep 2016 16:02
Reporter: Kenny Gryp Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7.15 OS:Any
Assigned to: Marc ALFF CPU Architecture:Any
Tags: Memory, MySQL, PFS

[21 Sep 2016 16:18] Kenny Gryp 
Description:

the sys.memory_global_% tables are reporting innodb buffer pool memory usage show the total allocatable memory usage. This makes it less useful to be used as it's not showing actual memory usage.

How to repeat:

I added `/etc/my.cnf`:

```
[mysqld]
performance-schema-instrument='memory/%=ON'
```

restarted mysql, didn't do anything:

```
57-community mysql> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 322.59 MiB      |
+-----------------+
1 row in set (0.00 sec)

57-community mysql>  select * from sys.memory_global_by_current_bytes limit 5;
+----------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name                                                           | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+----------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/buf_buf_pool                                           |             1 | 131.06 MiB    | 131.06 MiB        |          1 | 131.06 MiB | 131.06 MiB     |
| memory/innodb/log0log                                                |             9 | 32.01 MiB     | 3.56 MiB          |          9 | 32.01 MiB  | 3.56 MiB       |
| memory/performance_schema/events_statements_history_long             |             1 | 13.66 MiB     | 13.66 MiB         |          1 | 13.66 MiB  | 13.66 MiB      |
| memory/performance_schema/events_statements_history_long.sqltext     |             1 | 9.77 MiB      | 9.77 MiB          |          1 | 9.77 MiB   | 9.77 MiB       |
| memory/performance_schema/events_statements_summary_by_digest.tokens |             1 | 9.77 MiB      | 9.77 MiB          |          1 | 9.77 MiB   | 9.77 MiB       |
+----------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
5 rows in set (0.00 sec)

57-community mysql> show global variables like 'innodb_buffer_%'
    -> ;
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)
```

With 512MB buffer:

```
57-community mysql> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 736.90 MiB      |
+-----------------+
1 row in set (0.01 sec)

57-community mysql> select * from sys.memory_global_by_current_bytes limit 5;
+----------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name                                               | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+----------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/buf_buf_pool                               |             4 | 524.25 MiB    | 131.06 MiB        |          4 | 524.25 MiB | 131.06 MiB     |
| memory/innodb/log0log                                    |             9 | 32.01 MiB     | 3.56 MiB          |          9 | 32.01 MiB  | 3.56 MiB       |
| memory/innodb/hash0hash                                  |            41 | 16.29 MiB     | 406.95 KiB        |         41 | 25.31 MiB  | 632.25 KiB     |
| memory/performance_schema/events_statements_history_long |             1 | 13.66 MiB     | 13.66 MiB         |          1 | 13.66 MiB  | 13.66 MiB      |
| memory/innodb/os0event                                   |         98739 | 12.81 MiB     | 136 bytes         |      98743 | 12.81 MiB  | 136 bytes      |
+----------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
5 rows in set (0.00 sec)

57-community mysql> show global variables like 'innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 536870912      |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)
```

```
  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
 5259 mysql     20   0 1543408 218504   7660 S  0.0 21.5   0:00.38 mysqld
```

As you can see the reported memory of 736MB is not correct

I tried it on a third machine with a 5GB innodb buffer pool:

```
mysql> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 5.57 GiB        |
+-----------------+
1 row in set (0.00 sec)

mysql>  select * from sys.memory_global_by_current_bytes limit 3;
+----------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name                                               | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+----------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/buf_buf_pool                               |            40 | 5.21 GiB      | 133.25 MiB        |         40 | 5.21 GiB   | 133.25 MiB     |
| memory/innodb/hash0hash                                  |            76 | 150.38 MiB    | 1.98 MiB          |         76 | 231.43 MiB | 3.05 MiB       |
| memory/innodb/log0log                                    |             9 | 32.08 MiB     | 3.56 MiB          |          9 | 32.08 MiB  | 3.56 MiB       |
+----------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
3 rows in set (0.00 sec)

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                                             
25715 mysql     20   0 6717m 670m  10m S  0.0  9.1   0:04.82 mysqld              

SHOW ENGINE INNODB STATUS 
global buffer pool info:
Free buffers       318040
Database pages     9600
Old database pages 3699
Modified db pages  0
Pending reads      0
```

It seems like my buf_buf_pool is the ~ innodb_buffer_pool_size instead of the 150MB of actually used buffer pool size.

Suggested fix:

This makes sys.memory_global_total and sys.memory_global_by_current_bytes style tables less interesting to be used as it's not really reporting actually used memory.
[22 Sep 2016 7:00] MySQL Verification Team 
Hello Kenny,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[22 Sep 2016 7:44] Marc ALFF 
From the bug description:
"
the sys.memory_global_% tables are reporting innodb buffer pool memory usage show the total allocatable memory usage. This makes it less useful to be used as it's not showing actual memory usage.
"

This is incorrect.

The performance schema (and therefore the sys schema) reports -- allocated -- memory usage, not -- allocatable -- memory usage.

So, if a buffer pool of X Gb is allocated, X Gb will be reported.

Now, this particular memory inside innodb is indeed used for innodb data, "allocated" (by innodb own memory allocator) from the buffer pool.

What is missing here is instrumentation that shows how the innodb buffer pool is used (and possibly what it contains), but this is a different feature request.
[22 Sep 2016 16:02] Kenny Gryp 
Hello Marc,

Thanks for the explanation. You're right.

I was just confused after reading the documentation on https://github.com/mysql/mysql-sys/blob/master/README.md which talks about memory usage.

I created PR to change the wording a bit more: https://github.com/mysql/mysql-sys/pull/109