…:29>select * from t; +——+ | i | +——+ | 21 | | 30 | | 26 | +——+ 3 rows in set (0.00 sec) root@localhost:test 14:51:40… ENGINE INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec, process no 2909, OS thread id 1100101952 inserting mysql tables in use… no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 0 27638 lock_mode X insert…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
…used: ## 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…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… Percona Xtrabackup, MyLVMBackup and others use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period for… just using Innodb tables and you’re not actively changing users, stored procedures etc (which are stored in MyISAM tables anyway) you can consider using –no…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table participating in the…, and obviously when the first operand used in the build step is such that the hash table fits in memory. You can read more… have used TPC-H Query #3 and ran it on TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x This query will roughly be evaluated in following steps… 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…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… 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 Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let… limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…uses MySQL to identify potential causes. Is it working with large blobs ? Using user variables ? Prepared Statements ? memory tables ? In…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…. InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ COMMITTED for all write queries, in spite… gap locks? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct… 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test`.`t` trx id 72C lock mode IX RECORD…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
Many people use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it works… new structure in the dump but no data. Here is example: SESSION1: mysql> show tables; +——————–+ | Tables_in_dumptest | +——————–+ | A | | B | | C | +——————–+ 3 rows in set…
Comment: How to recover a single InnoDB table from a Full Backup
March 22, 2012 By 7 Comments
… of my mysql database using the innobackupex-1.5.1 command. I have the innodb_file_per_table directive set in the my.cnf… MYISAM table – but none of the InnoDB tables. Is there something which I am missing? Do I have to set any directive in the my.cnf file to access the InnoDB table? Please help. Thanks in advance. Abhilasj
