我在 centOS digital ocean droplet 上运行 wordpress
我的 mysql 几乎每小时都会崩溃一次——出现内存不足错误
手动重启 mysqld 可以通过 sudo service mysqld restart 来恢复
有谁知道这可能是什么原因造成的？
以下是崩溃时的配置和日志：
/etc/my.cnf
[mysqld]
max_connections = 50
wait_timeout = 3600
interactive_timeout = 3600
open_files_limit = 2048
table_open_cache = 1024
max_allowed_packet = 16M
max_heap_table_size = 256M
tmp_table_size = 256M
key_buffer_size = 128M
join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 256K
/var/log/消息：
/var/log/messages:Sep 16 17:51:38 centos kernel: mysqld invoked oom-killer: gfp_mask=0x6200ca(GFP_HIGHUSER_MOVABLE), nodemask=(null), order=0, oom_score_adj=0
/var/log/messages:Sep 16 17:51:38 centos kernel: ? virtballoon_oom_notify+0x25/0x70 [virtio_balloon]
/var/log/messages:Sep 16 17:51:38 centos kernel: oom_kill_process.cold.29+0xb/0x10
/var/log/messages:Sep 16 17:51:38 centos kernel: [ pid ] uid tgid total_vm rss pgtables_bytes swapents oom_score_adj name
/var/log/messages:Sep 16 17:51:38 centos kernel: oom_reaper: reaped process 11402 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB
日志控制
Sep 16 17:51:38 centos kernel: mysqld invoked oom-killer: gfp_mask=0x6200ca(GFP_HIGHUSER_MOVABLE), nodemask=(null), order=0, oom_score_adj=0
Sep 16 17:51:38 centos kernel: mysqld cpuset=/ mems_allowed=0
Sep 16 17:51:38 centos kernel: CPU: 0 PID: 11418 Comm: mysqld Kdump: loaded Not tainted 4.18.0-147.5.1.el8_1.x86_64 #1
Sep 16 17:51:38 centos kernel: Hardware name: DigitalOcean Droplet, BIOS 20171212 12/12/2017
Sep 16 17:51:38 centos kernel: Call Trace:
Sep 16 17:51:38 centos kernel: dump_stack+0x5c/0x80
Sep 16 17:51:38 centos kernel: dump_header+0x6e/0x27a
Sep 16 17:51:38 centos kernel: ? virtballoon_oom_notify+0x25/0x70 [virtio_balloon]
Sep 16 17:51:38 centos kernel: oom_kill_process.cold.29+0xb/0x10
Sep 16 17:51:38 centos kernel: out_of_memory+0x1ba/0x490
Sep 16 17:51:38 centos kernel: __alloc_pages_slowpath+0xc0f/0xce0
Sep 16 17:51:38 centos kernel: __alloc_pages_nodemask+0x245/0x280
Sep 16 17:51:38 centos kernel: filemap_fault+0x3d8/0x860
Sep 16 17:51:38 centos kernel: ? hrtimer_try_to_cancel+0x25/0x100
Sep 16 17:51:38 centos kernel: ? pmd_devmap_trans_unstable+0x2a/0x40
Sep 16 17:51:38 centos kernel: ? alloc_set_pte+0x38a/0x480
Sep 16 17:51:38 centos kernel: ? _cond_resched+0x15/0x30
Sep 16 17:51:38 centos kernel: __xfs_filemap_fault+0x6d/0x200 [xfs]
Sep 16 17:51:38 centos kernel: __do_fault+0x20/0x80
Sep 16 17:51:38 centos kernel: do_fault+0x18d/0x3e0
Sep 16 17:51:38 centos kernel: ? futex_wake+0x90/0x170
Sep 16 17:51:38 centos kernel: __handle_mm_fault+0x539/0x6b0
Sep 16 17:51:38 centos kernel: handle_mm_fault+0xda/0x200
Sep 16 17:51:38 centos kernel: __do_page_fault+0x22b/0x4e0
Sep 16 17:51:38 centos kernel: do_page_fault+0x32/0x110
Sep 16 17:51:38 centos kernel: ? async_page_fault+0x8/0x30
Sep 16 17:51:38 centos kernel: async_page_fault+0x1e/0x30
Sep 16 17:51:38 centos kernel: RIP: 0033:0x55c787761910
Sep 16 17:51:38 centos kernel: Code: Bad RIP value.
Sep 16 17:51:38 centos kernel: RSP: 002b:00007fc6eb4f7c10 EFLAGS: 00010202
Sep 16 17:51:38 centos kernel: RAX: 0000000000000001 RBX: 0000000000000001 RCX: 0000000000000000
Sep 16 17:51:38 centos kernel: RDX: 00007fc70037dd58 RSI: 0000000000000000 RDI: 00007fc70037dd58
Sep 16 17:51:38 centos kernel: RBP: 00007fc6eb4f7d20 R08: 0000000000000000 R09: 000000000000f41e
Sep 16 17:51:38 centos kernel: R10: 00007fc6eb4f7960 R11: 0000000000000206 R12: 0000000000000000
Sep 16 17:51:38 centos kernel: R13: 00007fc7004365f8 R14: 000055c78919a5f0 R15: 000000000000001e
Sep 16 17:51:38 centos kernel: Mem-Info:
Sep 16 17:51:38 centos kernel: active_anon:398471 inactive_anon:13614 isolated_anon:0#012 active_file:127 inactive_file:3062 isolated_file:0#012 unevictable:0 di
rty:0 writeback:0 unstable:0#012 slab_reclaimable:6282 slab_unreclaimable:16715#012 mapped:25089 shmem:47803 pagetables:8459 bounce:0#012 free:13017 free_pcp:0 free_cma:0
Sep 16 17:51:38 centos kernel: Node 0 active_anon:1593884kB inactive_anon:54456kB active_file:508kB inactive_file:12248kB unevictable:0kB isolated(anon):0kB isol
ated(file):0kB mapped:100356kB dirty:0kB writeback:0kB shmem:191212kB shmem_thp: 0kB shmem_pmdmapped: 0kB anon_thp: 724992kB writeback_tmp:0kB unstable:0kB all_unreclaimable? no
Sep 16 17:51:38 centos kernel: Node 0 DMA free:7424kB min:392kB low:488kB high:584kB active_anon:7720kB inactive_anon:0kB active_file:0kB inactive_file:96kB unev
ictable:0kB writepending:0kB present:15992kB managed:15908kB mlocked:0kB kernel_stack:16kB pagetables:76kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB
Sep 16 17:51:38 centos kernel: lowmem_reserve[]: 0 1759 1759 1759 1759
Sep 16 17:51:38 centos kernel: Node 0 DMA32 free:44644kB min:44660kB low:55824kB high:66988kB active_anon:1586164kB inactive_anon:54456kB active_file:508kB inact
ive_file:12152kB unevictable:0kB writepending:0kB present:2080740kB managed:1857680kB mlocked:0kB kernel_stack:3264kB pagetables:33760kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB
Sep 16 17:51:38 centos kernel: lowmem_reserve[]: 0 0 0 0 0
Sep 16 17:51:38 centos kernel: Node 0 DMA: 12*4kB (U) 14*8kB (UME) 14*16kB (UME) 42*32kB (UME) 19*64kB (UME) 9*128kB (UME) 5*256kB (M) 4*512kB (UME) 0*1024kB 0*2
048kB 0*4096kB = 7424kB
Sep 16 17:51:38 centos kernel: Node 0 DMA32: 1126*4kB (UME) 986*8kB (UME) 547*16kB (UME) 215*32kB (UME) 78*64kB (UME) 25*128kB (UME) 7*256kB (UME) 9*512kB (ME) 2
*1024kB (M) 0*2048kB 0*4096kB = 44664kB
Sep 16 17:51:38 centos kernel: Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=2048kB
Sep 16 17:51:38 centos kernel: 50995 total pagecache pages
Sep 16 17:51:38 centos kernel: 0 pages in swap cache
Sep 16 17:51:38 centos kernel: Swap cache stats: add 0, delete 0, find 0/0
Sep 16 17:51:38 centos kernel: Free swap = 0kB
Sep 16 17:51:38 centos kernel: Total swap = 0kB
Sep 16 17:51:38 centos kernel: 524183 pages RAM
Sep 16 17:51:38 centos kernel: 0 pages HighMem/MovableOnly
Sep 16 17:51:38 centos kernel: 55786 pages reserved
Sep 16 17:51:38 centos kernel: 0 pages hwpoisoned
Sep 16 17:51:38 centos kernel: [ pid ] uid tgid total_vm rss pgtables_bytes swapents oom_score_adj name
Sep 16 17:51:38 centos kernel: [ 581] 0 581 29841 4014 262144 0 0 systemd-journal
Sep 16 17:51:38 centos kernel: [ 671] 81 671 16453 665 163840 0 -900 dbus-daemon
Sep 16 17:51:38 centos kernel: [ 678] 993 678 31878 133 147456 0 0 chronyd
Sep 16 17:51:38 centos kernel: [ 763] 0 763 22005 777 212992 0 0 systemd-logind
Sep 16 17:51:38 centos kernel: [ 807] 0 807 149740 633 385024 0 0 NetworkManager
Sep 16 17:51:38 centos kernel: [ 1030] 0 1030 35492 246 294912 0 0 login
Sep 16 17:51:38 centos kernel: [ 1031] 0 1031 56358 30 73728 0 0 agetty
Sep 16 17:51:38 centos kernel: [19456] 989 19456 254011 1927 184320 0 -900 do-agent
Sep 16 17:51:38 centos kernel: [16142] 0 16142 146563 1980 503808 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [16201] 0 16201 27984 822 237568 0 0 sendmail
Sep 16 17:51:38 centos kernel: [16221] 51 16221 17601 395 159744 0 0 sendmail
Sep 16 17:51:38 centos kernel: [19175] 991 19175 175468 25412 720896 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19176] 991 19176 175976 25799 724992 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19183] 991 19183 175999 27675 729088 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19184] 991 19184 174437 25598 712704 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19185] 991 19185 174953 24836 720896 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19186] 991 19186 174945 24946 720896 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19187] 991 19187 175991 26901 724992 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19193] 991 19193 173913 24350 708608 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19195] 991 19195 174937 24418 716800 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19196] 991 19196 175495 26576 720896 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19197] 991 19197 173880 24662 708608 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19207] 991 19207 174970 25162 716800 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19208] 991 19208 175473 26660 724992 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19209] 991 19209 174950 24137 716800 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19210] 991 19210 174979 25985 720896 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19211] 991 19211 174946 25183 716800 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19227] 991 19227 175434 25320 720896 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19228] 991 19228 173963 24288 712704 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19229] 991 19229 175480 26312 724992 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19230] 991 19230 175980 27686 729088 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19231] 991 19231 173936 24946 708608 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19242] 991 19242 175464 26445 729088 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [19243] 991 19243 175455 26387 724992 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [30717] 991 30717 174336 23067 708608 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [30737] 991 30737 174850 24520 716800 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [30738] 991 30738 174840 23350 708608 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [30739] 991 30739 174914 24975 716800 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [30740] 991 30740 173324 22868 704512 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [30741] 991 30741 175294 26677 720896 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [30743] 991 30743 173860 23508 704512 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [30744] 991 30744 174879 26783 720896 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [30745] 991 30745 173342 22907 700416 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [30746] 991 30746 174349 24161 708608 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [30747] 991 30747 174877 25364 724992 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [10335] 1001 10335 23312 342 221184 0 0 systemd
Sep 16 17:51:38 centos kernel: [10350] 1001 10350 79149 1082 319488 0 0 (sd-pam)
Sep 16 17:51:38 centos kernel: [28009] 1001 28009 58294 203 90112 0 0 bash
Sep 16 17:51:38 centos kernel: [28037] 0 28037 88552 264 307200 0 0 sudo
Sep 16 17:51:38 centos kernel: [28041] 0 28041 58792 167 94208 0 0 bash
Sep 16 17:51:38 centos kernel: [28114] 0 28114 39869 321 331776 0 0 sshd
Sep 16 17:51:38 centos kernel: [28117] 1001 28117 39869 328 319488 0 0 sshd
Sep 16 17:51:38 centos kernel: [28118] 1001 28118 58261 162 86016 0 0 bash
Sep 16 17:51:38 centos kernel: [28205] 0 28205 88552 261 294912 0 0 sudo
Sep 16 17:51:38 centos kernel: [28208] 0 28208 58825 209 94208 0 0 bash
Sep 16 17:51:38 centos kernel: [29844] 0 29844 29705 649 221184 0 0 nginx
Sep 16 17:51:38 centos kernel: [29845] 991 29845 38341 1801 282624 0 0 nginx
Sep 16 17:51:38 centos kernel: [31882] 0 31882 61464 219 114688 0 0 crond
Sep 16 17:51:38 centos kernel: [ 8147] 0 8147 77482 3076 331776 0 0 rsyslogd
Sep 16 17:51:38 centos kernel: [ 8156] 0 8156 77886 165 188416 0 0 gssproxy
Sep 16 17:51:38 centos kernel: [ 8169] 987 8169 40058 205 217088 0 0 rngd
Sep 16 17:51:38 centos kernel: [ 8182] 32 8182 16780 180 172032 0 0 rpcbind
Sep 16 17:51:38 centos kernel: [12562] 0 12562 37436 240 155648 0 -1000 auditd
Sep 16 17:51:38 centos kernel: [12564] 0 12564 12121 94 139264 0 0 sedispatch
Sep 16 17:51:38 centos kernel: [12588] 998 12588 406517 1271 327680 0 0 polkitd
Sep 16 17:51:38 centos kernel: [12597] 0 12597 158765 3689 442368 0 0 tuned
Sep 16 17:51:38 centos kernel: [12636] 0 12636 24081 598 221184 0 -1000 systemd-udevd
Sep 16 17:51:38 centos kernel: [12797] 0 12797 23242 226 204800 0 -1000 sshd
Sep 16 17:51:38 centos kernel: [21239] 0 21239 105551 493 438272 0 0 sssd
Sep 16 17:51:38 centos kernel: [21241] 0 21241 107218 664 446464 0 0 sssd_be
Sep 16 17:51:38 centos kernel: [21242] 0 21242 107745 405 466944 0 0 sssd_nss
Sep 16 17:51:38 centos kernel: [11402] 27 11402 381489 104936 1277952 0 0 mysqld
Sep 16 17:51:38 centos kernel: [11496] 991 11496 172880 18671 671744 0 0 php-fpm
Sep 16 17:51:38 centos kernel: [12890] 0 12890 198407 18847 901120 0 0 dnf
Sep 16 17:51:38 centos kernel: Out of memory: Killed process 11402 (mysqld) total-vm:1525956kB, anon-rss:419744kB, file-rss:0kB, shmem-rss:0kB
Sep 16 17:51:38 centos kernel: oom_reaper: reaped process 11402 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB
Sep 16 17:51:38 centos systemd[1]: mysqld.service: Main process exited, code=killed, status=9/KILL
Sep 16 17:51:38 centos systemd[1]: mysqld.service: Failed with result 'signal'.
Sep 16 17:51:38 centos systemd[1]: mysqld.service: Service RestartSec=100ms expired, scheduling restart.
Sep 16 17:51:38 centos systemd[1]: mysqld.service: Scheduled restart job, restart counter is at 1.
Sep 16 17:51:38 centos systemd[1]: Stopped MySQL 8.0 database server.
Sep 16 17:51:38 centos systemd[1]: Starting MySQL 8.0 database server...
Sep 16 17:51:38 centos mysql-check-socket[12938]: Socket file /var/lib/mysql/mysql.sock exists.
Sep 16 17:51:38 centos mysql-check-socket[12938]: No process is using /var/lib/mysql/mysql.sock, which means it is a garbage, so it will be removed automatically
Sep 16 17:51:39 centos systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE
Sep 16 17:51:39 centos systemd[1]: mysqld.service: Failed with result 'exit-code'.
Sep 16 17:51:39 centos systemd[1]: Failed to start MySQL 8.0 database server.
自由的
total used free shared buff/cache available
Mem: 1873588 1563424 69388 191208 240776 19560
Swap: 0 0 0
答案1
您的问题有两个方面，一个是问题，一个是问题。我认为第二个方面是问题。
您的数据库可能太大，以致没有足够的内存来存储它。
更合理的解释是内存管理出现问题。Linux 的设计目标是使用交换空间，而您却没有。您应该创建一个交换文件（即使它相对较小）并启用交换空间。只需交换即可向 Linux 发送信号，这对于内存管理很重要，并且可能会解决您的问题
交换可以是分区（首选）或文件（通常更简单且相当不错，但性能略差）。您可能还想将其设置vm.swappiness为较低的值（例如 0 或 10）。这会向内存管理器发送信号，以将交换作为最后的手段，这样您的系统在将内存块交换到磁盘时就不会无响应。
答案2
注释掉 sort_buffer_size 和 join_buffer_size。更改这些几乎总是适得其反。
您一次有多少个临时表（隐式或显式）？如果数量很少而且很大，则需要减少 max_heap_table_size。
答案3
将这些降低到不超过 RAM 的 1％。
max_heap_table_size = 256M
tmp_table_size = 256M
对于 2GB 设置来说，这可能不应超过 500M：
innodb_buffer_pool_size
答案4
我解决了这个问题。原来是我的 php-fpm 配置错误。我通过调整 PHP-FPM (Nginx) 的子进程解决了这个问题