Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from serverfault.com Jan 11 '12 at 12:29

This question came from our site for professional system and network administrators.

5 Answers

up vote 8 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
    
Dear anonymous downvoters - please, write the reason of downvote, be fair –  Oleg Dok Jul 11 '13 at 4:11
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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