5

I am just using a very few InnoDB tables (e.g. less than 1MB), but during MySQL startup, it said 

InnoDB: Initializing buffer pool, size = 128.0M

Does it mean even I am using in such a small size, the server still use 128M RAM?

Improve this question

5 Answers 5

Reset to default
10

According to the MySQL Documentation, the InnoDB Buffer Pool is set to 128MB by default in MySQL 5.5.

You can show how much of the InnoDB Buffer Pool is in use and reserved as follows:

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
;

I have it set to 256M in MySQL 5.5.12 for Windows. Here is my output:

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>

You can set this value when MySQL starts up. For example:

To set it to use 1G of RAM

[mysqld]
innodb_buffer_pool_size=1G

To set it to use 256MB of RAM

[mysqld]
innodb_buffer_pool_size=256M

After changing it in my.cnf, you must restart mysql for it to take effect.

Improve this answer
1
  • Thanks for this query, lets say you have 1GB memory and you allocated 500MB to Buffer pool, You can see the only 200MB used by buffer pool. So how about the remaining 300MB? will this memory stay with OS.? Like whenever mysql needs more memory then it'll automatically pick it from the OS till 500?, to simply the question, OS will reserve the 500MB for Innodb whether it is required or not, it'll never give this memory to other processes.Is this correct?
    – TheDataGuy
    Sep 12, 2018 at 19:42
4

The buffer pool is used by MySQL for caching InnoDB data and indexes in memory. It is not the total memory used but your MySQL DB

If your InnoDB tables are that small 128MB is more than enough and effectively all InnoDB tables will be treated as in-memory tables. The 128Mb is a limit of how large the buffer can be and it is only filled when needed.

You can read about it here

Improve this answer
0

MySQL allocates the memory for the bufferpool at startup, but only actually occupies the memory as needed. This means that the MySQL will not start if it cannot grab ahold of 128M of RAM, but your server will not initially use that 128M of memory. Until the bufferpool is fully utilized, the RAM will be available to other applications.

Improve this answer
0

128 M was historically great buffer pool size. but it is now really history :) Consider to give a much more pool size. Many patterns says that you must or may give innodb buffer pool 70-80% of your server's total memory. Optimizing InnoDB buffer pool is common practice in MySQL Optimization stage. But be aware to mis-estimate your RAM. you must keep enough RAM for another tasks. Apache for eg. First of all generate an optimal my.cnf (config file) for you server. You can do this using simple but great Percona online Tool. it will very useful start point for you: percona online tools

Improve this answer
-1

Yes - at its maximum! The point of buffer pool - is to use memory cache, not any other 8-)

Improve this answer
1
  • Dear anonymous downvoters - please, write the reason of downvote, be fair
    – Oleg Dok
    Jul 11, 2013 at 4:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.