Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-142

pt-online-schema-change find_child_tables slow


    • New Feature
    • Status: Done
    • Medium
    • Resolution: Fixed
    • 3.0.3
    • 3.0.4
    • None
    • None


      tested on
      pt-online-schema-change 3.0.2
      mysql Ver 14.14 Distrib 5.6.35-80.0, for debian-linux-gnu (x86_64) using readline 6.3
      pt-online-schema-change D=himalaya_novocar_brainstorm,t=Estimation,u=root,p=***** --alter-foreign-keys-method=auto --alter="MODIFY type set('INVOICE','PLANNING','ATR')" --dry-run

      There are a couple of tickets referencing the inherit slowness of information schema. Most are closed because you can set --alter-foreign-keys-method to none, so it doesn't do the check.

      But, this does not solve the problem. It simply avoids it. What if you actually do want the foreign-keys ?

      The real problem is that the query in pt-online-schema-change that is used to find the child tables in sub find_child_tables is slow:

      my $sql = "SELECT table_schema, table_name "
      . "FROM information_schema.key_column_usage "
      . "WHERE referenced_table_schema='$tbl->

      {db}' "
      . "AND referenced_table_name='$tbl->{tbl}'";

      But... simply adding "table_schema='$tbl->{db}

      '" will speed this up.
      I propose:
      my $sql = "SELECT table_schema, table_name "
      . "FROM information_schema.key_column_usage "
      . "WHERE table_schema='$tbl->

      {db}' AND referenced_table_schema='$tbl->{db}

      ' "
      . "AND referenced_table_name='$tbl->



      In the current version, foreign keys that are made across databases will be included. I my proposal, only foreign keys from the same database will be checked.
      I can't find a reason why anyone would make a foreign key cross databases, but I'm sure there are some that do have a valid use case for this.

      I believe the default way should only check the same table_schema. And if people want to check cross database they can do this using a extra flag?

      I'm reporting this as a bug and not a blueprint, because the current code is simply not usable when working with a lot of databases. I hope this is ok.

      Launchpad: https://bugs.launchpad.net/percona-toolkit/+bug/1690122




            carlos.salguero Carlos Salguero (Inactive)
            carlos.salguero Carlos Salguero (Inactive)
            0 Vote for this issue
            1 Start watching this issue



              Smart Checklist