联系我们:416326801#qq.com(#改成@)

Latest from the blog

Fatal error: cannot allocate memory for the buffer pool

标签: MySQL

今天线上数据库异常挂了两次,查看日志发现致命错误Fatal error: cannot allocate memory for the buffer pool

修改/etc/my.cnf,适当降低innodb_buffer_pool_size参数值,从256M调整为128M

170602 17:51:27 mysqld_safe Number of processes running now: 0
170602 17:51:27 mysqld_safe mysqld restarted
170602 17:51:27 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.48-log) starting as process 3476 ...
170602 17:51:27 InnoDB: The InnoDB memory heap is disabled
170602 17:51:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170602 17:51:27 InnoDB: Compressed tables use zlib 1.2.7
170602 17:51:27 InnoDB: Initializing buffer pool, size = 256.0M
170602 17:51:27 InnoDB: Completed initialization of buffer pool
170602 17:51:27 InnoDB: Fatal error: cannot allocate memory for the buffer pool
170602 17:51:27 [ERROR] Plugin 'InnoDB' init function returned error.
170602 17:51:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170602 17:51:27 [ERROR] Unknown/unsupported storage engine: InnoDB
170602 17:51:27 [ERROR] Aborting
170602 17:51:27 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
170602 17:51:27 mysqld_safe mysqld from pid file /data/mysql/yuxinle.pid ended


贴上几个关键指标,供以后出现同类问题时参考

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

mysql> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 574   |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 8191  |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 3000  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>  show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 4     |
+----------------------+-------+
1 row in set (0.00 sec)

mysqld_safe Number of processes running now

标签: MySQL

今天腾讯云主机触发CPU>80%报警,当即登录运行top发现mysqld占用cpu较高

查看mysql.err文件提示mysqld_safe Number of processes running now,

几乎同一时间apache日志出现报错PHP Warning:  PDO::query(): MySQL server has gone away和 PHP Warning:  PDO::query(): Error reading result set's header

考虑mysql分配内存问题,修改/etc/my.cnf的两个参数:key_buffer_size、 innodb_buffer_pool_size 

  • 如果仅使用MyISAM存储引擎,设置 key_buffer_size 为可用内存的20%,(再加上设置 innodb_buffer_pool_size = 0 )
  • 如果仅使用InnoDB存储引擎,设置 innodb_buffer_pool_size 为可用内存的 70%, (设置 key_buffer_size = 10M,很小但不是0.)

sqlerr.png

MySQL server has gone away原因,wait_timeout值太小:

wait_timeout.png

show global variables like '%timeout';

修改/etc/my.cnf,调整wait_timeout值,一般设置为10-30即可,需要重启mysql

直接在命令行设置,不重启mysql

set global wait_timeout=10;

set.png

1 2 3 4 ... Next »