MySQL Forums
Forum List  »  Performance

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
Re: mysqlimport get very slow for large files
1613
August 07, 2012 08:12AM


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.