MySQL内存使用的查看方式详解
前言
本文主要给大家介绍了关于MySQL内存使用查看的相关内容，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍吧
使用版本：MySQL5.7
官方文档
在performance_schema有如下表记录内存使用情况
mysql>showtableslike'%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| +-------------------------------------------------+
每个内存统计表都有如下统计列：
*COUNT_ALLOC，COUNT_FREE：对内存分配和释放内存函数的调用总次数
*SUM_NUMBER_OF_BYTES_ALLOC，SUM_NUMBER_OF_BYTES_FREE：已分配和已释放的内存块的总字节大小
*CURRENT_COUNT_USED：这是一个便捷列，等于COUNT_ALLOC-COUNT_FREE
*CURRENT_NUMBER_OF_BYTES_USED：当前已分配的内存块但未释放的统计大小。这是一个便捷列，等于SUM_NUMBER_OF_BYTES_ALLOC-SUM_NUMBER_OF_BYTES_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列的低和高水位标记
内存统计表允许使用TRUNCATETABLE语句。使用truncate语句时有如下行为：
*通常，truncate操作会重置统计信息的基准数据（即清空之前的数据），但不会修改当前server的内存分配等状态。也就是说，truncate内存统计表不会释放已分配内存
*将COUNT_ALLOC和COUNT_FREE列重置，并重新开始计数（等于内存统计信息以重置后的数值作为基准数据）
*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列值
*此外，按照帐户，主机，用户或线程分类统计的内存统计表或memory_summary_global_by_event_name表，如果在对其依赖的accounts、hosts、users表执行truncate时，会隐式对这些内存统计表执行truncate语句
简单来说，就是可以根据用户、主机、线程、账号、全局的维度对内存进行监控。同时库sys也就这些表做了进一步的格式化，可以使得用户非常容易的观察到每个对象的内存开销：
mysql>selectevent_name,current_allocfromsys.memory_global_by_current_byteslimit10; +-----------------------------------------------------------------------------+---------------+ |event_name|current_alloc| +-----------------------------------------------------------------------------+---------------+ |memory/performance_schema/events_statements_history_long|13.66MiB| |memory/performance_schema/events_statements_history_long.sqltext|9.77MiB| |memory/performance_schema/events_statements_history_long.tokens|9.77MiB| |memory/performance_schema/events_statements_summary_by_digest.tokens|9.77MiB| |memory/performance_schema/table_handles|9.06MiB| |memory/performance_schema/events_statements_summary_by_thread_by_event_name|8.67MiB| |memory/sql/String::value|6.02MiB| |memory/performance_schema/memory_summary_by_thread_by_event_name|5.62MiB| |memory/performance_schema/events_statements_summary_by_digest|4.88MiB| |memory/sql/TABLE|4.35MiB| +-----------------------------------------------------------------------------+---------------+
默认情况下performance_schema只对performance_schema进行了内存开销的统计。根据你的MySQL安装代码区域可能包括performance_schema、sql、client、innodb、myisam、csv、memory、blackhole、archive、partition和其他。
查看innodb相关的内存监控是否开启，默认不开启
mysql>SELECT*FROMperformance_schema.setup_instruments ->WHERENAMELIKE'%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*FROMperformance_schema.setup_instruments WHERENAMELIKE'%memory/innodb%'; +-------------------------------------------+---------+-------+ |NAME|ENABLED|TIMED| +-------------------------------------------+---------+-------+ |memory/innodb/adaptivehashindex|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|
对所有可能的对象进行内存监控。因此，还需要做下面的设置：
mysql>updateperformance_schema.setup_instrumentssetenabled='yes'wherenamelike'memory%'; QueryOK,306rowsaffected(0.00sec) Rowsmatched:376Changed:306Warnings:0
但是这种在线打开内存统计的方法仅对之后新增的内存对象有效，重启数据库后又会还原设置：
如想要对全局生命周期中的对象进行内存统计，必须在配置文件中进行设置，然后重启：
[mysqld] performance-schema-instrument='memory/%=COUNTED'
可以使用sys库下的memory_global_by_current_bytes表来查询相同的底层数据，该模式表显示了全局服务器内当前内存使用情况，按分配类型进行细分。
mysql>SELECT*FROMsys.memory_global_by_current_bytes WHEREevent_nameLIKE'memory/innodb/buf_buf_pool'\G ***************************1.row*************************** event_name:memory/innodb/buf_buf_pool current_count:1 current_alloc:131.06MiB current_avg_alloc:131.06MiB high_count:1 high_alloc:131.06MiB high_avg_alloc:131.06MiB
此sys模式查询通过current_alloc()代码区域聚合当前分配的内存：
mysql>SELECTSUBSTRING_INDEX(event_name,'/',2)AS code_area,sys.format_bytes(SUM(current_alloc)) AScurrent_alloc FROMsys.x$memory_global_by_current_bytes GROUPBYSUBSTRING_INDEX(event_name,'/',2) ORDERBYSUM(current_alloc)DESC; +---------------------------+---------------+ |code_area|current_alloc| +---------------------------+---------------+ |memory/innodb|843.24MiB| |memory/performance_schema|81.29MiB| |memory/mysys|8.20MiB| |memory/sql|2.47MiB| |memory/memory|174.01KiB| |memory/myisam|46.53KiB| |memory/blackhole|512bytes| |memory/federated|512bytes| |memory/csv|512bytes| |memory/vio|496bytes| +---------------------------+---------------+
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。