乞丐版阿里雲，mysql服務無法啟動問題
轉載自: http://www.deman.club/view.html#4653656886519368
今日，自己搭建一套個人博客系統，使用的是乞丐版阿里雲伺服器 內存 1G,存儲40G。在某個月黑風高的夜晚，mysql無法啟動，報錯信息如下:
InnoDB: mmap(549453824 bytes) failed; errno 12
2018-01-16 23:13:59 10149 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2018-01-16 23:13:59 10149 [ERROR] Plugin 'InnoDB' init function returned error.
Advertisements
2018-01-16 23:13:59 10149 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-01-16 23:13:59 10149 [ERROR] Unknown/unsupported storage engine: InnoDB
2018-01-16 23:13:59 10149 [ERROR] Aborting
從報錯信息來看,無法為 InnoDB 引擎緩衝池申請足夠的內存，一番嘗試，mysql有這樣一個配置項 ： innodb_buffer_pool_size
那麼這個配置項應該如何配置？
首先,查看資料庫運行時的各項數據指標。
Advertisements
1）當前緩衝池配置大小
show global variables like 'innodb_buffer_pool_size';
本地數據如下：
mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
2) 查看緩衝池數據的大小
show global status like 'Innodb_buffer_pool_pages_total';
本地數據如下:
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)
3) 查看innodb頁大小
mysql> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)
以上三個指標，含義如下：
Innodb_buffer_pool_pages_data ： The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and
clean pages.
Innodb_buffer_pool_pages_total： The total size of the InnoDB buffer pool, in pages.
Innodb_page_size ： InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be
easily converted to bytes
計算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
當結果 > 95% 則增加 innodb_buffer_pool_size， 建議使用物理內存的 75%
當結果 < 95% 則減少 innodb_buffer_pool_size，
建議設置大小為： Innodb_buffer_pool_pages_data* Innodb_page_size * 1.05 / (1024*1024*1024)
命令如：SET GLOBAL innodb_buffer_pool_size= 32423423：單位kb