I don't see any transactions/commits at all. Doesn't that mean you're
using some flavor of autocommit? (I'm not that familiar with MySQL's
defaults) I also don't see any locking.which must mean you're incurring
locking overhead on a per-insert basis.
The MySQL dump format that I recommended emulating looks like this:
LOCK TABLES `accounts_contacts` WRITE;
/*!40000 ALTER TABLE `accounts_contacts` DISABLE KEYS */;
INSERT INTO `accounts_contacts` VALUES
('6ff90374-26d1-5fd8-b844-4873b2e42091',
'11ba0239-c7cf-e87e-e266-4873b218a3f9','503a06a8-0650-6fdd-22ae-4873b245ae53',
'2008-07-23 05:24:30',1),
...
('7dab11e1-64d3-ea6a-c62c-487ce17e4e41','79d6f6e5-50e5-9b2b-034b-487ce1dae5af',
'7b886f23-571b-595b-19dd-487ce1eee867','2008-07-23 05:24:30',1);
/*!40000 ALTER TABLE `accounts_contacts` ENABLE KEYS */;
UNLOCK TABLES;
Have you tried that? In particular, I'd make sure you hold a write lock
and do all the writes in a single insert (the former probably being much
more important than the latter).
Tom
On Thu, Sep 12, 2013 at 6:10 PM, Ben Companjen <[email protected]>wrote:
> Thanks Anand, Bryan.
>
> My guess was right: not committing every record reduces the load on the
> harddisk and speeds things up, even though it still is pretty slow.
>
> Anyway, I put the script in the OL Dump scripts collection:
> https://github.com/bencomp/oldumpscripts/blob/master/dump2mysql.py
> next to the existing dump2csv.py:
> https://github.com/bencomp/oldumpscripts/blob/master/dump2csv.py
>
> Neither convert everything in every record, but it's all copy/paste from
> here.
>
> Ben
>
>
> On 1 September 2013 23:45, Bryan Fordham <[email protected]> wrote:
>
>> If you want to post your script, perhaps someone can speed it up.
>>
>> Thanks,
>> --B
>>
>> Sent from my phone. Please excuse any spelling or grammar errors.
>> On Sep 1, 2013 5:42 PM, "Ben Companjen" <[email protected]> wrote:
>>
>>> Hi all,
>>>
>>> I created a Python script that reads a dump file and puts the edition
>>> records in a MySQL database.
>>>
>>> It works (when you manually create the tables), but it's very slow:
>>> 10000 records in about an hour, which means all editions will take
>>> about 10 days of continuous operation.
>>>
>>> Does anybody have a faster way? Is there some script for this in the
>>> repository?
>>>
>>> Regards,
>>>
>>> Ben
>>> _______________________________________________
>>> Ol-tech mailing list
>>> [email protected]
>>> http://mail.archive.org/cgi-bin/mailman/listinfo/ol-tech
>>> To unsubscribe from this mailing list, send email to
>>> [email protected]
>>>
>>
>> _______________________________________________
>> Ol-tech mailing list
>> [email protected]
>> http://mail.archive.org/cgi-bin/mailman/listinfo/ol-tech
>> To unsubscribe from this mailing list, send email to
>> [email protected]
>>
>>
>
> _______________________________________________
> Ol-tech mailing list
> [email protected]
> http://mail.archive.org/cgi-bin/mailman/listinfo/ol-tech
> To unsubscribe from this mailing list, send email to
> [email protected]
>
>
_______________________________________________
Ol-tech mailing list
[email protected]
http://mail.archive.org/cgi-bin/mailman/listinfo/ol-tech
To unsubscribe from this mailing list, send email to
[email protected]
