'InnoDB' Tag

• Easy SELECT COUNT(*) with split()

  June 8, 2013

  The two conservative ways of getting the number of rows in an InnoDB table are: SELECT COUNT(*) FROM my_table: provides with an accurate number, but makes for a long running transaction which take ages on large tables. Long transactions make for locks SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='my_schema' AND TABLE_NAME='my_table', or get same info via [...]

  posted in MySQL by shlomi

• Converting compressed InnoDB tables to TokuDB 7.0.1

  June 5, 2013

  Or: how to make it work in TokuDB version 7.0.1. This is a follow up on a discussion on the tokudb-user group. Background I wanted to test TokuDB's compression. I took a staging machine of mine, with production data, and migrated it from Percona Server 5.5 To MariaDB 5.5+TokuDB 7.0.1. Migration went well, no problems. [...]

  posted in MySQL by shlomi

• State of InnDB Online DDL in MySQL 5.6.9-RC (good news included)

  December 18, 2012

  5.6.9-RC is out, and I was curious to see how the online DDL has improved since my 5.6.8 review. I also owe James Day this review, since he came up with results inconsistent with my own. We both agreed the dataset I was using was too small, but I got similar results even on larger [...]

  posted in MySQL by shlomi

• Killing InnoDB idle transactions

  December 4, 2012

  The issue of terminating long-time idle open InnoDB transaction has been discussed recently by many. I wish to add my share, by proposing a quick and clean solution via common_schema. common_schema 1.2 provides with the innodb_transactions view, which relies on INNODB_TRX - one of the InnoDB Plugin views in INFORMATION_SCHEMA - as well as on [...]

  posted in MySQL by shlomi

• State of InnDB Online DDL in MySQL 5.6.8-RC

  November 20, 2012

  5.6.8-rc is out, and so I'm following up on InnoDB's online DDL new feature: the ability to SELECT, INSERT, DELETE, UPDATE a table even while an ALTER TABLE is executing on same table. The brief summary Not as advertised; many things can't be done. The longer review I'm using 5.6.8-rc 64bit binary distribution for Linux, [...]

  posted in MySQL by shlomi

• InnoDB DDL: kudos to quick responders on bugs.mysql.com

  October 18, 2012

  Continuing my experiments with 5.6 InnoDB online DDL, a bug which I've opened, and another which I commented on were quickly answered and explained by the Oracle/MySQL team. On both accounts I'm happy to acknowledge the issue is resolved; in both cases I failed to produce a real bug scenario. Good lesson. Kudos for quick [...]

  posted in MySQL by shlomi

• Experimenting with 5.6 InnoDB Online DDL (bugs included)

  October 18, 2012

  MySQL 5.6 offers the groundbreaking online DDL operations for InnoDB. Most common use cases will enjoy this feature, and the need for online alter table scripts will decrease. This is a killer feature! I've put this new feature to the usability test. How did it go? Not too well, I'm afraid. [Updates to this text [...]

  posted in MySQL by shlomi

• Thoughts on MySQL 5.6 new replication features

  October 15, 2012

  After playing a little bit with MySQL 5.6 (RC), and following closely on Giuseppe's MySQL 5.6 replication gotchas (and bugs), I was having some thoughts. These are shared for a few reasons: Maybe I didn't understand it well, and someone could correct me Or I understood it well, and my input could be of service [...]

  posted in MySQL by shlomi

• MySQL 5.6 new features: the user's perspective

  October 5, 2012

  This is a yet-another compilation of the new MySQL 5.6 feature set. It is not a complete drill down. This list reflects what I believe to be the interesting new features user and usability -wise. For example, I won't be listing InnoDB's split of kernel mutex. I'm assuming it can have a great impact on [...]

  posted in MySQL by shlomi

• Getting rid of huge ibdata file, no dump required, part II

  May 30, 2012

  This post continues Getting rid of huge ibdata file, no dump required, part I, where I describe way of converting your single-tablespace InnoDB database into a file-per-table one, without the pain of exporting and importing everything at once. In previous part we put aside the issue of foreign keys. We address this issue now. What [...]

  posted in MySQL by shlomi