| Bug #84174 | memory_global_total in sys schema can not represent a real memory usage | ||
|---|---|---|---|
| Submitted: | 13 Dec 2016 8:41 | Modified: | 15 Dec 2016 1:05 |
| Reporter: | zte zte | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.7.16, 5.7.17 | OS: | SUSE (12) |
| Assigned to: | CPU Architecture: | Any | |
[13 Dec 2016 10:17]
MySQL Verification Team
Hello Zte, Thank you for the report. Thanks, Umesh
[13 Dec 2016 12:46]
Mark Leith
This looks to me like it's actually as a result of the memory instrumentation not being fully enabled - by default almost none of it is enabled, other than the instrumentation that Performance Schema does on itself - the numbers you have reported are probably what is being used by the P_S tables. To have all global buffers, and all per thread allocations, accounted for correctly within this view, you should enable all memory instrumentation on start up, i.e. add this to your my.cnf: performance-schema-instrument='memory/%=ON' If you could verify that this works more correctly for you (they may not be *exactly* the same), then I'll mark this as a documentation bug instead, so that it's clear that what is tracked here is what is actually enabled and accounted for.
[14 Dec 2016 1:24]
zte zte
I start mysql server with "performance-schema-instrument='memory/%=ON'".This works more correctly,but it still can not represent a real memory usage.
1、mysql> select *from memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 2.33 GiB |
+-----------------+
1 row in set (0.00 sec)
2、top
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
32327 yzs 20 0 3358720 448572 8748 S 0.000 1.367 0:01.49 mysqld
[14 Dec 2016 9:47]
Mark Leith
As I said, it won't be exactly the same (we use libraries that are not instrumented etc., so it's really an approximation of the memory that we have allocated within our own code). However, without knowing the overall memory on the system you are showing data for, I can't tell what that percentage of memory usage represents (and hence the discrepancy) - how much memory is in this system, what does that percentage represent in real terms?
[15 Dec 2016 1:05]
zte zte
The system has 32 GB in total. percentage represent 438M. Tasks: 248 total, 1 running, 247 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.2 us, 0.2 sy, 0.0 ni, 99.6 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem: 32824476 total, 10851744 used, 21972732 free, 221904 buffers KiB Swap: 41946108 total, 0 used, 41946108 free. 8605804 cached Mem mysql> select 32824476*0.01367/1024; +-----------------------+ | 32824476*0.01367/1024 | +-----------------------+ | 438.193932539 | +-----------------------+ 1 row in set (0.00 sec)
[15 Dec 2016 9:49]
Mark Leith
Never mind, I don't know why I asked this, when I should have just used VIRT/RES, "brainfart". So with RES/VIRT, it shows it really is more in the ballgame. As I said above, it will never represent all memory as shown by the operating system, as we don't track all library memory allocations, and we don't track for instance loading the binary in to memory etc. I'm changing this to a docs bug, so that it can be documented a little more specifically about what this number represents, vs OS numbers. Cheers, Mark
[21 Jan 2020 3:00]
Demon Chen
Hi,
I've meet the same problem.
The result we get from mysql is:
select * from memory_global_total limit 10;
+-----------------+
| total_allocated |
+-----------------+
| 15.18 GiB |
+-----------------+
select * from sys.memory_global_by_current_bytes limit 10;
+-----------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+-----------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/buf_buf_pool | 96 | 12.29 GiB | 131.06 MiB | 96 | 12.29 GiB | 131.06 MiB |
| memory/sql/TABLE | 186846 | 1.32 GiB | 7.41 KiB | 198458 | 1.61 GiB | 8.48 KiB |
| memory/innodb/hash0hash | 76 | 359.58 MiB | 4.73 MiB | 76 | 554.10 MiB | 7.29 MiB |
| memory/innodb/os0event | 2407854 | 312.30 MiB | 136 bytes | 2407874 | 312.30 MiB | 136 bytes |
| memory/innodb/mem0mem | 72421 | 288.40 MiB | 4.08 KiB | 127484 | 726.76 MiB | 5.84 KiB |
| memory/performance_schema/table_handles | 17 | 154.06 MiB | 9.06 MiB | 17 | 154.06 MiB | 9.06 MiB |
| memory/sql/Query_cache | 3 | 64.02 MiB | 21.34 MiB | 3 | 64.02 MiB | 21.34 MiB |
| memory/innodb/log0log | 11 | 64.01 MiB | 5.82 MiB | 11 | 64.01 MiB | 5.82 MiB |
| memory/innodb/ha_innodb | 9420 | 62.78 MiB | 6.82 KiB | 18818 | 144.27 MiB | 7.85 KiB |
| memory/sql/TABLE_SHARE::mem_root | 16940 | 55.81 MiB | 3.37 KiB | 16940 | 55.81 MiB | 3.37 KiB |
+-----------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
The mem Mysqld exactly use is :
375302 mysql 20 0 36.968g 0.021t 7704 S 0.3 17.4 292:43.39 mysqld
----21.75 GiB
free -h
total used free shared buff/cache available
Mem: 125G 90G 20G 4.1G 14G 27G
Swap: 0B 0B 0B
So what else that the P_s not monitored can occupy so much mem？
Description: The documentation states: total_allocated: The total bytes of memory allocated within the server. memory_global_total in sys schema can not represent a real memory usage. 1、mysql> select *from memory_global_total; +-----------------+ | total_allocated | +-----------------+ | 140.37 MiB | +-----------------+ 1 row in set (0.01 sec) 2、top PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 30675 yzs 20 0 3358720 472020 8824 S 0.000 1.438 0:01.06 mysqld How to repeat: See description.