[MySQL] MySQL Database setup recommendation for best results.

[omarfilip]

Matt, Sergey's SCREEN solution did the trick. The DB is in and working.

Any thoughts on the very long import time it required?

[Matt]

It is a bad ISP, or a bad computer.

130 MB should never take that long.

Have you tried another ISp or another computer? I suggest you download a large file like that SQL and test it from:

Different ISP
Different Computer
Different Computer on Different ISP
Same Computer on Different ISP
Same Computer on Same ISP

Doing those tests will tell you where your problem is.

[omarfilip]

No, this was a file already uploaded to my vps and already unzipped. All I had to do was issue the mysql command.

It was all done on the glowhost vps (cpvps118-vern). Bad computer?

[Matt]

Not sure why it took so long, perhaps because of MySQL or perhaps because of the VPS enviroment.

Maybe we can get a DBA to post here and give us a normal import time for a DB of that size.

[omarfilip]

For what it's worth, during the first few import tries (which timed out after 10 minutes) the rate of import was approximately 50MB/10min or 5MB/min. The successful import was 130MB/2.5hr or 0.9MB/min.

[jmarcv]

What was 130MB? The final data or the sql file?
Also may be the format of the sql file
I think possibly what could be an issue is that the redirect method reads the whole file into mem and depending on how mem is arranged, it could have been hitting the swap file pretty heavily. That would slow it down. I'll bet the disk IO was pretty high too with all that reading and writing. Theres no real answer on how long these things should take. I assume this was a one shot deal anyway? If not, I can take a look next time.

[omarfilip]

Yes John-Marc, it was a one shot deal and it's finished.

The file size of the database.sql was ~130MB.

Not sure what you mean by final data vs. sql file.

Perhaps there was something else going on at the same time, maybe the nightly backup, because the first few attempts had a better rate of import than the one that ultimately succeeded.

Omar