You probably missed the news, but…
PT-1751: Adds –where param to pt-online-schema-change
This brings the possibility to perform what I would call an “inverted purge” because you are not actually purging rows from your multi-terabyte table, but rather, you copy the small percentage of rows you want to keep to a new table and then drop the original table. This not only saves you from doing far more DELETE operations but also saves you from blocking the OPTIMIZE TABLE or from running pt-online-schema-change anyway to rebuild the table.
We used to perform this type of operation in two steps: first run pt-archiver to purge the unwanted rows, and then run pt-online-schema-change to rebuild the table. This certainly worked, but the purge operation could take days and tends to trash the buffer pool, as it will read into memory all the pages that contain rows to be deleted (which for the majority of cases that we see in Support, is 70% or more of some very-large table).
The new –where option will work like regular pt-online-schema-change but will only copy part of rows that interest you into a new table, and then you will have to “manually” perform the tables swap. As mentioned before, we assume the number of rows to be retained is smaller than the number of rows to be purged, and thus, the time to copy is shorter than the time to DELETE and should also incur less buffer-pool trashing.
To test the process, we did this:
Create the schema:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
cat << EOT | mysql -BNvvv DROP DATABASE if exists mydb; CREATE DATABASE mydb; CREATE TABLE mydb.mytable ( id INT NOT NULL auto_increment PRIMARY KEY, some_date_column TIMESTAMP NOT NULL, KEY sdc(some_date_column) ); EOT |
Fill up with one row per day for 18 months.
|
1 2 3 4 5 6 7 8 |
ts=$(date -d"2023-01-01" +%s); ts_end=$(date -d"2024-07-01" +%s); while [[ $ts < $ts_end ]]; do { d=$(date -d "@${ts}" +'%F %T'); echo "INSERT INTO mydb.mytable VALUES (NULL, '${d}');"; ts=$((ts + 86400)); } done | mysql mydb -BN; mysql -e "SELECT MIN(some_date_column) as earliest, MAX(some_date_column) as latest FROM mydb.mytable"; |
Then run a simple loop that continues inserting one row every five seconds with the current time stamp so we can see if the effect of locks on the table and verify the triggers work as expected to keep the _new table updated:
|
1 2 3 4 5 6 7 |
while true; do { echo "SELECT * FROM mydb.mytable ORDER BY id DESC LIMIT 1;"; echo "BEGIN;"; echo "INSERT INTO mydb.mytable VALUES (NULL, NOW());"; sleep 5; # this allows us to see the RENAME waiting for the transaction to commit... echo "COMMIT;"; } done | mysql mydb -BN; |
And now comes what you all have been waiting for: run pt-online-schema-change to copy ONLY the rows we want to preserve. For our example, we’ll pretend we want to keep all data from 2024:
|
1 2 3 4 5 6 |
pt-online-schema-change --execute --alter="ENGINE=INNODB" --where="some_date_column > '2023-12-31'" --host=127.0.0.1 --port=3306 --user=msandbox --password=msandbox --no-drop-triggers --no-drop-old-table --no-drop-new-table --no-swap-tables --set-vars="foreign-key-checks=0" --alter-foreign-keys-method="rebuild_constraints" D=mydb,t=mytable; |
The output of pt-osc will look something like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
No slaves found. See --recursion-method if host tp-support03.tp.int.percona.com 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 No foreign keys reference `mydb`.`mytable`; ignoring --alter-foreign-keys-method. Altering `mydb`.`mytable`... Creating new table... Created new table mydb._mytable_new OK. Altering new table... Altered `mydb`.`_mytable_new` OK. 2024-07-04T01:48:47 Creating triggers... 2024-07-04T01:48:57 Created triggers OK. 2024-07-04T01:48:57 Copying approximately 213 rows... 2024-07-04T01:49:09 Copied rows OK. Not dropping old table because --no-drop-triggers was specified. Not dropping triggers because --no-drop-triggers was specified. To drop the triggers, execute: DROP TRIGGER IF EXISTS `mydb`.`pt_osc_mydb_mytable_del` DROP TRIGGER IF EXISTS `mydb`.`pt_osc_mydb_mytable_upd` DROP TRIGGER IF EXISTS `mydb`.`pt_osc_mydb_mytable_ins` Not dropping the new table `mydb`.`_mytable_new` because --no-drop-new-table was specified. To drop the new table, execute: DROP TABLE IF EXISTS `mydb`.`_mytable_new`; Successfully altered `mydb`.`mytable`. |
Once the pt-osc process completes the chunks copy, the triggers will remain attached to the current (full) table, so the new table will continue to get updates as they arrive.
|
1 2 3 4 5 6 7 8 |
mysql [localhost:53704] {msandbox} (mydb) > SHOW TABLES; +----------------+ | Tables_in_mydb | +----------------+ | _mytable_new | | mytable | +----------------+ 2 rows in set (0.01 sec) |
|
1 2 3 4 5 6 7 8 9 |
mysql [localhost:53704] {msandbox} (mydb) > SELECT trigger_name FROM information_schema.triggers WHERE event_object_table='mytable'; +-------------------------+ | TRIGGER_NAME | +-------------------------+ | pt_osc_mydb_mytable_del | | pt_osc_mydb_mytable_upd | | pt_osc_mydb_mytable_ins | +-------------------------+ 3 rows in set (0.01 sec) |
To complete the process, you should swap the tables. This is atomic and will wait for transactions to finish, get MDL lock, and perform both renames under the scope of the same MDL lock:
|
1 2 3 |
RENAME TABLE mydb.mytable TO mydb.mytable_old, mydb._mytable_new TO mydb.mytable; |
At this point, you could do LEFT JOIN between the old and new tables to verify there are no missing IDs.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT o.id AS ids_from_old_table_that_are_missing_in_new_table FROM mydb.mytable_old AS o LEFT JOIN mydb.mytable AS n ON n.id = o.id WHERE o.some_date_column > '2023-12-31' AND n.id IS NULL; # check min/max date just to verify things got purged as expected SELECT MIN(some_date_column) as earliest, MAX(some_date_column) as latest FROM mydb.mytable_old; SELECT MIN(some_date_column) as earliest, MAX(some_date_column) as latest FROM mydb.mytable; |
After successfully swapping the new and the old table, the output looks like this:
|
1 2 |
mysql [localhost:53704] {msandbox} (mydb) > SELECT o.id AS ids_from_old_table_that_are_missing_in_new_table FROM mydb.mytable_old AS o LEFT JOIN mydb.mytable AS n ON n.id = o.id WHERE o.some_date_column > '2023-12-31' AND n.id IS NULL; Empty set (0.00 sec) # |
Perfect! No missing rows!
The delayed value in “latest” tells us that the old table is no longer in sync! This is the expected status, so it’s no problem.
|
1 2 3 4 5 6 7 |
mysql [localhost:53704] {msandbox} (mydb) > SELECT MIN(some_date_column) as earliest, MAX(some_date_column) as latest FROM mydb.mytable_old; +---------------------+---------------------+ | earliest | latest | +---------------------+---------------------+ | 2023-01-01 00:00:00 | 2024-07-04 02:05:41 | +---------------------+---------------------+ 1 row in set (0.00 sec) |
Notice that “earliest” is now 2024, confirming that the purge finished correctly, and “latest” shows fresh values confirming that writes arrive at the new table.
|
1 2 3 4 5 6 7 |
mysql [localhost:53704] {msandbox} (mydb) > SELECT MIN(some_date_column) as earliest, MAX(some_date_column) as latest FROM mydb.mytable; +---------------------+---------------------+ | earliest | latest | +---------------------+---------------------+ | 2024-01-01 00:00:00 | 2024-07-04 02:06:41 | +---------------------+---------------------+ 1 row in set (0.00 sec) |
The new table has the latest data, plus we checked it had all the rows we wanted from the old table, so we can now drop the old table to instantly reclaim the space:
|
1 |
DROP TABLE mydb.mytable_old; |
As a reminder: be mindful when dropping tables as there are still bugs that cause InnoDB to scan the Adaptive Hash Index as part of the process to purge pages from the table being removed, and it will basically block traffic while holding doing this, so if your AHI is very large it can lead to noticeable stalls, thus is best-done off-hours. See https://bugs.mysql.com/bug.php?id=91977 for the gory details.
So, using the new –where option you can reduce the total time and effort necessary to purge the rows and reclaim space, and it will be done with the least possible impact and locking.
Percona offers secure, tested, open source software complete with advanced features like backup, monitoring, and encryption only otherwise found in MySQL Enterprise Edition.
Your PT-OSC command line above is incorrect，the correct one should be:
####=====================================================
shell> pt-online-schema-change –execute –alter=”ENGINE=INNODB” \
–where=”some_date_column > ‘2023-12-31′” \
–host=127.0.0.1 –port=3306 –user=admin –password=123456 \
–no-drop-old-table –no-drop-new-table –swap-tables –force \
D=mydb,t=mytable
####=====================================================
—–
The mytable table retains the data for the year 2024.
__mytable_new retains the original table data.
This test is probably an example of changing the table after pt-osc, so maybe this example added the –no-swap-tables option,
1. Create New Table & Data Sink
2. Later inspection or Rename processing when no access to the table is made
* Swap-tables are right if you’re going to change it right away
In fact documentation for –where indicates:
Hence my command line… but I admit your use of the options is pretty witty 🙂