… that I used. EC2 Configuration I chose m2.4xlarge instance as that’s the instance type with highest memory available, and memory is… THAN MAXVALUE ENGINE = InnoDB) */ While the structure of the table with secondary indexes is as follows: CREATE TABLE `purchases_index` ( `transactionid` int(11…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… variables are allocated (and how much memory they use). Memory Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query…_LENGTH: 16384 INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL 3 rows in set (0.00 sec) Innodb Memory Usage Finally it…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to… that traditional index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index record…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… is being used then, after the selected rows are read from table1, the values of indexed columns that will be used to perform… have used TPC-H Query #3 and ran it on TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset… when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB buffer pool size is set to…
Comment: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 23, 2012 By 10 Comments
… be to use the same random dives trick Innodb uses – You can do 100 partial random dives in the index tree for each index, you… reading it. This would get you approximate in memory fit for each index and it can be refreshed every so often. Now… cases as frequently you would have only some portion in memory, but it is the portion which is queried the most…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… of your Innodb log files to be able to use a lot of memory for write buffering efficiently. Q30: Question about using solid state…:-). At the moment the server has around 21 GB of memory used for applications (of which Mysql is the main one with…: Seems that adaptive hash indexes are not used for primary key lookups or not showed up in “show innodb statusâ€. select * from…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… not have a good use for that memory anyway. Another thing you should keep into account is Innodb allocates more memory in structures related… Innodb is caching already. Innodb cache is more efficient compared to OS cache because there is no copying, due to adaptive hash indexes… they usually play on the safe side, having enough “free” memory used as Cache and Buffers. Kevin Burton wrote a good post…
Post: InnoDB memory usage
May 30, 2006 By 6 Comments
…_size innodb_log_buffer_size adaptive index hash, size= innodb_buffer_pool / 64 system dictionary hash, size = 6 * innodb_buffer_pool_size / 512 memory for sync_array, which is used…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
… together with index and generally highly optimized Innodb builds hash indexes which helps to speed up lookup by indexes by passing BTREE index and using hash… request next row matching index Note: This applies to CPU bound workload with all content fitting in memory. In other cases situation…
