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等）