mysql 内存相关命令

show GLOBAL STATUS;

SELECT ( @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size 
+ @@max_connections * (@@binlog_cache_size + @@thread_stack + @@read_buffer_size 
+ @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@tmp_table_size ) 
) / 1024 /1024 AS MAX_MEM_MB; 

SELECT ( @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size 
) / 1024 /1024 AS MAX_MEM_MB; 

show VARIABLES;

SELECT  (@@binlog_cache_size + @@thread_stack + @@read_buffer_size 
+ @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@tmp_table_size ) 
 / 1024 /1024 AS MAX_MEM_MB; 

show processlist ;

show full PROCESSLIST;

show  table status;

Show global status ;

show global status like 'open%';

show variables like 'max_heap_table%';

show variables like 'innodb_buffer_pool%';

show status like 'innodb_buffer_pool_read%';

show variables like 'table_open_cache%';

show variables like 'flush_time%';

show variables like '%table%';

show open tables;

show global status like 'Max_used_connections'

show variables like 'max_tmp%'

show global status like '%tmp%table%'

show variables like 'query_cache_size';
show global status like 'Qcache%';
select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME like 'Qcache%';

SELECT( ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size+ @@join_buffer_size + @@binlog_cache_size + @@thread_stack +@@tmp_table_size + @@bulk_insert_buffer_size + @@max_allowed_packet +@@net_buffer_length ) ) / (1024*1024) AS MEMORY_MB;

select(@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@key_buffer_size)/ 1024 /1024 AS MEMORY_MB;

mysql使用的内存 = 全局共享内存+最大线程数×线程独享内存

把tmp_table_size放入全局共享内存,线程独享内存即连接数乘以的部分
SET @kilo_bytes=1024;
SET @mega_bytes=@kilo_bytes*1024;
SET @giga_bytes=@mega_bytes*1024;
SELECT (@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@binlog_cache_size+@@thread_cache_size+@@table_open_cache+@@table_definition_cache+@@key_buffer_size+@@tmp_table_size+@@max_connections*(@@thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size))/@giga_bytes AS MAX_MEMORY_GB;

SET @kilo_bytes=1024;
SET @mega_bytes=@kilo_bytes*1024;
SET @giga_bytes=@mega_bytes*1024;
SELECT (@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@binlog_cache_size+@@thread_cache_size+@@table_open_cache+@@