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)

 表的详细作用可以参考 

    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 |

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

`count_alloc` bigint(20) unsigned not

`count_free` bigint(20) unsigned not

`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`

`high_count_used`

`low_number_of_bytes_used`

`current_number_of_bytes_used`

`high_number_of_bytes_used`

bigint(20) not null

)

engine=performance_schema default charset=utf8

1 row in set (0.00

     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

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

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:

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‘; //当前连接所占内存

‘memory_used‘; //

mysql占用所有的内存，但不包括存储引擎层分配的内存(buf_pool,dict_cache等）