mysql 导致 load average 过高

问题描述

通过终端登录时候后需要等待 几秒 才登录到系统; 然后执行其他命令时; 时而快，时而慢;而使用Debian镜像没有出现过类似问题,排除了家里网络问题

登录成功注意到信息: #System information disabled due to load higher than 4.0#,猜测是系统负载过高造成....
`

执行 uptime 输出：

ubuntu@ubuntu:~$ uptime
 06:08:53 up 33 min,  1 user,  load average: 4.75, 4.88, 4.50
ubuntu@ubuntu:~$ uptime
 06:08:55 up 33 min,  1 user,  load average: 4.75, 4.88, 4.50
ubuntu@ubuntu:~$ uptime
 06:08:55 up 33 min,  1 user,  load average: 4.75, 4.88, 4.50

因为系统为4核心,可以看出在 1min,5min,15min 系统还是过载

执行 top 输出：

top - 06:11:03 up 35 min,  1 user,  load average: 4.94, 4.88, 4.55
Tasks: 146 total,   1 running, 145 sleeping,   0 stopped,   0 zombie
%Cpu0  :  0.7 us,  1.7 sy,  0.0 ni, 82.8 id, 14.8 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu1  :  0.7 us,  3.0 sy,  0.0 ni, 61.5 id, 34.8 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu2  :  0.7 us,  1.0 sy,  0.0 ni, 97.7 id,  0.7 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu3  :  0.7 us,  1.3 sy,  0.0 ni, 17.5 id, 80.5 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :   7810.3 total,   6697.4 free,    582.0 used,    530.9 buff/cache
MiB Swap:      0.0 total,      0.0 free,      0.0 used.   7116.4 avail Mem 

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND 
   4952 mysql     20   0 1049172 375468  30992 S   7.6   4.7   0:01.41 mysqld  
   2000 mongodb   20   0  973476  73396  31316 S   1.0   0.9   0:25.15 mongod  
   4940 ubuntu    20   0   10692   3256   2692 R   1.0   0.0   0:00.27 top     
    210 root      20   0       0      0      0 D   0.3   0.0   0:02.78 usb-sto+
   2060 redis     20   0   50212   4628   3088 S   0.3   0.1   0:05.78 redis-s+
   3288 root      20   0       0      0      0 I   0.3   0.0   0:00.67 kworker+
   3703 root      20   0       0      0      0 I   0.3   0.0   0:00.83 kworker+
      1 root      20   0  167636  10668   7116 S   0.0   0.1   0:06.21 systemd 
      2 root      20   0       0      0      0 S   0.0   0.0   0:00.02 kthreadd
      3 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_gp

再次执行 top 命令输出

top - 06:14:14 up 38 min,  1 user,  load average: 4.87, 5.02, 4.68
Tasks: 148 total,   2 running, 146 sleeping,   0 stopped,   0 zombie
%Cpu(s):  2.2 us,  4.0 sy,  0.0 ni, 46.5 id, 47.3 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :   7810.3 total,   6866.6 free,    423.6 used,    520.1 buff/cache
MiB Swap:      0.0 total,      0.0 free,      0.0 used.   7274.6 avail Mem 

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND 
   5206 mysql     20   0  254784 206804  20384 R  14.2   2.6   0:00.43 mysqld  
   2000 mongodb   20   0  973476  73396  31316 S   1.0   0.9   0:27.05 mongod  
      1 root      20   0  167636  10668   7116 S   0.7   0.1   0:06.46 systemd 
    908 root      19  -1   67452  14556  13572 S   0.3   0.2   0:01.05 systemd+
   1964 root      20   0  237596   6764   5852 S   0.3   0.1   0:00.30 account+
   2060 redis     20   0   50212   4628   3088 S   0.3   0.1   0:06.31 redis-s+
   4150 root      20   0       0      0      0 I   0.3   0.0   0:00.37 kworker+
   4152 root      20   0       0      0      0 I   0.3   0.0   0:00.41 kworker+
   5125 ubuntu    20   0   10692   3284   2716 R   0.3   0.0   0:00.49 top     
      2 root      20   0       0      0      0 S   0.0   0.0   0:00.02 kthreadd
      3 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_gp  
      4 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_par+
      6 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 kworker+
      7 root      20   0       0      0      0 I   0.0   0.0   0:00.36 kworker+
      8 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 mm_perc+

从上面 top 输出可以看出可能是 mysqld 的问题 下面停掉 mysql服务

sudo systemctl stop mysql

重新登录后执行 uptime 命令

ubuntu@ubuntu:~$ uptime
 07:04:59 up  1:29,  1 user,  load average: 2.77, 3.93, 4.48
ubuntu@ubuntu:~$ uptime
 07:05:00 up  1:29,  1 user,  load average: 2.77, 3.93, 4.48
ubuntu@ubuntu:~$ uptime
 07:05:00 up  1:29,  1 user,  load average: 2.77, 3.93, 4.48
ubuntu@ubuntu:~$ uptime
 07:05:01 up  1:29,  1 user,  load average: 2.77, 3.93, 4.48
ubuntu@ubuntu:~$ uptime
 07:05:01 up  1:29,  1 user,  load average: 2.77, 3.93, 4.48

从上面可以看出 load average 基本正常了,现在的问题是为什么 mysql造成 load average 这么高？

环境

简述
系统为 ubunt server 使用 U盘(32G) 安装在 raspberry-pi 4(8G RAM) 上面
click here to see ubuntu raspberry-pi image info

系统信息

ubuntu@ubuntu:~$ uname -a
Linux ubuntu 5.4.0-1045-raspi #49-Ubuntu SMP PREEMPT Wed Sep 29 17:49:16 UTC 2021 aarch64 aarch64 aarch64 GNU/Linux

CPU 信息

ubuntu@ubuntu:~$ lscpu
Architecture:                    aarch64
CPU op-mode(s):                  32-bit, 64-bit
Byte Order:                      Little Endian
CPU(s):                          4
On-line CPU(s) list:             0-3
Thread(s) per core:              1
Core(s) per socket:              4
Socket(s):                       1
Vendor ID:                       ARM
Model:                           3
Model name:                      Cortex-A72
Stepping:                        r0p3
CPU max MHz:                     1500.0000
CPU min MHz:                     600.0000
BogoMIPS:                        108.00
Vulnerability Itlb multihit:     Not affected
Vulnerability L1tf:              Not affected
Vulnerability Mds:               Not affected
Vulnerability Meltdown:          Not affected
Vulnerability Spec store bypass: Vulnerable
Vulnerability Spectre v1:        Mitigation; __user pointer sanitization
Vulnerability Spectre v2:        Vulnerable
Vulnerability Srbds:             Not affected
Vulnerability Tsx async abort:   Not affected
Flags:                           fp asimd evtstrm crc32 cpuid

架构:aarch64, 1 个 cpu , 4 核心

以往经历

之前因为 aria2 log 占满整个分区导致系统卡顿,命令执行缓慢

执行 df -m 输出:

Filesystem      Size  Used Avail Use% Mounted on
udev            3.7G     0  3.7G   0% /dev
tmpfs           782M  4.0M  778M   1% /run
/dev/sdb2        29G  6.1G   21G  23% /
tmpfs           3.9G     0  3.9G   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           3.9G     0  3.9G   0% /sys/fs/cgroup
/dev/loop0       49M   49M     0 100% /snap/core18/2248
/dev/loop1       49M   49M     0 100% /snap/core18/2068
/dev/loop2       62M   62M     0 100% /snap/lxd/19040
/dev/loop3       27M   27M     0 100% /snap/snapd/10709
/dev/loop4       60M   60M     0 100% /snap/lxd/20330
/dev/loop5       28M   28M     0 100% /snap/snapd/11843
/dev/sdb1       253M  166M   87M  66% /boot/firmware
tmpfs           782M     0  782M   0% /run/user/1000

Ref

understanding-the-load-average

ubuntu-server linux

ubuntu

mysql mysql优化

阅读 4.5k

3 个回答

Stephanie

✓ 已被采纳

排查过程

经过排查通过 sudo systemctl start mysql 命令后 mysql没有启动成功(能在top里看到mysqld在跑,但通过lsof,netstat 都找不端口号;最后通过 先彻底清理配置文件重新安装解决). 之后mysql,redis,mongodb 都启动后 load average: 2.25, 2.13, 1.57

通过iozone 查看U盘、机械硬盘在Raspberry,MacOS 上的IO性能

Raspberry4(arrch64,8G) 通过USB 3.0 端口接入U盘

    Command line used: iozone -e -I -a -s 50M -r 4k -r 16m -i 0 -i 1 -i 2
    Output is in kBytes/sec
    Time Resolution = 0.000001 seconds.
    Processor cache size set to 1024 kBytes.
    Processor cache line size set to 32 bytes.
    File stride size set to 17 * record size.
                                                              random    random     bkwd    record    stride                                    
              kB  reclen    write  rewrite    read    reread    read     write     read   rewrite      read   fwrite frewrite    fread  freread
           51200       4     2373     2441    18793    18975     8621      410                                                                
           51200   16384     6099     5178    78318    78463    78398     6812

上面大概读的性能基本是:70M/s; 写的性能 5M/s

Mac OS(x86_64) 通过USB 3.0 端口接入U盘

    Command line used: iozone -e -I -a -s 50M -r 4k -r 16m -i 0 -i 1 -i 2
    Output is in kBytes/sec
    Time Resolution = 0.000001 seconds.
    Processor cache size set to 1024 kBytes.
    Processor cache line size set to 32 bytes.
    File stride size set to 17 * record size.
                                                              random    random     bkwd    record    stride                                    
              kB  reclen    write  rewrite    read    reread    read     write     read   rewrite      read   fwrite frewrite    fread  freread
           51200       4    13139     7940    85120  2640658  1985344    12898                                                                
           51200   16384     7662     9648   129762  4704900  4814108    11219

上面大概读的性能基本是:120M/s; 写的性能 7M/s

Raspberry4(arrch64,8G) 通过USB3.0 接入 HDD(500G)

 Command line used: iozone -e -I -a -s 50M -r 4k -r 16m -i 0 -i 1 -i 2
 Output is in kBytes/sec
 Time Resolution = 0.000001 seconds.
 Processor cache size set to 1024 kBytes.
 Processor cache line size set to 32 bytes.
 File stride size set to 17 * record size.
                                                           random    random     bkwd    record    stride                                    
           kB  reclen    write  rewrite    read    reread    read     write     read   rewrite      read   fwrite frewrite    fread  freread
        51200       4    18309    20514    24823    25471      582     1067                                                                
        51200   16384    77660    95436    99719   114945   114657    76049

上面大概读的性能基本是:100M/s; 写的性能 70M/s

u盘的读还可以,写就有点..... 系统工具:iozone,iotop,top,lsof