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 2524 Jun Zhang 08/03/2012 09:44AM
Re: mysqlimport get very slow for large files 1605 Aftab Khan 08/03/2012 11:04AM
Re: mysqlimport get very slow for large files 935 Jun Zhang 08/03/2012 03:06PM
Re: mysqlimport get very slow for large files 876 Aftab Khan 08/04/2012 04:35AM
Re: mysqlimport get very slow for large files 897 Jun Zhang 08/07/2012 08:12AM
Re: mysqlimport get very slow for large files 774 Aftab Khan 08/07/2012 08:39AM
Re: mysqlimport get very slow for large files 828 Jun Zhang 08/07/2012 10:21AM
Re: mysqlimport get very slow for large files 706 Rick James 08/05/2012 08:58AM
Re: mysqlimport get very slow for large files 620 Jun Zhang 08/07/2012 11:07AM
Re: mysqlimport get very slow for large files 696 Rick James 08/08/2012 08:34AM
Re: mysqlimport get very slow for large files 587 Jun Zhang 08/08/2012 04:19PM
Re: mysqlimport get very slow for large files 1138 Aftab Khan 08/09/2012 04:15AM
Re: mysqlimport get very slow for large files 687 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.