… belong to which indexes in which tables. Using thing information and standard TABLES table we can see how well different tables fit in buffer pool. SELECT d.*, ROUND… the table in buffer pool than on disk. Though it seems to work well enough for estimation purposes. UPDATE: We have changed the Schema in…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
… | +————–+————–+————–+——+——-+——–+———+ 13 rows in set (0.04 sec) This query shows information about how many pages are in buffer pool for given table (cnt), how many of them are dirty (dirty), and what is the percentage of index fits in memory…
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
… differences in the two implementations is the use of buffer pool mutex in Oracle MySQL, this mutex is not used by “Lazy Drop Table” implementation. While the MySQL’s version of drop table…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… using Innodb tables you will want to give all memory you do not need for other needs for Innodb Buffer Pool. This of… temporary tables and otherwise running complex queries. It is usually much better to simply check it. Start MySQL With 10GB Innodb buffer pool… could be locks set while accessing pages in the buffer pool. If page turns out to be in swap rather than memory you will…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… ALTER TABLE performance with fast index creation and without it with different buffer pool sizes. Results are pretty interesting. I used modified Sysbench table for these tests because original table as initially created… 6GB buffer pool which is large enough to fit complete table with index (but is a bit too small to keep 2 copies) In…
Post: Performance problem with Innodb and DROP TABLE
February 3, 2011 By 23 Comments
… 1TB at the high end in the next few year. This will get the list of pages in buffer pool to be getting close… of tables in a batch can cause serve performance problems. The stalls can take more than a second for very large buffer pool. If you’re using Innodb tables as transient tables where you create and drop them…
Post: The relationship between Innodb Log checkpointing and dirty Buffer pool pages
February 17, 2012 By 10 Comments
… Innodb updates buffer pool pages in memory and records page operations in the transaction (redo) log. Behind the scenes those updated (dirty) buffer pool pages are… more than one thread (particularly if you have relatively few tables and/or use the global tablespaces) *cough ext3 cough*. innodb…
Post: What is stored InnoDB buffer pool
September 28, 2008 By 8 Comments
…in InnoDB buffer pool at the moment ? It …_TYPE_ID | PAGE_TYPE | INDEX_NAME | TABLE_SCHEMA | TABLE_NAME | +———–+——-+——–+———+———-+————+———–+————–+————–+—————-+—————–+————–+——————+ | 0 | 0 | 7 | 0 | 0…
Post: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
… command to preload table to Innodb buffer pool, which would simply go through .ibd file sequentially and inject pages in the buffer pool. This would make… 
So As I mentioned you can load Innodb Table Clustered Index in the buffer pool pretty efficiently by using something like SELECT count(*) FROM…
Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)
June 19, 2012 By 2 Comments
… expect (courtesy of Alexey Kopytov), using 1Mil rows Sysbench table. Buffer Length | alter table sbtest add key(c) 1MB 34 sec 8MB 26… alter table. You may be wondering why in this case table rebuild is so close in performance to building index by sort with small buffer… case also would be nice. The table also was fitting in buffer pool completely in this case which means table rebuild could have done fast too…
