[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to view how much innodb buffer pool size using

Posted on 2011-10-07
9
Medium Priority
?
567 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:fosiul01
  • 3
  • 3
  • 3
9 Comments
 
LVL 38

Accepted Solution

by:
wesly_chen earned 1600 total points
ID: 36933117
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36933137
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
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 400 total points
ID: 36933167
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Author Comment

by:fosiul01
ID: 36934128
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
 
LVL 29

Author Comment

by:fosiul01
ID: 36934149
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
 
LVL 38

Expert Comment

by:wesly_chen
ID: 36934200
Innodb_buffer_pool_pages_total  * Innodb_page_size = 589824 * 16384 = 9663676416 Bytes
= 9 GB
0
 
LVL 29

Author Comment

by:fosiul01
ID: 36934257
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
 
LVL 38

Expert Comment

by:wesly_chen
ID: 36934326
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36934480
I provided those earlier, but thanks for clarifying - I had to go offline for awhile.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written articles previously comparing SARDU and YUMI.  I also included a couple of lines about Easy2boot (easy2boot.com).  I have now been using, and enjoying easy2boot as my sole multiboot utility for some years and realize that it deserves …
This article will show you step-by-step instructions to build your own NTP CentOS server.  The network diagram shows the best practice to setup the NTP server farm for redundancy.  This article also serves as your NTP server documentation.
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Suggested Courses
Course of the Month20 days, 5 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question