… came out my insert still be blocked. The SHOW ENGINE INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec, process…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s), heap… 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…
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… *************************** SESSION_ID: 7231 TABLE_SCHEMA: test TABLE_NAME: z ENGINE: InnoDB NAME: #sql516_1c3f_0 TABLE_ROWS: 0 AVG_ROW_LENGTH… I do as it is least intrusive. Run SHOW ENGINE INNODB STATUS and look for memory information block, which can use like…
Comment: InnoDB thread concurrency
March 19, 2012 By 4 Comments
… i ask you quesiton? i issue the command ‘show engine innodb status‘, is shows there are 10 io threads(4 are read… (write thread) but i found that the value of the innodb_thread_concurrency is 8. so, what is wrong with this…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…. Now let’s take a look at the status counters. MySQL Status Counters These status counters were captured when performing the benchmark on… from the status counters above that both MySQL 5.6 and MariaDB 5.5 are reporting high numbers for Innodb_buffer_pool…, 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… 2.2G Innodb_data_reads 329115 355323 143808 335526 16164 15506 Innodb_pages_read 329115 355323 143808 358308 144798 144881 Innodb_rows_read… two other most important numbers are values for Innodb_buffer_pool_reads and Innodb_data_read. We can see that with appropriately…
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 … READ LOCK anyway you can consider doing SHOW SLAVE STATUS/SHOW MASTER STATUS to get proper position instead of relying on that…
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… slave, it has no binlog of its own. SHOW SLAVE STATUS says Master_Log_File: binlog.000062 Relay_Log_File: relaylog…
Comment: How much space does empty Innodb table take ?
April 3, 2012 By 7 Comments
Bruno, There is a lot of overhead in InnoDB. * A 16KB block is never completely full. After a lot …. Peter, I have empirically determined that “free space” (of TABLE STATUS) does not include free space in the 16KB blocks. And…
