Percona Series / Revolutionise ALTER TABLE

7 min readOct 29, 2020

If you ever run into an ALTER TABLE on larger tables on your Percona Cluster (spoiler: not just for Percona Cluster) you have probably noticed the problem that there is a lock which doesn’t allow the application use that table at all.

The solution comes with the Percona Toolkit helper. This is the pt-online-schema-change. The online schema change…

…alters a table’s structure without blocking reads or writes. — Original documentation

By using this tool we perform ANY alter table events without locking. (Based on the restriction, detailed in the original documentation.)

Installation

I’m an RPM-based person so I will only touch these sections, but based on the links you can see the alternatives.

Under the following link you can choose the RPM-based version. Then add the yum repository with the command.

sudo percona-release enable pt release
sudo percona-release setup -y pt
pt-online-schema-change # this is what we will use

How is it working?

Basically everything that it is implementing we can do by ourselves, but it’s doing automatically with the protection against data-loss.

1. Create a copy of the original table’s structure as a new table and prefix it with an underscore. In our case tx is the original table and it will be _tx.
2. Do the alter operations on the new table.
3. Create triggers to redirect the traffic into the new table. In case of foreign keys we have to setup an additional flag, but let’s talk about that later.
4. Copy all of the existing data into the new table in small chunks. We can play with these chunks to make this process faster.
5. When it finishes copying the data into the new table, it drops the original table do an atomic RENAME TABLE.

Basic details

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=store,t=table

With the original documentation’s example we can have the most basic command.

pt-online-schema-change: name of the program
— alter: flag defines the modification which we want to perform. In this case we add a new INT column to the table
D=store,t=table: used for creating DSN. In this case D is the database and t is the table name

This code won’t affect the table, because for safety reasons we must add an extra flag: — execute

Testing environment

There is a two node Percona XtraDB Cluster 5.7 with the preloaded data. The suspect table has more than 10 million rows in it. Also a Go application which is loading the data into the table concurrently.

Useful flags

execute: not surprisingly executes the alter. If it’s not passed the changes won’t happen.
alter: defines the schema change where multiple modifications should be comma separated.
ask-pass: asks for the password. If it is not specified, there will be a Cannot connect to MySQL error.
check-alter: parses the alter and warns in case of possible unintended behaviour.
chunk-size: number of rows copied over to the new temporary table.
critical-load: in previous tests there were 100 concurrent goroutines which loaded the data into the test-environment, so I used this flag to change the count of working threads, Threads_running=150, but this flag broke the cluster and it took 2 days to recover it.
dry-run: risky, but fast solution. Creates the new table without any other things.
new-table-name: specifies the new table’s name.
pid: can be useful to pretend double runs. For example when using pipelines, if the pid file exists and the pid is still running it won’t start the execution.
print: prints the SQL commands applied by the tool.
quiet: doesn’t print anything.

Test #1

Data load on the node1 with 10 concurrent goroutine, schema change on the node2. It will add a new column called importance.

pt-online-schema-change --alter "ADD COLUMN importance INT" --execute --ask-pass D=database,t=tx

Enter MySQL password:
No slaves found.  See --recursion-method if host percona-node2 has slaves.                                        
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.                      
Operation, tries, wait:  
  analyze_table, 10, 1  
  copy_rows, 10, 0.25  
  create_triggers, 10, 1  
  drop_triggers, 10, 1  
  swap_tables, 10, 1  
  update_foreign_keys, 10, 1
Altering `fluidpay`.`tx`...
Creating new table...
Created new table fluidpay.__tx_new OK.
Altering new table...
Altered `fluidpay`.`__tx_new` OK.
2020-10-29T12:41:41 Creating triggers...
2020-10-29T12:41:41 Created triggers OK.
2020-10-29T12:41:41 Copying approximately 9942954 rows...                                                         Copying `fluidpay`.`tx`:   4% 10:38 remain...Copying `fluidpay`.`tx`:  99% 00:05 remain
2020-10-29T13:02:55 Copied rows OK.
2020-10-29T13:02:55 Analyzing new table...
2020-10-29T13:02:56 Swapping tables...
2020-10-29T13:02:56 Swapped original and new tables OK.
2020-10-29T13:02:56 Dropping old table...
2020-10-29T13:02:57 Dropped old table `fluidpay`.`_tx_old` OK.                                          
2020-10-29T13:02:57 Dropping triggers...
2020-10-29T13:02:57 Dropped triggers OK.
Successfully altered `fluidpay`.`tx`.

