前言
本文主要给大家介绍了关于mysql内存使用查看的相关内容，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍吧
使用版本：mysql 5.7
在performance_schema有如下表记录内存使用情况
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 | +-------------------------------------------------+
每个内存统计表都有如下统计列：
* 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列的低和高水位标记
内存统计表允许使用truncate table语句。使用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> 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 | +-----------------------------------------------------------------------------+---------------+
默认情况下performance_schema只对performance_schema进行了内存开销的统计。根据你的mysql安装代码区域可能包括performance_schema、sql、client、innodb、myisam、csv、memory、blackhole、archive、partition和其他。
查看innodb相关的内存监控是否开启，默认不开启
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 |
对所有可能的对象进行内存监控。因此，还需要做下面的设置：
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
但是这种在线打开内存统计的方法仅对之后新增的内存对象有效，重启数据库后又会还原设置：
如想要对全局生命周期中的对象进行内存统计，必须在配置文件中进行设置，然后重启：
[mysqld] performance-schema-instrument='memory/%=counted'
可以使用sys库下的memory_global_by_current_bytes表来查询相同的底层数据，该模式表显示了全局服务器内当前内存使用情况，按分配类型进行细分。
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()代码区域聚合当前分配的内存：
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 | +---------------------------+---------------+
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对移动技术网的支持。
如对本文有疑问， 点击进行留言回复！！
集合类 ArrayList 和 LinkedList 直接用 Innodb（MySQL 5.0
MySQL Innodb 存储结构 和 存储Null值 用法详解
我要评论
网友评论