Skip navigation links

MySQL Forums :: InnoDB :: Performance Problems for large inserts into INNODB table via LOAD DATA


Advanced Search

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


Subject Views Written By Posted
Performance Problems for large inserts into INNODB table via LOAD DATA 3479 Michael Vitale 12/27/2010 09:59AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1175 Rick James 12/29/2010 10:17AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1682 Michael Vitale 02/27/2011 07:11AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 937 Michael Vitale 02/27/2011 07:18AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1376 Rick James 02/27/2011 09:04AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1560 Michael Vitale 02/28/2011 09:01AM
Re: Performance Problems for large inserts into INNODB table via LOAD DATA 1526 Rick James 02/28/2011 11: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.