MySQL 和 Apache 内存不足

MySQL 和 Apache 内存不足

你能帮助我找出哪个 SQL 查询导致我的 MySQL 内存耗尽吗?

因此,在控制台中运行 dmesg 命令,我得到以下结果:

[root@dmzemf httpd]# dmesg -T | grep 'Out of memory'
[Tue Apr 16 11:21:19 2019] Out of memory: Kill process 7522 (mysqld) score 185 or sacrifice child
[Tue Apr 16 11:23:26 2019] Out of memory: Kill process 11628 (mysqld) score 108 or sacrifice child
[Tue Apr 16 11:25:36 2019] Out of memory: Kill process 11847 (mysqld) score 109 or sacrifice child
[Tue Apr 16 11:25:36 2019] Out of memory: Kill process 12010 (httpd) score 106 or sacrifice child
[Tue Apr 16 11:53:14 2019] Out of memory: Kill process 12070 (mysqld) score 116 or sacrifice child
[Tue Apr 16 11:53:14 2019] Out of memory: Kill process 12008 (httpd) score 99 or sacrifice child
[Tue Apr 16 12:04:01 2019] Out of memory: Kill process 13530 (mysqld) score 127 or sacrifice child
[Tue Apr 16 12:04:01 2019] Out of memory: Kill process 13554 (mysqld) score 128 or sacrifice child
[Tue Apr 16 12:04:01 2019] Out of memory: Kill process 13563 (mysqld) score 128 or sacrifice child
[Tue Apr 16 14:01:15 2019] Out of memory: Kill process 18465 (mysqld) score 117 or sacrifice child
[Tue Apr 16 14:01:15 2019] Out of memory: Kill process 18666 (httpd) score 116 or sacrifice child
[Tue Apr 16 14:07:07 2019] Out of memory: Kill process 20734 (mysqld) score 120 or sacrifice child
[Tue Apr 16 14:07:07 2019] Out of memory: Kill process 20683 (httpd) score 75 or sacrifice child
[Tue Apr 16 14:07:11 2019] Out of memory: Kill process 21072 (httpd) score 100 or sacrifice child
[root@dmzemf httpd]# 

我还检查了文件 /var/log/mariadb.log:

