…. The SHOW ENGINE INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec, process no 2909, OS thread id 1100101952 inserting mysql tables… lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 31929, query id 484616 localhost root update insert…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global buffers, such as innodb… first things I do as it is least intrusive. Run SHOW ENGINE INNODB STATUS and look for memory information block, which can use… and fixed. This is where your MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory can help us…
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), heap size 1248, 3 row lock(s), undo log entries 1 MySQL… source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… 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 read_ahead, while in MySQL… rows than MySQL 5.5, as can be seen by the numbers reported for the status counter Innodb_rows_read. While MySQL 5.6…
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… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… other possibilities like InnoDB doing read_ahead by noticing the sequential access pattern. BKA is available in both MySQL 5.6 and… let’s take a look at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size… by the high numbers of Innodb_buffer_pool_read_ahead when the buffers are sized appropriately, which shows that the IO access…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 27, 2012 By 20 Comments
…is different issue. In many versions Innodb would print wrong position for current binary log …://www.mysqlperformanceblog.com/2008/01/29/no-more-mysql-crash-safe-replication-in-50/ We have …READ LOCK anyway you can consider doing SHOW SLAVE STATUS/SHOW MASTER STATUS to get proper position instead of …
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 position… a slave, it has no binlog of its own. SHOW SLAVE STATUS says Master_Log_File: binlog.000062 Relay_Log_File: relaylog…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…innodb_status_fileblogpercona.commanual innodb_strict_modeblogpercona.commanual innodb_support_xablogpercona.commanual innodb_sync_spin_loopsblogpercona.commanual innodb_table_locksblogpercona.commanual innodb_thread_concurrencyblogpercona.commanual innodb…
