mysql内存和CPU监控

内存使用监控

默认只对 performance_schema 库进行内存统计，对全局内存统计需要手工开启 //动态开启，开启后开始统计

update performance_schema.setup_instruments set
enabled = 'yes' where name like 'memory%';

//配置文件中添加，重启生效 performance-schema-instrument=‘memory/%=COUNTED’

• 查看实例内存消耗分布，sys 库下有多张 memory 相关视图用于协助用户定位分析内存溢出类问题

SELECT event_name,
       current_alloc
FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE 'memory%innodb%';
+-------------------------------------------+---------------+
| event_name                                | current_alloc |
+-------------------------------------------+---------------+
| memory/innodb/buf_buf_pool                | 134.31 MiB    |
| memory/innodb/log0log                     | 32.01 MiB     |
| memory/innodb/mem0mem                     | 15.71 MiB     |
| memory/innodb/lock0lock                   | 12.21 MiB     |
| memory/innodb/os0event                    | 8.37 MiB      |
| memory/innodb/hash0hash                   | 4.74 MiB      |
...
+-------------------------------------------+---------------+
42 rows in set (0.01 sec)

CPU使用率过高

查看CPU飙高的mysql线程

top -H -p <mysqld进程id>

#top -H -p 1821
top - 21:04:24 up 10 min,  1 user,  load average: 0.00, 0.02, 0.04
Threads:  31 total,   0 running,  31 sleeping,   0 stopped,   0 zombie
%Cpu(s):  90.0 us,  0.2 sy,  0.0 ni, 99.8 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  1867048 total,   390212 free,  1227176 used,   249660 buff/cache
KiB Swap:  2097148 total,  2097148 free,        0 used.   454824 avail Mem 

   PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                                               
  1942 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:00.26 mysqld                                                                                                
  1821 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:13.02 mysqld                                                                                                
  1924 mysql     20   0 12.327g 1.082g  12004 S  90 60.8   0:00.00 mysqld   

根据具体PID，定位问题SQL

SELECT a.THREAD_OS_ID,b.id,b.user,b.host,b.db,b.command,b.time,b.state,b.info
FROM performance_schema.threads a,information_schema.processlist b
WHERE b.id = a.processlist_id and a.THREAD_OS_ID=<具体pid>;