Hot schema change with TokuDB

Hot schema change with TokuDB

Hot schema change refers to the ability to change a table schema without rebuilding the table. TokuDB implements many hot schema change operations by using fractal tree broadcast update messages and deferred execution of their update functions. Hot schema change with TokuDB occurs when alter table SQL statements are executed. MySQL's executes an alter table operation with various phases, each phase running with a selected concurrency level. Alter table operations that TokuDB can not support cause MySQL to rebuild the table.

Alter table phases

Alter table has 4 phases: check if the alter operation is supported, prepare to do the alter operation, do the alter operation, and commit the alter operation. The check phase supports index addition, index deletion, column addition, column deletion, column expansion, and some other simple operations. To simplify the code, only one alter operation at a time is supported. The check phase sets the locking level needed later.

Alter table assumptions

TokuDB assumes that the table will be reopened after the alter table operation completes.

Alter table locking

Mapping of alter table lock levels to MDL levels for the various alter table phases.

	XLOCK	SLOCK	SLOCK AFTER PREPARE	NOLOCK AFTER PREPARE	NOLOCK
check	U	U	U	U	U
prepare	X	SNW	X	X	U
alter	X	SNW	SNW	U	U
commit	X	X	X	X	X

Index addition

• Check if the index can be added. If yes, then run the alter phase with MDL SNW (allows reads) or MDL U (allows reads and writes).
• The prepare phase does nothing.
• The alter phase builds the new index using the bulk loader or the indexer.
• The commit action relies on the alter transaction being committed or aborted. If the alter is NOT committed, then the new index is closed and deleted.

Index deletion

• Check if the index can be deleted. If yes, then run the alter phase with MDL X.
• The prepare action does nothing.
• The alter action transactionally updates the table status and deletes the index fractal tree.
• The commit action relies on the alter transaction being committed or aborted. If the alter is NOT committed, then the index is reopened.

Column addition

• Check if the column can be added. If yes, then run the alter phase with MDL X.
• The prepare action does nothing.
• The alter action generates a row mutator function that will add the new column to the old row. An update broadcast message is transactionally sent to the fractal tree with the row mutator function. The row mutator is executed when the update message is applied to a leaf entry.
• The commit action relies on the alter transaction being committed or aborted.

Column deletion

• Check if the column can be deleted. If yes, then run the alter phase with MDL X.
• The prepare action does nothing.
• The alter action generates row mutator function that will delete the column from the old rows. An update broadcast message is transactionally sent to the fractal tree with the row mutator function. The row mutator is executed when the update message is applied to a leaf entry.
• The commit action relies on the alter transaction being committed or aborted.

Column expansion

• Check if the column type expansion is supported. If yes, then run the alter phase with MDL X.
• The prepare action does nothing.
• The alter action generates a row mutator function that will expand the columns in the old rows. An update broadcast message is transactionally sent to the fractal trees with the column expansion row mutator function. The row mutator function is executed when it is applied to a leaf entry.
• The commit action relies on the alter transaction being committed or aborted.

Column expansions supported

• char and binary expansion is supported
• varchar and varbinary expansion is supported
• integer and unsigned integer expansion is supported
• text and blob expansion is supported
• change null is not supported
• change default value is supported
• shrink is not supported
• multiple column alters is not supported

Rename a column

Column rename is an FRM only change which TokuDB alter table detects and supports. The action is to do nothing.

Alter table metadata

How does one change the compression, node size, basement node size, and fanout for the table’s indexes?