Not the answer you need?
Register and ask your own question!

any considerations before updating large table with pt-osc?

steven_luyckxsteven_luyckx Current User Role Poster
I want to update a table that holds just under 1 billion rows and 200GB of data. I'm confident that I can use the pt-osc for this. But are there any considerations to take into account? For example: staying away from backup windows, etc.

Best Answer

Answers

  • jriverajrivera Percona Support Engineer Percona Staff Role
    Here are some pre-flight checks:
    • Make sure there are no long running transactions at the start of the operation and before the RENAME stage of pt-osc.
    • Make sure to check if there are any foreign keys
    • Check for triggers, latest MySQL version should support triggers but it would be best to test first with --dry-run option before --execute
    • Where are you going to use pt-osc eg. standalone, master-slave, PXC/Galera cluster, etc?
    • Generally we recommend testing in your staging environment with similar setup to check if everything goes well before doing it in production.
  • steven_luyckxsteven_luyckx Current User Role Poster
    Thanks jrivera for the quick answer!
    The update has already been applied to test and acceptance systems, in each case first with the --dry-run option.
    Databases were updated successfully, but both databases are tiny in size compared to the production database.
    Yes, the table has foreign keys pointing to other tables. I'm using the --alter-foreign-keys-method auto parameter for that.
    The table is never part of long running transactions. That is for sure.
    The database is part of a MASTER-SLAVE setup. I'm using the --recursion-method hosts parameter for that.
    Anything else that should need my attention prior to the update?
    So, generally speaking, there is no size/rowcount restriction to the usage of pt-osc, correct?
  • steven_luyckxsteven_luyckx Current User Role Poster
    Thanks again. I will continue with confidence then ;-)
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.