![]() In this situation the only option is to flush all dirty pages, otherwise the data can become inconsistent. These operations add overhead to DDL statements that use ALGORITHM=COPY. ![]() No undo logging or associated redo logging is required for ALGORITHM=INPLACE. The reason for this is that undo and redo logging is disabled for this operation: This is a much less known fact and very sparsely documented. What is also interesting is that ALTER TABLE with the INPLACE ALGORITHM (which will be the default for adding a field) will need to force flushing of all dirty pages and wait until it is done. On the left, we can observe a steady rate of the table rebuild, followed by four spikes corresponding to rebuilding of the four indices. In our Percona Server version 5.7, ALTER TABLE helios ADD COLUMN was executed in place. On the smaller server, where ALTER TABLE was faster, the relevant PMM monitoring plots show: Investigating the issueĪfter verifying that the disk I/O throughput is the same on both servers, we investigated the reason for such a large difference in the duration of ALTER TABLE helios ADD COLUMN query using Percona Monitoring and Management (PMM) to record and review performance. Yet on another “large” Percona Server, where the same table was 30 GB in size, it took more than 4 hours to add the same column. In one of our “small” Percona Servers, it took a little more than 5 min to add a column to the 13 GB InnoDB table. For example, adding a field to a large table on a “beefy” server with 128G of RAM can take unexpectedly long time. Online DDLs are great for common operations like add/drop a column, however we have found out that these can be significantly slower. For the list of all online DDLs in MySQL 5.7 you can refer to this document. Then, for InnoDB tables, the new algorithms were introduced, which do not involve the full table copy and some operations do not apply the table level lock – first the online add index algorithm was introduced for InnoDB, then the non-blocking add columns or online DDLs. During this operation the table was locked to prevent data inconsistency. Some historyĪ long time ago, all “ALTER TABLE” (DDLs) operations in MySQL were implemented by creating a new table with the new structure, then copying the content of the original table to the new table, and finally renaming the table. As a result, all dirty pages of the altered table/tablespace have to be flushed before the ALTER TABLE completion. ![]() One of the reasons for such behavior is the lesser known limitation of ALTER TABLE (with default ALGORITHM=INPLACE) that avoids REDO operations. In this blog post we describe the case when this is not so. Usually one would expect that ALTER TABLE with ALGORITHM=COPY will be slower than the default ALGORITHM=INPLACE. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |