Big Dumps And Upload Speed

[Taxidermista]

First of all a big-big thanks for your program, and sorry for my poor english. 🙂

Now the question: I'm trying to restore 4 tables in an Invision Power Board database from a previous phpMyAdmin backup, they are pretty big: 25, 40, 60 and 260 MB, so I'm using the DB > IMPORT FROM SQL DUMP… feature of the program. All went smoothly but for one thing: speed. Netlimiter monitoring shows a constant 2 KB/s SQLyog upload speed.

Is it normal? Should I expend almost 29 hours to upload my 260 MB table?? I've been searching these forums from top to bottom and nobody's talking about upload speeds. I don't know what to think about this.

Thanks a lot for your help. 🙂

[Ritesh]

Are you using HTTP Tunneling or Direct Connection?

[peterlaursen]

To me it sounds like the server is overloaded! Or just badly configured!

I have experienced write speed at my webhost at 6-8 KB/sec but not as low as 2 🙁

Sometimes load varies a lot depending on time of day or week.

BTW: How do the insert statements look like?

If there is one statement for each row, it can be MUCH slower than if the statements are longer. No matter … there is not much you can do about it this time.

[Taxidermista]

Ritesh wrote on Sep 9 2005, 05:59 AM:

Are you using HTTP Tunneling or Direct Connection?

[post=”7110″]<{POST_SNAPBACK}>[/post]

Direct connection.

peterlaursen wrote on Sep 9 2005, 07:02 AM:

To me it sounds like the server is overloaded! Or just badly configured!

I have experienced write speed at my webhost at 6-8 KB/sec but not as low as 2  🙁

Sometimes load varies a lot  depending on time of day or week.

BTW: How do the insert statements look like?

If there is one statement for each row, it can be MUCH slower than if the statements are longer.  No matter … there is not much you can do about it this time.

[post=”7111″]<{POST_SNAPBACK}>[/post]

Yes, one for row. The server is dedicated (or this is what they said, who knows…) and the forum is obviously down right now so no reason for overloading.

I'll try later some SQLyog-only backup/restore operations to see what happens.

[Ritesh]

Very strange that its so slow on a direct connection. Do you find any sluggishness with other options?

BTW, whats your connection speed?

[Taxidermista]

Ritesh wrote on Sep 9 2005, 12:32 PM:

Very strange that its so slow on a direct connection. Do you find any sluggishness with other options?

Nope, and ftp upload is fast, 32 KB/s sustained.

Quote:

BTW, whats your connection speed?

[post=”7114″]<{POST_SNAPBACK}>[/post]

DSL 1024/380, typical spaniard “broadband”. 😆

[Ritesh]

Are your SQL dump as BULK INSERTS or INDIVIDUAL INSERTs?

Can you try importing the dump from the mysql command line tool?

[Taxidermista]

Ritesh wrote on Sep 9 2005, 12:49 PM:

Are your SQL dump as BULK INSERTS or INDIVIDUAL INSERTs?

Can you try importing the dump from the mysql command line tool?

[post=”7116″]<{POST_SNAPBACK}>[/post]

They are indivual inserts. Small tables upload with phpMyAdmin go fast, I don't know nothing about mysql command line tools. 🙁

[Ritesh]

Now I am really at lost of ideas. Starting from v4.05, we have vastly improved the Import data from SQL dump module and our profiling shows that its as fast as the MySQL command line tool (in some cases even better).

The most strange fact is that all other options work as expected 😮

[Taxidermista]

Thanks anyway. I guess we are dealing with a really bad hosting here. Any suggestions about good hosting options are welcome (if it's not against the rules).

[peterlaursen]

Quote:

They are indivual inserts.

That is the problem, I believe. A server configuration issue that re-negotiation of the connection for the execution of each insert is so slow.

Try a test: Export a subset of a database with about 100-500 KB of data in one insert statement import it again and see what happens.

Actually, if you use SQLyog for export you can have BULK insert statement or INDIVIDUAL insert statements. I miss the option to choose a BULK size myself. The default BULK size is almost 5 MB and that is too much for my server configuration, and using INDIVIDUAL statements is slow.

[Taxidermista]

peterlaursen wrote on Sep 9 2005, 01:40 PM:

That is the problem, I believe.  A server configuration issue that re-negotiation of the connection for the execution of each insert is so slow.

Try a test: Export a subset of a database with about 100-500 KB of data in one insert statement import it again and see what happens.

Actually, if you use SQLyog for export you can have BULK insert statement or INDIVIDUAL insert statements.  I miss the option to choose a BULK size myself. The default BULK size is almost 5 MB and that is too much for my server configuration, and using INDIVIDUAL statements is slow.

[post=”7120″]<{POST_SNAPBACK}>[/post]

I'll do that, I'll try SQLyog backup and play with the bulk size option.

[Author]

Posts