MySql Import speed(Was mysql version OSX)

Andy Hilton andyh at totallybrilliant.com
Mon Dec 20 14:21:39 EST 2010 

Mike

Usually when the workbench 'goes mad' it means it hit an error and simply bypassed a whole chunk of the data so beware....

The command line allows you to set all manner of things like indexing etc in your restrore statement - a quick google will tell you all you need to know about it - but you can do all sorts with it - very very powerful....

Trust it far more than workbench restore.....

Provided the import file was/is a .sql file it will more than likely be a MySQL dump.....

Andy

On Dec 20, 2010, at 2:15 PM, Mike Matthews wrote:

> Hello Andy, Bruno & David,
> 
> After about 40 minutes, the throughput went mad, I have now completed 27gb of import in just under 2 hours, so getting on with it.  When this is done, I'll try Andy's test to see if that is faster.
> 
> The Workbench seems ok then, but a bit odd perhaps, but I used it.  Don't know how to turn off the building of indexes on import but understand the consequences however.  
> 
> I don't have a clue what type of file have here David in truth, but I am creating a new file called ibdata1 in the mysql folder.  There are also 3 other folders that have been created with schemas in them by the looks.  So I guess MySQL is storing the data in this huge file and the definition stuff elsewhere.
> 
> So, when all done, do I also need to install the mySQL connection stuff for Studio 4 or 5 to connect to this big old file?
> 
> Thanks guys.
> 
> Mike
> 
> 
> 
> 
> 
> 
> On 20 Dec 2010, at 18:23, David Swain wrote:
> 
>> Mike,
>> 
>> Yes, I agree with Andy - IF what you have is a SQL script file rather than a true "import" file. Reading in and executing a SQL script is NOT the same as "importing data", although both will lead to adding data to your database (at least, if the script contains INSERT statements, among other things). Reading and executing a script will always be much slower than a true import.
>> 
>> If you have a comma- or tab-delimited file (a file that can actually be imported from), you can either use the mysqlimport command line tool or log into the mysql interactive command line client and issue a LOAD DATA INFILE command with appropriate parameters.
>> 
>> I can help you with any of these if you need.
>> 
>> (Sorry I wasn't here sooner - had to step out for a doctor appointment.)
>> 
>> Regards,
>> David
>> 
>> On Dec 20, 2010, at 1:06 PM, Andy Hilton wrote:
>> 
>>> Mike
>>> 
>>> yes the workbench gui import is pretty dreadful - switch to a command line interface and try something like :
>>> 
>>> mysql --user=root --password=mypassword mynewdbname < c:pathtomydumpfile.sql
>>> 
>>> That will go quite fast !
>>> 
>>> Andy
>>> 
>>> 
>>> On Dec 20, 2010, at 1:00 PM, Mike Matthews wrote:
>>> 
>>>> 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
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com

More information about the omnisdev-en mailing list