Wednesday, December 03, 2008

Recovery features for ALTER TABLE of partitioned tables

A feature which hasn't been so public about the implementation
of partitioning is the support for atomicity of many ALTER TABLE
statements using partitioned tables.

This atomicity exists for
ALTER TABLE ADD PARTITION ....
ALTER TABLE REORGANIZE PARTITION ...
ALTER TABLE DROP PARTITION ...
ALTER TABLE COALESCE PARTITION

Given that partitioning often works with very large tables it
was desirable to have a higher level of security for ALTER TABLE
of partitioned tables. To support this a DDL log was implemented.
This DDL log will in future versions be used also for many other
meta data statements. The DDL log will record all files added,
renamed and dropped during an ALTER TABLE command as above.

The design is done in such a way that the ALTER TABLE will either
fail and then all temporary files will be removed (even in the
presence of crashes of MySQL Server). Otherwise the ALTER TABLE
will succeed even if not all old files have been removed at
the time of crash. The DDL log will be checked at restart of
MySQL Server and will REDO or UNDO all necessary changes to
complete the ALTER TABLE statement.

Given that MySQL Server crashes isn't likely to happen very often
in customer environments it was also desirable to add error
injection to the MySQL Server for testing purposes.

Here is a short cut from the file sql_partition.cc that displays
what happens here:

if (write_log_drop_shadow_frm(lpt) ||
ERROR_INJECT_CRASH("crash_drop_partition_1") ||
mysql_write_frm(lpt, WFRM_WRITE_SHADOW) ||
ERROR_INJECT_CRASH("crash_drop_partition_2") ||
write_log_drop_partition(lpt) ||
ERROR_INJECT_CRASH("crash_drop_partition_3") ||

At each ERROR_INJECT_CRASH it is possible to prepare
MySQL Server such that it will crash at this point in
the next statement using dbug statements that can
be issued also as SQL statements now.

So here one can see that we first log preparatory
actions, insert a test point, continue with the
next step of ALTER TABLE, insert a new test point,
write the next log entry, insert new test point,
and so forth.

With this recovery mechanism the new ALTER TABLE
statements should not cause problems with the
partitioned table after the ALTER TABLE even in
the presence of crashes in the middle of the
ALTER TABLE statement.

4 comments:

Anonymous said...

Hi Mikael,
I have a question, and I want you to help me solve it, please. I'm starting to work with partition in Mysql. I want to know if there is any way to increase the maximum number of partitions Mysql. In addition I would like to know why this number is so small (1024).

Very thanks!!

Att

Ricardo Gomez

Anonymous said...

Hi Mikael,
I have a question, and I want you to help me solve it, please. I'm starting to work with partition in Mysql. I want to know if there is any way to increase the maximum number of partitions Mysql. In addition I would like to know why this number is so small (1024).

Att
Ricardo Gomez

Anonymous said...

I'm using a high load mysql server (5.1.30) and I have to say, I'm not impressed with the error-recovery capabilities of it...

I'm doing lots of alter-tables (add/drop partitions), and whenever my server crash (usualy during a power-spike), I'm left with leftover files that don't allow me to continue working.

Most of my problems are because of crashes during an alter-table of a memory table (though I did encounter several problems with innodb), where I'm usually left with #sql-table_name files which make the table unaccesable...

Whenever that occurs, I usually have to drop my entire database, delete the leftover files manually, and hope it'll work again...

stefan said...

I wonder if this DDL log will evolve so that one day we can have transaction support for DDL commands? That's a feature I would really appreciate.

Regards,
Stefan