Mysql

InnoDB(不是真的)記憶體不足?

  • July 17, 2016

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_ 配置選項。

引用自:https://serverfault.com/questions/789244