☰

MySQL 在 Grafana 仪表板查询后崩溃

tag-icon • tag-icon mysql centos7 grafana
MySQL 在 Grafana 仪表板查询后崩溃

我有一个装有 Centos 7 的小型 VPS。它运行使用 MySQL 作为数据库的 django，并且还运行从 MySQL 读取数据的 Grafana。

当我打开 Grafana 仪表板时，MySQL 服务器立即崩溃。有时它会重新启动，有时它会卡在“服务器正在启动”状态。

我尝试检查日志，但没有发现任何有用的东西，而且我对 MySQL 的经验有限，所以也许还有其他地方需要检查而我不知道。

日志：

查询日志：

2019-05-30T19:22:35.356783Z     7 Connect   
2019-05-30T19:22:35.364031Z     7 Query SELECT @@skip_networking, @@skip_name_resolve, @@have_ssl='YES', @@ssl_key, @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_crl, @@ssl_crlpath, @@tls_version
2019-05-30T19:22:35.377731Z     7 Quit  
[root@tamales ~]# myquery
2019-05-30T19:27:03.729553Z    25 Quit  
2019-05-30T19:27:03.942917Z    24 Query SELECT
  created AS "time",
  temperature
FROM weather_weather
ORDER BY created
2019-05-30T19:27:03.953663Z    23 Query SELECT
  created AS "time",
  temperature
FROM weather_weather
ORDER BY created
2019-05-30T19:27:04.005351Z    29 Connect   grafana@localhost on myproject using TCP/IP
2019-05-30T19:27:04.005499Z    28 Connect   grafana@localhost on myproject using TCP/IP
2019-05-30T19:27:04.005592Z    27 Connect   grafana@localhost on myproject using TCP/IP
2019-05-30T19:27:04.005775Z    26 Connect   grafana@localhost on myproject using TCP/IP
2019-05-30T19:27:04.083534Z    26 Query SELECT
  created AS "time",
  temperature
FROM weather_weather
ORDER BY created
2019-05-30T19:27:04.084228Z    27 Query SELECT
  created AS "time",
  humidity
FROM weather_weather
ORDER BY created
2019-05-30T19:27:04.086203Z    28 Query SELECT
  created AS "time",
  humidity
FROM weather_weather
ORDER BY created
2019-05-30T19:27:04.087198Z    29 Query SELECT
  created AS "time",
  humidity
FROM weather_weather
ORDER BY created
2019-05-30T19:27:06.411145Z    29 Quit  
2019-05-30T19:27:06.451327Z    24 Quit  
2019-05-30T19:27:06.493827Z    23 Quit  
2019-05-30T19:27:06.496899Z    28 Quit  
2019-05-30T19:27:06.527625Z    26 Quit  
2019-05-30T19:27:06.538793Z    27 Quit  
/usr/sbin/mysqld, Version: 8.0.14 (MySQL Community Server - GPL). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 8.0.14 (MySQL Community Server - GPL). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 8.0.14 (MySQL Community Server - GPL). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

错误日志：

2019-05-30T19:22:25.589770Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.14)  MySQL Community Server - GPL.
2019-05-30T19:22:32.288142Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.14) starting as process 798
2019-05-30T19:22:35.059292Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-05-30T19:22:35.110710Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.14'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2019-05-30T19:22:35.377699Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

崩溃后的 Systemctl 状态：

* mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: activating (start) since Thu 2019-05-30 15:31:00 EDT; 3s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3965 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3988 (mysqld)
   Status: "SERVER_BOOTING"
   CGroup: /system.slice/mysqld.service
           `-3988 /usr/sbin/mysqld

Systemd 日志：

May 30 15:22:23 tamales systemd[1]: Stopping MySQL Server...
May 30 15:22:25 tamales systemd[1]: Stopped MySQL Server.
May 30 15:31:00 tamales systemd[1]: Stopped MySQL Server.
May 30 15:31:00 tamales systemd[1]: Starting MySQL Server...
May 30 15:31:05 tamales systemd[1]: mysqld.service: main process exited, code=killed, status=9/KILL
May 30 15:31:05 tamales systemd[1]: Failed to start MySQL Server.
May 30 15:31:05 tamales systemd[1]: Unit mysqld.service entered failed state.
May 30 15:31:05 tamales systemd[1]: mysqld.service failed.
May 30 15:31:05 tamales systemd[1]: mysqld.service holdoff time over, scheduling restart.
May 30 15:31:05 tamales systemd[1]: Stopped MySQL Server.
May 30 15:31:05 tamales systemd[1]: Starting MySQL Server...
May 30 15:31:10 tamales systemd[1]: mysqld.service: main process exited, code=killed, status=9/KILL
May 30 15:31:10 tamales systemd[1]: Failed to start MySQL Server.
May 30 15:31:10 tamales systemd[1]: Unit mysqld.service entered failed state.
May 30 15:31:10 tamales systemd[1]: mysqld.service failed.
May 30 15:31:10 tamales systemd[1]: mysqld.service holdoff time over, scheduling restart.
May 30 15:31:10 tamales systemd[1]: Stopped MySQL Server.
May 30 15:31:10 tamales systemd[1]: Starting MySQL Server...

答案1

256MB 的 RAM 几乎不足以处理 MySQL。如果在同一台机器上运行其他程序，就会显得拥挤。（我不知道 Grafana 的内存占用情况。）

为 buffer_pool 分配的 128MB 使情况雪上加霜。MySQL 需要 RAM 来做其他事情，因此 MySQL 实际上使用了大部分 RAM。

你可以尝试这些：

innodb_buffer_pool_size = 40M
max_connections = 4

以此来减少 RAM 使用量。这可能有效，也可能无效。如果 buffer_pool 设置得太小，可能会出现其他问题并造成麻烦。

相关内容