Details

Type: Task
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Fix Version/s: 11.2.1
Component/s: Data Definition - Alter Table
Labels:

Description

Implement online ALTER TABLE above the storage engine layer by mimicking what InnoDB does since MariaDB 10.0.

Intro

ALTER TABLE can perform many various table metadata alterations, individually or batched (many alterations at once). It supports different algorithms for applying those alterations and different lock levels restricting access to the table while it's being altered. What algorithm and lock level to use depends on the storage engine, requested alterations and explicitly specified algorithm and lock, if any. If no algorithm or lock level is explicitly specified, the server is supposed to select the best algorithm/lock combination automatically.

While certain alterations (like adding a column) can be done by certain storage engines (like InnoDB) internally (using InnoDB-specific ALGORITHM=INSTANT) and without locking the table (LOCK=NONE), the most universal ALTER TABLE algorithm that supports arbitrary alterations in arbitrary combinations is the COPY algorithm and it locks the table, allowing only read access during the whole ALTER TABLE duration. When the server has to resort to the COPY algorithm (because no other one can perform the requested set of alterations) it often means long periods of the application being essentially down, because the table cannot be written into.

The goal of this task is to allow the COPY algorithm to work without read-locking the table. In other words, this should make the combination ALGORITHM=COPY, LOCK=NONE possible.

Implementation

The COPY algorithm for ALTER ONLINE TABLE is supposed to do the following:

Exclusively acquire the table Metadata Lock (MDL).
Acquire the table lock for read (TL_READ)
Read the first record. In table is empty, online is skipped (goto 11).
Set up (a separate, per-table one) row-based replication for tracking changes from concurrent DMLs ("online changes").
Downgrade the MDL lock.
Copy the table contents (using a non-locking read if supported by the storage engine).
Apply the online changes from the replicated contents.
Unlock the table lock
Exclusively lock the table MDL (upgrade to MDL_SHARED_WRITE).
Apply any remaining online changes.
Swap the old and new table, unlock, drop the old table.

This would remove some limitations that currently exist with the InnoDB-only online table rebuild. Basically, anything that is supported by ALGORITHM=COPY should 'just work' (however see the limitations section). The bulk copying could still happen in copy_data_between_tables(). A few examples:

Arbitrary changes of column type will be possible, without duplicating any conversion logic.
It will be possible to add virtual columns (materialized or not) together with adding indexes, while allowing concurrent writes (~~MDEV-13795~~, ~~MDEV-14332~~).
The ENGINE or the partitioning of a table can be changed, just like any other attribute.

[Not implemented here] We should remove the online table rebuild code from InnoDB (row_log_table_apply() and friends), and just let InnoDB fall back to this. The only ALTER ONLINE TABLE that could better be implemented inside storage engines would be ADD INDEX. Then, ALGORITHM=INPLACE would no longer be misleading, because it would mean exactly the same as the ALGORITHM=NOCOPY that was introduced in ~~MDEV-13134~~. Before this, we must implement ~~MDEV-515~~ (bulk load into an empty InnoDB table) to avoid a performance regression.

Behavior of different engines

The per-engine behavior depends on what operations can happen concurrently while TL_READ is held.

Innodb can do any DML (except TRUNCATE i presume). It lazily opens the read view once the first record is read during the copy stage. This means that in theory some transaction can slip concurrently between TL_READ-locked table and first record is read. This is why we first read one record out, and then set up the online change buffer.
Myisam/Aria only allow inserts in parallel with reads: The last table's record offset is remembered for the table handle, so copy stage will read out only the changes, that are already there. Other DMLs will be blocked until table lock is released.
Online is disabled for temporary tables.
For other engines, it depends on whether is it possible to acquire a particular table lock in parallel with TL_READ.

Limitations

