Re: New online alter table interface [Re: Adding new funtions to handler interface]

Marko Mäkelä wrote:
> Martin,
>
> On Wed, Sep 19, 2007 at 04:32:22PM +0200, Martin Skold wrote:
>
>> Hi!
>>
>>

Hi!
>> The new on-line alter table interface is now in mysql-5.2 source tree, see
>> below.
>> Note also that there is a syntax extension to ALTER TABLE:
>> ALTER [ONLINE | OFFLINE] TABLE ...
>> where ALTER ONLINE will fail if the storage engine doesn't support this
>> operation on-line, and ALTER OFFLINE will always copy the table.
>>
>
> Online index creation usually means that the data in the table may be
> updated while the index is being created. InnoDB does not support that,
> but it also does not need to copy the table. It locks the table in
> share mode when creating indexes. (The table will be locked in exclusive
> mode when creating a PRIMARY KEY.)
>
> In the code excerpt that you attached, I could not find anything that
> would temporarily disable an index. In online index creation, one would
> probably want to disable the use of the index until it has been
> fully created.
>

The new interface doesn't change the semantics of the on-line add/drop index in 5.1, it just wraps it in a more generic interface. Support for disabling indexes could be added separately to this. Note that the ALTER OFFLINE is not really "offline" such in Oracle, it is just
a way to enforce the copying alter table. However, since we do support specifying
TABLESPACE in 5.1 (currently only used by Ndb) a possible extension could be
to support altering tablespaces and make them offline.

The primary goal with the new interface was to support additional on-line operations such as adding columns.
This is work in progress and more changes will need to follow (such as DDL locking and table versioning to support parallel reference to the old and new table instances).

> Also, InnoDB will have to disable a newly created index from old
> transactions, because it does not copy the history (delete-marked
> records and such) when creating an index. We asked for a virtual
> method in class handler, but it wasn't implemented in 5.1:
>
> /** Check if an index can be used by this transaction.
> * @param keynr key number to check
> * @return true if available, false if the index
> * does not contain old records that exist
> * in the read view of this transaction */
> virtual bool is_index_available(uint keynr) { return true; }
>
> Can you please implement this or a similar method in 5.2 and make the
> query optimizer use it?
>
>

I will bring this up at the on-going development meeting we currently have in Heidelberg.
On-line alter is a hot topic here.

BR
-- Martin
> Best regards,
>
> Marko Mäkelä
> Innobase Oy/Oracle Corp.
>

-- 
MySQL Internals Mailing List
For list archives: 
http://lists.mysql.com/internals
To unsubscribe:    
http://lists.mysql.com/internals?unsub=lists@pantek.com