In XtraDB we have the table INNODB_BUFFER_POOL_PAGES_INDEX which shows which pages belong to which indexes in which tables. Using thing information… * 16384 / ( data_length + index_length ), 2) fit FROM (SELECT schema_name, table_name, COUNT(*) cnt, SUM(dirty), SUM(hashed) FROM INNODB_BUFFER_POOL_PAGES_INDEX…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Innodb_buffer_pool_pages_datablogpercona.commanual Innodb_buffer_pool_pages_dirtyblogpercona.commanual Innodb_buffer_pool_pages_flushedblogpercona.commanual Innodb_buffer_pool_pages_freeblogpercona.commanual Innodb_buffer_pool_pages_latchedblogpercona.commanual Innodb_buffer_pool_pages…
Post: MySQL 5.5.8 - in search of stability
January 3, 2011 By 11 Comments
… pages in the InnoDB buffer pool. This value is calculated from the output of mysqladmin ext -i10 using this formula: (100*Innodb_buffer_pool_pages_dirty)/(1+Innodb_buffer_pool_pages_data+Innodb_buffer_pool_pages_free). This is the exact formula that InnoDB uses internally to estimate current innodb_dirty_pages_pct. checkpoint…
Comment: When EXPLAIN estimates can go wrong!
October 15, 2011 By 7 Comments
… extended-status | grep -i innodb_buffer_pool_pages | Innodb_buffer_pool_pages_data | 141 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 0 | | Innodb_buffer_pool_pages_free | 10098 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_total | 10239 | After…
Post: Different flavors of InnoDB flushing
January 13, 2011 By 9 Comments
… used when InnoDB tries to read data from disk, but there are no free pages. In this case, InnoDB has to flush some data to… flushing is used. There is a variable in SHOW STATUS, innodb_buffer_pool_pages_flushed, but it shows the sum of flushing from both… 5.5, I added the SHOW STATUS variable innodb_buffer_pool_pages_LRU_flushed, which shows only pages flushed by the LRU list. So, let…
Post: How long Innodb Shutdown may take
September 2, 2010 By 9 Comments
… as workload and innodb_log_buffer_size and can be anywhere from 10 to 90% in the real life workloads. Innodb_buffer_pool_pages_dirty status will show you the actual data. Now the flush speed also depends on…
Post: XtraDB feature: save / restore buffer pool
January 20, 2010 By 20 Comments
… of buffer pool ? There are several reasons. First, it’s not rate on modern servers to have 32GB+ of RAM, with allocated InnoDB buffer_pool 26GB or more. When you do restart of server, it may take long time to populate cache with useful data… may want to sort ib_lru_dump in order of pages in tablespaces, so RESTORE will be performed in most sequential…
Post: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
… I mentioned a way I use to preload Clustered Index (data) for Innodb tables. Though I thought this topic would benefit from… if Innodb would implement command to preload table to Innodb buffer pool, which would simply go through .ibd file sequentially and inject pages in the buffer pool…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
… completely fits in the Innodb Buffer Pool (512MB). We also have larger table 4GB which does not fit in the buffer pool. We’re running… as data_size_mb from innodb_sys_tables as t inner join innodb_sys_indexes as i using(table_id) inner join innodb_buffer_pool_pages_index as p using(index_id) where t.schema=’test’ group by i.index_id \G INDEX_NAME DATA_SIZE…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…? HT: You are right, bugs that cause data corruption, crashes, or wrong query results get … I mean, if the database buffer pool can buffer many writes to the same page, then fewer disk writes are…on this this matter the other day – choosing innodb_buffer_pool_size Q36: There have been several MySQL …