Embedded server doesn't support LOCK=NONE, Until HAVE_REPLICATION is enabled there (or until some finer refactoring).
DROP SYSTEM VERSIONING is not currently supported, but the support can be added on demand
ALTER TABLE ... ORDER BY is not and cannot be supported
Tables which are referenced by FOREIGN KEYs with CASCADE operations, see ~~MDEV-29068~~
ALTER IGNORE TABLE
Adding autoinc to the existing column, when NO_AUTO_VALUE_ON_ZERO is not present, and there were no unchanged UNIQUE NOT NULL keys. A NULL column is always impossible to update to AUTOINC with Online COPY.
Sequences are not supported
ADD COLUMN ... AUTO_INCREMENT and ADD COLUMN ... DEFAULT(NEXTVAL(..))
MODIFY ... NOT NULL DEFAULT(NEXTVAL(..)), if the column initially was NULLable
Sequences
Engines S3 and CONNECT

[Old part] Challenges

We should replicate the online rebuild on slaves in parallel, so that the master and slaves will be able to commit at roughly the same time. This would be something similar to ~~MDEV-11675~~, which would still be needed for native online ADD INDEX, which would avoid copying the table.

In InnoDB, there is some logic for logging the changes when the PRIMARY KEY columns are changed, or a PRIMARY KEY is being added. The 'row event log' online_log will additionally contain the PRIMARY KEY values in the new table, so that the records can easily be found. The online_log will contain INSERT, UPDATE, and DELETE events.

We will need some interface from ROLLBACK inside the storage engine to the 'row event log', so that BEGIN; INSERT; ROLLBACK will also create a DELETE event. Similarly, we will need an interface that allows CASCADE or SET NULL operations from FOREIGN KEY constraints to be relayed to the 'row event log'.

Starting with MariaDB 10.2, there is an optimization that avoids unnecessarily sorting the data by PRIMARY KEY when the sorting does not change. Search for skip_pk_sort. It would be nice if the future ~~MDEV-515~~ code inside InnoDB could be informed of this, so that it can assume that the data is already sorted by PRIMARY KEY.

If there exist FOREIGN KEY constraints on the being-rebuilt table, then this approach should work just as fine as the current online table rebuild in InnoDB: The constraints would be enforced on the old copy of the table until the very end where we switch the tables, and from that point on, on the new copy of the table.

Initially, we could disable ONLINE...ADD FOREIGN KEY. That could be easier to implement after moving the FOREIGN KEY processing from InnoDB to the SQL layer.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

output.txt
343 kB
2024-01-11 21:44
Remove-InnoDB-online-table-rebuild.patch
133 kB
2018-11-10 12:51

Issue Links

causes

MCOL-5603 Online alter can break data consistency

Confirmed

MDEV-28771 Assertion `table->in_use && tdc->flushed' failed after ALTER

Closed

MDEV-28816 Assertion `wsrep_thd_is_applying(thd)' failed in int wsrep_ignored_error_code(Log_event*, int)

Closed

MDEV-28825 Server crash in binlog_online_alter_end_trans

Closed

MDEV-28930 ALTER TABLE Deadlocks with parallel TL_WRITE

Closed

MDEV-28943 Online alter fails under LOCK TABLE with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON

Closed

MDEV-28944 XA assertions failing in binlog_rollback and binlog_commit

Closed

MDEV-28949 Deadlock between online alter and DML

Closed

MDEV-28959 Online alter ignores strict table mode

Closed

MDEV-28967 Assertion `marked_for_write_or_computed()' failed in Field_new_decimal::store_value / online_alter_read_from_binlog

Closed

MDEV-29013 ER_KEY_NOT_FOUND/lock timeout upon online alter with long unique indexes

Closed

MDEV-29038 XA assertions failing in binlog_rollback and binlog_commit #2

Closed

MDEV-29056 Replica SQL thread stops with 1846 error on ALTER ONLINE after LOCK WRITE

Closed

MDEV-29067 Online alter ignores check constraint violation

Closed

MDEV-29068 Cascade foreign key updates do not apply in online alter

Closed

MDEV-29069 ER_KEY_NOT_FOUND upon concurrent online auto-increment addition and DELETE

Closed

MDEV-29506 Assertion `!table->pos_in_locked_tables' failed in tc_release_table

Closed

