Online alter changes
When a change is made to a table, such as adding a field to a form or creating an index, a change needs to be made on a database level. This is commonly done via an ALTER operation. However, in current versions of MySQL, these alter operations require a table lock, which can block other queries from running, which can potentially cause an outage.
In order to prevent this from happening, we have introduced what is known as online alter in Calgary. Online alter operations are completed in the following steps:
- An empty table is created with the same schema as the table that is to be altered.
- The ALTER operation is performed on the new, empty table.
- Triggers are added on the live table to copy any changes that are made against the live table to the new table.
- All data is copied in chunks, from the live table to the new table.
- Once all of the data is copied, the tables are swapped. This requires a very brief table lock.
- The old live table is dropped.
This method will only briefly lock the table, but the operations as a whole can take significantly longer as an online alter as it requires all data in the table to be copied. In Dublin, we have introduced task table flattening, which has significantly increased the size of the task table and any of its child tables because it is the same on a database level. For more information, see Task Table Flattening in the ServiceNow Product Documentation.
In Calgary and Dublin, multiple changes to a single table are performed in multiple online alters. As each online alter requires a full copy of the table, this can greatly increase the amount of time required for the changed to complete. This issue is partially addressed in Eureka, in which multiple changes against the same table are bundled in an online alter when they are applied via an update set. However, the issue still remains for changes via the slush bucket, which has been logged as a Problem ticket.