MySQL 說“InnoDB: Initializing buffer pool, size = 128.0M”，是不是表示它正在使用 128MB RAM？

我只使用了很少的 InnoDB 表（例如小於 1MB），但是在 MySQL 啟動期間，它說

InnoDB: Initializing buffer pool, size = 128.0M

這是否意味著即使我使用這麼小的尺寸，伺服器仍然使用 128M 記憶體？

根據 MySQL 文件，InnoDB 緩衝池在 MySQL 5.5 中預設設置為 128MB。

您可以顯示有多少 InnoDB 緩衝池正在使用和保留，如下所示：

SELECT
   BufferPoolUsed BytesUsed,
   (BufferPoolUsed / power(1024,1)) UsedKB,
   (BufferPoolUsed / power(1024,2)) UsedMB,
   (BufferPoolUsed / power(1024,3)) UsedGB,
   BufferPoolReserved BytesReserved,
   (BufferPoolReserved / power(1024,1)) ReservedKB,
   (BufferPoolReserved / power(1024,2)) ReservedMB,
   (BufferPoolReserved / power(1024,3)) ReservedGB
FROM
   (
       SELECT
           (A.num * B.num) BufferPoolUsed,
           (A.num * C.num) BufferPoolReserved
       FROM
           (SELECT variable_value num FROM information_schema.global_status
           WHERE variable_name='Innodb_page_size') A,
           (SELECT variable_value num FROM information_schema.global_status
           WHERE variable_name='Innodb_buffer_pool_pages_data') B,
           (SELECT variable_value num FROM information_schema.global_status
           WHERE variable_name='Innodb_buffer_pool_pages_total') C
   ) AA
;

我在 MySQL 5.5.12 for Windows 中將其設置為 256M。這是我的輸出：

mysql> SELECT
   ->     BufferPoolUsed BytesUsed,
   ->     (BufferPoolUsed / power(1024,1)) UsedKB,
   ->     (BufferPoolUsed / power(1024,2)) UsedMB,
   ->     (BufferPoolUsed / power(1024,3)) UsedGB,
   ->     BufferPoolReserved BytesReserved,
   ->     (BufferPoolReserved / power(1024,1)) ReservedKB,
   ->     (BufferPoolReserved / power(1024,2)) ReservedMB,
   ->     (BufferPoolReserved / power(1024,3)) ReservedGB
   -> FROM
   ->     (
   ->         SELECT
   ->             (A.num * B.num) BufferPoolUsed,
   ->             (A.num * C.num) BufferPoolReserved
   ->         FROM
   ->             (SELECT variable_value num FROM information_schema.global_status
   ->             WHERE variable_name='Innodb_page_size') A,
   ->             (SELECT variable_value num FROM information_schema.global_status
   ->             WHERE variable_name='Innodb_buffer_pool_pages_data') B,
   ->             (SELECT variable_value num FROM information_schema.global_status
   ->             WHERE variable_name='Innodb_buffer_pool_pages_total') C
   ->     ) AA
   -> ;
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
| BytesUsed | UsedKB | UsedMB   | UsedGB             | BytesReserved | ReservedKB | ReservedMB | ReservedGB |
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
|   6864896 |   6704 | 6.546875 | 0.0063934326171875 |     268435456 |     262144 |        256 |       0.25 |
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
1 row in set (0.00 sec)

mysql>

您可以在 MySQL 啟動時設置此值。例如：

將其設置為使用 1G 的 RAM

[mysqld]
innodb_buffer_pool_size=1G

將其設置為使用 256MB 的 RAM

[mysqld]
innodb_buffer_pool_size=256M

在my.cnf中修改後，必須重啟mysql才能生效。

引用自：https://dba.stackexchange.com/questions/10496