RE: Major Difference in response times when using Load Infile uti lity
At 11:31 AM 9/13/2005, you wrote: 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. I'm not that familiar with InnoDb, but I wonder if it could be your transaction/binary log files? Can you turn these off to see if there is a speed improvement? If there is an improvement, then you'll need to move these files to a different drive (different spindle). Mike 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 = 10 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,
RE: Major Difference in response times when using Load Infile uti lity
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 = 10 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/my
RE: Major Difference in response times when using Load Infile uti lity
Hi I am INNODB tables only. (I have only one big table) Autocommit I have already disabled. And it doesn't have any foreign keys in it. Do I still need to set the FOREIGN_KEY_CHECKS to '0'?? 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]
RE: Major Difference in response times when using Load Infile uti lity
Yes, there are indexes on the table. Do you mean to say index is the culprit. sujay -Original Message- From: Peter J Milanese [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 13, 2005 4:58 PM To: Sujay Koduri; mysql Subject: Re: Major Difference in response times when using Load Infile utility Are there indexes on the table? Could be that. --Original Message-- From: Sujay Koduri To: mysql Sent: Sep 13, 2005 5:24 AM Subject: Major Difference in response times when using Load Infile utility hi , I am using the Load Infile utility to load data from file to MySQL DB. When trying to load different amounts of data, I observed a notable difference in the time taken by that. 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. Can some one explain why this difference is coming. Also it will be great if someone can suggest how we can improve the performance of the first test. Thank you sujay - Sent from my NYPL BlackBerry Handheld. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]