243.mysql 内存统计相关-526互联

1.查看mysql是否打开了关于innodb内存相关的监控
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory/innodb%';
2.没有打开的话开一下(自己判断: 重启数据库会恢复原有设置)
# update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';
3.如想要对全局生命周期中的对象进行内存统计，必须在配置文件中进行设置(对第二步中重启恢复的持久化操作)
[mysqld]
performance-schema-instrument='memory/%=COUNTED'


4.全局服务器内当前内存使用情况
SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/innodb/buf_buf_pool'\G

5.统计mysql先所有event_name的内存使用情况
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;

6.查看 MySQL 总消耗内存
select * from sys.memory_global_total;

7.将mysql中的字节转成人类容易阅读的单位
SELECT variable_name, 
		sys.format_bytes(variable_value) as  variable_value2 
FROM performance_schema.global_variables 
WHERE variable_name = 'max_allowed_packet';

8.查看总体内存占用情况(每个组件)
select event_name,sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED) as CURRENT_NUMBER_OF_BYTES_USED2 from performance_schema.memory_summary_global_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc LIMIT 20;

9.查看线程内存占用情况
select thread_id,event_name,sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED) as CURRENT_NUMBER_OF_BYTES_USED2 from performance_schema.memory_summary_by_thread_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 20;

10.查看回话级别的buffer相关信息
select VARIABLE_NAME, VARIABLE_VALUE, concat(VARIABLE_VALUE/1024/1024,' MB') AS VARIABLE_VALUE_MB from performance_schema.session_variables where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size', 'innodb_buffer_pool_chunk_size');



11. # 查看线程信息(内存分配大小)
SELECT * FROM performance_schema.threads where name="thread/sql/one_connection" limit 1\G

SELECT
	pl.ID,
	pl.user,
	pl.HOST,
	pl.DB,
	pl.COMMAND,
	pl.`TIME`,
	pl.info,
	td.THREAD_ID,
	td.PARENT_THREAD_ID,
	tsb.current_allocated
FROM
	information_schema.processlist pl
left join performance_schema.threads td on
	td.PROCESSLIST_ID = pl.ID
left join sys.memory_by_thread_by_current_bytes tsb on 
	tsb.thread_id = td.THREAD_ID 
where
	pl.COMMAND != "Sleep";

SELECT * from performance_schema.socket_instances si where si.THREAD_ID = 20131;

-- select sys.format_bytes(sum(msb.CURRENT_NUMBER_OF_BYTES_USED)) from performance_schema.memory_summary_by_thread_by_event_name msb where msb.THREAD_ID = 78680;
SELECT * from sys.memory_by_thread_by_current_bytes msb where msb.THREAD_ID = 78680;

SELECT * FROM performance_schema.threads where name="thread/sql/one_connection" limit 1\G