You need to be aware of what the InnoDB Buffer Pool breakdown is from the status variables
Please run the following query
select var,concat(numunit,' ',unit) size from
(
select var,format(num/power(1024,ex),2) numunit,SUBSTR(units,ex*2+1,2) unit
from
(
select var,num,FLOOR(LOG(IF(num=0,1,num))/LOG(1024)) ex
from
(
select variable_name var,variable_value*pagesize num
from information_schema.global_status AAA,
(
select variable_value pagesize
from information_schema.global_status
where variable_name='innodb_page_size'
) BBB
where AAA.variable_name like 'innodb_buffer_pool_pages%'
) AA
) A,(select 'B KBMBGBTB' units) B
) M;
This will gives a status of the Buffer Pool's current usage and how much data has been flushed from it.
Example
mysql> select var,concat(numunit,' ',unit) size from
-> (
-> select var,format(num/power(1024,ex),2) numunit,SUBSTR(units,ex*2+1,2) unit
-> from
-> (
-> select var,num,FLOOR(LOG(IF(num=0,1,num))/LOG(1024)) ex
-> from
-> (
-> select variable_name var,variable_value*pagesize num
-> from information_schema.global_status AAA,
-> (
-> select variable_value pagesize
-> from information_schema.global_status
-> where variable_name='innodb_page_size'
-> ) BBB
-> where AAA.variable_name like 'innodb_buffer_pool_pages%'
-> ) AA
-> ) A,(select 'B KBMBGBTB' units) B
-> ) M;
+----------------------------------+-----------+
| var | size |
+----------------------------------+-----------+
| INNODB_BUFFER_POOL_PAGES_DATA | 9.17 GB |
| INNODB_BUFFER_POOL_PAGES_DIRTY | 0.00 B |
| INNODB_BUFFER_POOL_PAGES_FLUSHED | 179.59 GB |
| INNODB_BUFFER_POOL_PAGES_FREE | 32.00 KB |
| INNODB_BUFFER_POOL_PAGES_MISC | 177.05 MB |
| INNODB_BUFFER_POOL_PAGES_TOTAL | 9.34 GB |
+----------------------------------+-----------+
6 rows in set (0.00 sec)
mysql>
I have the following
- 9.34G Buffer Pool (9566 MB)
- 9.17G of Data
- 177 .05 MB for the adaptive hash index and administrative stuff
- 32 KB free
- No dirty pages
Why is INNODB_BUFFER_POOL_PAGES_FLUSHED
so sky high at 179.59 GB
?
That is the amount of flushing the buffer pool has had to commit to the InnoDB Architecture.
Here is a diagram
In my case,
mysql> SELECT variable_value INTO @Uptime FROM information_schema.global_status WHERE variable_name='Uptime'; SELECT NOW() "Right Now",MySQLStartupTime "MySQL Started",TimeDisplay "MySQL Has Been Running For" FROM (SELECT NOW() - INTERVAL @Uptime SECOND MySQLStartupTime) M,(SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'',IF(dy=1,'1 day ',CONCAT(dy,' days '))),IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours '))),IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),' ',' ')) TimeDisplay FROM (SELECT dy,hr,mn,MOD(sec_aaaa,60) sc FROM (SELECT dy,hr,FLOOR((sec_aaa - dy*86400 - hr*3600)/60) mn,sec_aaa sec_aaaa FROM (SELECT dy,FLOOR((sec_aa - (dy*86400))/3600) hr,sec_aa sec_aaa FROM (SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa FROM (SELECT @Uptime sec_a) A) AA) AAA) AAAA) B) N;
Query OK, 1 row affected (0.02 sec)
+---------------------+---------------------+-------------------------------+
| Right Now | MySQL Started | MySQL Has Been Running For |
+---------------------+---------------------+-------------------------------+
| 2014-08-08 17:13:42 | 2014-06-26 16:38:56 | 43 days 34 minutes 46 seconds |
+---------------------+---------------------+-------------------------------+
1 row in set (0.00 sec)
MySQL has been up for 43 days. It has flushed 179GB out of the Buffer Pool.
The Buffer Pool has to flush dirty pages to three places
- the Log Buffer
- the Insert Buffer
- the .ibd file for the Table
I have a rather busy server and it's a VM.
In your case, 18,699,968 K of flushes from a 2,069,572 K Buffer Pool is nothing to worry about.
SELECT VERSION();