As we can see in the logs the copy took 21 minutes and 14 seconds. At this stage we had 10.428.055 rows in that table. Lock happened only in the swapping tables section, and it took 1 second. At this time the database refused to handle any operations from the Go tool.

Test #2

Same as the first, just adding print and increasing chunk-size to 10000 (by default it’s 1000).

It’s taken 40 minutes and 7 seconds, so more rows in a chunk wasn’t performing better. There are the SQL commands as well.

Test #3

Do the same as the first, just print the SQL commands again, and use the dry-run. It’s probably going to cause data-loss, because there won’t be triggers to redirect the traffic to the new table. I started the schema change and after starting the load tool before these all there were 10.428.055 rows in that table.

Keep in mind: dry-run and execute are mutually exclusive.

$ pt-online-schema-change --alter "ADD COLUMN data_field2 INT" --print --dry-run --ask-pass D=fluidpay,t=tx

Enter MySQL password:
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Starting a dry run.  `fluidpay`.`tx` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...CREATE TABLE `fluidpay`.`__tx_new` (
`id` char(20) NOT NULL,
`importance` int(11) DEFAULT NULL,
`data_field` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Created new table fluidpay.__tx_new OK.
Altering new table...
ALTER TABLE `fluidpay`.`__tx_new` ADD COLUMN data_field2 INTAltered `fluidpay`.`__tx_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.INSERT LOW_PRIORITY IGNORE INTO `fluidpay`.`__tx_new` (`id`, `importance`, `data_field`) SELECT `id`, `importance`, `data_field` FROM `fluidpay`.`tx` FORCE INDEX(`PRIMARY`) WHERE ((`id` >=?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 95321 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `fluidpay`.`tx` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.DROP TRIGGER IF EXISTS `fluidpay`.`pt_osc_fluidpay_tx_del`
DROP TRIGGER IF EXISTS `fluidpay`.`pt_osc_fluidpay_tx_upd`
DROP TRIGGER IF EXISTS `fluidpay`.`pt_osc_fluidpay_tx_ins`2020-10-29T17:15:42 Dropping new table...
DROP TABLE IF EXISTS `fluidpay`.`__tx_new`;
2020-10-29T17:15:42 Dropped new table OK.
Dry run complete.  `fluidpay`.`tx` was not altered.

At this point I have no clue what is the real purpose of the dry-run, it’s finished under 1 second. So I opened the documentation and tried to figure it out. I guess this will give us a glance how the table will look after the alter. So it made sense to use it with the --[no]drop-new-table flag.

Test #4

max-load can optimise the execution, but as my personal opinion it’s not too secure. During this test I added max-load Threads_running=100 option which can break the cluster (already happened with the critical-load flag).

So with this change the copy took 20 minutes and 29 seconds with a very small CPU footprint.

UID PID   %usr   %sys  %guest %wait %CPU   Command
993 3392  16.83  1.98  0.00   0.00  18.81  mysqld
0   95661 1.00   0.00  0.00   0.00  1.00   pt-online-schem

That wasn’t a huge improvement. So we see that the modification of this value won’t make the alter faster.

Test #5

Tried to break the cluster again, as I did in the past with the critical-load. So in that case I increased the concurrent routines to 100 in the load tool and added a critical-load with Threads_running=200. The Go loader had some errors but it’s worked properly. It’s taken 1 hour, 8 minutes and 27 seconds, probably because of the bigger load, surprisingly it didn’t brake the cluster like last time.

Average: UID PID  %usr  %sys %gue %wait %CPU  Command
Average: 993 3392 10.84 1.68 0.00 0.00  12.53 mysqld
Average: 0   96758 0.21 0.05 0.00 0.05  0.26  pt-online-schem

In case of this crash, I found this solution, but I didn’t try it.

Is it working with MySQL?

In theory NOT, so they mention that it works with Percona XtraDB Cluster.

I was curios so I tried it on a mysql Ver 8.0.21 for Linux on x86_64 (Source distribution) and it worked.

$ pt-online-schema-change — alter “ADD COLUMN data_field2 INT” — execute — ask-pass D=test,t=Persons...Successfully altered `test`.`Persons`.

Conclusion

This tool has lots of safety concerns so it’s really hard to break the cluster if we use it properly. However, the documentation warns us that it can be risky to run it without proper production backup and try-out on test databases.