Можете ли вы помочь мне, как я могу определить, какой 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, которые создают эту проблему, но я не могу их идентифицировать ... Не могли бы вы мне помочь?
** ОБНОВИТЬ **
Ха, я не сказал, что проблема, скорее всего, вызвана приложением PHP, которое работает с таблицей, содержащей более 20 столбцов ...
Вывод команды 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
Надеюсь, это поможет нам понять, что мы можем сделать в этом случае ... Заранее благодарю!
Я думаю, у вас недостаточно ресурсов на сервере http, а mysql имеет наивысший рейтинг, чтобы проверить, какие запросы занимают много времени и ресурсов, возьмите ПОКАЗАТЬ ПОЛНЫЙ СПИСОК ПРОЦЕССОВ вывод через каждые 5 минут, это поможет идентифицировать запрос. Для http oom проверьте количество подключений в этот момент времени, поскольку я не уверен в вашей рабочей нагрузке, пожалуйста, проверьте также свой mpm, из журналов я вижу, что вы используете prefork, что может быть не лучшим вариантом для вашего типа рабочей нагрузки, любезно обзор.
Способ решения этой проблемы - установить ограничения на выделение памяти таким образом, чтобы система при полной загрузке не могла занять всю память машины.
Проблема вызвана тем, что ваше программное обеспечение пытается выделить больше памяти, чем доступно. Если вы установите максимальную память для db и db достигнет 100% использования, это замедлит db, но не вызовет OOM в системе. То же самое и с apache.