MySQL Forums
Forum List  »  General

Re: Alter table with million of records - estimate how long does it take
Posted by: Rick James
Date: July 15, 2011 09:34PM

A long time.
SHOW TABLE STATUS LIKE 'HistLogIpVpn'; -- to see how big it is.
SHOW VARIABLES LIKE 'key_buffer%'; -- this could be critical to the performance.
How much RAM do you have?

Any ALTER takes essentially the same amount of time. It will copy over all the data and rebuild all the indexes. (Even for something as trivial as changing the limit on a VARCHAR.) You have a lot of indexes. If you can get rid of some at the same time, that will help some.

Since you have replication turned on, the following will happen.
1. ALTER on Master -- long time.
2. replicate to slave -- fast
3. ALTER on Slave (the other Master) -- long time. While this is happening, other things being replicated will be delayed until the ALTER finishes.

I say a "long time" -- between a few minutes and an hour. Without knowing the key_buffer_size, table size, disk speed, etc, I cannot be more specific. Even with those values, I cannot predict very well.

During the ALTER, other operations on the table (at least) will be blocked.

Since you have "Master-Master", there is an excellent alternative, with essentially no "down time":
1. A: Stop all writes to Master A. Redirect all traffic to B.
2. A,B: SLAVE STOP; (both directions)
3. A: SET SQL_BIN_LOG=0; ALTER TABLE...; This does the ALTER but does not replicate it to B.
4. A,B: START SLAVE; (and wait for replication to catch up).
(Now flip to do B)
5. A: Allow writes.
6. B: Disallow writes and redirect traffic to A.
7. A,B: SLAVE STOP; (both directions)
8. B: SET SQL_BIN_LOG=0; ALTER TABLE...;
9. A,B: START SLAVE; (and catchup)
10. Put B back online.

Options: ReplyQuote


Subject
Written By
Posted
Re: Alter table with million of records - estimate how long does it take
July 15, 2011 09:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.