Bug #90561 pfs memory statistics missing InnoDB buffer pool
Submitted: 23 Apr 2018 0:17 Modified: 7 Jun 2018 21:59
Reporter: Morgan Tocker Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:8.0.11 OS:Any
Assigned to: CPU Architecture:Any

[23 Apr 2018 0:17] Morgan Tocker
Description:
I installed 8.0.11 GA on my macbook, and was peeking around at the resources a minimal install took.

The numbers for InnoDB look too good to be true (default buffer pool is 128MiB).

How to repeat:
select SUBSTRING_INDEX(event_name, '/', 2) as g, SUM(t1.current_count), format_bytes(SUM(t1.current_alloc)) as current_alloc, SUM(t1.high_alloc) from x$memory_global_by_current_bytes t1 group by g order by SUM(t1.current_alloc) desc;
+---------------------------+-----------------------+---------------+--------------------+
| g                         | SUM(t1.current_count) | current_alloc | SUM(t1.high_alloc) |
+---------------------------+-----------------------+---------------+--------------------+
| memory/performance_schema |                   734 | 271.25 MiB    |          284426880 |
| memory/innodb             |                  2814 | 55.30 MiB     |           64006403 |
| memory/sql                |                  5554 | 9.28 MiB      |           15847583 |
| memory/mysys              |                  2628 | 8.89 MiB      |            9456672 |
| memory/temptable          |                     1 | 1.00 MiB      |           16777216 |
| memory/mysqlx             |                    16 | 4.27 KiB      |               4712 |
| memory/myisam             |                     1 | 696 bytes     |                696 |
| memory/blackhole          |                     1 | 88 bytes      |                 88 |
| memory/csv                |                     1 | 88 bytes      |                 88 |
| memory/vio                |                     2 | 16 bytes      |                 16 |
+---------------------------+-----------------------+---------------+--------------------+
10 rows in set (0.01 sec)

select * from memory_global_by_current_bytes where event_name LIKE 'memory/innodb%';                                                                                   +-------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name                                | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+-------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/ut0link_buf                 |             2 | 24.00 MiB     | 12.00 MiB         |          2 | 24.00 MiB  | 12.00 MiB      |
| memory/innodb/ut0new                      |             6 | 16.08 MiB     | 2.68 MiB          |          6 | 16.08 MiB  | 2.68 MiB       |
| memory/innodb/ut0pool                     |             1 | 4.00 MiB      | 4.00 MiB          |          1 | 4.00 MiB   | 4.00 MiB       |
| memory/innodb/hash0hash                   |            32 | 3.70 MiB      | 118.32 KiB        |         32 | 3.70 MiB   | 118.32 KiB     |
| memory/innodb/os0file                     |           129 | 2.60 MiB      | 20.66 KiB         |        131 | 3.74 MiB   | 29.26 KiB      |
| memory/innodb/buf0dblwr                   |             4 | 2.02 MiB      | 516.34 KiB        |          5 | 2.05 MiB   | 419.48 KiB     |
| memory/innodb/memory                      |           948 | 1.85 MiB      | 2.00 KiB          |       1037 | 2.20 MiB   | 2.18 KiB       |
| memory/innodb/ha_innodb                   |           172 | 200.66 KiB    | 1.17 KiB          |        172 | 200.66 KiB | 1.17 KiB       |
| memory/innodb/lock0lock                   |            33 | 170.97 KiB    | 5.18 KiB          |         33 | 170.97 KiB | 5.18 KiB       |
| memory/innodb/sync0arr                    |             2 | 132.49 KiB    | 66.25 KiB         |          2 | 132.49 KiB | 66.25 KiB      |
| memory/innodb/fsp0file                    |             1 | 128.02 KiB    | 128.02 KiB        |          3 | 256.08 KiB | 85.36 KiB      |
| memory/innodb/trx0rseg                    |           389 | 108.88 KiB    | 287 bytes         |        389 | 108.88 KiB | 287 bytes      |
| memory/innodb/trx0undo                    |           291 | 95.48 KiB     | 336 bytes         |        301 | 98.77 KiB  | 336 bytes      |
| memory/innodb/row0sel                     |            15 | 57.80 KiB     | 3.85 KiB          |         15 | 57.80 KiB  | 3.85 KiB       |
| memory/innodb/read0read                   |           208 | 56.88 KiB     | 280 bytes         |        208 | 56.88 KiB  | 280 bytes      |
| memory/innodb/log0log                     |             3 | 32.13 KiB     | 10.71 KiB         |          3 | 32.13 KiB  | 10.71 KiB      |
| memory/innodb/ut0lock_free_hash           |           258 | 26.05 KiB     | 103 bytes         |        258 | 26.05 KiB  | 103 bytes      |
| memory/innodb/lexyy                       |             3 | 16.15 KiB     | 5.38 KiB          |          3 | 16.15 KiB  | 5.38 KiB       |
| memory/innodb/sync0sharded_rw             |             1 | 12.02 KiB     | 12.02 KiB         |          1 | 12.02 KiB  | 12.02 KiB      |
| memory/innodb/dict0dict                   |            57 | 8.54 KiB      | 153 bytes         |         58 | 8.88 KiB   | 157 bytes      |
| memory/innodb/rem0rec                     |            87 | 6.08 KiB      | 72 bytes          |         92 | 6.37 KiB   | 71 bytes       |
| memory/innodb/trx0trx                     |            16 | 4.38 KiB      | 280 bytes         |         16 | 4.38 KiB   | 280 bytes      |
| memory/innodb/btr0pcur                    |            26 | 4.31 KiB      | 170 bytes         |         26 | 4.31 KiB   | 170 bytes      |
| memory/innodb/mem0mem                     |            86 | 3.46 KiB      | 41 bytes          |         90 | 3.62 KiB   | 41 bytes       |
| memory/innodb/fil0fil                     |             8 | 2.69 KiB      | 344 bytes         |         11 | 2.07 MiB   | 192.25 KiB     |
| memory/innodb/buf0buf                     |             1 | 1.45 KiB      | 1.45 KiB          |          2 | 1.76 KiB   | 900 bytes      |
| memory/innodb/btr0sea                     |            11 | 1.39 KiB      | 129 bytes         |         17 | 30.00 KiB  | 1.76 KiB       |
| memory/innodb/trx0purge                   |             2 | 720 bytes     | 360 bytes         |          3 | 720 bytes  | 240 bytes      |
| memory/innodb/srv0srv                     |             1 | 632 bytes     | 632 bytes         |          1 | 632 bytes  | 632 bytes      |
| memory/innodb/trx0sys                     |             1 | 576 bytes     | 576 bytes         |          1 | 576 bytes  | 576 bytes      |
| memory/innodb/log and page archiver       |             2 | 440 bytes     | 220 bytes         |          2 | 440 bytes  | 220 bytes      |
| memory/innodb/log0recv                    |             1 | 384 bytes     | 384 bytes         |          3 | 2.00 MiB   | 683.14 KiB     |
| memory/innodb/buf0flu                     |             2 | 280 bytes     | 140 bytes         |          2 | 280 bytes  | 140 bytes      |
| memory/innodb/trx0i_s                     |             1 | 152 bytes     | 152 bytes         |          1 | 152 bytes  | 152 bytes      |
| memory/innodb/buf_stat_per_index_t        |             2 | 144 bytes     | 72 bytes          |          2 | 144 bytes  | 72 bytes       |
| memory/innodb/ibuf0ibuf                   |             1 | 136 bytes     | 136 bytes         |          1 | 136 bytes  | 136 bytes      |
| memory/innodb/ut_lock_free_hash_t         |             2 | 136 bytes     | 68 bytes          |          2 | 136 bytes  | 68 bytes       |
| memory/innodb/clone data                  |             1 | 136 bytes     | 136 bytes         |          1 | 136 bytes  | 136 bytes      |
| memory/innodb/undo::Tablespaces           |             2 | 120 bytes     | 60 bytes          |          2 | 120 bytes  | 60 bytes       |
| memory/innodb/ut0wqueue                   |             1 | 96 bytes      | 96 bytes          |          1 | 96 bytes   | 96 bytes       |
| memory/innodb/usr0sess                    |             2 | 80 bytes      | 40 bytes          |          2 | 80 bytes   | 40 bytes       |
| memory/innodb/pars0lex                    |             1 | 56 bytes      | 56 bytes          |          1 | 56 bytes   | 56 bytes       |
| memory/innodb/dict_stats_bg_recalc_pool_t |             1 | 56 bytes      | 56 bytes          |          1 | 56 bytes   | 56 bytes       |
| memory/innodb/ut0list                     |             1 | 40 bytes      | 40 bytes          |          1 | 40 bytes   | 40 bytes       |
+-------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
44 rows in set (0.00 sec)

