几乎每一个小时都会崩溃

Question

我在centOS数字海洋液滴上运行wordpress

我的mysql几乎每小时都会崩溃--内存不足。

手动重新启动mysqld，通过sudo服务mysqld重新启动它。

有人知道是什么导致了这一切吗？

以下是我在崩溃时的配置和日志：

/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/messages:

/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: 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'.

Answer 1

评论sort_buffer_size和join_buffer_size。改变这些几乎总是适得其反。

在任何时候有多少临时表(隐表或显式表)？如果超过极少数，而且它们很大，您将需要减少max_heap_table_size。