MDEV-30891 Assertion `!table->versioned(VERS_TRX_ID)' failed in Write_rows_log_event::binlog_row_logging_function

Closed

MDEV-30902 Server crash in LEX::first_lists_tables_same

Closed

MDEV-30924 Server crashes in MYSQL_LOG::is_open upon ALTER vs FUNCTION

Closed

MDEV-30925 Assertion `share->now_transactional' failed in translog_write_record / online_alter_read_from_binlog

Closed

MDEV-30945 RPL tests are failing with MSAN use-of-uninitialized-value in bitmap_intersect

Closed

MDEV-30949 Direct leak in my_register_filename / binlog_online_alter_end_trans

Closed

MDEV-30984 Online ALTER table is denied with non-informative error messages

Closed

MDEV-30987 main.alter_table_online times out with view-protocol

Closed

MDEV-31033 ER_KEY_NOT_FOUND upon online COPY ALTER on a partitioned table

Closed

MDEV-31040 Server crashes in MYSQL_LOG::is_open upon ALTER on partitioned table

Closed

MDEV-31043 ER_KEY_NOT_FOUND upon concurrent ALTER and transaction

Closed

MDEV-31058 ER_KEY_NOT_FOUND upon concurrent CHANGE column to autoinc and DML

Closed

MDEV-31059 "Slave SQL" errors upon concurrent DML and erroneous ALTER

Closed

MDEV-31128 Server crashes in Rows_log_event::find_row upon concurrent DML and ALTER

Closed

MDEV-31136 Online ALTER is allowed on master but fails on slave with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON

Closed

MDEV-31172 Server crash or ASAN errors in online_alter_check_autoinc

Closed

MDEV-31563 Server crashes in MDL_ticket::downgrade_lock

Closed

MDEV-31601 Some ALTER TABLE .. fail when they worked before, and with a wrong error message

Closed

MDEV-31624 Online ALTER fails due to intermediate DML which is later overridden

Open

MDEV-31631 Online ALTER adding auto-increment column to a table with history behaves differently from non-online

Closed

MDEV-31646 Online alter applies binlog cache limit to cache writes

Closed

MDEV-31677 Assertion `bitmap_is_set(cols, i)' failed upon online ALTER with binlog_row_image=NOBLOB

Closed

MDEV-31755 Replica's DML event deadlocks with online alter table

Closed

MDEV-31775 Server crash in Rows_log_event::update_sequence upon online alter on sequence

Closed

MDEV-31776 Online ALTER reports the number of affected rows incorrectly

Closed

MDEV-31777 ER_GET_ERRNO upon online alter with concurrent DML on CONNECT table

Closed

MDEV-31781 ALTER TABLE ENGINE=s3 fails

Closed

MDEV-31804 Assertion `thd->m_transaction_psi == __null' fails upon replicating online ALTER

Closed

MDEV-31812 Add switch to old_mode to disable non-locking ALTER

Closed

MDEV-31838 Assertion fails in binlog_log_row_online_alter upon parallel replication with two-phase alter and MINIMAL binlog_row_image

Closed

MDEV-31906 Processlist shows stage and max stage 0 during online alter

Open

MDEV-32126 Assertion `!writer.checksum_len || writer.remains == 0' fails upon concurrent online ALTER and transactions with failing statements and binary log enabled

Closed

MDEV-32444 Data from orphaned XA transaction is lost after online alter

Closed

MDEV-32510 ASAN use-after-poison in online alter with rocksdb under SERIALIZABLE isolation level

Open

MDEV-33094 row-by-row logging needs to be disabled for the online ALTER log application stage

Open

MDEV-33330 Server crash or assertion failure in binlog_get_pending_rows_event

Closed

includes

MDEV-27986 Galera testing of ALTER ONLINE TABLE

Closed

is blocked by

MDEV-30985 Replica stops with error on ALTER ONLINE with Geometry Types

Closed

relates to

MDEV-9260 Improve progress report on on-line alter table

Open

MDEV-12512 Accurately report progress for ALTER TABLE...ALGORITHM=INPLACE

Open

MDEV-15250 UPSERT during ALTER-TABLE results in 'Duplicate entry' error for alter

Closed

MDEV-16354 Allow ALGORITHM=INPLACE for ADD COLUMN…PERSISTENT AS

Open

MDEV-18845 Introduce alter_lock to allow refusing non-online ALTER TABLE

Open

MDEV-28942 Online alter does not support ORDER BY

Closed

MDEV-28966 Assertion `row_data' failed in unpack_row / online_alter_read_from_binlog

