MySQL 不断在我的服务器上崩溃。我正在使用 VestaCP 运行几十个站点,并且几个月来没有遇到 MySQL 问题。
我在具有 12GB 内存的 Linode VPS 上运行,通常其中 9GB 是免费的。
MySQL 错误日志如下所示:
160711 22:32:36 [Note] Plugin 'FEDERATED' is disabled.
160711 22:32:36 InnoDB: The InnoDB memory heap is disabled
160711 22:32:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160711 22:32:36 InnoDB: Compressed tables use zlib 1.2.8
160711 22:32:36 InnoDB: Using Linux native AIO
160711 22:32:39 InnoDB: Initializing buffer pool, size = 1.0G
InnoDB: mmap(1098907648 bytes) failed; errno 12
160711 22:32:39 InnoDB: Completed initialization of buffer pool
160711 22:32:39 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160711 22:32:39 [ERROR] Plugin 'InnoDB' init function returned error.
160711 22:32:39 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160711 22:32:39 [ERROR] Unknown/unsupported storage engine: InnoDB
160711 22:32:39 [ERROR] Aborting
160711 22:32:39 [Note] /usr/sbin/mysqld: Shutdown complete
160711 22:33:43 [Note] Plugin 'FEDERATED' is disabled.
160711 22:33:43 InnoDB: The InnoDB memory heap is disabled
160711 22:33:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160711 22:33:43 InnoDB: Compressed tables use zlib 1.2.8
160711 22:33:43 InnoDB: Using Linux native AIO
160711 22:33:43 InnoDB: Initializing buffer pool, size = 1.0G
160711 22:33:43 InnoDB: Completed initialization of buffer pool
160711 22:33:43 InnoDB: highest supported file format is Barracuda.
这组特定的错误似乎是在发出service mysql restart
命令后发生的。
我花了很多时间查看这个特定的错误:Fatal error: cannot allocate memory for the buffer pool
. 我发现的一切都表明缺少 ram 是问题所在,大多数人都使用极低内存系统(通常为 512MB 左右)。显然,在 12GB 的内存和 9GB 的空闲内存下,内存不足应该不会有问题,所以我不确定在这种情况下该怎么做。
运行free -h
产生以下结果:
free -h 的命令行结果 此外,日志文件中有大量条目,如下所示:
160711 22:31:58 [Warning] Aborted connection 5131 to db: 'xxxx' user: 'xxxx' host: 'localhost' (Got timeout reading communication packets)
160711 22:31:58 [Warning] Aborted connection 5127 to db: 'xxxx' user: 'xxxx' host: 'localhost' (Got timeout reading communication packets)
160711 22:32:00 [Warning] Aborted connection 5136 to db: 'xxxx' user: 'xxxx' host: 'localhost' (Got timeout reading communication packets)
160711 22:32:01 [Warning] Aborted connection 5134 to db: 'xxxx' user: 'xxxx' host: 'localhost' (Got timeout reading communication packets)
160711 22:32:01 [Warning] Aborted connection 5133 to db: 'xxxx' user: 'xxxx' host: 'localhost' (Got timeout reading communication packets)
160711 22:32:01 [Warning] Aborted connection 5112 to db: 'xxxx' user: 'xxxx' host: 'localhost' (Got timeout reading communication packets)
160711 22:32:01 [Warning] Aborted connection 5135 to db: 'xxxx' user: 'xxxx' host: 'localhost' (Got timeout reading communication packets)
160711 22:32:04 [Warning] Aborted connection 5137 to db: 'xxxx' user: 'xxxx' host: 'localhost' (Got timeout reading communication packets)
160711 22:32:05 [Warning] Aborted connection 5140 to db: 'xxxx' user: 'xxxx' host: 'localhost' (Got timeout reading communication packets)
最后,我确实尝试将 InnoDB 缓冲池的大小从 512MB 增加到 1GB,但这似乎没有帮助。我是否只是增加缓冲池大小直到它停止崩溃?
编辑:感谢到目前为止的回复!这是我当前的 my.cnf:
[client]
port=3306
socket=/var/run/mysqld/mysqld.sock
[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock
[mysqld]
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
port=3306
basedir=/usr
datadir=/var/lib/mysql
log-warning=2
tmpdir=/tmp
lc-messages-dir=/usr/share/mysql
log_error=/var/log/mysql/error.log
#skip-networking
symbolic-links=0
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 256M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#innodb_use_native_aio = 0
innodb_file_per_table
max_connections=1500
max_user_connections=200
wait_timeout=10
interactive_timeout=50
long_query_time=5
innodb_log_buffer_size = 32M
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 768M
!includedir /etc/mysql/conf.d/
你的 innodb 缓冲池太小了。您需要增加它和其他 4 个 innodb 参数一样。基本上,您在 mysql 配置中告诉 innodb 引擎它获得了多少内存。据推测,这是为了防止机器内存不足时发生可怕的事情。
如果你有很多 innodb 表,让缓冲池非常大,理想情况下它可以是你所有常用的 innodb 数据的大小。由于我没有为上一份工作制作的 mysql 配置,因此我忘记了所有 innodb_ 配置选项。
InnoDB/MySQL 由于某种原因无法分配 1G。同时,free -h 显示一些空闲内存。这意味着 mysql 内部或 mysql 外部分配内存的东西。上面的my.cnf没有显示任何额外的内存分配,但是你有这个包括:“!includedir /etc/mysql/conf.d/” 请检查这个目录的内容,看看是否有其他文件(除了debian .cnf 这只是密码),它有一些配置选项。您也可以尝试启动 MySQL 并观察“top”并检查虚拟内存增长。
另一种可能性是: