Tag Archives: InnoDB

Fresh dogfood: Migrating to InnoDB fulltext search on bugs.mysql.com

Even frequent visitors to bugs.mysql.com can sometimes miss the little note in the bottom right corner of each page:

Page generated in 0.017 sec. using MySQL 5.6.11-enterprise-commercial-advanced-log

That text changed this past weekend, going from MySQL Enterprise 5.6.10 to 5.6.11.  But more importantly, the collection of MyISAM tables which support the bugs system were also converted to InnoDB.  There’s a little story to tell here about eating this particular helping of dogfood which also amplifies changelog comments, so here it is:
Continue reading Fresh dogfood: Migrating to InnoDB fulltext search on bugs.mysql.com

InnoDB now works with read-only media

With a handful of exceptions, few people deliberately choose to deploy MySQL on read-only media – but there are cases where being able to access InnoDB data that way comes in handy.  As it happened, I had exactly this need a few months back, and the excellent InnoDB development team at Oracle has recently implemented this feature in MySQL 5.6.

Continue reading InnoDB now works with read-only media

Smarter InnoDB transportable tablespace management operations

I’ve noted previously that the new transportable tablespaces for InnoDB in MySQL 5.6 are a big step forward, and newly-released 5.6.9-rc makes importing tablespaces a bit easier.  In previous versions, you had to have a .cfg file to import the InnoDB tablespace.  That file is produced during FLUSH TABLE <tbl> FOR EXPORT operations, and contains InnoDB metadata that’s not contained in the .ibd tablespace file itself.  I filed a feature request requesting the .cfg file be made optional, and Sunny implemented it: Continue reading Smarter InnoDB transportable tablespace management operations

Understanding InnoDB transportable tablespaces in MySQL 5.6

If you’re anything like me,  your initial reaction upon hearing about transportable tablespaces for InnoDB in 5.6 was to imagine it like MyISAM, where you can copy the .frm, .myi and .myd files around to your heart’s content, and everything will be great.  You might have read Sunny’s excellent blog, and realized that there’s a bit more to it than that – you have to explicitly prepare the tablespace for copying using FLUSH TABLES FOR EXPORT.  That’s perfectly acceptable for the bulk of use cases, such as single-table InnoDB backups, and opens up exciting new possibilities for moving or copying InnoDB data at the filesystem level.

But for situations where the need is a little different, you might really dug into it and start to wonder about the .cfg files produced during the FLUSH TABLES FOR EXPORT operation.  What happens if you don’t have – and can’t produce – that information?  What does that .cfg file contain that makes it critical to success importing the tablespace later? Continue reading Understanding InnoDB transportable tablespaces in MySQL 5.6

Deprecated in MySQL 5.6 – ignore_builtin_innodb

Back when MySQL 5.1 was first released, Oracle (which didn’t at the time own Sun or MySQL) wanted to add new InnoDB functionality faster than MySQL could effectively incorporate it into the server, and the InnoDB plugin was introduced.  It provided 5.1 users the option of deploying a more advanced version of InnoDB, or using the more conservative built-in version baked into MySQL.  By the time the plugin reached GA status, though, Oracle had announced the acquisition of Sun (and MySQL), and things started to change.  The coordination between the InnoDB development team at Oracle and the MySQL development team increased once we were all “under one roof”.  The strategic directions of InnoDB and MySQL were aligned, and the results can be seen in the tremendous work done for MySQL 5.5 and 5.6.  In fact, the InnoDB plugin was removed with MySQL 5.5:

The built-in InnoDB storage engine within MySQL is the original form of distribution for the storage engine. Contrast with the InnoDB Plugin. Starting with MySQL 5.5, the InnoDB Plugin is merged back into the MySQL code base as the built-in InnoDB storage engine (known as InnoDB 1.1).

This distinction is important mainly in MySQL 5.1, where a feature or bug fix might apply to the InnoDB Plugin but not the built-in InnoDB, or vice versa.

One artifact of the “two different InnoDB engines” introduced with the plugin remains:  the ignore_builtin_innodb configuration option.  As of 5.6.5 and later, that option is now deprecated, and has no effect:

D:\mysql-5.6.5-m8-win32>bin\mysqld –no-defaults –console –port=3307 –ignore-builtin-innodb
120531 10:54:30 [Warning] ignore-builtin-innodb is ignored and will be removed in future releases.

Since MySQL 5.5, we’ve focused on and invested in InnoDB as a core element and default storage engine for MySQL.  As such, in 5.5, we removed the InnoDB plugin.  And now with 5.6, we are eliminating the option to disable InnoDB.

 

Identifying and killing blocking transactions in InnoDB

The MySQL Server has a few options to help deal with transactions holding InnoDB locks for excessive periods of time.  The –innodb-lock-wait-timeout option is one such option, but that just affects statements waiting on locks already held by another transaction.  If you want to ensure your application isn’t holding locks for long periods of time to start with, what options do you have?

Well, the manual has a useful example that leverages InnoDB tables in INFORMATION_SCHEMA to show which transactions are locking rows needed by other transactions.  That’s cool stuff, and you can take this a step further by looking for all connections with open transactions which exceed a given threshold or are blocking other transactions:

