MySql Import speed(Was mysql version OSX)

Mon Dec 20 14:22:52 EST 2010

Mike,

I agree with Bruno that this is a VERY large dump file! We (MySQL trainers from the original company) have always warned against using mysqldump to create files larger than 3-5GB because the execution of that script to recover the "backup" would take too long to run. But since so few people using MySQL take professional training, they think this is what they should do no matter what size their database. (It is also the only technique offered through the GUI tools...)

If the dump was created using mysqldump, the resulting script will most likely already contain commands for disabling the non-unique keys, but that directive is only applicable to MyISAM tables. The storage engine for each table that you are creating in the script is already assigned within the script. I agree that MyISAM is much faster to import (or INSERT) into (for many reasons), so taking a little time to update that script to make all those tables MyISAM tables should pay off big benefits.

A parameter that can affect your execution speed is --max_allowed_packet. If your script file contains multi-row INSERT statements with a huge number of tuples of data in them, setting this to a large number (of bytes) can "speed the read" of that data.

*******************

Mike,

Just saw your more recent posting.

You appear to indeed have a script file and you are creating InnoDB tables. The file "ibdata1" is the default name for the InnoDB tablespace file. Your guess about all the data going into ibdata1 and the various database directories containing just schema information is correct - although there may also be MyISAM tables in the mix. If so, you will notice an occasional file in those database directories with .MYD and .MYI extensions. those are MyISAM data and index files.

Regards,

David

On Dec 20, 2010, at 1:32 PM, Bruno Del Sol wrote:

> Hi Mike,
> 
> I never had to import such a huge dump file in mysql, so I can only make general recommandations :
> 
> - there are several options you can try in mysqldump while exporting that will speed up the import (but maybe it's already too late for that, if i read you correctly)
> - Forget the workbench GUI, this product is too new to be trusted, use the mysql command line application with the fastest parameters
> - tailor your my.cnf as my-innodb-heavy-4G.cnf sample you'll find in /usr/local/mysql/support-files/
> - disable the replication log
> - if you don't need integrity checks, use myisam tables instead of the default innodb
> - if possible, create the indexes once the import is done
> 
> HTH
> Regards
> Bruno
> 
> By Design
> http://www.bydesign.fr
> Bruno Del Sol
> bruno.delsol at bydesign.fr
> tel (33) 01 48 78 47 37
> 46, rue de La Tour d'Auvergne
> 75009 Paris (France)
> 
> 
> Le 20/12/10 19:00, Mike Matthews a écrit :
>> Hello Bruno,
>> 
>> Bang on the nail with that answer.  Quite why this error is in the installer, just beats me, but going now.  Now for part 2, always is a part 2 of course.  Sorry, but I know nothing of MySql, just Frontbase.
>> 
>> I want to import a customers current dump file, so I can look and see if I want to use the data and move it to our systems, so a speculative day so far.  The data is a huge 48.0 GB file, yup, quite big.  And it is a single file that came from a Linux box called a BigRedBox.  The company has gone kaput, hence my interest.
>> 
>> The import speed is very slow, how can I get it faster please?
>> 
>> I am using the Workbench GUI to import from a single file, I have turned off the log file by setting log-error=NONE, but painfully slow.  It seems to be making a single DB file again, how can I make it go faster, it will take about 3 days currently.
>> 
>> The throughput according to the Workbench is a poor 60kbs.
>> 
>> Thanks
>> 
>> Mike
>> 
>> 
>> 
>> Mike Matthews
>> Managing Director
>> Lineal Software Solutions Ltd
>> 
>> Past Authorised Apple Reseller,  Omnis Business Partner
>> 
>> Tel: 01271 850 550
>> 
>> Email: mike.matthews at lineal.co.uk
>> 
>> Web: www.lineal.co.uk
>> 
>> 
>> On 20 Dec 2010, at 14:11, Bruno Del Sol wrote:
>> 
>>> Hi Mike,
>>> 
>>> The "server won't start" issue is on all 5.5.x version on Mac os. There is a documented workaround which involves editing the mysql.server file, read this for instance :
>>> 
>>> http://www.rickwargo.com/2010/12/16/installing-mysql-5-5-on-os-x-10-6-snow-leopard-and-rails-3/
>>> 
>>> I'm not aware of any reason you shouldn't use the 64 bit version.
>>> 
>>> If you prefer to be more conservative, the latest 5.1.x should be fine also.
>>> 
>>> RegARDS
>>> BRUNO
>>> 
>>> By Design
>>> http://www.bydesign.fr
>>> Bruno Del Sol
>>> bruno.delsol at bydesign.fr
>>> tel (33) 01 48 78 47 37
>>> 46, rue de La Tour d'Auvergne
>>> 75009 Paris (France)
>>> 
>>> 
>>> Le 20/12/10 14:33, Mike Matthews a écrit :
>>>> Thanks David,
>>>> 
>>>> I have the 32 bit version installed today, but the sever won't start.  The conf file won't write or be created, that is why I asked about 32/64 bit.  Downloading the 64 bit version.....
>>>> 
>>>> I have also installed the Workbench app, but not installed any connectors.  Are these needed as well, if I am just using the WorkBench?
>>>> 
>>>> Thanks
>>>> 
>>>> Mike
>>>> 
>>>> 
>>>> 
>>>> 
>>>> On 20 Dec 2010, at 13:28, David Swain wrote:
>>>> 
>>>>> Hi Mike,
>>>>> 
>>>>> Version 5.5.8 has just been released as GA. It has significant performance enhancements over previous versions, among other things. I have not had a chance to test it with Omnis Studio yet as I was teaching a private MySQL bootcamp class (8am - 6pm each day) for a dozen sysadmins at eBay all last week, but hope to get to it before the year ends. I'm sure Mitford House has kept up with it, though.
>>>>> 
>>>>> Regards,
>>>>> 
>>>>> David
>>>>> 
>>>>> On Dec 20, 2010, at 8:16 AM, Mike Matthews wrote:
>>>>> 
>>>>>> Hello All,
>>>>>> 
>>>>>> Which version of MySql should I download please?
>>>>>> 
>>>>>> MacBook Pro, OSX 10.6.5 workstation
>>>>>> 
>>>>>> Thanks
>>>>>> 
>>>>>> Mike Matthews
>>>>>> Managing Director
>>>>>> Lineal Software Solutions Ltd
>>>>>> 
>>>>>> Past Authorised Apple Reseller,  Omnis Business Partner
>>>>>> 
>>>>>> Tel: 01271 850 550
>>>>>> 
>>>>>> Email: mike.matthews at lineal.co.uk
>>>>>> 
>>>>>> Web: www.lineal.co.uk
>>>>>> 
>>>>>> 
>>>>>> _____________________________________________________________
>>>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>>> _____________________________________________________________
>>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>> _____________________________________________________________
>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com