Import From Sql Statements Slow

[DARAB]

When importing a database using Importing from SQL statements on one computer, it takes 30 minutes. When I import the same statements from the same file on another computer that has faster specs, it takes 12-15 hours. Are there settings in MySQL that would impove the import on the faster computer or are there SQLyog settings I need to change?

[peterlaursen]

Is the .SQL generated with SQLyog and if yes how is BULK settings?

http://www.webyog.com/faq/24_101_en.html

I would experiment a little with different BULKs on BOTH servers! Say 100 KB, 400 KB, 1 MB, 4 MB, 10 MB for instance. But the difference is impressive!

also: Do you have direct connection to both? Note that if one (the slow one) is connected with HTTP-tunnel, the HTTP-connection will need to be (re-)established for each statement- and that may take a few seconds every time. So if you have individual INSERT statement (one statement per row and a million rows then calculate yourself (3 seconds * 1.000.000) – where NOTHING AT ALL happens!

[DARAB]

Thanks for responding Peter.

I forgot to mention that I'm importing SQL statements to my laptop locally so there is no network involved and it is still running slowly.

I'm assuming I need to make a change in the my.ini file of MySQL to alter the BULK setting. Do you know what the variable name is for BULK?

[peterlaursen]

thx for quoting me .. but you may say something too 😮

[DARAB]

Sorry about that one post.

I meant to say:

Yes, I created the SQL statement file with SQLyog where I used the Export Database as SQL Statements…

I forgot to mention that I'm importing SQL statements to my laptop locally so there is no network involved and it is still running slowly.

I'm assuming I need to make a change in the my.ini file of MySQL to alter the BULK setting. Do you know what the variable name is for BULK?

[peterlaursen]

the BULK setting is an internal SQLyog setting. It is not a MySQL server parameter.

the BULK specifies how long a generated INSERT statement will be allowed to be in the .SQL file.

You probably have some my.ini 'templates' (named like 'my.large', 'my.medium'. You can try to rename either of those to my.ini and see what difference it makes.

[DARAB]

When you say “the BULK setting is an internal SQLyog setting” are you referring to the “Max Packet Size (in K:cool: for BULK INSERT statements” on the General tab of Preferences in SQLyog? If so, I have unchecked Get Server Default and set the Custom Size to 10000 but no performance improvement.

[peterlaursen]

Yes that was what I meant. It is effective 1O MB. A big One.

[DARAB]

I have changed the “Max Packet Size (in K:cool: for BULK INSERT statements” on the General tab of Preferences in SQLyog to 10000. I ran the import again and it has ben running for over 12 hours and it looks like it has 4-6 hours to go. This same import took 35 minutes on a slower machine. I've compared the my.ini files between the 2 machines and if there are differences, my machine that is taking such a long time to import has higher settings. The version of MySQL on the machine that imports in 35 minutes is a slightly older version and is an alpha. Could there be a different MySQL file that has parameters I need to change?

[peterlaursen]

I wish I could answer this!

anybody else?

[TomBez]

DARAB wrote on Sep 5 2006, 05:50 PM:

I have changed the “Max Packet Size (in K:cool: for BULK INSERT statements” on the General tab of Preferences in SQLyog to 10000. I ran the import again and it has ben running for over 12 hours and it looks like it has 4-6 hours to go. This same import took 35 minutes on a slower machine. I've compared the my.ini files between the 2 machines and if there are differences, my machine that is taking such a long time to import has higher settings. The version of MySQL on the machine that imports in 35 minutes is a slightly older version and is an alpha. Could there be a different MySQL file that has parameters I need to change?

do the databases use different charsets?

what happens if you set your my.ini settings to the same values as the slower machine has?

does the slower machine have a raid-disk-subsystem (or something like a cache-controller)?

are the database engines on both machines the same? it looks like a bit like you would use INNO-DB for your laptop and myisam on the slower machine.

[DARAB]

do the databases use different charsets?

No.

what happens if you set your my.ini settings to the same values as the slower machine has?

No significant change.

does the slower machine have a raid-disk-subsystem (or something like a cache-controller)?

No.

are the database engines on both machines the same? it looks like a bit like you would use INNO-DB for your laptop and myisam on the slower machine.

INNO-DB for both.

[peterlaursen]

Will the 'my.ini' from the fast machine work on the slow machine?

I think we should try if the reason for this in in MySQL configuration by 'reversing the files' if possible.

Is (various types of) logging and/or debugging enabled on the slow server?

Any idea if it could be network related? bad cable and such trivial things?

[TomBez]

DARAB wrote on Sep 9 2006, 08:05 PM:

are the database engines on both machines the same? it looks like a bit like you would use INNO-DB for your laptop and myisam on the slower machine.

INNO-DB for both.

I think it is a problem of indizis, looks like to me that on the slower machine they are deactivated during bulk insert and on the faster machine they are activated.

[DARAB]

What is indizis?

[peterlaursen]

= Indexes

Do you import data into an existing table or do you crete teh table with the import?

[Author]

Posts