我只是使用了很少的InnoDB表(例如小于1MB),但在MySQL启动期间,它说
InnoDB: Initializing buffer pool, size = 128.0M
这是否意味着即使我在如此小的尺寸使用,服务器仍然使用128M RAM?
解决方法:
您可以显示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才能生效.
标签:linux,mysql,ubuntu,innodb
来源: https://codeday.me/bug/20190805/1593334.html