Closed

MDEV-29007 Assertion `marked_for_write_or_computed()' failed upon online ADD COLUMN .. FIRST

Closed

MDEV-30985 Replica stops with error on ALTER ONLINE with Geometry Types

Closed

MDEV-515 innodb bulk insert

Closed

MDEV-10453 Allow ONLINE or ALGORITHM=INPLACE with OPTIMIZE TABLE

Open

MDEV-11675 Lag Free Alter On Slave

Closed

MDEV-13795 ALTER TABLE…DROP PRIMARY KEY, ADD PRIMARY KEY fails when VIRTUAL columns exist

Closed

MDEV-14332 Corruption during online table-rebuilding ALTER when VIRTUAL columns exist, causes assertion failure

Closed

MDEV-15471 Assertion `new_clustered == ctx->need_rebuild()' failed in ha_innobase::commit_inplace_alter_table

Stalled

MDEV-15641 InnoDB crash while committing table-rebuilding ALTER TABLE

Closed

MDEV-18127 Handling DuplicateKey error while doing Online DDL

Open

MDEV-28774 Assertion `table->in_use && tdc->flushed' failed in bool TABLE_SHARE::visit_subgraph(Wait_for_flush*, MDL_wait_for_graph_visitor*)

Closed

split to

MDEV-31942 Online alter: support cascade foreign keys

Open

links to

Bug #77097 InnoDB Online DDL should support change data type

Bug #98600 Optimize table fails with duplicate entry on UNIQUE KEY

mentioned in: Page Loading...; Page Loading...

(48 causes, 1 includes, 1 is blocked by, 18 relates to, 1 split to, 2 links to, 2 mentioned in)

Sub-Tasks

1.	Test MDEV-16329 (ALTER ONLINE TABLE) - Core server part	Closed	Elena Stepanova
2.	Test MDEV-16329 (ALTER ONLINE TABLE) - Replication part	Closed	Angelique Sklavounos (Inactive)
3.	Test MDEV-16329 (ALTER ONLINE TABLE) - Galera part	Closed	Ramesh Sivaraman

Activity

Ascending order - Click to sort in descending order

View 37 older comments

BJ Quinn added a comment - 2023-12-20 21:54

Thanks! Do you think that's what's causing the alternating active/inactive cycles, or is it something that's affecting the overall efficiency of the ALTER?

BJ Quinn added a comment - 2023-12-20 21:54 Thanks! Do you think that's what's causing the alternating active/inactive cycles, or is it something that's affecting the overall efficiency of the ALTER?

Marko Mäkelä added a comment - 2023-12-21 07:47

MDEV-33094 was filed for further optimizing the online log application. It is currently writing undo log records inside InnoDB, for no good reason.

bjquinn, I do not have any idea what could be causing the active/inactive cycles. Would it be possible to collect stack traces of all threads (attach a debugger to the running process) while the system is inactive? (Or just something like http://poormansprofiler.org once per second?) Also, a system profiler like perf or offcputime could be helpful, but the latter is tricky because you’d typically need all code to be compiled with -fno-omit-frame-pointer in order to get meaningful stack traces (because the stack unwinder in the Linux kernel requires frame pointers; see 1234). Back in September, I successfully used offcputime in ~~MDEV-32050~~ to identify one bottleneck that I was completely unaware of.

Marko Mäkelä added a comment - 2023-12-21 07:47 MDEV-33094 was filed for further optimizing the online log application. It is currently writing undo log records inside InnoDB, for no good reason. bjquinn , I do not have any idea what could be causing the active/inactive cycles. Would it be possible to collect stack traces of all threads (attach a debugger to the running process) while the system is inactive? (Or just something like http://poormansprofiler.org once per second?) Also, a system profiler like perf or offcputime could be helpful, but the latter is tricky because you’d typically need all code to be compiled with -fno-omit-frame-pointer in order to get meaningful stack traces (because the stack unwinder in the Linux kernel requires frame pointers; see 1234 ). Back in September, I successfully used offcputime in MDEV-32050 to identify one bottleneck that I was completely unaware of.

BJ Quinn added a comment - 2023-12-21 17:01 - edited

Thanks Marko. I should be able to set it up to capture the stack traces, this system is not yet in production so I should be able to do whatever is necessary. I'll try to get that to you soon.

Stéphane also had a good suggestion to test mysql -e ’’select * from bigtable’ > /dev/null and see if I get a similar active/inactive cycle. I did not, but it does settle on about 65% CPU usage over time after starting at 100% CPU usage. Disks are 10% to 25% busy, so I don't think that's the bottleneck here.

EDIT: I'm going to be out of town the next couple of weeks so some of this data might be delayed.

BJ Quinn added a comment - 2023-12-21 17:01 - edited Thanks Marko. I should be able to set it up to capture the stack traces, this system is not yet in production so I should be able to do whatever is necessary. I'll try to get that to you soon. Stéphane also had a good suggestion to test mysql -e ’’select * from bigtable’ > /dev/null and see if I get a similar active/inactive cycle. I did not, but it does settle on about 65% CPU usage over time after starting at 100% CPU usage. Disks are 10% to 25% busy, so I don't think that's the bottleneck here. EDIT: I'm going to be out of town the next couple of weeks so some of this data might be delayed.

BJ Quinn added a comment - 2024-01-11 21:44

Marko, attached is the result of poormansprofiler.org, ran once a second while the CPU was idle. Please let me know if this is helpful or you need me to collect this data any differently.

One thing I noticed that Stephane pointed out was that it got better, at least early on, if innodb_log_file_size was set larger, but as the table I'm testing with is much larger than I can reasonably set innodb_log_file_size to, the issue recurs after a while anyway. But it may be related.

Thanks!! output.txt

BJ Quinn added a comment - 2024-01-11 21:44 Marko, attached is the result of poormansprofiler.org, ran once a second while the CPU was idle. Please let me know if this is helpful or you need me to collect this data any differently. One thing I noticed that Stephane pointed out was that it got better, at least early on, if innodb_log_file_size was set larger, but as the table I'm testing with is much larger than I can reasonably set innodb_log_file_size to, the issue recurs after a while anyway. But it may be related. Thanks!! output.txt

BJ Quinn added a comment - 2024-01-17 20:44

Marko, please disregard. This may have ended up being a hardware problem that was affecting multiple identical servers. A firmware bug in our SSDs. In case anyone is interested, here was the problem and apparent solution – https://forum-proxmox-com.translate.goog/threads/nvme-qid-timeout.51579/?_x_tr_sl=de&_x_tr_tl=en&_x_tr_hl=en&_x_tr_pto=sc

BJ Quinn added a comment - 2024-01-17 20:44 Marko, please disregard. This may have ended up being a hardware problem that was affecting multiple identical servers. A firmware bug in our SSDs. In case anyone is interested, here was the problem and apparent solution – https://forum-proxmox-com.translate.goog/threads/nvme-qid-timeout.51579/?_x_tr_sl=de&_x_tr_tl=en&_x_tr_hl=en&_x_tr_pto=sc

People

Assignee:: Nikita Malyavin

Reporter:: Marko Mäkelä

Votes:: 10 Vote for this issue

Watchers:: 28 Start watching this issue

Dates

Created:: 2018-05-30 09:12

Updated:: 2024-11-17 22:12

Resolved:: 2023-08-16 09:41

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Engine-independent online ALTER TABLE

Details

Description

Intro

Implementation

Behavior of different engines

Limitations

[Old part] Challenges

Attachments

Attachments

Issue Links

Sub-Tasks

Activity

People

Dates

Git Integration