…MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for the tests. Following is the configuration that I used: ## InnoDB…InnoDB) */ While the structure of the table with secondary indexes is as follows: CREATE TABLE `purchases_index… size of the B+tree, having multiple buffer pool…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… 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: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… physically sorted indexes with Innodb (with index built by sort) this is when this should be helpful the most. Q3: Does Innodb secondary Indexes trees are… little known new features in MySQL 5.1 Q15: How frequently does Innodb fuzzy checkpointing is activated HT: InnoDB flushes about 128 dirty…
Post: Bug#12704861
November 20, 2011 By 1 Comment
… is fixed in MySQL 5.1.60. I’ll repeat here the full documentation from the release notes: “InnoDB Storage Engine: Data… the record update, the index tree would be corrupted. The correct solution is to make the freed clustered index pages unavailable to the BLOB allocation. This function is also a likely culprit of InnoDB hangs that were…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…KEY `id` (`id`), KEY `type_created` (`type`,`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci …: NULL rows: 339184 Extra: Using where; Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count… and make the estimation very precise if the index tree is not …
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 1 Comment
…index definitions and related stuff, double write buffer, insert buffer which temporary stores records to be inserted in the index tree… the following can be helpful: mysql> select * from innodb_rseg; +———+———-+———-+———+————+———–+ | rseg_id | space_id | zip_…
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
… Database Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance… 400,000,000 rows. This creates a table+index data size approximately twice as big as memory,…-disk threshold, as famously happens in B-Tree inserts (see InnoDB vs TokuDB for example). This doesn’t…
Post: Detailed review of Tokutek storage engine
April 28, 2009 By 21 Comments
… https://launchpad.net/mysql-patch/mytools What makes fractal indexes so interesting is the amount of IO operations to update index tree is significantly less than for usual B-Tree index. It’s as if Fractal Trees turn random IO into… binary form and mysqld binary does not contain InnoDB. Tokutek tells me that InnoDB will be included in a future release. With…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… going in random locations in index tree which will require a lot of IO in case of index tree not fitting into memory. This… not really the problem. What is about Secondary Keys ? For Innodb tables UUID have extremely poor impact on your secondary key… problem which can be fixed in MySQL 5.1 Data Clustering This again applies to Innodb tables aspect of primary key selection…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… size rows, auto increment primary key and couple of extra indexes. CREATE TABLE IF NOT EXISTS `$tableName` ( `id` int(10) unsigned… RAM MySQL version We used MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql… parameters: For MyISAM / InnoDB ./configure –prefix=/usr/local/mysqltest/mysql- –with-innodb For Falcon ./configure –prefix=/usr/local/mysqltest/mysql- –with-falcon mysqld startup…