Suggested fix:
In 5.7 there was something called memory/innodb/buf_buf_pool:

| memory/innodb/buf_buf_pool                                                     |             1 | 131.06 MiB    | 131.06 MiB          |          1 | 131.06 MiB  | 131.06 MiB          |

Just to verify this wasn't some kind of new lazy load initialization, I loaded some dummy data:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 414501
Buffer pool size   8191
Free buffers       3472
Database pages     4699
Old database pages 1714
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1115, created 3584, written 4305
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4699, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
[23 Apr 2018 16:45] MySQL Verification Team
Hi Morgan,

You are quite right. I got results quite similar to yours. I guess that this is not specific to macOS ???

Verified as reported:

These are my results with innodb_buffer_pool of 4 Gb, which are nowhere to be found:

mysql> select SUBSTRING_INDEX(event_name, '/', 2) as g, SUM(t1.current_count), format_bytes(SUM(t1.current_alloc)) as current_alloc, SUM(t1.high_alloc) from x$memory_global_by_current_bytes t1 group by g order by SUM(t1.current_alloc) desc;
+---------------------------+-----------------------+---------------+--------------------+
| g                         | SUM(t1.current_count) | current_alloc | SUM(t1.high_alloc) |
+---------------------------+-----------------------+---------------+--------------------+
| memory/performance_schema |                   734 | 211.17 MiB    |          221426880 |
| memory/innodb             |                  2865 | 157.70 MiB    |          169229242 |
| memory/mysys              |                  3368 | 32.91 MiB     |           34512773 |
| memory/sql                |                  6814 | 10.52 MiB     |           17073800 |
| memory/temptable          |                     1 | 1.00 MiB      |            1048576 |
| memory/mysqlx             |                    16 | 4.27 KiB      |               4712 |
| memory/myisam             |                     1 | 696 bytes     |                696 |
| memory/blackhole          |                     1 | 88 bytes      |                 88 |
| memory/csv                |                     1 | 88 bytes      |                 88 |
| memory/vio                |                     2 | 16 bytes      |                 16 |
+---------------------------+-----------------------+---------------+--------------------+
10 rows in set (0.01 sec)

The other table is similar.
[7 Jun 2018 21:59] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.13 release, and here's the changelog entry:

Buffer pool memory allocation was not fully accounted for in Performance
Schema memory/innodb/buf_buf_pool statistics.