11

I'm a PHP developer so don't be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB so what i tried:

  1. Alter table in screen with table lock. Took ~30hours and nothing. So I just stopped it. First I made a mistake because I didn't end all transactions but the 2nd time was no multilock. Status was copy to tmp table.

  2. Since I also need to apply partitioning for this table we decide to make dump, rename and make table with same name and new structure. But dump is making strict copy(at least I didn't found something else). So i added to dump a new column with sed and query it. But some strange errors began. I believe it was caused by charset. Table in utf-8 and file became us-ascii after sed. So I got errors(unknown command '\'') on 30% of data. So this is also a bad way.

What are other options to accomplish this and speed performance(I can do it with php script, but it will took ages). What will be performance of INSERT SELECT in this case.

Thanks for any advance.

migrated from stackoverflow.com Jun 18 '13 at 17:45

This question came from our site for professional and enthusiast programmers.

11

Use MySQL Workbench. You can right-click a table and select "Send to SQL Editor" --> "Create Statement". This way no table "properties" will be forgotten to add (including CHARSET or COLLATE).
With this huge amount of data I'd recommend cleaning up either the table or the data structure you use (a good DBA comes handy). If not possible:

  • rename the table (ALTER) and create a new one with the CREATE script you get from Workbench. You can also extend that query with the new field you need
  • BULK LOAD the data from the old table to the new one:
    SET FOREIGN_KEY_CHECKS = 0;
    SET UNIQUE_CHECKS = 0;
    SET AUTOCOMMIT = 0;
    INSERT INTO new_table (fieldA, fieldB, fieldC, ..., fieldN)
       SELECT fieldA, fieldB, fieldC, ..., fieldN
       FROM old_table
    SET UNIQUE_CHECKS = 1;
    SET FOREIGN_KEY_CHECKS = 1;
    COMMIT;
    

    This way you avoid indexing/etc to run record by record. The "update" to the table still will be slow (as the amount of data is huge) but this is the fastest way I can think of.

    EDIT: Read this article to get details about the commands used in the above sample query ;)
  • My options is fine. And i got SET NAMES utf8 and COLLATION.But meh idk why 30% of data corrupted after sed. I think bulk load will be the fastest but maybe something more exist that i'm missing. Thank you Mark – ineersa Jun 18 '13 at 13:13
  • 1
    @ineersa data corruption can have many reasons: e.g. you opened the file with an editor that doesn't support all chars and saved it. Or, the way you try to import from dump corrupts the data (it's buggy and can't read the file properly). Or, same guy may identify part of some data as an expression (e.g. "james\robin" == "\r" as expression) or command, etc. This is why I never recommend using dump, not even with binary data dump tool only, not even with dev.mysql.com/doc/refman/5.6/en/mysqldump.html (or BCP for MS SQL Server). It goes wrong far too many times... – user23243 Jun 18 '13 at 13:22
  • yeap i tried with hex-blob. it doesnt help. Also you right after using sed mysql identify \' as command in some names(not in all). Thats strange and buggy. Will try bulk load tonight. Hope it will be done atleast in 10-15 hrs. – ineersa Jun 18 '13 at 13:38
  • @ineersa hope it will. you can also try adding only part of the data, let's say 10% of it to see how much time that takes - and have an estimate for the whole transaction. It'll be a very rough estimate though, things can go slow if caches/memory/whatever gets filled up / overloaded. – user23243 Jun 18 '13 at 13:42
  • 1
    Thanks Mark. Worked awesome. Even faster then restore from dump. Took ~ 5 hrs. – ineersa Jun 19 '13 at 12:30
5

Your sed idea is a decent method, but without the errors or the command you ran, we can't help you.

However, a well known method for making online changes to large tables is pt-online-schema-change. The simplistic overlook of what this tool does is copied from the documentation:

pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table.

This method might also take a while to complete, but during the process the original table will be completely useable.

  • I will try bulk loading later tonight. If it wont work will need this tool probably. Errors are caused by inetifieng some symbols after using sed as commands. For example 'D\'agostini' will cause error unknown command '\''. But not always , like in 30% of cases. Thats strange and buggy. Same comes even with hex-blob dumps. Thank you Derek. – ineersa Jun 18 '13 at 13:46
2

alter table add column, algorithm=inplace, lock=none will alter a MySQL 5.6 table without copying the table and without locking impact.

Just tested this yesterday, mass inserted 70K rows into a 280K row 7 partition table, 10K rows into each partition, with 5 seconds sleep in between to allow other throughput.

Started the mass inserts, then in separate session started the online alter statement above in MySQL Workbench, the alter finished before the inserts, two new columns were added, and no rows resulted from the alter meaning MySQL did not copy any rows.

  • 1
    Why this answer is not getting more votes?, is it not working? – fguillen Dec 20 '17 at 10:22
1

I think mydumper/myloader is a good tool for operations like this: Is getting better every day. You can utilise your CPUs and can load data in parallelhttp://www.percona.com/blog/2014/03/10/new-mydumper-0-6-1-release-offers-several-performance-and-usability-features/

I have managed to load hundreds of gigabytes mysql tables in hours.

Now, when it comes to adding a new column, is tricky as MySQL copies the whole table across to memory TMP area with ALTER TABLE... Although MySQL 5.6 says it can do online schema changes, I haven't managed to do them online for massive tables with no lock contention as yet.

1

Currently, the best option for altering huge tables is probably https://github.com/github/gh-ost

gh-ost is a triggerless online schema migration solution for MySQL. It is testable and provides pausability, dynamic control/reconfiguration, auditing, and many operational perks.

gh-ost produces a light workload on the master throughout the migration, decoupled from the existing workload on the migrated table.

It has been designed based on years of experience with existing solutions, and changes the paradigm of table migrations.

-2

i just had the same problem. A little workaround:

CREATE TABLE new_table SELECT * FROM oldtable;

DELETE FROM new_table

ALTER TABLE new_table ADD COLUMN new_column int(11);

INSERT INTO new_table select *, 0 from old_table

drop table old_table; rename table new_table TO old_table;

  • Why not just add a where clause to the create table statement so it won't select any data? Also truncating the table would be more efficient then deleting the data – Joe W Nov 20 '18 at 15:21

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.