mysql> SET @threshold = 30;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT p.user,
->      LEFT(p.HOST, LOCATE(':', p.HOST) - 1) host, p.id,
->      TIMESTAMPDIFF(SECOND, t.TRX_STARTED, NOW()) duration,
->      COUNT(DISTINCT ot.REQUESTING_TRX_ID) waiting
->    FROM INFORMATION_SCHEMA.INNODB_TRX t
->    JOIN INFORMATION_SCHEMA.PROCESSLIST p
->      ON ( p.ID = t.TRX_MYSQL_THREAD_ID )
->    LEFT JOIN INFORMATION_SCHEMA.INNODB_LOCK_WAITS ot
->      ON ( ot.BLOCKING_TRX_ID = t.TRX_id )
->    WHERE t.TRX_STARTED + INTERVAL @threshold SECOND <= NOW()
->    GROUP BY LEFT(p.HOST, LOCATE(':', p.HOST) - 1), p.id, duration
->    HAVING duration >= @threshold OR waiting > 0;
+------+-----------+----+----------+---------+
| user | host      | id | duration | waiting |
+------+-----------+----+----------+---------+
| root | localhost |  4 |     1062 |       0 |
| ODBC | localhost |  5 |      276 |       0 |
+------+-----------+----+----------+---------+
2 rows in set (0.08 sec)

The next step you can take is to create an EVENT which logs this, or even issues KILL CONNECTION (sadly, there is no KILL TRANSACTION, and KILL QUERY doesn’t rollback the transaction) commands on the blocking transaction.  You can also create control tables that allow you to fine-tune reporting or action thresholds for specific user/host combinations:

CREATE TABLE test.innodb_trx_kill_threshold (
 user VARCHAR(100) NOT NULL,
 host VARCHAR(100) NOT NULL,
 trx_kill_threshold INT UNSIGNED,
 PRIMARY KEY (user, host)
) ENGINE = InnoDB;

CREATE TABLE test.innodb_trx_kill_audit (
 id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 user VARCHAR(100) NOT NULL,
 host VARCHAR(100) NOT NULL,
 connection_id INT UNSIGNED NOT NULL,
 trx_duration INT UNSIGNED NOT NULL,
 other_trx_waiting INT UNSIGNED NOT NULL DEFAULT 0,
 kill_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY (user, host, connection_id)
) ENGINE = InnoDB;

CREATE OR REPLACE VIEW test.innodb_trx_past_threshhold AS
SELECT p.user, 
     LEFT(p.HOST, LOCATE(':', p.HOST) - 1) host, p.id, 
     TIMESTAMPDIFF(SECOND, t.TRX_STARTED, NOW()) duration,
     IFNULL(kt.trx_kill_threshold, 30) threshold, 
     (SELECT COUNT(ot.REQUESTING_TRX_ID) 
       FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS ot 
       WHERE ot.BLOCKING_TRX_ID = t.TRX_id) waiting
   FROM INFORMATION_SCHEMA.INNODB_TRX t 
   JOIN INFORMATION_SCHEMA.PROCESSLIST p 
     ON ( p.ID = t.TRX_MYSQL_THREAD_ID )
   LEFT JOIN test.innodb_trx_kill_threshold kt 
     ON ( p.USER = kt.user AND LEFT(p.HOST, LOCATE(':', p.HOST) - 1) = kt.host )
   WHERE t.TRX_STARTED + INTERVAL IFNULL(kt.trx_kill_threshold, 30) SECOND  0 ;

DELIMITER ||
CREATE PROCEDURE kill_long_innodb_trx (IN kill_connections INT)
 BEGIN
 DECLARE done INT DEFAULT FALSE;
 DECLARE l_user, l_host VARCHAR(100);
 DECLARE l_timeout, l_duration, l_conn_id, l_other_trx_waiting INT UNSIGNED;
 DECLARE cur CURSOR FOR 
   SELECT user, host, id, duration, threshold, waiting 
   FROM test.innodb_trx_past_threshhold;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 OPEN cur;

 read_loop: LOOP
  FETCH cur INTO l_user, l_host, l_conn_id, l_duration, l_timeout, l_other_trx_waiting;
  IF done THEN
    LEAVE read_loop;
  END IF;
  INSERT INTO test.innodb_trx_kill_audit
    ( user, host, connection_id, trx_duration, other_trx_waiting)
      VALUES
    ( l_user, l_host, l_conn_id, l_duration, l_other_trx_waiting )
    ON DUPLICATE KEY UPDATE trx_duration = l_duration, 
      other_trx_waiting = GREATEST(other_trx_waiting, l_other_trx_waiting);
  IF kill_connections THEN
    SET @stmt = CONCAT('KILL CONNECTION ', l_conn_id);
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
  END IF;

 END LOOP;

 CLOSE cur;

 END;
||
DELIMITER ;

CREATE EVENT innodb_trx_killer ON SCHEDULE EVERY 5 SECOND DO CALL kill_long_innodb_trx (1);

And now there’s an EVENT running every 5 seconds which will kill connections having InnoDB transactions older than the threshold (30s, unless configured differently for the user/host combination in the control table).  It will also log information to an audit table (test.innodb_trx_kill_audit) about the connections before they are killed.  One could easily tweak the stored procedure to simply record the audit details instead of killing connections.  Here’s an example of this in action:

mysql> BEGIN;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO p VALUES (2, 3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT SLEEP(35);
+-----------+
| SLEEP(35) |
+-----------+
|         0 |
+-----------+
1 row in set (35.00 sec)

mysql> COMMIT;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

And the audit table:

mysql> SELECT * FROM test.innodb_trx_kill_audit\G
*************************** 1. row ***************************
               id: 1
             user: ODBC
             host: localhost
    connection_id: 157
     trx_duration: 31
other_trx_waiting: 0
        kill_time: 2012-08-28 13:08:07
1 row in set (0.00 sec)

Note:  It’s been pointed out to me that there are a few similar, earlier suggestions on how to do this, or something similar.