Steve,
you should set autocommit=0 and only call commit after the
whole batch of inserts.
If you use autocommit=1 and insert each row in a separate transaction,
the database has to flush the log physically to disk after each
insert, which means only some 50-200 insertions per second, depending
on the rotation speed of your disk.
I will look into the log flush behavior of InnoDB. I now call
fsync (= file flush) twice after each log write, and the write is
done with a separate i/o thread. I have to try doing the write with the
same thread, and calling fsync only once. Ideally, we should be able
to make one commit for each rotation of the disk.
What is your operating system? Are you using an IDE disk?
Regards,
Heikki
http://www.innobase.fi
>I've been doing some testing to determine tradeoffs with performance
>for using the Innobase or BDB tables.
>The testing application contains bulk inserts which must be processed
>as quickly as possible. The results I'm getting look very bad for
>innobase. Am I doing something wrong?
>
>I'm inserting 49,990 rows via file containing individual inserts
>(1 insert per sql statement). The table contains 7 columns and 6indexes.
>Why is there so much difference on the time taken to insert thefiles?
>averages:
>
>MyISAM 45 seconds
>INNODB 1000 seconds
>BDB 600 seconds
>
>The machine is a development testing machine and is memory challenged
>(192megs ram) these inserts were the only significant process on the
>machine at the time (each ran 4 times).Here are my innodb settings
>---------------------------------------
>innodb_data_file_path = ibdata:800M
>innodb_data_home_dir = /usr/mysql/ibdata
>set-variable = innodb_mirrored_log_groups=1
>innodb_log_group_home_dir = /usr/mysql/iblogs
>set-variable = innodb_log_files_in_group=3
>set-variable = innodb_log_file_size=50Mset-variable = innodb_log_buffer_size=8M
>innodb_flush_log_at_trx_commit=1innodb_log_arch_dir = /usr/mysql/iblogs
>innodb_log_archive=0set-variable = innodb_buffer_pool_size=20M
>set-variable = innodb_additional_mem_pool_size=20M
>set-variable = innodb_file_io_threads=4
>set-variable = innodb_lock_wait_timeout=50
>-------------------------------------------------
>I expected some loss over MyISAM but the magnitude is much higher than
>expected, especially in light of the fact that others have been seeing
>innodb to be as fast or faster than MyISAM.
>---------------------------------------------------------------------
>Before posting, please check: http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
