![MySQL 在生产服务器上不断“噗通”](https://linux22.com/image/705560/MySQL%20%E5%9C%A8%E7%94%9F%E4%BA%A7%E6%9C%8D%E5%8A%A1%E5%99%A8%E4%B8%8A%E4%B8%8D%E6%96%AD%E2%80%9C%E5%99%97%E9%80%9A%E2%80%9D.png)
我有一台运行 Ubuntu 和 MySQL 的生产服务器。同一台服务器上没有运行其他服务。它有 32GB 内存。MySQL 服务设置为始终重启:
ExecStart=/usr/sbin/mysqld
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=always
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755
dmesg 没有报告任何相关问题:
[Sat May 27 21:40:35 2017] blk_update_request: I/O error, dev fd0, sector 0
[Sat May 27 21:40:35 2017] floppy: error -5 while reading block 0
[Wed Jun 7 02:48:53 2017] blk_update_request: I/O error, dev fd0, sector 0
[Wed Jun 7 02:48:53 2017] floppy: error -5 while reading block 0
[Wed Jun 7 02:49:18 2017] blk_update_request: I/O error, dev fd0, sector 0
[Wed Jun 7 02:49:18 2017] floppy: error -5 while reading block 0
我已禁用性能模式:
performance_schema = off
我运行了 badblocks,没有出现任何错误:
Checking for bad blocks in non-destructive read-write mode
From block 0 to 31456238
Checking for bad blocks (non-destructive read-write test)
Testing with random pattern: done
Pass completed, 0 bad blocks found. (0/0/0 errors)
与 fsck 相同:
fsck from util-linux 2.27.1
e2fsck 1.42.13 (17-May-2015)
/dev/sdb1: clean, 375/1966080 files, 3268974/7864059 blocks
错误日志似乎显示了一次相当干净的关机:
2017-06-06T13:16:38.251027Z 67 [Note] Aborted connection 67 to db: 'ivr' user: 'root' host: 'ivr.teleline.co.il' (Got timeout reading communication packets)
2017-06-06T23:48:37.094552Z 0 [Note] Giving 16 client threads a chance to die gracefully
2017-06-06T23:48:37.094646Z 0 [Note] Shutting down slave threads
2017-06-06T23:48:39.094785Z 0 [Note] Forcefully disconnecting 15 remaining clients
2017-06-06T23:48:39.094867Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 6 user: 'root'
2017-06-06T23:48:39.094963Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 14 user: 'root'
2017-06-06T23:48:39.095017Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 18 user: 'root'
2017-06-06T23:48:39.095061Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 8 user: 'root'
2017-06-06T23:48:39.095156Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 7 user: 'root'
2017-06-06T23:48:39.095191Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 15 user: 'root'
2017-06-06T23:48:39.095226Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 10 user: 'root'
2017-06-06T23:48:39.095260Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 9 user: 'root'
2017-06-06T23:48:39.095295Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 16 user: 'root'
2017-06-06T23:48:39.095341Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 237 user: 'root'
2017-06-06T23:48:39.096103Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 11 user: 'root'
2017-06-06T23:48:39.096470Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 252 user: 'root'
2017-06-06T23:48:39.096757Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 12 user: 'root'
2017-06-06T23:48:39.096883Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 224 user: 'root'
2017-06-06T23:48:39.096928Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 13 user: 'root'
2017-06-06T23:48:39.097892Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2017-06-06T23:48:39.103817Z 0 [Note] Binlog end
2017-06-06T23:48:39.116325Z 0 [Note] Shutting down plugin 'auth_socket'
2017-06-06T23:48:39.116369Z 0 [Note] Shutting down plugin 'ngram'
2017-06-06T23:48:39.116375Z 0 [Note] Shutting down plugin 'ARCHIVE'
2017-06-06T23:48:39.116383Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2017-06-06T23:48:39.116394Z 0 [Note] Shutting down plugin 'partition'
2017-06-06T23:48:39.116400Z 0 [Note] Shutting down plugin 'MyISAM'
2017-06-06T23:48:39.116412Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2017-06-06T23:48:39.116420Z 0 [Note] Shutting down plugin 'CSV'
2017-06-06T23:48:39.116427Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2017-06-06T23:48:39.116457Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2017-06-06T23:48:39.116465Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2017-06-06T23:48:39.116470Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2017-06-06T23:48:39.116475Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2017-06-06T23:48:39.116480Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2017-06-06T23:48:39.116611Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2017-06-06T23:48:39.116628Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2017-06-06T23:48:39.116634Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2017-06-06T23:48:39.116639Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2017-06-06T23:48:39.116644Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2017-06-06T23:48:39.116649Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2017-06-06T23:48:39.116654Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2017-06-06T23:48:39.116659Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2017-06-06T23:48:39.116664Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2017-06-06T23:48:39.116669Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2017-06-06T23:48:39.116674Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2017-06-06T23:48:39.116679Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2017-06-06T23:48:39.116683Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2017-06-06T23:48:39.116709Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2017-06-06T23:48:39.116715Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2017-06-06T23:48:39.116720Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2017-06-06T23:48:39.116725Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2017-06-06T23:48:39.116729Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2017-06-06T23:48:39.116734Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2017-06-06T23:48:39.116739Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2017-06-06T23:48:39.116743Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2017-06-06T23:48:39.116748Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2017-06-06T23:48:39.116753Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2017-06-06T23:48:39.116758Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2017-06-06T23:48:39.116762Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2017-06-06T23:48:39.116767Z 0 [Note] Shutting down plugin 'InnoDB'
2017-06-06T23:48:39.121885Z 0 [Note] InnoDB: FTS optimize thread exiting.
2017-06-06T23:48:39.122281Z 0 [Note] InnoDB: Starting shutdown...
2017-06-06T23:48:39.224242Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2017-06-06T23:48:39.224601Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 170607 2:48:39
2017-06-06T23:48:40.562889Z 0 [Note] InnoDB: Shutdown completed; log sequence number 8015926855
2017-06-06T23:48:40.563991Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-06-06T23:48:40.564012Z 0 [Note] Shutting down plugin 'MEMORY'
2017-06-06T23:48:40.564023Z 0 [Note] Shutting down plugin 'sha256_password'
2017-06-06T23:48:40.564028Z 0 [Note] Shutting down plugin 'mysql_native_password'
2017-06-06T23:48:40.565683Z 0 [Note] Shutting down plugin 'binlog'
2017-06-06T23:48:40.569582Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
该服务器没有公共 IP,只能通过 VPN 访问。此外,bash 历史记录不显示除我运行过的命令之外的任何命令。
然而,它时不时就会关闭,直到手动重新启动才会重新打开。我完全没有主意了,正准备简单地重写代码以使用不同的数据库(例如 MongoDB)。要么这样做,要么创建一个每service mysql start
5 分钟运行一次的 cron 作业。我还有什么其他选择?
答案1
我会关注:
- 尝试提高日志记录级别,看看是否可能没有捕获到应该捕获的内容
- 增加系统整体的日志记录和诊断。我发现,即使事情听起来毫无关联,上游的某些东西也可能导致下游的另一个程序以意想不到的方式失败。例如,我曾经使用过一个电话系统,当数据库的一部分似乎遇到 IO 吞吐量问题时,该系统就会崩溃。基本上,我提倡运行诊断程序以查看是否有发生模式。当然,这意味着全面(电源、IO、网络、硬件、内存、CPU 等)。当然,这也意味着你以某种方式限制监控,使其不会干扰系统上正在发生的事情
- 无论如何,我都会扫描恶意软件和安全事件,以防万一。我见过一些非常奇怪的恶意软件入口点
- 我也会尝试进一步隔离问题。你确定那台机器本身没有问题吗(软件或硬件)?考虑到那里有免费的托管选项和虚拟化技术,你在可能重写程序之前不尝试这个似乎很奇怪。这两个选项也都快速且易于测试
- 我可能会尝试创建一个简单的脚本来记录关闭事件的发生时间。一些简单的脚本,比如一个 while 循环,它会定期休眠,但也会检查 MySQL 何时/是否关闭。显然,这只是几行代码,但可以让您检查事情是否在固定的时间间隔内发生
- 我会尝试重新配置 MySQL 本身,看看是否存在你没有注意到的限制
- 我还会尝试任何兼容版本的替代数据库(但兼容)选项,以防它是程序本身固有的问题(根据您所写的内容,我没有足够的信息来继续进行下去)。这也节省了您的时间,因为您不必重写程序来查看它是否是您所做工作的真正问题或仅仅是配置问题
- 我还想关注的另一件事是冲突的程序。例如,如果你在安全或软件领域工作,你会发现你会时不时地遇到一些奇怪和意想不到的问题。不管你怎么想,这仍然是有可能的……
- 我认识一些人,他们曾经做过你所提倡的事情。即定期重新启动程序以克服软件本身的困难