1

I am having a problem with MySQL.

I have a large table (8.5 million records) and I have executed an alter table add index command. When I do that it seems like all the tables are locked.

When I did a list processlist I can see that all the processes are in sleep stage and the alter is running.

Why would this lock up all the table in the database? I start getting timeout errors.

Is it a configuration error at my end? what do I need to do so this won't happen again?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Mike
  • 2,735
  • 11
  • 44
  • 68
  • There's a pretty current explanation of locking with MySQL Data Definition Language changes [in this Oracle blog](https://blogs.oracle.com/mysqlinnodb/entry/online_alter_table_in_mysql). You should probably specify which version and storage engine you are using. – hardmath Jun 10 '13 at 18:44
  • Although [this similar Question was asked here long ago](http://stackoverflow.com/questions/463677/alter-table-without-locking-the-table), it continues to receive new Answers off and on. Worth inspecting for ideas... – hardmath Jun 10 '13 at 18:51
  • hardmath. Thank you for your responce. I still don't see the solution for this problem. it is something the MySQL does not do a good job or? – Mike Jun 10 '13 at 19:33
  • Hi, Mike. An `alter table` command is changing the DDL of the affected table, so most DBMS will obtain an exclusive lock on the table in order perform such a change. For MySQL it would be helpful to know the version and storage engine (e.g. InnoDB) you are using. – hardmath Jun 10 '13 at 20:17
  • I am using innoDB for storage engine. innodb_version 1.2.10, version 5.6.10-log version_comment MySQL Community Server (GPL) version_compile_machine x86_64 version_compile_os Win64 – Mike Jun 10 '13 at 20:44

1 Answers1

1

Perhaps the solution is as simple as adding the ALGORITHM = INPLACE clause to your ALTER TABLE...ADD INDEX command.

The introduction last year of Online ALTER TABLE in MySQL 5.6 was aimed at minimizing the sort of impacts you report, where such DDL commands can cause delays and timeouts to other processes.

Subject to certain limitations which likely don't affect you, the period of time required for an exclusive lock on the table being changed can now be reduced to two "brief periods at the beginning and end".

An Overview of Online DDL sketches some of the history and mechanics of creating indexes (or dropping them) without the time-consuming table copying used in some previous versions.

The detailed syntax for ALTER TABLE shows where ALGORITHM = INPLACE falls in the scheme of this command. The above linked Overview has a Summary Grid for Online Status of DDL Operations. Although ALTER TABLE syntax allows you to specify LOCK=NONE (or LOCK=SHARED), by default MySQL 5.6 will attempt to use this mode for an online operation.

hardmath
  • 8,753
  • 2
  • 37
  • 65