Announcement

Announcement Module
Collapse
No announcement yet.

pt-online-schema-change for optimizing very large table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • pt-online-schema-change for optimizing very large table

    pt-online-schema-change to optimize a very large table with massive deletes to reclaim the space.
    I am in a very active 24/7 with very high QPS and can't afford table lock. Our environment is Percona 5.5.34 running on ubuntu 12.04. Each time I have tried pt-online-schema-change as soon as the triggers get incepted the table gets locked and consequently we encounter an outage and I end up killing the process and killing the triggers takes a long time to roll back.

    We generally end up, doing work on slave and do the VIP failover as the Percona tool doesn't work.

    Any recommendation as how to use this tool?
    Greatly appreciate it.

    -Sheyda

  • #2
    Hi,

    The nature of pt-online-schema-change is, ALTER the table without locking. As per documentation "pt-online-schema-change emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it."
    http://www.percona.com/doc/percona-t...ma-change.html

    So, I don't understand how it can get lock the table. Can you tell where you have seen the table lock? Can you provide the statement which you are running? and also output of "show engine innodb status" while running it on server? Thanks.

    Comment


    • #3
      It sounds like if you make a dummy alteration, like changing one index name, the process ends up with rebuilding a fresh new optimized copy of the initial table.
      Am I correct ?

      Comment

      Working...
      X