Mysql
InnoDB(不是真的)記憶體不足?
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_ 配置選項。