Blocking Use Cases of pt-online-schema-change:
- It is not possible to reduce the overhead of the tool to 0. Pt-online-schema-change gives you an option to define the maximum allowed replication lag and, if that threshold is crossed, it stops to copy data between the old and new table. It is also possible to pause the background process entirely. The problem is that we are talking only about the background process of running INSERTs. It is not possible to reduce the overhead caused by the fact that every operation in “yourtable” is duplicated in “yourtable_new” through triggers. If you remove the triggers, the old and new table would go out of sync without any means to sync them again. Therefore, when you run pt-online-schema-change on your system, it always adds some overhead, even if it is paused or throttled. How big overhead depends on how many writes hit the table which is undergoing a schema change.
- Another issue is caused again by triggers – precisely by the fact that, to create triggers, one has to acquire a lock on MySQL’s metadata. This can become a serious problem if you have highly concurrent traffic or if you use longer transactions. Under such load, it may be virtually impossible to use pt-online-schema-change due to the fact that it is not able to acquire metadata lock to create the required triggers. Additionally, the process of acquiring metadata can also lock further transactions, basically grinding all database operations to halt.
- Rename table requires lock on table any long transcation or high load keep rename table operation in waiting for mtadata lock, which cause further transactions to be on hold till completion of rename table, which may cause max connection limit reached in database.
- Yet another problem are foreign keys – unfortunately, there is no simple way of handling them. Pt-online-schema-change gives you two methods to approach this issue. Neither of those are really good. The main issue here is that a foreign key of a given name can only refer to a single table and it sticks to it – even if you rename the table referred to, the foreign key will follow this change. This leads to the problem: after RENAME TABLE, the foreign key will point to ‘yourtable_old’, not ‘yourtable’.
Blocking Use Cases of gho-ost:
- Does not support SBR(Statment based Replication) or Mixed, Requires atleast one slave with RBR(Row Based Replication).
- Does not Support Foreign Keys;
- Does not Support tables schema change with triggers.
- Does not support Galera Cluster.
- Minimal row image is not supported (which makes your binlogs grow larger), JSON and generated columns in 5.7 are not supported.
- Network traffic is increased compared to pt-online-schema-change – not only gh-ost has to copy data but it also has to copy binary logs.
Benefits of gho-ost:
- You case stop 100% load of schema change process.
Supporting Links:
DatabaseSolutions 