主库实列发生OOM,实例进程由于占用内存达到linux系统的最大阈值,导致linux系统kill了mysql实例进程,可以通过如下方式查看mysql使用了多少内存:
查看每个线程占用多少内存,然后乘以正在运行的线程(也就是排查sleep的)。
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;
showglobal status like '%threads%';
查看MySQL全局占用多少内存
select(@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@key_buffer_size)/ 1024 1024 AS MEMORY_MB;
查看performance_schema占用多少内存
SELECTSUBSTRING_INDEX(event_name,'/',2) AS code_area,sys.format_bytes(SUM(current_alloc)) AS current_alloc FROMsys.x$memory_global_by_current_bytes GROUP BYSUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;
查看memory存储引擎占用多少内存
selectsum(max_data_length)/1024/1024 as MEMORY_MB from tables whereengine='memory';