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

LP #1174848: pt-online-schema-changes locks information_schema

    XMLWordPrintable

    Details

      Description

      **Reported in Launchpad by mike taylor last update 30-03-2015 09:13:32

      When using pt-online-schema-change in a busy environment, we get a blocking query on the information_schema.key_column_usage table.

      When querying the information_schema in mysql 5.5.27, the query that tries to look for foreign keys (child tables) is blocking the information_schema for around 50 to 100 seconds. This is bad, and causes huge spikes in page load times.
      We have no foreign keys, in fact, the only value in the referenced_* columns is NULL.

      Is there a way to stop this query blocking, or can we have a flag such as --nocheck-foreign-keys?

      (to get around this I just hacked the pt-online-schema-change program and removed the bit that looks for child tables)

      command line used to run:
      pt-osc --execute --alter "ENGINE=INNODB ROW_FORMAT=COMPRESSED" D=dbname,t=table_name

      Tool version:
      pt-online-schema-change 2.2.1

      MySQL Version:
      mysql Ver 14.14 Distrib 5.5.25a, for Linux (x86_64) using readline 5.1

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist