memory 监控 mysql vs percona vs maria

oracle mysql 5.7

    在performance_schema 通过以下表展现内存信息。这些表实际engine为performance_schema。这些表数据实际是以数组的形式存储在内存中的（thread_array，memory_class_array等），这些表主要展现线程级别的内存分配，不考虑系统级别的内存分配(如 buf_pool, dict_cache  等）。

    mysql> show tables like '%mem%';
+-----------------------------------------+
| Tables_in_performance_schema (%mem%) |
+-----------------------------------------+
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name |
| memory_summary_by_thread_by_event_name |
| memory_summary_by_user_by_event_name |
| memory_summary_global_by_event_name |
+-----------------------------------------+
5 rows in set (0.01 sec)

 表的详细作用可以参考 http://dev.mysql.com/doc/refman/5.7/en/memory-summary-tables.html

    mysql> desc memory_summary_by_thread_by_event_name;

+------------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------+---------------------+------+-----+---------+-------+
| THREAD_ID | bigint(20) unsigned | NO | | NULL | |
| EVENT_NAME | varchar(128) | NO | | NULL | |
| COUNT_ALLOC | bigint(20) unsigned | NO | | NULL | |
| COUNT_FREE | bigint(20) unsigned | NO | | NULL | |
| SUM_NUMBER_OF_BYTES_ALLOC | bigint(20) unsigned | NO | | NULL | |
| SUM_NUMBER_OF_BYTES_FREE | bigint(20) unsigned | NO | | NULL | |
| LOW_COUNT_USED | bigint(20) | NO | | NULL | |
| CURRENT_COUNT_USED | bigint(20) | NO | | NULL | |
| HIGH_COUNT_USED | bigint(20) | NO | | NULL | |
| LOW_NUMBER_OF_BYTES_USED | bigint(20) | NO | | NULL | |
| CURRENT_NUMBER_OF_BYTES_USED | bigint(20) | NO | | NULL | |
| HIGH_NUMBER_OF_BYTES_USED | bigint(20) | NO | | NULL | |
+------------------------------+---------------------+------+-----+---------+-------+
12 rows in set (0.03 sec)

     mysql> show create table memory_summary_by_thread_by_event_name\G

*************************** 1. row ***************************
Table: memory_summary_by_thread_by_event_name
Create Table: CREATE TABLE `memory_summary_by_thread_by_event_name` (
`THREAD_ID` bigint(20) unsigned NOT NULL,
`EVENT_NAME` varchar(128) NOT NULL,
`COUNT_ALLOC` bigint(20) unsigned NOT NULL,
`COUNT_FREE` bigint(20) unsigned NOT NULL,
`SUM_NUMBER_OF_BYTES_ALLOC` bigint(20) unsigned NOT NULL,
`SUM_NUMBER_OF_BYTES_FREE` bigint(20) unsigned NOT NULL,
`LOW_COUNT_USED` bigint(20) NOT NULL,
`CURRENT_COUNT_USED` bigint(20) NOT NULL,
`HIGH_COUNT_USED` bigint(20) NOT NULL,
`LOW_NUMBER_OF_BYTES_USED` bigint(20) NOT NULL,
`CURRENT_NUMBER_OF_BYTES_USED` bigint(20) NOT NULL,
`HIGH_NUMBER_OF_BYTES_USED` bigint(20) NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

     select * from memory_summary_by_thread_by_event_name where thread_id=1 order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10; //查看单个连接内存明细。

  保存内存统计信息的结构体  

 struct PFS_memory_stat
 {
  bool m_used;
  size_t m_alloc_count;
  size_t m_free_count;
  size_t m_alloc_size;
  size_t m_free_size;

    size_t m_alloc_count_capacity;
  size_t m_free_count_capacity;
  size_t m_alloc_size_capacity;
  size_t m_free_size_capacity;

    ......

    }

    对应关系

    CURRENT_COUNT_USED = @c m_alloc_count - @c m_free_count
  LOW_COUNT_USED + @c m_free_count_capacity = CURRENT_COUNT_USED
  CURRENT_COUNT_USED + @c m_alloc_count_capacity = HIGH_COUNT_USED
  CURRENT_SIZE_USED = @c m_alloc_size - @c m_free_size
  LOW_SIZE_USED + @c m_free_size_capacity = CURRENT_SIZE_USED
  CURRENT_SIZE_USED + @c m_alloc_size_capacity = HIGH_SIZE_USED

 信息收集：

  入口都在PSI_MEMORY_CALL, 内存的分配和释放都都调用此接口。

   count_alloc:统计分配情况

   count_free:统计释放情况

 结果展示：

  以memory_summary_by_thread_by_event_name表为例。其实现在storage\perfschema\table_mems_by_thread_by_event_name.cc中，其实PERFORMANCE_SCHEMA下的表记录的读取实现都在storage\perfschema目录下

   make_row ：跟据不同纬度从PFS_memory_stat中构造行

   read_row_values：读取设置行数据

       PFS_memory_stat_row::set_field

 

Percona/5.5:

在 show engine innodb status中增加了一些信息，加粗字体部分，参见crv_printf_innodb_monitor。

每个结构内存总大小是存储结构本身大小和存储结构元素大小之和

例如 Adaptive hash index 2052135264 (605538536 + 1446596728)

605538536：Adaptive  hash 结构所占大小

1446596728：hash 结构存储的记录总大小

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 35299262464; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
  Adaptive hash index 2052135264 (605538536 + 1446596728)
  Page hash 8851208 (buffer pool 0 only)
  Dictionary cache 766503482 (141607408 + 624896074)
  File system 11451832 (82672 + 11369160)
  Lock system 85249560 (84999896 + 249664)
  Recovery system 0 (0 + 0)
  Dictionary memory allocated 624896074
Buffer pool size 2097148
Buffer pool size, bytes 34359672832 
Free buffers 1
Database pages 2008854
Old database pages 741468
Modified db pages 214412
Pending reads 0 
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 966629375, not young 5036013048
48.43 youngs/s, 146.06 non-youngs/s
Pages read 826958847, created 46300728, written 1281936044
31.64 reads/s, 1.79 creates/s, 28.14 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2008854, unzip_LRU len: 0
I/O sum[9896]:cur[364], unzip sum[0]:cur[0]

附：这里Total memory allocated ，在innodb_use_sys_malloc=on,记录的是系统所有内存的分配情况，在innodb_use_sys_malloc=off 时主要记录buf_pool的内存分配，上例显示的是on的情况。这里看到Total memory allocated 比Buffer pool size, bytes 要大些，是因为Total memory allocated除了包含Buffer pool size, bytes，还包含page控制信息（event,metux)的，可以参考这里的改进

 

Maria/10.0

  maria10.0 中也加入了对连接的内存监控，其实基本和RDS实现一致。在my_malloc,my_realloc，my_free接口中通过调函数update_malloc_size更新连接和全局的memory_used值。

  在分配和释放的地方都通过MY_THREAD_SPECIFIC来指定内存是否从指定的连接上分配。

通过以下语句都可访问内存使用。

show full processlist;  //mem_used当前连接所占内存

show status like 'Memory_used'; //当前连接所占内存

show status like 'Memory_used'; // mysql占用所有的内存，但不包括存储引擎层分配的内存(buf_pool,dict_cache等）