MySQL Forums
Forum List  »  InnoDB

5-6-10 - DDL ONLINE for ALTER TABLE and temp tables
Posted by: Didier Dieudonne
Date: May 03, 2013 10:43AM

Hello all,

I have several questions about MySQL5.6-10 version and online DDL.
I have several quite big tables (several Gb) with several hundreds of INSERTs per second on them and some reads in parallel.
Problem is coming from the modification of table structure online.
I can't prevent transactions from reading or updating the data stored in theses tables when I am obliged to change the table structure (i.e. add 1 or more columns in).
With older versions of MySQL (< 5.5), creation (or deletion) of 1 or more columns implied that an exclusive lock was done on table during the ALTER command was running.
I wanted to test the Online DDL feature of MySQL 5.6.10 (with InnoDB engine 1.2.10), that seems to be improved from this point of view, allowing the updates/reads during the ALTER TABLE ADD col thanks to the option LOCK.
(http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-syntax.html)
Some quick tests (add 1 simple varchar(2) column at the end of table) on a small table effectively confirm that an insert can be performed while the ALTER command is still processing.
Some other tests on a bigger table of 5 Gb were more difficult.
But, it seems I have a problem with this feature:
1) ALTER TABLE creates a temporary table in the same directory as the original table (http://dev.mysql.com/doc/refman/5.6/en/temporary-files.html).
While the documentation says that tempo tables created by ALTER TABLE are in the same directory that original table, if tmpdir variable is not set in the my.cnf (so the server uses /tmp as default underUnix), the ALTER TABLE command aborts after several seconds with an error saying that my table is full. In fact, my file system /tmp is too small (512Mb) and can't contain what I think to be the copy of the changed table or part of it. If I use a bigger file system by specifying it in the tmpdir variable, The ALTER TABLE finishes OK.
I would conclude that the documentation does not reflect the reality, else what is created in the temporary area tmpdir ?
I even added the option ALGORITHM=INPLACE to be sure , but result is the same.
2) For question of performances, we wanted to install this tempo area on RAMDisk. But, if all my tempo tables created by ALTER TABLE also go on RAM Disk, this will not be very efficient and we have a risk to saturate the RAMDisk
Is there a possibility to differenciate tempo zones used by ALTER TABLE than those used for other operations?

If you have any suggestions ... don't hesitate !
Thanks in advance.
DD

Options: ReplyQuote


Subject
Views
Written By
Posted
5-6-10 - DDL ONLINE for ALTER TABLE and temp tables
2499
May 03, 2013 10:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.