MySQL內(nèi)存使用的查看方式詳解
本文主要給大家介紹了關(guān)于MySQL內(nèi)存使用查看的相關(guān)內(nèi)容，分享出來供大家參考學(xué)習(xí)，下面話不多說了，來一起看看詳細(xì)的介紹吧
使用版本：MySQL 5.7
官方文檔
在performance_schema有如下表記錄內(nèi)存使用情況
mysql> show tables like '%memory%summary%'; +-------------------------------------------------+ | Tables_in_performance_schema (%memory%summary%) | +-------------------------------------------------+ | 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 | +-------------------------------------------------+
每個內(nèi)存統(tǒng)計表都有如下統(tǒng)計列：
* COUNT_ALLOC，COUNT_FREE：對內(nèi)存分配和釋放內(nèi)存函數(shù)的調(diào)用總次數(shù)
* SUM_NUMBER_OF_BYTES_ALLOC，SUM_NUMBER_OF_BYTES_FREE：已分配和已釋放的內(nèi)存塊的總字節(jié)大小
* CURRENT_COUNT_USED：這是一個便捷列，等于COUNT_ALLOC - COUNT_FREE
* CURRENT_NUMBER_OF_BYTES_USED：當(dāng)前已分配的內(nèi)存塊但未釋放的統(tǒng)計大小。這是一個便捷列，等于SUM_NUMBER_OF_BYTES_ALLOC - SUM_NUMBER_OF_BYTES_FREE
* LOW_COUNT_USED，HIGH_COUNT_USED：對應(yīng)CURRENT_COUNT_USED列的低和高水位標(biāo)記
* LOW_NUMBER_OF_BYTES_USED，HIGH_NUMBER_OF_BYTES_USED：對應(yīng)CURRENT_NUMBER_OF_BYTES_USED列的低和高水位標(biāo)記
內(nèi)存統(tǒng)計表允許使用TRUNCATE TABLE語句。使用truncate語句時有如下行為：
* 通常，truncate操作會重置統(tǒng)計信息的基準(zhǔn)數(shù)據(jù)（即清空之前的數(shù)據(jù)），但不會修改當(dāng)前server的內(nèi)存分配等狀態(tài)。也就是說，truncate內(nèi)存統(tǒng)計表不會釋放已分配內(nèi)存
* 將COUNT_ALLOC和COUNT_FREE列重置，并重新開始計數(shù)（等于內(nèi)存統(tǒng)計信息以重置后的數(shù)值作為基準(zhǔn)數(shù)據(jù)）
* SUM_NUMBER_OF_BYTES_ALLOC和SUM_NUMBER_OF_BYTES_FREE列重置與COUNT_ALLOC和COUNT_FREE列重置類似
* LOW_COUNT_USED和HIGH_COUNT_USED將重置為CURRENT_COUNT_USED列值
* LOW_NUMBER_OF_BYTES_USED和HIGH_NUMBER_OF_BYTES_USED將重置為CURRENT_NUMBER_OF_BYTES_USED列值
* 此外，按照帳戶，主機(jī)，用戶或線程分類統(tǒng)計的內(nèi)存統(tǒng)計表或memory_summary_global_by_event_name表，如果在對其依賴的accounts、hosts、users表執(zhí)行truncate時，會隱式對這些內(nèi)存統(tǒng)計表執(zhí)行truncate語句
簡單來說，就是可以根據(jù)用戶、主機(jī)、線程、賬號、全局的維度對內(nèi)存進(jìn)行監(jiān)控。同時庫sys也就這些表做了進(jìn)一步的格式化，可以使得用戶非常容易的觀察到每個對象的內(nèi)存開銷：
mysql> select event_name,current_alloc from sys.memory_global_by_current_bytes limit 10; +-----------------------------------------------------------------------------+---------------+ | event_name | current_alloc | +-----------------------------------------------------------------------------+---------------+ | memory/performance_schema/events_statements_history_long | 13.66 MiB | | memory/performance_schema/events_statements_history_long.sqltext | 9.77 MiB | | memory/performance_schema/events_statements_history_long.tokens | 9.77 MiB | | memory/performance_schema/events_statements_summary_by_digest.tokens | 9.77 MiB | | memory/performance_schema/table_handles | 9.06 MiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 8.67 MiB | | memory/sql/String::value | 6.02 MiB | | memory/performance_schema/memory_summary_by_thread_by_event_name | 5.62 MiB | | memory/performance_schema/events_statements_summary_by_digest | 4.88 MiB | | memory/sql/TABLE | 4.35 MiB | +-----------------------------------------------------------------------------+---------------+
默認(rèn)情況下performance_schema只對performance_schema進(jìn)行了內(nèi)存開銷的統(tǒng)計。根據(jù)你的MySQL安裝代碼區(qū)域可能包括performance_schema、sql、client、innodb、myisam、csv、memory、blackhole、archive、partition和其他。
查看innodb相關(guān)的內(nèi)存監(jiān)控是否開啟，默認(rèn)不開啟
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 | | memory/performance_schema/events_waits_summary_by_account_by_event_name | YES | NO | | memory/performance_schema/events_stages_summary_by_account_by_event_name | YES | NO | | memory/performance_schema/events_statements_summary_by_account_by_event_name | YES | NO | | memory/performance_schema/events_transactions_summary_by_account_by_event_name | YES | NO | | memory/performance_schema/memory_summary_by_account_by_event_name | YES | NO | | memory/performance_schema/events_stages_summary_global_by_event_name | YES | NO | | memory/performance_schema/events_statements_summary_global_by_event_name | YES | NO | | memory/performance_schema/memory_summary_global_by_event_name | YES | NO | | memory/performance_schema/hosts | YES | NO | | memory/performance_schema/events_waits_summary_by_host_by_event_name | YES | NO | | memory/performance_schema/events_stages_summary_by_host_by_event_name | YES | NO | | memory/performance_schema/events_statements_summary_by_host_by_event_name | YES | NO | | memory/performance_schema/events_transactions_summary_by_host_by_event_name | YES | NO |
可以通過條件縮小范圍：
mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory/innodb%'; +-------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +-------------------------------------------+---------+-------+ | memory/innodb/adaptive hash index | NO | NO | | memory/innodb/buf_buf_pool | NO | NO | | memory/innodb/dict_stats_bg_recalc_pool_t | NO | NO | | memory/innodb/dict_stats_index_map_t | NO | NO | | memory/innodb/dict_stats_n_diff_on_level | NO | NO | | memory/innodb/other | NO | NO | | memory/innodb/row_log_buf | NO | NO | | memory/innodb/row_merge_sort | NO | NO | | memory/innodb/std | NO | NO | | memory/innodb/trx_sys_t::rw_trx_ids | NO | NO |
對所有可能的對象進(jìn)行內(nèi)存監(jiān)控。因此，還需要做下面的設(shè)置：
mysql> update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%'; Query OK, 306 rows affected (0.00 sec) Rows matched: 376 Changed: 306 Warnings: 0
但是這種在線打開內(nèi)存統(tǒng)計的方法僅對之后新增的內(nèi)存對象有效，重啟數(shù)據(jù)庫后又會還原設(shè)置：
如想要對全局生命周期中的對象進(jìn)行內(nèi)存統(tǒng)計，必須在配置文件中進(jìn)行設(shè)置，然后重啟：
[mysqld] performance-schema-instrument='memory/%=COUNTED'
可以使用sys庫下的memory_global_by_current_bytes表來查詢相同的底層數(shù)據(jù)，該模式表顯示了全局服務(wù)器內(nèi)當(dāng)前內(nèi)存使用情況，按分配類型進(jìn)行細(xì)分。
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
此sys模式查詢通過current_alloc()代碼區(qū)域聚合當(dāng)前分配的內(nèi)存：
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 | 843.24 MiB | | memory/performance_schema | 81.29 MiB | | memory/mysys | 8.20 MiB | | memory/sql | 2.47 MiB | | memory/memory | 174.01 KiB | | memory/myisam | 46.53 KiB | | memory/blackhole | 512 bytes | | memory/federated | 512 bytes | | memory/csv | 512 bytes | | memory/vio | 496 bytes | +---------------------------+---------------+
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，如果有疑問大家可以留言交流，謝謝大家對本站的支持。
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信