RE: Major Difference in response times when using Load Infile uti lity

[mos]

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 = 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:teamspike@stripped]
>Sent: Tuesday, September 13, 2005 8:48 PM
>To: mysql@stripped
>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/mysql?unsub=1
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1