我正在运行带有 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。
希望这对大家有帮助。我没有经验,所以我发布这些答案也是为了将来参考 :)