2

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.

Improve this question
4
  • Share your my.cnf and server spec, please. – eroomydna Jan 26 '15 at 22:09
  • 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 – Glasnhost Jan 27 '15 at 12:05
  • 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]) – eroomydna Jan 27 '15 at 12:07
  • just edited now! Nothing in my.cnf and 4GB ram, 1cpu, it's a virtual machine, I don't know about writing policy – Glasnhost Jan 27 '15 at 12:09
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.

Improve this answer
2
  • my pleasure, post back your results and if you can advise further anyone that hits the same issue in the future. :) – eroomydna Jan 27 '15 at 18:00
  • 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 – Glasnhost Feb 3 '15 at 21:09

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.