Innodb buffer pool size variable and buffer pool size in innodb status mismatch

Question

I tried increasing the innodb buffer pool size to 8GB recently but in my innodb status the pool size looks like the previous value it was configured to (500MB in my case).

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 9576946034; in additional pool allocated 19271936
**Buffer pool size   524288**
Free buffers       2
Database pages     508873
Modified db pages  5
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 443350, created 76998, written 683297
0.00 reads/s, 0.00 creates/s, 0.37 writes/s
Buffer pool hit rate 1000 / 1000

But this is what innodb status shows,

mysql> show variables like 'innodb_%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_additional_mem_pool_size | 52428800               | 
| innodb_autoextend_increment     | 8                      | 
| innodb_buffer_pool_awe_mem_mb   | 0                      | 
| innodb_buffer_pool_size         | 8589934592             |

Could anybody please let me know what causes this difference?

RandomSeed · Accepted Answer · 2013-10-25 12:30:09Z

3

You are looking at the wrong figures.

Total memory allocated 9576946034

This is consistent with your innodb_buffer_pool_size setting:

InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size.

Buffer pool size is the number of pages.

answered Oct 25, 2013 at 12:30

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mahesh Patil · Accepted Answer · 2013-10-25 12:45:24Z

You should compare with Total Memory allocated value in Show Engine InnoDB status; output

The usage of the InnoDB Buffer Pool can be measured with the SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_%' command. The sum of data, misc and free pages is equivalent to total pages. And the number of total pages multiplied by Innodb_page_size corresponds to your innodb_buffer_pool_size.

(Innodb_buffer_pool_pages_data+ Innodb_buffer_pool_pages_misc + Innodb_buffer_pool_pages_free) = Innodb_buffer_pool_pages_total

Innodb_buffer_pool_pages_total * Innodb_page_size = innodb_buffer_pool_size

From Show Engine InnoDB Status output:

You can see total memory allocated by Innodb (sometimes it is higher than you anticipated), amount of memory allocated in additional memory pool (so you can check if it is sized right), total number of pages in buffer pool, number of pages free, pages allocated by database pages and dirty pages. From these values you can learn if your buffer pool is sized well â if you have constantly a lot of pages free, it probably means your active database size is smaller than allocated buffer pool size so you can tune it down. Even if free pages is zero as in this case database pages will not be equal to total size of buffer pool, because buffer pool also stores lock information, adaptive hash indexes and some other system structures.

http://www.fromdual.com/innodb-variables-and-status-explained

http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/

Collectives™ on Stack Overflow

Innodb buffer pool size variable and buffer pool size in innodb status mismatch

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related