…],but it came out my insert still be blocked. The SHOW ENGINE INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… gap locks? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s… need to create locks to prevent that from happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except… information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 27, 2012 By 20 Comments
Kristian, This is different issue. In many versions Innodb would print wrong position for current binary log (especially if … TABLES WITH READ LOCK anyway you can consider doing SHOW SLAVE STATUS/SHOW MASTER STATUS to get proper position instead of relying on that…
Comment: InnoDB thread concurrency
March 19, 2012 By 4 Comments
…, can i ask you quesiton? i issue the command ‘show engine innodb status‘, is shows there are 10 io threads(4 are read, 4… (write thread) but i found that the value of the innodb_thread_concurrency is 8. so, what is wrong with this…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB NAME: #sql516_1c42_2 TABLE_ROWS: 0 AVG_ROW_LENGTH… Innodb has allocated. In fact this is often one of the first things I do as it is least intrusive. Run SHOW ENGINE INNODB STATUS and look for memory information block, which can use…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 27, 2012 By 20 Comments
… target InnoDB, when it recovers, says: … 120327 11:25:22 InnoDB: Starting an apply batch of log records to the database… … InnoDB: Apply batch completed InnoDB: Last MySQL binlog file… a slave, it has no binlog of its own. SHOW SLAVE STATUS says Master_Log_File: binlog.000062 Relay_Log_File: relaylog…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… from the status counters above that both MySQL 5.6 and MariaDB 5.5 are reporting high numbers for Innodb_buffer_pool_read_ahead which shows that the access pattern was sequential and hence InnoDB decided to do…, as can be seen by the numbers reported for the status counter Innodb_rows_read. While MySQL 5.6 is also reporting…
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
… difference here. Now let’s take a look at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL… by the high numbers of Innodb_buffer_pool_read_ahead when the buffers are sized appropriately, which shows that the IO access pattern has been changed to become sequential. The two other most important numbers are values for Innodb…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info… is left by Innodb developers so SHOW INNODB STATUS can also be used as debugging tool. Next section you will find in SHOW INNODB STATUS is information…
