how to view how much innodb buffer pool size using

HI
We have setup

Innodb_buffer_pool size = 12 GB

Now how will we know, how much ram its actually using ?

its windows server.

what variables shall i check from mysql to see how much ram its its utilizing .

i have read few article

http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

but can you please specifiq about that varibles or command to check ram utilization.

thanks
LVL 29
fosiul01Asked:
Who is Participating?
 
wesly_chenConnect With a Mentor Commented:
Login to MySQL, type
SHOW INNODB STATUS;

in the "BUFFER POOL AND MEMORY" section, you will see something like bellow:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 23866120794; in additional pool allocated 12290560
Dictionary memory allocated 299024
Buffer pool size   1310720
Free buffers       902021
Database pages     358799
Modified db pages  76970
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 177208437, created 20143488, written 509266525
0.00 reads/s, 0.28 creates/s, 3.10 writes/s
Buffer pool hit rate 1000 / 1000
0
 
Kevin CrossChief Technology OfficerCommented:
They deprecated that status in MySQL 5.5 by the way.
In MySQL 5.5, you should use: SHOW ENGINE INNODB STATUS;

An alternative is:
-- http://dev.mysql.com/doc/refman/5.1/en/show-status.html
-- http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total';

The result will be in pages, which by default is 16KB, but you can check:
SHOW GLOBAL STATUS LIKE 'Innodb_page_size';
0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
The output is still the same: note, though, buffer pool size here is the same value you get from 'Innodb_buffer_pool_pages_total', which is important since that is in pages not bytes. You will still need to multiply that by 16KB to get size. So in my case, I am using 107MB out of 109-110MB total allocated.

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 114835456; in additional pool allocated 0
Dictionary memory allocated 31138
Buffer pool size   6848
Free buffers       6676
Database pages     172
...
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
fosiul01Author Commented:
hi

mine is like this

BUFFER POOL AND MEMORY
----------------------
Total memory allocated 10752867514; in additional pool allocated 25947648
Buffer pool size   589824
Free buffers       1
Database pages     567582
Modified db pages  5


so how much Buffer Pool Size ?? 500K ??

0
 
fosiul01Author Commented:
also
from console

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total';
+--------------------------------+--------+
| Variable_name                  | Value  |
+--------------------------------+--------+
| Innodb_buffer_pool_pages_total | 589824 |
+--------------------------------+--------+
1 row in set (0.17 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.17 sec)


so how much ram is utilizing ..   ??
0
 
wesly_chenCommented:
Innodb_buffer_pool_pages_total  * Innodb_page_size = 589824 * 16384 = 9663676416 Bytes
= 9 GB
0
 
fosiul01Author Commented:
hi thanks

is there any documentation of this calculation ?

total innodb_buffer_pol_size uses = Innodb_buffer_pool_pages_total  * Innodb_page_size

can you please give me the link
thanks
0
 
wesly_chenCommented:
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html#statvar_Innodb_buffer_pool_pages_total
Innodb_buffer_pool_pages_total : The total size of buffer pool, in pages

http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html#statvar_Innodb_page_size
Innodb_page_size : The compiled-in InnoDB page size (default 16KB)
0
 
Kevin CrossChief Technology OfficerCommented:
I provided those earlier, but thanks for clarifying - I had to go offline for awhile.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.