Skip navigation links

MySQL Forums :: Performance :: mysqlimport get very slow for large files


Advanced Search

Re: mysqlimport get very slow for large files
Posted by: Jun Zhang ()
Date: August 07, 2012 08:12AM

Aftab, thank you for looking into this. After changing the variables you suggested, mysqld is very slow to start.

mysql> show processlist;
+----+------+-----------+------------------------------+---------+------+-------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------------------------------+---------+------+-------+------------------------------------------------------------------------------------------------------+
| 6 | root | localhost | homo_sapiens_variation_67_37 | Query | 190 | NULL | LOAD DATA LOCAL INFILE '/extraspace/zhangj/homo_sapiens_variation_67_37/allele.txt' INTO TABLE `all |
| 7 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------------------------------+---------+------+-------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

cat /etc/my.cnf
[mysqld]
#datadir=/DATABASE/mysql
datadir=/MySQLdb/yongmao
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
bulk_insert_buffer_size=32M (was 8M)
myisam_sort_buffer_size=500M (was 8M)
key_buffer_size=20GB (was N)
myisam_max_sort_file_size=300GB (was much larger than this)

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

My RHEL 6 has 64G memory.

MySQL 5.1.52

allele.MYD is now growing at the rate of 20M/s, so in half an hour it should be the size of the txt file (33G).

Options: ReplyQuote


Subject Views Written By Posted
mysqlimport get very slow for large files 3388 Jun Zhang 08/03/2012 09:44AM
Re: mysqlimport get very slow for large files 2187 Aftab Khan 08/03/2012 11:04AM
Re: mysqlimport get very slow for large files 1205 Jun Zhang 08/03/2012 03:06PM
Re: mysqlimport get very slow for large files 1110 Aftab Khan 08/04/2012 04:35AM
Re: mysqlimport get very slow for large files 1126 Jun Zhang 08/07/2012 08:12AM
Re: mysqlimport get very slow for large files 963 Aftab Khan 08/07/2012 08:39AM
Re: mysqlimport get very slow for large files 1014 Jun Zhang 08/07/2012 10:21AM
Re: mysqlimport get very slow for large files 868 Rick James 08/05/2012 08:58AM
Re: mysqlimport get very slow for large files 760 Jun Zhang 08/07/2012 11:07AM
Re: mysqlimport get very slow for large files 867 Rick James 08/08/2012 08:34AM
Re: mysqlimport get very slow for large files 713 Jun Zhang 08/08/2012 04:19PM
Re: mysqlimport get very slow for large files 1541 Aftab Khan 08/09/2012 04:15AM
Re: mysqlimport get very slow for large files 847 Rick James 08/09/2012 07:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.