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 3499 Jun Zhang 08/03/2012 09:44AM
Re: mysqlimport get very slow for large files 2270 Aftab Khan 08/03/2012 11:04AM
Re: mysqlimport get very slow for large files 1251 Jun Zhang 08/03/2012 03:06PM
Re: mysqlimport get very slow for large files 1148 Aftab Khan 08/04/2012 04:35AM
Re: mysqlimport get very slow for large files 1158 Jun Zhang 08/07/2012 08:12AM
Re: mysqlimport get very slow for large files 1003 Aftab Khan 08/07/2012 08:39AM
Re: mysqlimport get very slow for large files 1055 Jun Zhang 08/07/2012 10:21AM
Re: mysqlimport get very slow for large files 900 Rick James 08/05/2012 08:58AM
Re: mysqlimport get very slow for large files 796 Jun Zhang 08/07/2012 11:07AM
Re: mysqlimport get very slow for large files 898 Rick James 08/08/2012 08:34AM
Re: mysqlimport get very slow for large files 745 Jun Zhang 08/08/2012 04:19PM
Re: mysqlimport get very slow for large files 1592 Aftab Khan 08/09/2012 04:15AM
Re: mysqlimport get very slow for large files 880 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.