When executing an ALTER TABLE, InnoDB (and XtraDB) will create two InnoDB transactions:

1. One transaction is created when the table being ALTERed is locked by the server.
   This will show up as something like “TABLE LOCK table `schema`.`table_name` trx id XXXX lock mode S” in SHOW ENGINE INNODB STATUS.
2. Another is created when adding or dropping an index to perform operations on the InnoDB data dictionary.

A little known fact is that the InnoDB data dictionary is somewhat transactional (the big thing it’s missing is any form of MVCC. It’s not ACID). The largest part of ALTER TABLE not being completely crash safe in MySQL is the MySQL server manipulating FRM files.

The only MySQL storage engine I’m aware of having fully crash safe DDL is MySQL Cluster (NDB).

Proving that MEMORY is/isn’t crash safe around DDL is an exercise left to the reader

Thanks go to …

I wrote one week ago about how to find duplicate indexes. This time we’ll learn how to find unused indexes to continue improving our schema and the overall performance. There are different possibilites and we’ll explore the two most common here. User Statistics from Percona Server and pt-index-usage.

User Statistics

User Statistics is an improvement on Percona Server that adds some tables to Information Schema with useful information to understand the server activity and identify the source of the load. …

I did a Webinar about MySQL Upgrade – Best Practices Yesterday and there were some questions we could not answer during Webinar, following Jay’s Lead I decided to post them as a Blog Post.

Q: Can you go directly MySQL 5.0 to 5.5 for MyISAM tables?
MyISAM have not been getting any significant development since MySQL 4.1, so in-place upgrade of MySQL 5.0 to MySQL 5.5 should be rather safe from data standpoint. There are still possibilities for sorting order and data type related changes, so you still need to run mysql_upgrade to check if any tables need to be rebuilt for new versions. Note this only applies to the data part of upgrade, you still need to keep into …

Today I was looking at the ALTER TABLE performance with fast index creation and without it with different buffer pool sizes. Results are pretty interesting. I used modified Sysbench table for these tests because original table as initially created only has index on column K which initially contains only zeros, which means index is very small and also very fast to build by insertion as insertions happen in the “end” of index tree. I’ve updated column to have bunch of long random strings update sbtest set c=concat(sha1(rand()),’-',sha1(rand()),’-',sha1(rand()),’-',sha1(rand()),’-',sha1(rand())); and added key on column C: alter table sbtest add key c(c); The box I’m using for test is rather old box with 8GB of RAM and 4*SATA disks in RAID10. I used 10mil row table which would look as following in terms of data and index size:

mysql> show table status like "sbtest" \G …

Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then you can probably schedule the table dropping process to run during off-peak hours. But I have seen cases where the tables had to be dropped more frequently (like every other hour), or when there was no such thing as off-peak hours, in such cases you need consistent performance. But dropping tables is known to cause stalls that exhibit themselves as reduced QPS during the table drops or stalls. Percona Server since version 5.1 has a feature know as “Lazy Drop Table” that alleviates the problem to a great extent but does not get rid of it completely. In the new releases of MySQL (versions >= 5.5.23) work has been done on reducing …

The MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0″ before making schema changes, InnoDB will not allow a column referenced by a foreign key constraint to be modified in such a way that the foreign key will reference a column with a mismatched data type. For instance, if we have these two tables:

CREATE TABLE foo (
  i INT NOT NULL PRIMARY KEY,
  j INT NOT NULL,
  INDEX(j),
  FOREIGN KEY (j) REFERENCES bar(j)
) ENGINE=INNODB;

CREATE TABLE bar (
  i INT NOT NULL PRIMARY KEY,
  j INT NOT NULL,
  INDEX(j)
) ENGINE=INNODB;

trying to do something like “ALTER TABLE bar DROP j” or “ALTER TABLE bar MODIFY COLUMN j j SMALLINT NOT NULL” will produce an error unless we first remove the foreign key constraint present in table “foo”. Indeed, if we try it, that’s exactly what happens:

(root@localhost) [foobar]> ALTER TABLE bar drop j;
ERROR 1025 (HY000): Error on rename of …

When I’m doing conventional ALTER TABLE in MySQL I can ignore default value and it will be assigned based on the column type. For example this alter table sbtest add column v varchar(100) not null would work even though we do not specify default value. MySQL will assign empty string as default default value for varchar column. This however does not work for pt-online-schema-change:

root@smt2:~# pt-online-schema-change --execute  --alter="add column v varchar(100) not null" D=sbtest,t=sbtest     Altering `sbtest`.`sbtest`...
Creating new table...
Created new table sbtest._sbtest_new OK.
Altering new table...
Altered `sbtest`.`_sbtest_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 10000060 rows...
Dropping triggers...
Dropped triggers OK.
Dropping new table...
Dropped new table OK.
`sbtest`.`sbtest` was not altered.
        (in cleanup) Error copying rows from `sbtest`.`sbtest` to `sbtest`.`_sbtest_new`: Copying …

Having duplicate keys in our schemas can hurt the performance of our database:

• They make the optimizer phase slower because MySQL needs to examine more query plans.

• The storage engine needs to maintain, calculate and update more index statistics

• DML and even read queries can be slower because MySQL needs update fetch more data to Buffer Pool for the same load

• Our data needs more disk space so our backups will be bigger and slower

In this post I’m going to explain the different types of duplicate indexes and how to find and remove them.

Duplicate keys on the same column

This is the easiest one. You can create multiple indexes on the same column and MySQL won’t complain. Let’s see this example:

mysql> alter table t add index(name);
mysql> alter table t add index(name);
…

Innodb can indexes built by sort since Innodb Plugin for MySQL 5.1 which is a lot faster than building them through insertion, especially for tables much larger than memory and large uncorrelated indexes you might be looking at 10x difference or more. Yet for some reason Innodb team has chosen to use very small (just 1MB) and hard coded buffer for this operation, which means almost any such index build operation has to use excessive sort merge passes significantly slowing down index built process.
Mark Callaghan and Facebook Team has fixed this in their tree back in early 2011 adding innodb_merge_sort_block_size variable and I was thinking this small patch will be merged to MySQL 5.5 promptly, yet it has not happen to date.

Here is example of gains you can expect (courtesy of Alexey Kopytov), using 1Mil rows Sysbench table.

Buffer Length   |  alter table sbtest add key(c)
1MB                      34 sec
8MB …

I was playing with Percona Server today and found fast_index_creation does not work quite exactly like described in the manual. The point I’m looking to make though it would be very hard to catch this problem without additional
information_schema tables we added in Percona Server.

I was doing simple ALTER TABLE such as: “alter table sbtest add z int not null;” which I expected would create table with primary key only copy the data to it and when create secondary key by sort. Looking at innodb_sys_tables and innodb_sys_indexes I discovered it is not the case:

mysql> select * from innodb_sys_tables;
+----------+--------+------------------+------+--------+-------+
| TABLE_ID | SCHEMA | NAME             | FLAG | N_COLS | SPACE | …