Most efficient way to alter a 4 billion row innodb table?

Hi All - I am using MySQL 4.1.10 on RedHat. I have one HUGE innodb table, it has 4.2 billion rows and is almost 500G in size. We are in a situation where we would like to add two columns to the table, eg, something like;


However, in testing with a smaller table(23 million rows) this operation not only takes a long time, but the size of the table almost doubles. The size growth seems unusual given that we aren't actually adding data yet. I am wondering if this is expected, or if there is a better way to add the columns, or if migrating up to mysql V5 would help?


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

But you are adding data - you've defined it as NOT NULL, so it is storing the default INTEGER value (0) for every row.
In terms of raw storage, an INT takes up 4 bytes (for MyISAM tables).  You have two columns, so you're effectively adding 8 bytes of storage per row.  That calculates out to about 30 GB of raw storage.  InnoDB has a different storage architecture that I don't know the details on, but definately requires more disk space than MyISAM (likely because of multi-versioning for ACID transaction support).
ALTER TABLE works by copying the existing table completely into another table, then replacing the original table with the new table.  So, yes, the disk space consumed by the table will more than double because it has to simultaneously hold the old table and new table.

You could do a manual process that would not increase the overall size by much.

CREATE TABLE newbig like big;

-- to make sure nobody is using them but us

--figure out a WHERE condition that applies to one Nth of your database of the rows in big.
--Call it $WHERE1
INSERT INTO newbig (...list fields from big...) SELECT ...list fields from big.. FROM big WHERE $WHERE1;

INSERT INTO newbig (...list fields from big...) SELECT ...list fields from big.. FROM big WHERE $WHERE2;

... and so on until you have all the rows copied into newbig and deleted from big.

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

jennfirAuthor Commented:
Thanks! - I am currently copying the database to another server where I can test, its taking awhile since its so large. I will try the solution above once it is done and let you know, it will probably be a day or two. I am hoping with this change the DB will only grow by 30G or so as Todd suggests.
Now I'm not so sure my answer will work well.  

The main problem is that the deleted rows could be in the middle of blocks, and these partially used blocks will still be occupied and not recycled for use by newbig.  It would wind up in the same situation as if you had used ALTER TABLE - the database files would grow to nearly double their size.

It would be ideal if the $WHERE conditions took the records in  the order they are actually recorded on the disk, so that the blocks could be recycled for use by newbig as they are freed from big.  OPTIMIZE TABLE won't help because it will just copy the table like ALTER TABLE.

The partial-page thing is a problem.  See the last paragraph of :

When you delete data from a table, InnoDB  contracts the corresponding B-tree indexes. Whether the freed space becomes available for other users depends on whether the pattern of deletes frees individual pages or extents to the tablespace. Dropping a table or deleting all rows from it is guaranteed to release the space to other users, but remember that deleted rows are physically removed only in an (automatic) purge operation after they are no longer needed for transaction rollbacks or consistent reads. (See Section 14.2.12, “Implementation of Multi-Versioning”.)
jennfirAuthor Commented:
Thanks NovaDenizen - if I am going to drop table 'big' once the inserts into 'newbig' are done, then it seems as if once I restart mysql, the space would be released? I realize I will need double the space until the table is dropped and needed, but I should be able to accomodate that temporarily. Unfortunately, I am having difficulties mounting my NAS device to the server(see so I am not in any position to test right now. Theoretically it seems like your above solution would work once the table is dropped, am I missing something?

Another problem is that your InnoDB datafiles will remain at the "high-water mark" once the process is complete.  I don't think there's any way to decomission InnoDB data files once they are configured into a mysqld server.  

I think I have come up with a better procedure.  I'm assuming your table has one primary key field, called id.  Another field is necessary to outsmart the query optimizer.  Use a small field in big, I'll call it other_small_field.  

There is no strict guarantee that mysql will return the rows in the order in which they occur on disk, but this procedure assumes that the query in the "INSERT INTO batchids SELECT..." query does just that by "outsmarting" the query optimizer.  You should try this with your reduced dataset to make sure the query doesn't push the 'high-water mark' up too high.

The procedure:

First, back your data up!  
Drop all indexes on big except the primary key index.

Make sure that "EXPLAIN SELECT id, other_small_field FROM big LIMIT 1000" has possible_keys=NULL, key=NULL, and type=ALL.  I believe this means that this particular query will return records in the order in which they appear on disk, not in some arbitrary index order.

Create the newbig table with the extra two fields, and _no_ indexes, not even a primary key.  Also need to create a table to hold the id's in each batch.
CREATE TABLE batchids (id <same type as in big> PRIMARY KEY, other_small_field <same as in big>) ENGINE=MEMORY;

These next steps have to be repeated many times until the entire table is copied over.  You can change the LIMIT 1000 to be whatever value you find to be most convenient, as long as you think your batchids table can take that many records.  
TRUNCATE batchids;
INSERT INTO batchids (id, other_small_field) SELECT id, other_small_field FROM big LIMIT 1000;
INSERT INTO newbig ( <all fields in big> ) SELECT big.<all fields in big> FROM batchids
                  STRAIGHT_JOIN big ON ( =;
DELETE big FROM batchids, big WHERE =;

After all records have been moved out of big:
DROP TABLE batchids;
ALTER TABLE newbig RENAME TO big, CHANGE COLUMN id id <type of id> PRIMARY KEY, ADD INDEX <add any other indexes that you dropped before>;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jennfirAuthor Commented:
I apologize for not responding recently, this project got tabled due to something else that came up and I do not know when I will be able to get back to it. But, when I do, I will try NovaDenizon's response, so I am going to take that as the accepted answer. Thanks All!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.