Hi, Jan! On Jul 10, Jan Lindström wrote:

I really do not see why you would want to define both algorithm and lock mode. In my transactional view ALGORITHM=COPY would mean EXCLUSIVE lock and INPLACE would mean SHARED lock. Is there some other lock that protects from concurrent online alter tables ? Lock NONE would mean I do not care what happens.

ALGORITHM=COPY does not mean an EXCLUSIVE lock even now. Never did. It always used TL_READ_NO_INSERT lock, which is compatible with TL_READ lock, so it's shared, in a sense. One can read the table that is being altered (yes, there was an exclusive lock for a short while too). COPY/NONE isn't very difficult to implement either. Say, you start a replication slave, copy the table there, ALTER it on the slave, wait for a slave to catch up, and swap master and a slave under an exclusive lock. While the table is being altered (on the slave) there is no lock (on the master) whatsoever. Other combinations are possible too, I can easily describe an implementation that has, say, INPLACE/EXCLUSIVE (with the, for example, archive engine). Regards, Sergei

Hi, On 8/14/2013 3:49 PM, Sergei Golubchik wrote:

Hi!

On Jul 10, Sergei Golubchik wrote:

...

In 5.5 we have ALTER ONLINE TABLE syntax, where ONLINE de facto means "without copying the data".

Now, after 5.6 merge we also have ALGORITHM=COPY|INPLACE and LOCK=NONE|SHARED|EXCLUSIVE.

I've mapped ONLINE to ALGORITHM=INPLACE, because that's what it means.

But it's confusing, one can have an "inplace" operation under an exclusive lock, if the engine wants it that way. And it won't be "online" by any stretch of imagination. One can also technically have a "copy" operation with a very permissive write-allow-write lock, and it'll be as online as one can get it.

So, I'm trying to make ONLINE to mean LOCK=NONE.

And as I've realized, there can no possibly be a truly online, LOCK=NONE ALTER TABLE operation. For example,

create table t1 (b int); alter table t1 modify b int default 5;

is not online in MyISAM. Which, kind of, makes sense, because the server have to take an exclusive metadata lock to update the table definition.

But for an InnoDB table such an operation is claimed to be online. Which is wrong, frm file still have to be updated, and an exclusive metadata lock is still needed:

I found it to be a documented limitation, although it took some browsing because the page has a rather strange name: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html Inside of it, however, hides the section "Limitations of Online DDL", which, among other things, says: "During each online DDL ALTER TABLE statement, regardless of the LOCK clause, there are brief periods at the beginning and end requiring an exclusive lock on the table (the same kind of lock specified by the LOCK=EXCLUSIVE clause). " Of course, the definition of the word "brief" might also require reconsidering. Regards, /E

create table t1 (b int) engine=myisam; alter table t1 modify b int default 5, lock=none; ^^^ error, use LOCK=EXCLUSIVE

create table t1 (b int) engine=innodb; start transaction; insert t1 values (1); -- in another connection alter table t1 modify b int default 5, lock=none; ^^^ no error. lock=none is accepted, but the statement hangs -- in yet another connection insert t1 values (2); ^^^ hangs, blocked by the alter table, that is not quite "online"

So, at best LOCK=NONE is inconsistent. But really, almost any ALTER will need an exclusive metadata lock, even if for an instant. So no ALTER TABLE can ever be really LOCK=NONE or ONLINE.

Unless we redefine what "online" mean. If not, there's no choice but stop claming an "online alter" support and remove ONLINE and LOCK=NONE from the syntax.

Opinions?

Regards, Sergei

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

HI! IMHO,as a user (not a developer) i will show my vision about 'alter'... i think the ONLINE is equal to: please don't lock my table and report "waiting table lock" at processlist for more than 1 second or users will kill me! =] hehe about algorithms i think it's something related to "using this one will expend more time, but use less disk", "this one will expend less time, but use temporary files", "this have bigger locks, this one have small locks" or something like this to help user selecting the best "online" model to use with current workload thinking again about syntax.. the "ONLINE" keyword could be removed... the ALTER TABLE METHOD=OFFLINE, method=nolock, method=shared, method=anyothers.... could do the same work, the ONLINE keyword is just a "please don't use OFFLINE algorithm" and i think we could have a new var here "default_offline_algorithm=xxxxxx" at my.cnf as a DBA, i think about use of resources and time, like any project management (cost, resource, time) I NEED online when i have some programs running queries at <1 qps or a 'soft/hard realtime' process, and i need a new index, for example, and table is very big (let's talk about >1GB) with a offline solution i can create index only when i halt all programs the online will solve many support problems to me, related to scheduled tasks. I think that's the main point, avoid scheduled tasks About internal algorithms and locks, i don't have many to say since i don't know many internal features and structures, sorry... in future i will help with internals :) good work guys! when complete this task please let'me (everybody) know via mail list :) i will test with >4GB tables in same day/week that i need new index and change some fields size