主頁 > 知識(shí)庫 > MySQL內(nèi)存使用的查看方式詳解

MySQL內(nèi)存使用的查看方式詳解
熱門標(biāo)簽：百度地圖標(biāo)注后不顯示 電話機(jī)器人市場(chǎng)趨勢(shì) 俄國(guó)地圖標(biāo)注app 昆明電信400電話辦理 電銷機(jī)器人 行業(yè) 溫州瑞安400電話怎么申請(qǐng) 淄博400電話申請(qǐng) 南昌高頻外呼系統(tǒng)哪家公司做的好 電銷機(jī)器人各個(gè)細(xì)節(jié)介紹

前言

本文主要給大家介紹了關(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  |
+-------------------------------------------------+

每個(gè)內(nèi)存統(tǒng)計(jì)表都有如下統(tǒng)計(jì)列：

* COUNT_ALLOC，COUNT_FREE：對(duì)內(nèi)存分配和釋放內(nèi)存函數(shù)的調(diào)用總次數(shù)

* SUM_NUMBER_OF_BYTES_ALLOC，SUM_NUMBER_OF_BYTES_FREE：已分配和已釋放的內(nèi)存塊的總字節(jié)大小

* CURRENT_COUNT_USED：這是一個(gè)便捷列，等于COUNT_ALLOC - COUNT_FREE

* CURRENT_NUMBER_OF_BYTES_USED：當(dāng)前已分配的內(nèi)存塊但未釋放的統(tǒng)計(jì)大小。這是一個(gè)便捷列，等于SUM_NUMBER_OF_BYTES_ALLOC - SUM_NUMBER_OF_BYTES_FREE

* LOW_COUNT_USED，HIGH_COUNT_USED：對(duì)應(yīng)CURRENT_COUNT_USED列的低和高水位標(biāo)記

* LOW_NUMBER_OF_BYTES_USED，HIGH_NUMBER_OF_BYTES_USED：對(duì)應(yīng)CURRENT_NUMBER_OF_BYTES_USED列的低和高水位標(biāo)記

內(nèi)存統(tǒng)計(jì)表允許使用TRUNCATE TABLE語句。使用truncate語句時(shí)有如下行為：

* 通常，truncate操作會(huì)重置統(tǒng)計(jì)信息的基準(zhǔn)數(shù)據(jù)（即清空之前的數(shù)據(jù)），但不會(huì)修改當(dāng)前server的內(nèi)存分配等狀態(tài)。也就是說，truncate內(nèi)存統(tǒng)計(jì)表不會(huì)釋放已分配內(nèi)存

 * 將COUNT_ALLOC和COUNT_FREE列重置，并重新開始計(jì)數(shù)（等于內(nèi)存統(tǒng)計(jì)信息以重置后的數(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)計(jì)的內(nèi)存統(tǒng)計(jì)表或memory_summary_global_by_event_name表，如果在對(duì)其依賴的accounts、hosts、users表執(zhí)行truncate時(shí)，會(huì)隱式對(duì)這些內(nèi)存統(tǒng)計(jì)表執(zhí)行truncate語句

簡(jiǎn)單來說，就是可以根據(jù)用戶、主機(jī)、線程、賬號(hào)、全局的維度對(duì)內(nèi)存進(jìn)行監(jiān)控。同時(shí)庫sys也就這些表做了進(jìn)一步的格式化，可以使得用戶非常容易的觀察到每個(gè)對(duì)象的內(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只對(duì)performance_schema進(jìn)行了內(nèi)存開銷的統(tǒng)計(jì)。根據(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 |

對(duì)所有可能的對(duì)象進(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)計(jì)的方法僅對(duì)之后新增的內(nèi)存對(duì)象有效，重啟數(shù)據(jù)庫后又會(huì)還原設(shè)置：

如想要對(duì)全局生命周期中的對(duì)象進(jìn)行內(nèi)存統(tǒng)計(jì)，必須在配置文件中進(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é)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章:
  • MySQL 內(nèi)存表和臨時(shí)表的用法詳解
  • MySQL8.0內(nèi)存相關(guān)參數(shù)總結(jié)
  • MySQL OOM（內(nèi)存溢出）的解決思路
  • MySql減少內(nèi)存占用的方法詳解
  • MySql優(yōu)化之InnoDB,4GB內(nèi)存,多查詢的my.ini中文配置方案詳解
  • MySQL常見內(nèi)存不足啟動(dòng)失敗的完美解決方法
  • MySQL占用內(nèi)存較大與CPU過高測(cè)試與解決辦法
  • MySQL 4G內(nèi)存服務(wù)器配置優(yōu)化
  • Mysql5.6啟動(dòng)內(nèi)存占用過高解決方案
  • 詳解分析MySQL8.0的內(nèi)存消耗

標(biāo)簽：嘉峪關(guān) 葫蘆島 洛陽 甘南 吐魯番 拉薩 安徽

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL內(nèi)存使用的查看方式詳解》，本文關(guān)鍵詞  MySQL,內(nèi)存,使,用的,查看,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL內(nèi)存使用的查看方式詳解》相關(guān)的同類信息！
  • 本頁收集關(guān)于MySQL內(nèi)存使用的查看方式詳解的相關(guān)信息資訊供網(wǎng)民參考！
    • 推薦文章