… details. Configuration First of all let me describe…InnoDB options 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…size of the table without secondary indexes is 56G while the size of the table…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… of this benchmark, I have used TPC-H Query #10 and ran it on TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size ~95G), because… to be reinitialized, because the buffer was small and not all index tuples could fit in the buffer. If this is…
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: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…, when table t1 would be joined to table t2, then selected rows from t1 would be read and then for all rows, index… of this benchmark, I have used TPC-H Query #3 and ran it on TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache is disabled…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…tables you can create both as permanent and temporary. There is a max_heap_table_size variable which allows you to limit size of MEMORY tables…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…get the same value twice. To accomplish that, InnoDB locks all index records found by the WHERE clause with …locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s), heap size 1248, 3 …no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 72C lock_mode X …
Post: Percona welcomes Drizzle 7.1
April 10, 2012 By Leave a Comment
…) Some of the Percona InnoDB patches …size of the Drizzle kernel (drizzled directory in the source) has shrunk from 149,586 to 146,454 lines of…of code. The remaining 68 plugins are not all trivial either, they include: authentication via PAM, authentication via HTTP, various DATA_DICTIONARY tables…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… of concurrent writes to about 6 tables, what version of Innodb/MySQL would you recommend? I’ve been staying at 4.1.14 because of all… you may need to increase size of your Innodb log files to be able to use a lot of memory for write buffering efficiently… process for all major database brands. Q37: We have a fairly large Innodb table (150GB) that is showing poor performance for full table scans…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… a lot about using short PRIMARY KEYs with Innodb tables due to the fact all other key will refer to the rows by… themselves were stored on the disk of course but meta data such as original image sizes, file size as well as thumbnail location…=InnoDB; Why did I use this solution compared to others: Innodb Tables – This table is getting much more reads than writes so transactional overhead of…
Post: InnoDB TABLE/INDEX stats
March 20, 2010 By 5 Comments
… table show statistics about InnoDB tables ( taken from InnoDB data dictionary). INNODB_TABLE_STATS is | table_name | table name in InnoDB internal style (‘database/table‘) | | rows | estimated number of all rows | | clust_size | cluster index (table…
