Monitoring MySQL Memory Usage

You can monitor MySQL memory usage using the Performance Schema and sys schema.

By default the instrumentation is disabled. Instruments can can be enabled by updating the ENABLED column of the Performance Schema setup_instruments table. Memory insturments have names in the form of memory/code_area/instrument_name\ where code_area is a value such as `sql` or innodb and instruments_name is the instrument detail.

mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%';
+--------------------------------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+--------------------------------------------------------------------------------+---------+-------+
| memory/performance_schema/mutex_instances | YES | NO |
| memory/performance_schema/rwlock_instances | YES | NO |
| memory/performance_schema/cond_instances | YES | NO |
| memory/performance_schema/file_instances | YES | NO |
| memory/performance_schema/socket_instances | YES | NO |
| memory/performance_schema/metadata_locks | YES | NO |
| memory/performance_schema/file_handle | YES | NO |
| memory/performance_schema/accounts | YES | NO |

Enabling Instruments

To enable all memory instruments, add the following line to the my.cnf config file :

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

InnoDB Buffer Pool Memory Used

Show the memory used by the InnoDB buffer pool

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb/buf_buf_pool'\G
*************************** 1. row ***************************
 EVENT_NAME: memory/innodb/buf_buf_pool
 COUNT_ALLOC: 1
 COUNT_FREE: 0
 SUM_NUMBER_OF_BYTES_ALLOC: 137428992
 SUM_NUMBER_OF_BYTES_FREE: 0
 LOW_COUNT_USED: 0
 CURRENT_COUNT_USED: 1
 HIGH_COUNT_USED: 1
 LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 137428992
 HIGH_NUMBER_OF_BYTES_USED: 137428992

sys Schema Memory Usage

We can obtain the above information using the sys schema memory_global_by_current_bytes

mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/innodb/buf_buf_pool'\G
*************************** 1. row ***************************
 event_name: memory/innodb/buf_buf_pool
 current_count: 1
 current_alloc: 131.06 MiB
current_avg_alloc: 131.06 MiB 
 high_count: 1
 high_alloc: 131.06 MiB
 high_avg_alloc: 131.06 MiB

Currently Allocated Memory

mysql> select * from x$memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 321167937 |
+-----------------+

By Code Area

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
 -> code_area, sys.format_bytes(SUM(current_alloc))
 -> AS current_alloc
 -> FROM sys.x$memory_global_by_current_bytes
 -> GROUP BY SUBSTRING_INDEX(event_name,'/',2)
 -> ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area | current_alloc |
+---------------------------+---------------+
| memory/innodb | 180.80 MiB |
| memory/performance_schema | 90.35 MiB |
| memory/sql | 18.86 MiB |
| memory/mysys | 16.27 MiB |
| memory/memory | 213.15 KiB |
| memory/myisam | 34.98 KiB |
| memory/blackhole | 512 bytes |
| memory/csv | 512 bytes |
+---------------------------+---------------+

Monitoring MySQL Memory Usage