When working with replication on very big table, we may face problem when update table structure (such as adding an index, removing an index, adding a field, etc.). There are few cases (such as changing indexes) running a local sql is better than having it goes with replication, since the whole replication process will be delayed during the replicated query is executed on slave. This also might cause extending lags between master(s) and slaver(s).
Recent time, we face big delay time due to the change in indexing on master database. Let’s have a look of how index creation happens in InnoDB:
In MySQL versions up to 5.0, adding or dropping an index on a table with existing data can be very slow if the table has many rows. The CREATE INDEX and DROP INDEX commands work by creating a new, empty table defined with the requested set of indexes. It then copies the existing rows to the new table one-by-one, updating the indexes as it goes. Inserting entries into the indexes in this fashion, where the key values are not sorted, requires random access to the index nodes, and is far from optimal. After all rows from the original table are copied, the old table is dropped and the copy is renamed with the name of the original table.
Beginning with version 5.1, MySQL allows a storage engine to create or drop indexes without copying the contents of the entire table. The standard built-in InnoDB in MySQL version 5.1, however, does not take advantage of this capability.
As we can see, since the dropping index process hold for a long time, it causes big lag between the master and the according slaver. So, we decide to skip this replicated query to run it later locally. We start by
stop slave;
, and
SET GLOBAL sql_slave_skip_counter = 1;
to skip the problematic query, and then
start slave;
again. And, yeah, the replication process continues, and delayed time is gradually reduced.
Note: For fast index creation/dropping in MySQL 5.1, it is recommended to use InnoDB plugin instead of using built-in InnoDB engine. But remember to make changes when upgrading from MySQL 5.1 to MySQL 5.5 regarding using this plugin.
