Bug #80780 Add Performance Schema / sys Schema Memory Monitoring Example
Submitted: 17 Mar 2016 11:55 Modified: 29 Mar 2016 11:49
Reporter: Daniel Price Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[17 Mar 2016 11:55] Daniel Price
Description:
A request from Morgan Tocker for "How MySQL Uses Memory"
http://dev.mysql.com/doc/refman/5.7/en/memory-use.html

Add an example similar to the following to "How MySQL Uses Memory": 

By enabling memory instrumentation in Performance Schema on startup, you can track current memory usage in MySQL:

In configuration:
--performance-schema-instrument='memory/%=COUNTED’

Query in SYS (on a MySQL 5.7 server with default configuration):

mysql> SELECT * FROM sys.memory_global_by_current_bytes;

-------------------------------------------

For an example that fits on one page:

mysql> SELECT substring_index(`x$memory_global_by_current_bytes`.`event_name`,'/',2) AS `subsystem`,
sys.`format_bytes`(sum(`x$memory_global_by_current_bytes`.`current_alloc`)) AS `current_alloc`
FROM `sys`.`x$memory_global_by_current_bytes` group by substring_index(`x$memory_global_by_current_bytes`.`event_name`,'/',2)
ORDER BY sum(`x$memory_global_by_current_bytes`.`current_alloc`) DESC;
+---------------------------+---------------+
| subsystem                 | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 845.45 MiB    |
| memory/performance_schema | 132.81 MiB    |
| memory/mysys              | 8.29 MiB      |
| memory/sql                | 5.91 MiB      |
| memory/memory             | 174.01 KiB    |
| memory/myisam             | 123.13 KiB    |
| memory/keyring            | 4.14 KiB      |
| memory/blackhole          | 512 bytes     |
| memory/csv                | 512 bytes     |
| memory/vio                | 8 bytes       |
+---------------------------+---------------+
10 rows in set (0.01 sec)

How to repeat:
See above
[17 Mar 2016 12:00] Daniel Price
Posted by developer:
 
Note to docs:
The example could use a structure similar to this P_S example:
https://dev.mysql.com/doc/refman/5.6/en/monitor-innodb-mutex-waits-performance-schema.html

Relevant P_S pages
http://dev.mysql.com/doc/refman/5.7/en/performance-schema-instrument-naming.html
http://dev.mysql.com/doc/refman/5.7/en/memory-summary-tables.html
[29 Mar 2016 11:49] Daniel Price
Posted by developer:
 
A memory use monitoring example was added to this page in the MySQL 5.7 and 5.8 reference manual. The addition should appear online within 24 hours:

http://dev.mysql.com/doc/refman/5.7/en/memory-use.html