RonDB training · module 05 of 25 · 14 min

Changing schema online

Which ALTER TABLE statements keep the table open for reads and writes, which ones copy it, and how the cluster itself grows.

Schema changes · the design goal

Schema changes are online, but not fast

Online

Reads and writes continue while most schema changes run. Availability comes first.

Not fast

Each step is logged so it can roll forward or back: a few tables per second. Slower with global replication on.

A server for DDL

The MySQL server running an ALTER TABLE blocks writes to that table; other MySQL servers do not. Run DDL from one that serves no application traffic.

In a feature store, schema changes can be a daily activity. They are not performance critical in themselves: what matters is that reads and writes keep running.

docs: alter_table.md (Meta Data Changes, Create Table) · rondb_spec_mysql.md (a MySQL server for metadata operations)

ALTER TABLE · two algorithms

ALTER TABLE: inplace changes the table where it is; copy builds a new one

ALGORITHM=INPLACE · online

  • Changes metadata, and builds an index or moves rows if needed.
  • Reads and writes from every API continue.
  • Fails at once if the change cannot be done inplace.

ALGORITHM=COPY · offline

  • Creates a new table, copies every row, swaps the names, drops the old one.
  • Writes from other servers during the copy make it fail.
  • Needs the table to be quiet: single user mode.

Set ndb_allow_copying_alter_table=OFF (default ON) and an ALTER that would copy fails instead, unless it says ALGORITHM=COPY.

docs: alter_table.md (Offline Operations) · code: ha_ndbcluster.cc (allow_copying_alter_table, is_copying_alter_table_allowed, Copying_alter::check_saved_commit_count) · mysql-test/suite/ndb/t/ndb_alter_table_copy_check.test

ALTER TABLE · in RonDB 26.10

ALTER TABLE: what runs inplace, and what needs a copy

Inplace · online
CREATE or DROP INDEX, one per statement
ADD or DROP UNIQUE INDEX
ADD COLUMN, with the rules on the next slide
RENAME COLUMN, on its own
ADD or DROP FOREIGN KEY
More partitions, REORGANIZE PARTITION
NDB_TABLE options such as READ_BACKUP
Copy · offline
DROP COLUMN, or a new column order
ADD or DROP PRIMARY KEY
New type, size, NULL or default of a column
New character set
Column from memory to disk or back
Fewer partitions; new TABLESPACE or ROW_FORMAT
TRUNCATE: drop and create

code: ha_ndbcluster.cc (check_inplace_alter_supported: supported flags and every inplace_unsupported reason) · mysql-test/suite/ndb/r/ndb_alter_table_online.result · docs: alter_table.md (Operations that require offline operations)

ADD COLUMN · four rules

ADD COLUMN: online when the new column starts as NULL everywhere

mysql real output · ndb_alter_table_online
ALTER TABLE t1 algorithm=inplace, ADD c TIMESTAMP;
-- table id unchanged: altered in place
ALTER TABLE t1 algorithm=inplace, ADD c CHAR(19) DEFAULT 17;
ERROR 0A000: ALGORITHM=INPLACE is not supported.
Reason: Adding column with non-null default
value is not supported online. Try ALGORITHM=COPY.
ALTER TABLE t1 algorithm=inplace, ADD d INT AFTER b;
ERROR 0A000: ALGORITHM=INPLACE is not supported.
Reason: Altering column order. Try ALGORITHM=COPY.
  • Nullable, no default other than NULL, no AUTO_INCREMENT or CURRENT_TIMESTAMP default.
  • Added at the end, and nothing else in the statement.
  • The table has its own primary key, not the hidden one.
  • Not on a table created with ROW_FORMAT=FIXED.

Memory and disk columns both work. Anything else takes a copying ALTER TABLE.

code: mysql-test/suite/ndb/r/ndb_alter_table_online.result (lines picked; the dimmed line summarises its table id check) · ha_ndbcluster.cc (check_inplace_alter_supported) · ndb_alter_table_online_disk.result

Row format · the dynamic part

A new column goes into the dynamic part, where NULL takes no space

fixed part fixed-size columns, at their full size+ reference to the variable part variable partvariable-size columns2 bytes of offset each dynamic partonly values that are not NULL c · added online · absent means NULL ROW_FORMAT=FIXED drops the reference to the variable part: less memory, but no place for c

