…innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_flushing = 1 innodb_adaptive_flushing_method = estimate innodb… index and only a primary index, and against the table purchases_index which has 3 secondary indexes…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…(Note that secondary keys in InnoDB contain primary key columns) For each pk_column value in step 1 do… still improvement and changes needed in the optimizer’s cost estimation algorithm. MariaDB 5.5 expands … further when buffer size is set large enough so that the index tuples fit in the buffer. …
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…. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both… aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… or another index in InnoDB … you had to calculate it yourself by multiplying size of row (which I should add is harder in the case…/link_out104, index: PRIMARY, space id: 12, root page 3 estimated statistics in dictionary: key vals: 25265338, leaf pages 497839, size pages 498304 real…
Post: InnoDB TABLE/INDEX stats
March 20, 2010 By 5 Comments
…index_size | index size in pages | | leaf_pages | number of leaf pages | Using these stats you can estimate how big is index…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
… mysql> ALTER TABLE test_table ADD INDEX (id); — 5 Tickets Used Query OK…estimate or wait for InnoDB to expose this information to us. What happens in …innodb_log_buffer_size = 8M innodb_log_files_in_group=2 innodb_log_file_size=1900M innodb_thread_concurrency=16 innodb_flush_method = O_DIRECT innodb…
Post: How much space does empty Innodb table take ?
December 16, 2008 By 7 Comments
…indexes. How much space will Innodb take: mysql> create table test_innodb(a int, b int) engine=innodb…estimated…InnoDB free: 0 kB 1 row in set (0.00 sec) Free Space for tables created in innodb…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
…size of Innodb TableSpaces) because it does not only contain data pages – it also contain adaptive hash indexes…estimates for this number. Eliminate Double Buffering – This is again very important for buffer pool size …have all data they need to proceed in the innodb buffer pool. I guess these …
Post: Predicting how long data load would take
May 24, 2007 By 12 Comments
… data in primary key order into Innodb tables for optimal performance, but effect does not end here. If indexes are built by inserts in Btree order in which… history about loading similar data of similar size on similar hardware use that as estimate. If not use your best guess based…
Post: Tuning for heavy writing workloads
October 14, 2009 By 9 Comments
…innodb_flush_method = O_DIRECT (the followings are XtraDB specific general settings) innodb_ibuf_active_contract = 1 innodb_adaptive_flushing = false innodb_adaptive_checkpoint = estimate…in InnoDB…
