Loading sql into mysql database, >500MB how long does this usually take?

Question

I'm doing this with UNIX, is there a way to see a progress bar or something like that? I'm afraid it's just stitting there, but I don't want to stop it.

Answer 1

How long it takes depends on a lot of factors, including whether this is a shared or dedicated machine, what CPU, memory and disk speed, what kind of MySQL tables you're loading into (not just the engine type, but also how many indexes and triggers you have on the tables).  

To check progress, log in from another terminal and run 'top' if you have it.  It gives the processes - your load database process should be at or near the top of the list, indicating it's cranking away, and taking up most of the CPU.  If you don't have 'top', look up man pages for the 'ps' command ('ps -ef' was what I used to use on Sun a long time ago).  That tells you it's running, and what else is competing with it for resources.  Now to actually check progress you'll have to log into mysql (from command line or via a graphical query tool) and run some queries to see what's been done to check actual progress.  (You should have some idea what's being loaded.)    

For example, you could run the 'show table status' query (or 'mysqlshow --status <db_name>' from the command line) to see how many tables have been loaded, and how many rows have been loaded into each.

Answer 2

There is no definite answer here because it totally depends on your systems disk speed and cpu speed. I would think for most average systems it would take about 15 minutes.

Answer 3

If you did a SOURCE command to build it, you might go out for a cup of coffee or two...

Or, maybe a short vacation even...

Depending on the complexity of the database, it can take a looooong time. I've seen 100MB databases that were somewhat complex take the better part of two hours to build using the SOURCE command...

Answer 4

Not sure with mysql, but a 2GB database takes about 1 to 2 minutes in postgresql. (including reindexing).