MySQL内存相关排查

select
    (@@key_buffer_size
#+ @@query_cache_size
+ @@tmp_table_size
+ @@innodb_buffer_pool_size
#+ @@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size
#+ @@max_connections *(
+(select count(1) from information_schema.processlist)*(
+ @@sort_buffer_size
+ @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@join_buffer_size
+ @@thread_stack
+ @@binlog_cache_size))/1024/1024/1024
show  processlist

select * from information_schema.processlist

show variables like '%innodb_flush_method%'
show variables where variable_name in ('key_buffer_size',
'query_cache_size',
'tmp_table_size',
'innodb_buffer_pool_size',
'innodb_additional_mem_pool_size',
'innodb_log_buffer_size',
'max_connections',
'sort_buffer_size',
'read_buffer_size',
'read_rnd_buffer_size',
'join_buffer_size',
'thread_stack',
'binlog_cache_size') 

select * from performance_schema.global_status

#查看innodb实际使用的量
set @ibpdata = (select variable_value from performance_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data');
select @ibpdata;
set @idbpgsize = (select variable_value from performance_schema.global_status where variable_name = 'innodb_page_size');
select @idbpgsize;

set @ibpsize = @ibpdata * @idbpgsize / (1024*1024*1024);
select @ibpsize;

select 
(select variable_value from performance_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data')
*(select variable_value from performance_schema.global_status where variable_name = 'innodb_page_size')
 / (1024*1024*1024);
 

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;