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.
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.”
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.
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 ?
Excuse me, but what is the complete pt-online-schema-change syntax for running optimize on an X table?