CODEWITHSUNDEEP
mysqlbufferinnodb
opensourcegeek
opensourcegeek

Reputation: 5972

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

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?

Upvotes: 4

Views: 32309

Answers (2)

Mahesh Patil
Mahesh Patil

Reputation: 1551

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/

Upvotes: 1

RandomSeed
RandomSeed

Reputation: 29809

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.

Upvotes: 3

Related Questions