Heikki Tuuri wrote:
>
> Steve,
>
> you should set autocommit=0 and only call commit after the
> whole batch of inserts.
>
so then if I'm inserting via a mysqldump file I should
append commit; to the last line of the file?
> 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 believe with BDB that if I inserted 700megs of data via one
transaction that I would have a 700 meg log file that would have
to be stuffed into the database on commit; it is not the same with
INNODB? If I only have 3 50meg log files is this going to fail?
> 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?
>
linux 2.2.12, yes, this machine has only one IDE disk on it.
> 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
