[ensembl-dev] mysqlimport stuck on human variation

[Liu, Mingyi]

Thanks I’ll check it out next time – this time in fact after the removal of the indices AND a very long wait the DB actually finished importing. I don’t know if the removal of indices help or not (didn’t feel like so as the speed seemed to be as slow as with the indices, but could’ve have). For next Ensembl import if I see the issue again I’ll try the settings you suggested.

Mingyi

From: dev-bounces at ensembl.org [mailto:dev-bounces at ensembl.org] On Behalf Of Will McLaren
Sent: Monday, January 18, 2016 9:16 AM
To: Ensembl developers list <dev at ensembl.org>
Subject: Re: [ensembl-dev] mysqlimport stuck on human variation

Hello,

One possible reason is the the temporary filesystem which MySQL uses is not large enough to contain the temporary files. This is set with the tmpdir= parameter in the my.cnf MySQL configuration file on your MySQL server. If your tmpdir is set to /tmp and you're on a system which reveals via df that /tmp is a small tmpfs type affair it may well be worth changing it.

You may also try setting the myisam_sort_buffer_size and myisam_max_sort_file_size parameters to higher values before starting the import.

Regards

Will McLaren
Ensembl Variation

On 15 January 2016 at 21:03, Liu, Mingyi <Mingyi.Liu at bms.com<mailto:Mingyi.Liu at bms.com>> wrote:
Hi,

For Ensembl83 in house installation, I downloaded all mysql files and ran the import commands. It always gets stuck on this step:

mysqlimport -P xxxx -L -h xxxx -u root -pxxxx homo_sapiens_variation_83_38 allele.txt

I noticed that the speed of import was initially fairly fast but slows to a crawl as the import continues for many hours - typically 3 days stuck on this import would still only import 30 GB of the 50GB of data and it just goes slower and slower and may never finish. I redid the step completely a couple times and the same issue. I then modified the homo_sapiens_variation_83_38.sql to get rid of all the 4 indices on the allele table and still the same thing.

Anyone else seeing this? Our mysql is “mysql  Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0”.

Thanks,

Mingyi
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

_______________________________________________
Dev mailing list    Dev at ensembl.org<mailto:Dev at ensembl.org>
Posting guidelines and subscribe/unsubscribe info: http://lists.ensembl.org/mailman/listinfo/dev
Ensembl Blog: http://www.ensembl.info/

________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20160119/0a5d62bd/attachment.html>