Mysql query hangs on import

Question

I dumped all my databases in order to import them into a slave.

mysqldump --all-databases > dump.sql

The import seems to be very very slow. Checking the processlist, I saw a simple insert on a 20 rows table that took 400+seconds.

I had to ignore some big and not critical tables from the dump in order to import in a reasonable time.

I don't understand if I'm doing something wrong, the whole dump was about 800MB and the "big" table I ignored was no more than 170K records, which I don't think it's big at all...

Also I often dump a single database to load it on a testing environment, usually without problem. It seems the --all-databases dump is very slow.

Any suggestion welcome.

• eroomydna: Share your my.cnf and server spec, please.
• Glasnhost: Oh, yes: mysql 5.1.69 on Centos6.4. Nothing configured in my.cnf, no buffer size, just all defaults I guess, which probably is part of the problem
• eroomydna: not quite the information needed. The variable options used in the mysql config file and the specification of the server (cpu, ram, hdd[raid,num. of disks, write cache policy])
• Glasnhost: just edited now! Nothing in my.cnf and 4GB ram, 1cpu, it's a virtual machine, I don't know about writing policy

Answer 1

I suspect you're suffering 'out-of-the-box' syndrome. For the whole world to know... MySQL config out of the box sucks.

Go to https://tools.percona.com/wizard and create better configuration based on the questions asked in the form. This should provide you with a better starting point. Once the output is generated, add it to your configuration file (/etc/my.cnf) and restart MySQL; review the mysql error log to ensure that your mysql instance started cleanly (beware of paths, permissions and the innodb_log_file_size size differences since that will require disposal of the old ib_log* files before new ones can be created). Once done, try your import again.

• eroomydna: my pleasure, post back your results and if you can advise further anyone that hits the same issue in the future. :)
• Glasnhost: of course I forgot about the innodb_log_file_size change, I had one the worst db crash ever, luckily on my local environment. The tool is good though, just need to be careful