Fixed-size columns take their full size; adding one would change every row. That needs a copy.

A column added online goes into the dynamic part. Every existing row already reads it as NULL, so only metadata changes.

docs: alter_table.md (Add Column) · code: ha_ndbcluster.cc (ROW_FORMAT=FIXED turns off the varpart reference; inplace-added columns are DYNAMIC; ndb_default_column_format FIXED)

CREATE INDEX · a scan in row id order, plus triggers

CREATE INDEX: an ordered index is built while the table takes writes

Rows in row id order build scan → in the indexnot yet update behind the scan: a trigger fixes the index update ahead: the scan indexes the new value

The build scans the table in row id order and inserts each row into the new index.

Triggers on insert, update and delete keep the part already built up to date.

Rows ahead of the scan need nothing: the scan reads their latest value when it gets there. Users only see the extra CPU and memory.

docs: alter_table.md (Create Ordered Index)

Online index work · what else it builds

Unique indexes and foreign keys are built online too

Unique index

A hidden table with a hash index, filled from the base table, kept in sync by triggers.

Foreign key

Needs an index on the child columns. Creating it scans the child to check existing rows; triggers check every later change.

At every restart

No index is on disk. A restarting node fills its hash indexes as it inserts the rows of its local checkpoint, then rebuilds the ordered indexes from the rows.

Dropping an index releases its memory; a drop waits up to 5 seconds for scans still using it. ndb_restore builds indexes offline, which is faster.

docs: alter_table.md (Create Unique Index, Create/Drop Foreign Key, Create/Drop Ordered Index)

PART 1

Growing the cluster

New node groups, then every table spread onto them, all online.

More data nodes · a whole node group at a time

Four steps to add a node group

  1. Add the new data nodes to config.ini with NodeGroup=65536: known to the cluster, holding no data.
  2. Start them. Their first start creates tablespaces and logfile groups, which can take a while.
  3. In ndb_mgm: CREATE NODEGROUP 3,4. A quick schema transaction; new tables use the new node group at once.
  4. For each existing table: ALTER TABLE t ALGORITHM=INPLACE, REORGANIZE PARTITION;

With 2 replicas, add 2 nodes at a time. An empty node group can be dropped again. Module 18 walks through it on a running cluster.

docs: alter_table.md (Add Node, Add Node Group, Reorganise existing tables)

REORGANIZE PARTITION · copy, write twice, switch, delete

REORGANIZE PARTITION: half the buckets move while the table stays open

node group 0 · existing P0960 buckets480 buckets P1960 buckets480 buckets P2960 buckets480 buckets P3960 buckets480 buckets node group 1 · new P4480 buckets P5480 buckets P6480 buckets P7480 buckets 1 · new partitions; a copy scan copies each row whose bucket moves 2 · meanwhile, every write to a moving row goes to both places 3 · the hashmap switches: P0 to P3 keep 480 buckets each; writes stop going to both places 4 · a delete scan removes the moved rows from P0 to P3

The hashmap's 3840 buckets sit 960 to a partition on 4 partitions (module 02).

New partitions appear in the new node group; a scan of each old partition copies the rows whose bucket now maps there.

During the move some rows exist in two partitions, and transactions keep both up to date.

The hashmap switches: P4 to P7 now own the moved buckets, P0 to P3 keep 480 each. Once scans started before the switch finish, each write goes to one partition again.

A second scan deletes what moved. No existing node needed memory for more rows than it had.

docs: alter_table.md (Reorganise existing tables) · code: dbdih/DbdihMain.cpp (ALTER TABLE REORG) · dbdict/Dbdict.cpp (alterTable_subOps)

REORGANIZE PARTITION · when it is safe

Reorganise after adding node groups; otherwise check memory first

Safe

  • After adding node groups: the new rows go to empty nodes.
  • Fully replicated tables: the new node group just becomes more replicas, nothing is deleted.

Take care, or not possible

  • More partitions on the same nodes needs memory for both copies during the move; out of memory rolls the change back.
  • Never fewer partitions online.
  • Not for tables with explicit PARTITIONS n or MAX_ROWS.

