欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL内存使用的查看方式详解

程序员文章站 2022-08-07 18:20:33
前言 本文主要给大家介绍了关于mysql内存使用查看的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧 使用版本:mysql 5.7 在p...

前言

本文主要给大家介绍了关于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 |
+---------------------------+---------------+

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对的支持。