… that you are modifying a partition it fits in main memory (and would have fit even with only a few GB of… because the entire table fits in main memory. Each row is on the order of 30 bytes in the primary table, and so… B-tree pretty efficiently. With a 55GB buffer pool, we would expect the primary table to simply fit. But with all 4 indexes the…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… have a small buffer pool and a very large number of rows from table2 that do not fit into the buffer pool, then worst case you could be reading the same page multiple times into the buffer pool. So considering… used in the build step is such that the hash table fits in memory. You can read more about the hash join…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool… 5.5 w/ mrr_buffer_size=4M Created_tmp_disk_tables 1 1 1 1 1 Created_tmp_tables 1 1 1 1… 136435 Innodb_buffer_pool_read_ahead 0 20920 23669 20920 23734 Innodb_buffer_pool_read_requests 1361851 1264739 1235472 1263290 1235781 Innodb_buffer_pool_reads 120548…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: How well do your tables fit in buffer pool
March 26, 2010 By 4 Comments
… 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(100 * cnt * 16384 / ( data_length + index_length ), 2) fit FROM…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 11 Comments
Understanding how well your tables and indexes fit to buffer pool are often very helpful to understand why some queries 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 (fit… many cases you would learn you need a certain fit to buffer pool for tables/indexes for reasonable performance, you may try to count…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
… much faster as well as ALTER TABLE (expecially together with ability to create index without table rebuilding) Buffer pool improvements. I would like to see couple of things here. To have kind of middle point insertion so full table… gives you the nice surprise of row being unable to fit to the page if many columns are long strings. I…
Post: What MySQL buffer cache hit rate should you target
June 29, 2006 By 5 Comments
… I’m asked. It could by MyISAM key_buffer or Innodb innodb_buffer_pool it does not really matter. In both … you can name 100% hit rate and all data fits in memory as perfect case but that is not …”, “OS file writes” from SHOW INNODB STATUS for Innodb tables. In MySQL 5.0+ you can also use Innodb_…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… HASH indexes by default. I’ve created rather similar test table: CREATE TABLE `test` ( `id` int(11) NOT NULL auto_increment, `c… slower than Innodb – in this case the table is small and fully fits in Innodb Buffer Pool and log flush on transaction commit was… the key file and data file. They of course were buffered by operation system, but it can’t do it as…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
… a small table (200MB) which 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 uniform sysbench OLTP on the small table…