docs: alter_table.md (Reorganise Table) · table_options.md (Setting explicit number of partitions) · code: ha_ndbcluster.cc ("REORGANIZE of table with MAX_ROWS", "Online reduction in number of fragments not supported")

PART 2

Offline changes, done safely

When a copy is unavoidable.

Copying ALTER TABLE · keep everyone else out

Single user mode lets one API node work alone

# in ndb_mgm
ENTER SINGLE USER MODE 55

# on the MySQL server with node id 55
ALTER TABLE t1 ALGORITHM=COPY,
  MODIFY b BIGINT;

# in ndb_mgm
EXIT SINGLE USER MODE
  • RonDB cannot lock a table across the whole cluster; single user mode is the safe way.
  • Start that MySQL server with a fixed node id, so you know which one to name.
  • If only MySQL servers write the table, LOCK TABLES t1 READ on every one of them also works.

docs: alter_table.md (Offline Operations, Single User Mode)

Zero downtime · for offline changes too

With two clusters, even a copying change stays online for the application

  1. Two clusters replicate to each other; the application uses cluster A.
  2. Back up cluster B and take it offline; upgrade its software.
  3. Restore the metadata with the changes: new types and sizes, dropped columns, new columns and indexes.
  4. Restore the data, then catch up from A's binary log. Move the application to B.
  5. Repeat with cluster A.

docs: alter_table.md (Online Solutions to Offline Operations) · modules 17 (backup and restore) and 24 (global replication)

Foreign keys · checked in the data nodes

Foreign keys hold for every API, not only SQL

ActionWhat happens
RESTRICTChecked at once: change the child rows before the parent.
NO ACTION · the defaultChecked at commit if ON DELETE and ON UPDATE both say it: the order inside the transaction does not matter. InnoDB treats it as RESTRICT.
CASCADEOne delete can delete without limit, and grow one transaction to any size.
SET NULLCan update many children, but does not cascade further.

ON UPDATE CASCADE needs a unique key in the parent: a primary key is never updated in RonDB, the row is deleted and inserted.

docs: foreign_keys.md · code: storage/ndb/plugin/ha_ndb_ddl_fk.cc (no ON clause gives NO ACTION) · dbdict/Dbdict.cpp (createFK_toCreateTrigger: deferred triggers when no action is set) · mysql-test/suite/ndb/r/ndb_fk_alter.result

foreign_key_checks · for one session

foreign_key_checks=0 turns foreign keys off for the session

What it does

  • Writes from that session fire no foreign key triggers: no checks, no cascades.
  • A foreign key created meanwhile is not checked against existing rows.
  • A child table can be created before its parent: RonDB puts a mock parent table in its place.

When to use it

  • Loading data in any order, as a mysqldump file does. The data nodes trust you meanwhile.

docs: foreign_keys.md · code: ha_ndbcluster.cc (OPTION_NO_FOREIGN_KEY_CHECKS → OO_DISABLE_FK) · dbtup/DbtupTrigger.cpp (executeTrigger skips FK triggers) · storage/ndb/plugin/ha_ndb_ddl_fk.cc (CreateFK_NoVerify, mock parent tables) · mysql-test/suite/ndb/r/ndb_fk_disable.result

Checklist · before a schema change in production

Checklist: eight habits for schema changes

  1. Run DDL from a MySQL server set aside for it.
  2. Write ALGORITHM=INPLACE, so a copy fails instead of starting.
  3. Or set ndb_allow_copying_alter_table=OFF.
  4. Add columns as nullable, at the end, alone.
  5. One index per statement.
  6. Avoid ROW_FORMAT=FIXED on tables that will grow columns.
  7. Schedule schema changes away from backups and node restarts.
  8. Copying changes only in single user mode.

docs: alter_table.md · rondb_schema_transaction.md · code: ha_ndbcluster.cc

Recap · module 05

What to take away

  • Schema changes are online but slow: a few tables per second.
  • Inplace: indexes, foreign keys, nullable columns at the end, more partitions.
  • Copy: dropping or changing columns, primary keys, fewer partitions. Use single user mode.
  • Grow with CREATE NODEGROUP, then REORGANIZE PARTITION on each table.
  • Foreign keys are enforced in the data nodes, for every API.

Next · 06 · Blocks, signals and the virtual machine

RonDB training · 25 modules · you are at 05

Next · 06 · Blocks and signals