…InnoDB, PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ While the structure of the table with secondary indexes is as follows: CREATE TABLE `purchases_index…
Comment: InnoDB's gap locks
May 7, 2012 By 14 Comments
…to SELECT … FOR UPDATE ” on my server ,and my results are as follow: Transaction1: root…INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec, process no 2909, OS thread id 1100101952 inserting mysql tables…page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 0…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… 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 during these benchmark runs and that… INDEX (i_l_orderkey) where c_mktsegment = ‘AUTOMOBILE’ and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < '1995-03-09' and…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… 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… 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: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let… what this enhancement actually 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…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… twice. To accomplish that, InnoDB locks all index records found by the WHERE clause with an exclusive lock and the gaps between them… space id 19 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 72C lock_mode X…/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… memory for table cache, especially if you’re using large blobs. It is easy to check though. Run “FLUSH TABLES” and see whenever…: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB NAME: #sql516_1c42_2 TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 INDEX_LENGTH…: 7231 TABLE_SCHEMA: test TABLE_NAME: z ENGINE: InnoDB NAME: #sql516_1c3f_0 TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 INDEX_LENGTH…
Comment: How to recover a single InnoDB table from a Full Backup
March 22, 2012 By 7 Comments
… problem and would like to recover a single table # xtrabackup_55 –backup –innodb-file-per-table –target-dir=/mnt/mysql/export/ –tables=data # … following errors InnoDB: Import: The extended import of data is being started. InnoDB: Import: 9 indexes have been detected. InnoDB: Progress in %: …
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… expected, as table is really mostly inserted in, updates and deletes are rare). And let’s take index domain_id table: art/link_out104, index: domain… feature to address this is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so page fill factor…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… Innodb tables and you’ve got crappy hardware, driver bug, kernel bug, unlucky power failure or some rare MySQL bug and some pages in Innodb… drop old table and convert new table back to Innodb after restarting without innodb_force_recovery option. You can also rename the old table in case… to make sure indexes are not corrupted. So we looked at how to get your data back from simple Innodb Table Corruption. In…
