MySQL Forums
Forum List  »  InnoDB

Re: Performance Problems for large inserts into INNODB table via LOAD DATA
Posted by: Michael Vitale
Date: February 27, 2011 07:11AM

Here is an update on my LOAD DATA situation. To repeat:

Environment:
- Centos 5.5 Linux 2.6.18-194.11.4.el5 x86_64 x86_64 GNU/Linux
- 2 dual quad core processors (8 CPUs)
- 64G RAM

38G of memory is devoted to InnoDB (innodb_buffer_pool_size=40802189312)

my.cnf:
[mysqld]
max_allowed_packet=16M
port=3306
datadir=/vol0/opt/mysql
socket=/var/lib/mysql/mysql.sock
innodb_data_file_path=ibdata1:200M;ibdata2:50M:autoextend
innodb_buffer_pool_size=40802189312
innodb_additional_mem_pool_size=500M
innodb_buffer_pool_instances=20
innodb_log_files_in_group=4
innodb_flush_log_at_trx_commit=1
innodb_log_file_size=524288000
innodb_log_buffer_size=10485760
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_io_capacity=2000
interactive_timeout=28800
net_write_timeout=60
wait_timeout=28800
connect_timeout=60
net_read_timeout=60
default-storage-engine=InnoDB
innodb_file_per_table=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


Load Process Description:
Running LOAD DATA commands from a python script file on the MySQL server. Data files are also on the MySQL server. No other processes are running during the LOAD DATA process. Before running the loads, executed the following commands from that script file for the load data connection session:
-- SET foreign_key_checks = 0
-- SET unique_checks = 0
-- SET sql_log_bin = 0

I do an explicit commit every 90 seconds.

My overall rate of inserts per second is 300-600 inserts per second.

Options: ReplyQuote




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.