Jump to content


Photo

Slow Upload


  • Please log in to reply
10 replies to this topic

#1 dbusr

dbusr

    Advanced Member

  • Members
  • PipPipPip
  • 33 posts

Posted 20 April 2012 - 02:31 AM

I just started using SQLYog Enterprise. One of the first things I tried was uploading. I had been using the community edition and it took forever to upload. I assumed the purchased version would remove a speed restriction,but apparently not. I found a few other posts about this problem but they were not resolved. Suggestions were made, like using block statements, but nothing made a difference for me. Am I correct that there's no fix for this?

#2 ashwin

ashwin

    Advanced Member

  • Members
  • PipPipPip
  • 275 posts
  • Gender:Male
  • Location:Bangalore, India

Posted 20 April 2012 - 04:50 AM

Could you please tell-

1) SQLyog(Help->About) and MySQL version(SELECT VERSION())

2) What type of connection(Direct/HTTP/SSH)?

3) Are you using Execute SQL script tool to upload SQL file? How big is the file?
Is the .SQL generated with SQLyog and if yes how is BULK settings?
http://www.webyog.co.../24_101_en.html

#3 dbusr

dbusr

    Advanced Member

  • Members
  • PipPipPip
  • 33 posts

Posted 20 April 2012 - 12:11 PM

1 - Enterprise 9.63

2 - Tried MySQL (which I assume is what you mean be Direct) and SSH. There's no noticeable difference.

3 - Yes, Execute SQL. It doesn't matter how big it is. If it is a small file (less than 5 - 10 MB) I will generally use phpmyadmin on the server but I tried a 3 MB file just now and after a minute it had uploaded 5%. I tried a large file (100 MB) and after several minutes it was still at 0%. Yes, the file is created with SQLYog. I tried it with the create buld statements on and off and with the chunk setting on and off. No difference at all with any of the changes,that I can see.

#4 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 20 April 2012 - 01:35 PM

With SQLyog you are connecting from a remote host to MySQL. With phpMyAdmin your are (usually) connecting from 'localhost'. Are you sure it is not a connection issue? Maybe you could try another client installed on same machine as SQLyog. If you have a local MySQL server you can use command line client (refer http://dev.mysql.com...h-commands.html).

Maybe also you could tell if you have binlogging enabled on the server with a "sync_binlog = 1" setting?
Computers make your grey hair come off ....

Peter Laursen
Webyog

#5 dbusr

dbusr

    Advanced Member

  • Members
  • PipPipPip
  • 33 posts

Posted 20 April 2012 - 06:24 PM

No, I don't think it's a connection issue. I can use the putty to upload a 100 MB file in less than a minute.

#6 ashwin

ashwin

    Advanced Member

  • Members
  • PipPipPip
  • 275 posts
  • Gender:Male
  • Location:Bangalore, India

Posted 23 April 2012 - 07:12 AM

Actually transfering data to MySQL takes time. "Putty uploads a 100 MB file in less than a minute"- Did you connect to MySQL and imported the file?
But most likely this is a server/remote network configuration issue. The remote host only absorbs the data in that rate. If you try importing from MySQL command line client the result would be the same(http://dev.mysql.com...h-commands.html).

#7 dbusr

dbusr

    Advanced Member

  • Members
  • PipPipPip
  • 33 posts

Posted 23 April 2012 - 02:30 PM

I'm not sure I follow what you are saying but if I use SQLYog to download a large database, it does it in under a minute. If I upload, it takes 10 hours or so, depending upon the size. I realize there is a difference in upload/download speeds but that wouldn't account for that. If the server wre throttling down the upload, it seems it would also do it with putty. People have mentioned in this thread, and in the the others I've found regarding this subject, that it is the server but everything seems to point to it being in this program. Is there a way to test the upload to confirm where the problem is at?

#8 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 25 April 2012 - 12:16 PM

You do not 'upload'. This is the basic misunderstanding here. This is NOT file transfer. The file contains SQL statements that each will need to be *executed* by the MySQL server. The server will execute one statement, reply back to the client (in case SQlyog) that it is ready for next statement etc.

Actually what happens inside SQLyog here is very little. It will be waiting for the 'go' from the server most of the time (for each statement). And if the server executes slowly or communication over the MySQL protocol to/from this server is slow this may be the problem. What kind of server is this? Is it some kind of 'shared hosting´?

You will need to compare with another client installed on same host/machine as SQLyog. For instance the 'mysql' command line client with the 'source' statement.

Alternatively you can let us try. We will then need to be able to connect. In that case please mail to support@webyog com with connection details and explain us how to reproduce this - including attaching the script.
Computers make your grey hair come off ....

Peter Laursen
Webyog

#9 dbusr

dbusr

    Advanced Member

  • Members
  • PipPipPip
  • 33 posts

Posted 27 April 2012 - 03:01 AM

Ahh, thanks for the clarification. It's a Linux shared server. SQLyog is setup on my computer running as a server (using wamp). If I upload locally, then it only takes a minute, if that. It is just when I try to populate the database on a different server that it is slow. I understand what you are saying about SLQyog waiting and the server is the one causing the delay and that may well turn out to be the problem. Though the fact that putty doesn't take much time is confusing to me.

In any event, I will setup a test account where you can see it yourself. I truly appreciate you offering to do this.

#10 dbusr

dbusr

    Advanced Member

  • Members
  • PipPipPip
  • 33 posts

Posted 28 April 2012 - 04:40 PM

Just a follow-up to complete this thread and hopefully save others from the same problem in the future. Peter tested the upload and it turned out to be the size of the bulk option was not set high enough. I had enabled that option but didn't change the size since I thought the server would provide the best setting. After changing it to 500, a 100 MB file uploads in 1-2 minutes.

The quality of SQLyog is only topped by the excellent support of the people at SQLyog. I don't think I've ever dealt with a company that offers such excellent service.

#11 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 29 April 2012 - 09:16 AM

Actually we have this FAQ (that I probably should have linked to from the beginning): http://www.webyog.co...and-bulks.html: "Basically you will have to experiment a little by yourself to find the settings that are optimal for you - and they of course may be different with different hosting providers if you have more. A practical experience with most cheap hosting is that CHUNKs setting of 2000-10000 (rows - depending on how many and what type of columns you have) and BULK setting of 100-500 (KB) normally work fine."

With shared hosting some memory restriction/quota may be in place per user or per connection. That could explain why a BULK size equalling 'max_allowed_packet' setting does not work well on this environment.
Computers make your grey hair come off ....

Peter Laursen
Webyog




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users