1

Strategy for migration of Huge MySQL (Percona 5.7) Table (InnoDB) in production. The aim is to have minimum downtime (offline schema change would take a week or more...)

old has a 100 plus columns we are planning to delete 90 columns and move (adjusted) data into new

THE PLAN

1) create new

2) transfer data from old to new

3) Alter old

ad 2)

Old Table (c1 .. c150):

Id  | c1    |c2     |c3 |
-------------------------
1   |A      |null   |F  |
2   |B      |Z      |H  |

New table:

Id  | OldId |colName    |value  |
---------------------------------
1   |1      |c1         |A      |
2   |1      |c3         |F      |
3   |2      |c1         |B      |
4   |2      |c2         |Z      |
5   |2      |c3         |H      |

to transfer data

INSERT INTO new(OldId,colName,value) SELECT Id, 'c1', c1 FROM old WHERE c1 IS NOT NULL;
INSERT INTO new(OldId,colName,value) SELECT Id, 'c2', c2 FROM old WHERE c2 IS NOT NULL;
.
.
.
INSERT INTO new(OldId,colName,value) SELECT Id, 'c150', c150 FROM old WHERE c150 IS NOT NULL;

How to execute statements similar to the ones listed above, with least impact on the production system (I know that Insert with Select will Lock both tables...), and make sure that new data inserted into old is transferred to new before switchover?

ad 3) for schema change I am planning to use GH-OST or pt-online-schema-change
most likely GH-OST as it does not create triggers, and can be easily paused/recovered.

  • 1
    You can do the whole task with pt-online-schema-change. Have you already failed with triggers? – eroomydna Dec 2 '16 at 16:35
  • 1
    Unclear -- where does 'VAL1` come from? Is it a constant? How many rows in the table? Are you "un-pivoting"? How much downtime can you afford? Will you keep TableA with its remaining columns? – Rick James Dec 2 '16 at 22:03
  • 1
    in additional to Rick James - what size of database (why offline changes could take weeks? You have petabytes database?) - it just for better understanding the problem. Just explain on real example - we are have huge table, and need migrate it fast. In our case analisis show - data could be change only from begin of month, solution - we are make migration on 1st day of month (night time) and rename table, create new and copy data only for last 1Hr, it take a minutes, than slowly transfer all other data. It is just example of possible ways – a_vlad Dec 2 '16 at 22:52
  • tested GH-OST but it seems like it is an early alpha tool with some serious problems with handling constraints.. looks promising however for the schema change pt-online-schema-change looks like a winner for now. @RickJames yes it is constant, and table size is multiple TB, database is heavily used for read/writes. – Marcin Wasiluk Dec 12 '16 at 13:03
  • @RickJames 24h downtime would be acceptable – Marcin Wasiluk Dec 12 '16 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.