[root@dmzemf mariadb]# tail mariadb.log 
Version: '5.5.60-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
190416 14:01:13 mysqld_safe Number of processes running now: 0
190416 14:01:13 mysqld_safe mysqld restarted
190416 14:01:13 InnoDB: The InnoDB memory heap is disabled
190416 14:01:13 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190416 14:01:13 InnoDB: Compressed tables use zlib 1.2.7
190416 14:01:13 InnoDB: Using Linux native AIO
190416 14:01:13 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 20734 ...
190416 14:01:13 InnoDB: Initializing buffer pool, size = 128.0M
190416 14:01:13 InnoDB: Completed initialization of buffer pool
190416 14:01:13 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
190416 14:01:14  InnoDB: Waiting for the background threads to start
190416 14:01:15 Percona XtraDB (http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 9910582483
190416 14:01:15 [Note] Plugin 'FEEDBACK' is disabled.
190416 14:01:15 [Note] Server socket created on IP: '0.0.0.0'.
190416 14:01:15 [Note] Event Scheduler: Loaded 0 events
190416 14:01:15 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
190416 14:07:06 mysqld_safe Number of processes running now: 0
190416 14:07:06 mysqld_safe mysqld restarted
190416 14:07:07 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 21142 ...
190416 14:07:08 InnoDB: The InnoDB memory heap is disabled
190416 14:07:08 InnoDB: Mutexes and rw_locks use GCC atomic builtins
190416 14:07:08 InnoDB: Compressed tables use zlib 1.2.7
190416 14:07:08 InnoDB: Using Linux native AIO
190416 14:07:08 InnoDB: Initializing buffer pool, size = 128.0M
190416 14:07:08 InnoDB: Completed initialization of buffer pool
190416 14:07:08 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
190416 14:07:10  InnoDB: Waiting for the background threads to start
190416 14:07:11 Percona XtraDB (http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 9913403499
190416 14:07:11 [Note] Plugin 'FEEDBACK' is disabled.
190416 14:07:11 [Note] Server socket created on IP: '0.0.0.0'.
190416 14:07:11 [Note] Event Scheduler: Loaded 0 events
190416 14:07:11 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

文件 /var/log/httpd/error_log 返回以下内容:

[root@dmzemf httpd]# tail error_log
[Sun Apr 14 03:17:16.513647 2019] [mpm_prefork:notice] [pid 1501] AH00163: Apache/2.4.6 (Red Hat Enterprise Linux) OpenSSL/1.0.2k-fips PHP/5.4.16 configured -- resuming normal operations
[Sun Apr 14 03:17:16.513667 2019] [core:notice] [pid 1501] AH00094: Command line: '/usr/sbin/httpd -D FOREGROUND'
[Mon Apr 15 11:26:38.754999 2019] [mpm_prefork:notice] [pid 1501] AH00170: caught SIGWINCH, shutting down gracefully
[Mon Apr 15 11:26:39.863554 2019] [core:notice] [pid 2894] SELinux policy enabled; httpd running as context system_u:system_r:httpd_t:s0
[Mon Apr 15 11:26:39.957750 2019] [mpm_prefork:notice] [pid 2894] AH00163: Apache/2.4.6 (Red Hat Enterprise Linux) OpenSSL/1.0.2k-fips PHP/5.4.16 configured -- resuming normal operations
[Mon Apr 15 11:26:39.957774 2019] [core:notice] [pid 2894] AH00094: Command line: '/usr/sbin/httpd -D FOREGROUND'
[Tue Apr 16 08:32:42.820348 2019] [mpm_prefork:notice] [pid 2894] AH00170: caught SIGWINCH, shutting down gracefully
[Tue Apr 16 08:32:43.949798 2019] [core:notice] [pid 1909] SELinux policy enabled; httpd running as context system_u:system_r:httpd_t:s0
[Tue Apr 16 08:32:44.056357 2019] [mpm_prefork:notice] [pid 1909] AH00163: Apache/2.4.6 (Red Hat Enterprise Linux) OpenSSL/1.0.2k-fips PHP/5.4.16 configured -- resuming normal operations
[Tue Apr 16 08:32:44.056391 2019] [core:notice] [pid 1909] AH00094: Command line: '/usr/sbin/httpd -D FOREGROUND'
[Tue Apr 16 13:21:09.454338 2019] [mpm_prefork:notice] [pid 1909] AH00170: caught SIGWINCH, shutting down gracefully
[Tue Apr 16 13:21:10.655994 2019] [core:notice] [pid 18170] SELinux policy enabled; httpd running as context system_u:system_r:httpd_t:s0
[Tue Apr 16 13:21:10.934372 2019] [mpm_prefork:notice] [pid 18170] AH00163: Apache/2.4.6 (Red Hat Enterprise Linux) OpenSSL/1.0.2k-fips PHP/5.4.16 configured -- resuming normal operations
[Tue Apr 16 13:21:10.934395 2019] [core:notice] [pid 18170] AH00094: Command line: '/usr/sbin/httpd -D FOREGROUND'
[root@dmzemf httpd]# 

另外,也许可以帮助文件 /etc/my.cnf.d/server.cnf 的内容

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
max_allowed_packet = 128M
#innodb_buffer_pool_size = 16M
#innodb_log_file_size = 128M
#innodb_log_buffer_size = 8M


# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]

# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

[mariadb-5.5]

当然,有一些 PHP 脚本会导致此问题,但我无法识别它们......您能帮帮我吗?

** 更新 **

嗯,我没有说该问题很可能是由处理超过 20 列的表的 PHP 应用程序引起的......

sar -u 1 10 命令的输出如下所示:

对于有效的页面:

[root@dmzemf ~]# sar -u 1 10
Linux 3.10.0-693.11.1.el7.x86_64 (dmzemf.domain.com)      04/18/2019      _x86_64_        (2 CPU)

09:24:20 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
09:24:21 AM     all      0.00      0.00      0.50      0.00      0.00     99.50
09:24:22 AM     all      0.00      0.00      0.50      0.00      0.00     99.50
09:24:23 AM     all      0.50      0.00      0.00      0.00      0.00     99.50
09:24:24 AM     all      0.00      0.00      0.00      0.00      0.00    100.00
09:24:25 AM     all     24.00      0.00      3.50      0.00      0.00     72.50
09:24:26 AM     all     46.73      0.00      4.02      0.50      0.00     48.74
09:24:27 AM     all      1.01      0.00      1.51      0.00      0.00     97.49
09:24:28 AM     all      1.50      0.00      0.50      0.00      0.00     98.00
09:24:29 AM     all      0.00      0.00      0.50      0.00      0.00     99.50
09:24:30 AM     all      0.00      0.00      0.00      0.00      0.00    100.00
Average:        all      7.37      0.00      1.10      0.05      0.00     91.48
[root@dmzemf ~]# 

对于出现问题的页面:

[root@dmzemf ~]# sar -u 1 10
Linux 3.10.0-693.11.1.el7.x86_64 (dmzemf.domain.com)      04/18/2019      _x86_64_        (2 CPU)

09:26:03 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
09:26:04 AM     all      0.00      0.00      0.00      0.00      0.00    100.00
09:26:05 AM     all     46.27      0.00      4.48      0.00      0.00     49.25
09:26:06 AM     all     47.24      0.00      2.51      0.00      0.00     50.25
09:26:07 AM     all      7.00      0.00      2.00      0.00      0.00     91.00
09:26:08 AM     all      0.00      0.00      0.00      0.00      0.00    100.00
09:26:09 AM     all      0.00      0.00      0.00      0.00      0.00    100.00
09:26:10 AM     all      0.00      0.00      0.00      0.00      0.00    100.00
09:26:11 AM     all      0.00      0.00      0.00      0.00      0.00    100.00
09:26:12 AM     all      0.00      0.00      0.00      0.00      0.00    100.00
09:26:13 AM     all      0.50      0.00      0.00      0.00      0.00     99.50
Average:        all     10.12      0.00      0.90      0.00      0.00     88.98
[root@dmzemf ~]# 

此外,top 命令的顶部如下所示:

Tasks: 180 total,   2 running, 178 sleeping,   0 stopped,   0 zombie
%Cpu(s): 27.0 us,  2.3 sy,  0.0 ni, 70.5 id,  0.0 wa,  0.0 hi,  0.2 si,  0.0 st
KiB Mem :  3882072 total,   182316 free,  1998020 used,  1701736 buff/cache
KiB Swap:  2097148 total,  1780112 free,   317036 used.  1472304 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                 
27141 apache    20   0  821852 473448   4424 R  49.2 12.2   0:03.45 httpd                                   
28289 mysql     20   0  971048 131304   8272 S  10.0  3.4   0:01.06 mysqld                                  
28531 emf       20   0  162040   2368   1592 R   0.3  0.1   0:00.09 top                                     
    1 root      20   0  201860   4860   2932 S   0.0  0.1 104:36.02 systemd                                 
    2 root      20   0       0      0      0 S   0.0  0.0   0:12.95 kthreadd                                
    3 root      20   0       0      0      0 S   0.0  0.0   0:17.60 ksoftirqd/0                             
    5 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 kworker/0:0H                            
    7 root      rt   0       0      0      0 S   0.0  0.0   0:02.86 migration/0                             
    8 root      20   0       0      0      0 S   0.0  0.0   0:00.00 rcu_bh                                  
    9 root      20   0       0      0      0 S   0.0  0.0 118:06.00 rcu_sched                               
   10 root      rt   0       0      0      0 S   0.0  0.0   2:47.77 watchdog/0                              
   11 root      rt   0       0      0      0 S   0.0  0.0   3:00.40 watchdog/1                              
   12 root      rt   0       0      0      0 S   0.0  0.0   0:02.30 migration/1                             
   13 root      20   0       0      0      0 S   0.0  0.0   0:20.28 ksoftirqd/1                             
   15 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 kworker/1:0H                            
   17 root      20   0       0      0      0 S   0.0  0.0   0:00.00 kdevtmpfs        

我希望这可以为我们提供线索,告诉我们在这种情况下我们能做什么......提前谢谢你!

答案1

解决这个问题的方法是设置内存分配限制,使得满载系统不能占用机器的所有内存。

问题是由于您的软件试图分配的内存超过可用内存量而引起的。如果您为数据库设置了最大内存,而数据库的使用率达到 100%,则数据库的速度会变慢,但不会导致系统出现 OOM。Apache 也是如此。

答案2

我认为你在服务器上没有足够的资源 http 和 mysql 有最高的 oom 分数,要检查哪些查询花费了很长时间和资源,请采取显示完整流程列表每 5 分钟输出一次,这将有助于识别查询。对于 http oom,请检查当时的连接数,因为我不确定您的工作负载,请也检查您的 mpm,从日志中我可以看到您正在使用 prefork,这可能不是您这种工作负载的最佳选择,请检查。

相关内容