How to add colum using INSTANT MySQL 8.0: InnoDB now supports Instant ADD COLUMN

MySQL 8.0: InnoDB now supports Instant ADD COLUMN 

Instant DDL has been one of the most requested InnoDB features for a very long time. With ever larger and rapidly growing datasets the ability to do DDL instantly is a must have feature in any web scale database.  Developers constantly need to add new columns to meet the constantly changing business requirements.  The ability to add ADD COLUMNs instantly is the first in a series of DDL statements that we plan to do instantly. The move to  a new transactional data dictionary in MySQL 8.0 has made this task a lot easier for us.  Prior to MySQL 8.0 the meta-data (data dictionary) was stored in flat files called .frm files. The .frm files are in  an arcane format that is long past its use by date.


Background:-

MySQL 5.6 was the first release to support  INPLACE DDL. Prior to MySQL 5.6 the only way to do DDL was to COPY the rows one by one.  INPLACE DDL  is mostly handled by InnoDB while COPY row by row is handled in the server layer.  Up until 8.0 (see labs release), InnoDB used to add columns to a table by rebuilding the table even for the INPLACE DDL algorithm.

  • For large tables it can take a long time especially in a Replication setup.
  • Disk space requirements will be more than double, roughly the same size as the existing table.
  • The DDL operation is resource hungry and puts a high demand on CPU, Memory and IO. This steals resources from user transactions.
  • If replication was involved, user may have to wait a even longer time for slave to be ready. The DDL is externalized after it completes.
New INSTANT algorithm:-

Many users have asked us about a way to avoid time consuming schema changes. This can now be achieved by (always) specifying ALGORITHM=INSTANT , this will guarantee that either the operation is done instantly or not at all. Furthermore, if ALGORITHM is not specified at all, server will first try the DEFAULT=INSTANT algorithm. If it can not be done, then server will try INPLACE algorithm; and if that can not be supported by SE, server will finally try COPY algorithm. The new syntax looks like:

ALTER TABLE table_name [alter_specification], ALGORITHM=INSTANT;

Currently, InnoDB supports INSTANT algorithm for these operations:

  • Change index option
  • Rename table (in ALTER way)
  • SET/DROP DEFAULT
  • MODIFY COLUMN
  • Add/drop virtual columns
  • Add columns(non-generated) – We call this instant ADD COLUMN
You can specify more than one operation in a single statement with ALGORITHM=INSTANT.

Side effects and trade offs:-

Since the instant ADD COLUMN won’t rebuild the table any more, so there are some side effects:

  • In older versions the row size would have been be checked upfront, and so the ADD COLUMN will have failed at the start. However, with the new instant ADD COLUMN, row size will only be checked by future updates to the rows.
  • In earlier versions, if the table or index was corrupted, it was possible to ‘fix’ things by rebuilding the table. With instant ADD COLUMN it is more challenging and we are looking at ways to mitigate this.
Limitations:-
Currently there are some limitations:

  • Only support adding columns in one statement, that is if there are other non-INSTANT operations in the same statement, it can’t be done instantly
  • Only support adding columns at last, not in the middle of existing columns
  • Not support COMPRESSED row format, which is seldom used
  • Not support a table which already has any fulltext index
  • Not support any table residing in DD tablespace
  • Not support temporary table(it goes with COPY)










Comments

Post a Comment

Popular posts from this blog

MySQL Point in Time Recovery: How To Configure And How Does it Work?

Image
 MySQL Point in Time Recovery: How To Configure And How Does it Work? The point-in-time recovery allows you to restore a MySQL database to a specific time in the past. The point-in-time recovery relies on two key components: Full backup: This serves as the foundation for recovery, providing the starting state of the database. Binary logs: These binary log files record all changes made to the database, allowing you to replay those changes to a desired point. If you don’t have a full backup or binary logs enabled, you cannot carry the point-in-time recovery. We’ll illustrate how to perform the point-in-time recovery in MySQL to recover a database to a specific point in time. Checking binar log status:- We need login to mysql server and run given below command:- show global variables like 'log_bin' ; Show output for enable mysql-bin log. Creating a new sample database & taking a full backup:- We are going to login in mysql databse and creating new database and table.given ...
Read more

MySQL Replication Switchover: Step-by-Step Guide

Image
 MySQL Replication Switchover: Step-by-Step Guide MySQL replication is a powerful feature that allows you to create redundant copies of your database for improved availability and scalability. In certain scenarios, it becomes necessary to switch the roles of the master and slave servers in a replication setup. This article will guide you through the process of performing a MySQL replication switchover, ensuring a smooth transition without any data loss. Let’s dive into the step-by-step instructions. I have tow node one is master node and another is slave node.Given below details:- Current Master: 192.168.70.10 Current Slave : 192.168.70.11 Verifying the Replication:- Before initiating the switchover, it’s important to verify that replication is functioning correctly on the current Slave server (192.168.70.11). Execute the following queries to ensure the replication status: mysql> show slave status\G This command displays the slave’s status, including information such as replicat...
Read more

Mysql Commercial Mysqlbackup:How to take Differential or Incremental Backup and resotre using mysqlbackup utility in mysql 8.0.37 enterprise edition

Image
Mysql Commercial Mysqlbackup:How to take Differential or Incremental Backup and resotre using mysqlbackup utility in mysql 8.0.37 enterprise edition Incremental vs Differential Backup:- The main difference between incremental and differential backups is how they save space and time by storing only changed files. However, the effectiveness of incremental backups is very different from differential backups. Before we dive into the differences between Incremental and Differential backups let’s review Full Backups.  Are you still using Full Backups? In its simplest form, a full backup is the starting point for both Incremental and Differential backup strategies. A full backup, of course, backs up all the data on a partition or disk by copying all disk sectors with data to the backup image file.  A full backup is time-consuming and requires a lot of space, it’s usually used as part of a backup plan with long intervals, like once a week or a month. During this time, if anything goes...
Read more