MySql 8 循环崩溃,没有特定错误,并且有足够的内存

MySql 8 循环崩溃,没有特定错误,并且有足够的内存

我正在运行带有 Mysql 8.0.27 的 Ubuntu 20.04.03。我已经多次从头开始重新安装了 LAMP,目前我只有 3 个 WordPress 网站,但我只测试了 1 个和 2 个网站。将 RAM 增加到 2GB 和 3GB 交换空间。

似乎没有什么效果,因为 Mysql 8.0.27 每晚都会崩溃,导致每个站点都出现数据库连接问题,即使这些站点都是全新的、没有任何流量的站点。当我编辑帖子或浏览任何这些网站时,MySql 再次崩溃。有时它甚至无法启动systemctl restart mysql

Apache 错误日志未显示任何重要内容:

/usr/sbin/mysqld (mysqld 8.0.27-0ubuntu0.20.04.1) starting as process 1524639

InnoDB initialization has started.

InnoDB initialization has ended.

Starting XA crash recovery... XA crash recovery finished.

A deprecated TLS version TLSv1 is enabled for channel mysql_main

A deprecated TLS version TLSv1.1 is enabled for channel mysql_main

CA certificate ca.pem is self signed.

Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock

我已经检查过,配置确实加载了所有可接受的 TLS 版本。因此,这里没有真正的错误。

journalctl -u mysql 最后日志记录:

Jan 28 10:29:37 www.ignicion.org systemd[1]: mysql.service: Failed with result 'signal'.
Jan 28 10:29:37 www.ignicion.org systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Jan 28 10:29:37 www.ignicion.org systemd[1]: Stopped MySQL Community Server.
Jan 28 10:29:37 www.ignicion.org systemd[1]: Starting MySQL Community Server...
Jan 28 10:29:43 www.ignicion.org systemd[1]: mysql.service: Main process exited, code=killed, status=9/KILL
Jan 28 10:29:43 www.ignicion.org systemd[1]: mysql.service: Failed with result 'signal'.
Jan 28 10:29:43 www.ignicion.org systemd[1]: Failed to start MySQL Community Server.
Jan 28 10:29:43 www.ignicion.org systemd[1]: mysql.service: Scheduled restart job, restart counter is at 6.
Jan 28 10:29:43 www.ignicion.org systemd[1]: Stopped MySQL Community Server.
Jan 28 10:29:43 www.ignicion.org systemd[1]: Starting MySQL Community Server...
Jan 28 10:29:50 www.ignicion.org systemd[1]: Started MySQL Community Server.

我知道这是内存问题,但为什么呢?并不是服务器做了这么多。我一直在监控进程,Mysql 是消耗所有内存的那个。同样的新数据库在 ubuntu 18 上运行良好,所以我真的没有主意,也找不到论坛上找到的相关问题的任何解决方案。我找到的一些解决方案是在谈论数据库/表损坏,但这是全新的数据库,硬件故障已被 Digital Ocean 支持部门丢弃。

我将不胜感激任何见解

更新 #1 没有安排备份或任何其他任务。这只是一个全新的安装和新数据库。这是我在 ubuntu 20 中的配置文件:/etc/mysql/mysql.conf.d

[mysqld]
user            = mysql
bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1
key_buffer_size         = 16M
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
max_binlog_size   = 100M
innodb_file_per_table = 1

从我的内核日志文件(tail -100 /var/log/kern.log)来看,该信号似乎SIGKILL 9 Term Kill signal由于内存使用量过多而杀死了 mysql

Jan 28 18:12:26 www kernel: [623011.971582] oom-kill:constraint=CONSTRAINT_NONE, 
nodemask= (null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/mysql.service,task=mysqld,pid=1669785,uid=113

Jan 28 18:12:26 www kernel: [623011.971617] Out of memory: Killed process 166978 
5 (mysqld) total-vm:720836kB, anon-rss:292028kB, file-rss:804kB, shmem-rss:0kB, 
UID:113 pgtables:816kB oom_score_adj:0

Jan 28 18:12:26 www kernel: [623012.005506] oom_reaper: reaped process 1669785 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB

所以我读到我应该检查 Mysql 缓冲区配置,这就是我目前所处的位置。

更新 #2 cat /proc/meminfo

MemTotal:        2030808 kB
MemFree:           54016 kB
MemAvailable:       3424 kB
Buffers:             240 kB
Cached:           285620 kB
SwapCached:        44532 kB
Active:          1188660 kB
Inactive:         495868 kB
Active(anon):    1187984 kB
Inactive(anon):   495088 kB
Active(file):        676 kB
Inactive(file):      780 kB
Unevictable:       19120 kB
Mlocked:           19120 kB
SwapTotal:       3145724 kB
SwapFree:              0 kB
Dirty:                 0 kB
Writeback:             0 kB
AnonPages:       1383352 kB
Mapped:           284872 kB
Shmem:            276064 kB
KReclaimable:      47968 kB
Slab:             171388 kB
SReclaimable:      47968 kB
SUnreclaim:       123420 kB
KernelStack:        7744 kB
PageTables:        59832 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:     4161128 kB
Committed_AS:    9186644 kB
VmallocTotal:   34359738367 kB
VmallocUsed:       16512 kB
VmallocChunk:          0 kB
Percpu:             1808 kB
HardwareCorrupted:     0 kB
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
ShmemPmdMapped:        0 kB
FileHugePages:         0 kB
FilePmdMapped:         0 kB
CmaTotal:              0 kB
CmaFree:               0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               0 kB
DirectMap4k:     1335276 kB
DirectMap2M:      761856 kB


ps  -aux --sort -rss|head -5
USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql    1715614  6.7 18.8 1763392 383344 ?      Ssl  22:21   0:01 /usr/sbin/mysqld
aceitep+ 1686006  0.0  2.0 342620 41076 ?        S    19:44   0:02 /bin/php-cgi7.4
aceitep+ 1686009  0.0  1.9 265576 39268 ?        S    19:44   0:02 /bin/php-cgi7.4
aceitep+ 1686001  0.0  1.8 342152 38348 ?        S    19:44   0:04 /bin/php-cgi7.4


因为我认为它与某些 mysql 变量有关,所以我运行了 MySql Tunner,并且给出了以下建议:

Variables to adjust:
    innodb_buffer_pool_size (>= 391.3M) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files                                                                              size equals to 25% of buffer pool size.

因此我将进行测试以增加这个数量并发布结果。

更新 #3 已添加innodb_buffer_pool_size = 512M 到我的/etc/mysql/mysql.conf.d/mysqld.cnf文件,但仍然崩溃。日志仍然相同。:(

当我运行 Show 时分配的总大内存为零,这正常吗?Engine InnoDB status;

----------------------
BUFFER POOL AND MEMORY
----------------------
**Total large memory allocated 0**
Dictionary memory allocated 1009720
Buffer pool size   32765
Free buffers       29298
Database pages     3405
Old database pages 1276
Modified db pages  0

那当我运行节目时怎么样variables like 'innodb_%';

innodb_buffer_pool_size           | 536870912
innodb_change_buffer_max_size    | 25

为什么缓冲池大小不匹配,这innodb_change_buffer_max_size是什么意思?我还应该检查哪些变量?再次感谢大家

答案1

好的。现在解决了。对于这种行为,没有具体的解决方案,因为内核会因为使用过多的 RAM 而杀死 mysql,当你试图找出原因时,却找不到具体的原因。所以你唯一能做的就是尝试使用变量并进行大量测试。我学到了什么:

  • 根据我的短暂经验,我不知道 Myslq 配置文件(Ubuntu 20.04 上的 /etc/mysql/mysql.conf.d/mysqlconf.d)没有显示太多变量,因为这些变量是默认设置的,所以如果我们想设置不同的值,我们必须手动在配置文件中添加每个变量。

  • 安装并运行 mysql tuner(谷歌搜索),这样它就可以指出您的特定设置的起点。就我而言,它建议增加 innodb_buffer_pool_size。并且 innodb_log_file_size 应等于 innodb_buffer_pool_size 值的 25%,以获得最佳性能。例如 innodb_buffer_pool_size = 1Gb innodb_log_file_size = 0.25Gb

  • 由于我的 RAM 为 2GB,因此我将 innodb_buffer_pool_size 设置为 512M。这只会让服务器在再次崩溃之前持续更长时间。因此,您必须从这里开始了解有关每个 Mysql 变量的更多信息,并根据数据库大小进行计算。

这是我的最终 Mysql 配置文件,您可以随意尝试此配置。只需知道我的数据库大小目前为 394MB:


[mysqld]
skip-log-bin
user            = mysql
bind-address            = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
general_log = on
general_log_file=/var/log/mysql/general.log

key_buffer_size                 = 1M
max_allowed_packet              = 1M
thread_stack                    = 200K
thread_cache_size               = 8
max_connect_errors              = 100
max_connections                 = 100
#table_cache                    = 64
#thread_concurrency             = 30
binlog_cache_size               = 1M
net_buffer_length               = 1M

default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 1 # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 400M   # Use up to 70-80% of RAM
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 16M
innodb_stats_on_metadata        = 0
#innodb_thread_concurrency      = 0
innodb_read_io_threads          = 40
innodb_write_io_threads         = 40
innodb_buffer_pool_chunk_size   = 10
innodb_lock_wait_timeout        = 600
innodb_flush_log_at_trx_commit  = 1
innodb_io_capacity              = 3000
innodb_io_capacity_max          = 4000
innodb_buffer_pool_dump_pct     = 80
innodb_flush_neighbors          = 0
innodb_doublewrite              = 0
innodb_change_buffer_max_size   = 10
innodb_old_blocks_pct           = 70
innodb_old_blocks_time          = 5000
innodb_use_native_aio           = ON

# The number of seconds the server waits for activity on an interactive connection before closing it.
interactive_timeout             = 600

# The number of seconds the server waits for activity on a noninteractive connection before closing it.
wait_timeout                    = 600
net_read_timeout                = 300
net_write_timeout               = 300
connect_timeout                 = 1800

# Table Settings
table_definition_cache          = 1K
table_open_cache                = 2K
table_open_cache                = 2K
open_files_limit                = 4000

max_heap_table_size             = 100M
tmp_table_size                  = 100M

#
# * Query Cache Configuration
#
#query_cache_limit               = 1M
#query_cache_size                = 100M
#query_cache_size                = 0
#query_cache_type                = 0



# Buffer Settings
join_buffer_size                = 256K
read_buffer_size                = 256K
read_rnd_buffer_size            = 256K
sort_buffer_size                = 128K
performance_schema              = ON

如果我的数据库大小随着时间的推移而增加(它们肯定会),我将必须增加这个值:


key_buffer_size = 1M
max_connections = 100
innodb_buffer_pool_size = 400M
join_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
sort_buffer_size = 128K

我可以将 innodb_buffer_pool_size 增加到 1Gb,但这意味着我必须减少其他变量并再次测试以测试最佳性能设置。(我怎么说呢,如果你不知道,这些变量需要进行大量研究)另一方面,即使我的数据库随着时间的推移而增加,我也可以让这些值保持不变,但 Mysql 将开始占用更多的 Swamp 内存,因此查询会随着时间的推移而变慢一些。

再说了,如果你负担得起的话,你可以增加服务器的 RAM。

希望这对大家有帮助。我没有经验,所以我发布这些答案也是为了将来参考 :)

相关内容