Not the answer you need? Try our search, browse our categories, or register and ask your own question!

Partitioned a table and change primary key using pt-online-schema-change

AntiLapaAntiLapa Active Member Poster
Hi,

I have a table which is mostly inserts (no updates, very few deletes) with a primary key on id which is auto_increase.
I want to use pt-online-schema-change to partitioned the table by date (another column).
For that I need to do 2 things - change the primary key to be (id,dateTS) and to add partitions.
Reading the documentation I saw --nocheck-unique-key-change is not advisable as data may be lost.

Is that indeed the case if I don't have updates (but inserts still uses 0 or null in the id column for the auto-increase) ? Do I have other option?

This is what I had in mind:
pt-online-schema-change --nocheck-unique-key-change --user $USER --password $PASS --host $WRITER D=db_name,t=table_name--alter "
drop primary key, add PRIMARY KEY (id,dateTS), PARTITION BY RANGE ( UNIX_TIMESTAMP(dateTS) )

  PARTITION p20190901 VALUES LESS THAN ( UNIX_TIMESTAMP(\'2019-09-01 00:00:00\') ),
  PARTITION p20191001 VALUES LESS THAN ( UNIX_TIMESTAMP(\'2019-10-01 00:00:00\') )
)"




Answers

  • vaibhav_upadhyay40vaibhav_upadhyay40 Contributor Active Member Mentor
    Hi @AntiLapa

    I would suggest not to use ''no-nocheck-unique-key-change" to avoid disaster. 
    Also try to split the operation into 2 parts.
    1) Altering PK
    2) Creating partition


    Sample query : (Test it in non-prod / Lower environment) 
    --dry-run 
    pt-online-schema-change D=schema,t=tablename --dry-run--user=user --password='password' --alter "DROP PRIMARY KEY,ADD PRIMARY KEY(id,dateTS)"

    --execute
    pt-online-schema-change D=schema,t=tablename --execute --user=user --password='password' --alter "DROP PRIMARY KEY,ADD PRIMARY KEY(id,dateTS)"

    There are few other options worth exploring. These options work on different areas during execution. For more details and suitable value as per your production load and requirement refer to the documentation and test thoroughly non-prod / Lower environment.

    --recursion-method
    --no-drop-old-table ----(keep the old table (with new name) as well as new table with newly desired table, hence you need to be careful with current disk space usage, expected data growth till the execution completes and additional requirement of disk is sufficient) 
    --critical-load 
    --max-load 
    --chunk-size (explicit value to copy rows)
    --max-lag    (set as per acceptable delay on slave)

     Hope this helps
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.