0

I have Table A which i'm tasked to add a new column is_fully_complete bit(1), and i'm thinking:

Would it be better to

a.) Create a new table Table_A_new like Table_A and add the new column there before inserting the data from Table A?

or

b.) rely on Alter Table Syntax to add a new column.

i tried option B but on my local machine and it's taking too long for a 10-min time frame. Table A by the way has about 1.32 million rows, and is running an InnoDB engine. We're using Percona Mysql 5.5 if it helps

EDIT: i forgot to mention we're also using Ubuntu 10.04 on our servers along with Percona Mysql 5.5

0

This is a question which gets asked regularly in some form or other here. Take a look at my reply to a similar one not so long ago.

Basically, there are two main tools out there for this - either Percona's pt-online-schema-change (available here) or Shlomi Noach's oak-online-alter-table (from here).

There are a couple of other possible solutions mentioned which may or may not be more applicable to Windows (you didn't mention your OS) but you do specifically mention MySQL 5.5 which appears to preclude this (unless Percona have back-ported it?).

The usual disclaimers apply - about testing before implementing in production.

[EDIT - in response to OP's comment]

With replication, the situation is, apparently, the following (from here).

REPLICATION

If you use pt-online-schema-change to alter a table on a server with slaves, be aware that:

The tool is not tested with replication
The tool can break replication if not used properly
Although the tool sets SQL_BIN_LOG=0 by default (unless --bin-log is specified), triggers which track changes to the table being altered
still write statements to the binary log
Replicaiton will break if you alter a table on a master that does not exist on a slave
Update slaves first if columns are being added or removed
Do not use this tool in production before testing it

So, not exactly confidence inspiring, but not a total no-no either. Just follow the recommendations and test on a non-production machine.

Another possibility would be to use the relatively new online DDL capacity introducted in MySQL 5.6. From here, check a report of someone who tried to use Percona 5.6's online DDL - but, they apparently didn't apply changes to slaves before masters. All I can say is follow the instructions, test and see how it goes, whatever method you eventually decide on.

Did you check out the table here (from my original post to which I referred you)? It appears to be very similar situation to yours - add a column to a table with ~1,100,000 records - no locking, done in 30 seconds with pt-online-schema-change? Might be good for you? :-)

  • we run linux ubuntu 12.04 LTS. i tried looking up on Percona's pt-online-schema change. but from what i understand, it should be able to handle with replications as long as the max lag doesn't exceed. I'll have to read more. thanks! i might as well edit my post to include the OS used – paacharon3 Aug 14 '14 at 10:10
  • yes i did check out the table from the link link. I'm inclined to use pt-online-schema-change since i don't think we can upgrade to 5.6 just yet, but then again I'll have to consult with my teammates if they've found other/similar solutions to this. thanks again! :-) – paacharon3 Aug 15 '14 at 2:50

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.