moving a 13 gb table to amazon rds

Question

I am moving our current database server from a regular EC2 instance running mysql to a RDS instance. I moved all the databases using the amazon-suggested method, simply piping the mysqldump output to a mysql client connected to the remote database (RDS instance). Everything worked fine for most databases, except one. After searching, the cause is a big, 13 Gb table that starts copying OK, but after some time the mysql client throws an error (sadly I didn't take note of the error code when it happened). It was "Connection lost" for sure.

This is the table:

CREATE TABLE `UniqueTextLookup` (
    `UniqueTextLookupID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TextID` CHAR(32) NOT NULL COLLATE 'ascii_bin',
    `CrcTextID` INT(10) UNSIGNED NOT NULL,
    `UniqueText` CHAR(255) NOT NULL,
    `CrcUniqueText` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`UniqueTextLookupID`),
    UNIQUE INDEX `TextID` (`TextID`),
    INDEX `UniqueText` (`UniqueText`(10)),
    INDEX `CrcTextID` (`CrcTextID`),
    INDEX `CrcUniqueText` (`CrcUniqueText`)
)

I suspect that the table size has something to do with the indexes more than the data (it's just a lookup table for unique texts, with some hacks to accelerate queries using CRC32 of some columns). It has 15 millon rows.

Any Ideas? I'm running the dump+pipe again to pinpoint the error code.

Some notes:

I cannot login by ssh to the RDS instance, so dumping to a file and copying by scp to the RDS server is not an option.

EDIT:

Checking with mytop It looks like this query (one of the lasts of the dump) :

/*!40000 ALTER TABLE `UniqueTextLookup` ENABLE KEYS */

gets stalled, it has been running for 322 seconds and counting...

Answer 1

I realize you've already worked around the issue, but here is the explanation and the fix for what you encountered. It's nice to be able to do it with the pipe, so you don't have to write that big file somewhere.

As you know, when you type a query into the mysql command line client, while that query is executing, the client doesn't accept any more input from you. That same thing essentially happens when you're stringing the these tools together with a pipe -- the target mysql client waits for the target server to return from the query before it can read more data from the pipe from mysqldump.

Meanwhile, as mysqldump is being blocked on writing to the pipe, because we're waiting for the target server to finish a query, it stops reading data from its socket connection. The OS will only buffer so much data before it stops accepting data from the origin server.

If we do something like ENABLE KEYS which takes a while, we hit a timeout... on the origin MySQL server. But the timeout value can be changed.

MySQL Server has two timers, net_write_timeout and net_read_timeout, which default to 60 and 30 seconds respectively, and which will cause the serve to tear down a client connection when the timer is exceeded while blocking on a write to or a read from the network.

Most likely, it's net_write_timeout you're hitting, so the origin server is giving up on the connection from mysqldump, which isn't accepting data fast enough (because it's blocked on its output). Or, more precisely, it isn't accepting data often enough. If any one statement takes too long to execute, the game is over, so, on the origin server:

SET GLOBAL net_write_timeout = 3600; # one hour

This is setting approximately how much time we can wait for any single query to finish executing on the destination server without the origin server timing out. Normally, you wouldn't want MySQL to sit and wait for an hour for a blocking client, so set the timer back after you're done.

Answer 2

I did:

mysqldump -u<local-user> --password=<password> --opt aux UniqueTextLookup > ./UniqueTextLookup.sql
-- Dump completed on 2012-12-20 13:57:57

cat ./UniqueTextLookup.sql | mysql -h<rdshost> -u<user> -p<pass> aux  

And it worked.

Answer 3

A 13GB MySQL Dump... outch.

Try pt-archiver from Percona toolkit, it transferts on the fly data between to instances and you can manage steps, ex : transfert 1000 lines by 1000.

It's preatty cool but CAUTION, take care to use --no-delete option, if you don't use it, data will be removed on source after transfert (bad idea...).

Example of command i used :

 ./pt-archiver --no-delete --ignore --statistics --limit 10000 --txn-size 10000 --progress 10000 --where 'ID_VALO between 8000000000 and 8001000000' --source h=<source-IP>,u=root,p=<password>,D=<source-DB>,t=<source-table> --dest h=<destination-IP>,u=root,p=<password>

Let me know