Hi all, I found that load infile should not take this much time(6 hrs) to load 5.5 million queries. Some people are saying it should not even take more than 10mins. So I think I am doing something wrong in my my.cnf file. I am Using MySQL 4.1.13 version and 2.4.20 kernel on RH9. I am including the my.cnf as well as the 'show table status' output here. Please let me know if any extra info is needed.
M/c config : 2G RAM and Dual CPU 32-bit. mysql> show table status; +------------+--------+---------+------------+---------+----------------+--- ----------+-----------------+--------------+-----------+----------------+--- ------------------+-------------+------------+-------------------+---------- +----------------+--------------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------------+--------+---------+------------+---------+----------------+--- ----------+-----------------+--------------+-----------+----------------+--- ------------------+-------------+------------+-------------------+---------- +----------------+--------------------------+ | stats | InnoDB | 9 | Dynamic | 4237002 | 1028 | 4357881856 | NULL | 150749184 | 0 | NULL | 2005-09-12 23:16:21 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 16188416 kB | +------------+--------+---------+------------+---------+----------------+--- ----------+-----------------+--------------+-----------+----------------+--- ------------------+-------------+------------+-------------------+---------- +----------------+--------------------------+ 1 row in set (2.41 sec) I am running the load file utility from the server itself. (server config..2G RAM, Dual CPU, RH9, 2.4.20 kernel, alloted 20G of disk space, both logs and data files are on same partition). I am including the my.cnf file which I am using now. Can someone please tell me if I am setting anything seriously wrong. $$cat /etc/my.cnf [mysqld] user=mysql datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock default-table-type=innodb init_connect='SET AUTOCOMMIT=0' transaction-isolation = READ-COMMITTED key_buffer = 250M wait_timeout = 100000 max_connections = 400 connect_timeout = 50 table_cache = 1024 max_allowed_packet = 4M sort_buffer_size = 2M read_buffer_size = 2M binlog_cache_size = 1M max_heap_table_size = 64M max_user_connections = 1000 thread_concurrency = 4 query_cache_type = 0 query_cache_limit = 2M query_cache_size = 32M #thread_stack = 96k tmp_table_size = 32M log_error log_warnings = 2 log_slow_queries long_query_time = 2 log_long_format tmpdir = /tmp # *** INNODB Specific options *** #This conf uses 65%(Between 50 and 80%) of RAM, so glibc crash #should not be a problem. innodb_buffer_pool_size = 700M innodb_data_home_dir = innodb_data_file_path = /data/data1:2G;/data/data2:2G;/data/data3:2G;/data/data4:2G;/data/data5:2G;/ data/data6:2G;/data/data7:2G;/data/data8:2G;/data/data9:2G;/data/data10:2G:a utoextend innodb_fast_shutdown = 1 innodb_file_io_threads = 4 innodb_flush_log_at_trx_commit = 0 innodb_log_buffer_size = 8M innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 90 innodb_log_file_size = 100M innodb_log_files_in_group =5 innodb_log_group_home_dir = /logs [mysql.server] user=mysql basedir=/var/lib [mysqldump] quick max_allowed_packet=16M [safe_mysqld] default-table-type=innodb init_connect='SET AUTOCOMMIT=0' err-log=/var/log/mysqld.log [mysqld_safe] default-table-type=innodb init_connect='SET AUTOCOMMIT=0' err-log=/var/log/mysqld.log open-files-limit = 4096 Thank you sujay -----Original Message----- From: Alan Williamson [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 13, 2005 8:48 PM To: mysql@lists.mysql.com Subject: RE: Major Difference in response times when using Load Infile utility > Test 1 > Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately. > > Test2 > Amount of data - 0.45 million rows. Time Taken - 2 mins approximately. Is this an InnoDB database by any chance? If it is, and it is a clean import, then disable the FOREIGN_KEY_CHECKS. SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0; This is a small tip i picked up on the MySQL documentation that someone had left in the comments and has been to date one of those tips that has literally saved DAYS of my life. a ps Remember to put them back on again after you finish the import SET AUTOCOMMIT = 1; SET FOREIGN_KEY_CHECKS=1; -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
