I use mysql as my database server.
I do a lot of batch adds to a database table.
Must I do a database commit occasionally or after every transaction to
flush transaction queues.

On 22 June, 03:51, SpreadTooThin <bjobrie... (AT) gmail (DOT) com> wrote:
Only if you are running with the InnoDB transactional engine.

On Jun 22, 3:35*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:
Indeed I am using InnoDB.
But must this commit be within the scope of a transaction...
or is a simple commit statement after every INSERT command sufficient?

On Jun 22, 3:35*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:
Indeed I am using InnoDB.
But must this commit be within the scope of a transaction...
or is a simple commit statement after every INSERT command sufficient?

Op 22-6-2010 20:37, SpreadTooThin schreef:
i suggest you read this chapter:
http://dev.mysql.com/doc/refman/5.1/...nsactions.html

once, when i have more time, and start using those transactions,
i will read it too ;-)


--
Luuk

On Tue, 22 Jun 2010 11:54:49 -0700 (PDT), SpreadTooThin wrote:
It suffices, but it's a lot slower than it needs to be.

--
42. When I capture the hero, I will make sure I also get his dog,
monkey, ferret, or whatever sickeningly cute little animal capable
of untying ropes and filching keys happens to follow him around.
--Peter Anspach's list of things to do as an Evil Overlord

On 2010-06-22 20:54, SpreadTooThin wrote:
Committing to often or to rarely will both lead to problems.
Transactions are something that needs to be designed to keep your data
consistent and maximise throughput. This will of course be very
difficult without a clear understanding of what a transaction is, so I
suggest you do some reading on transactions, isolation levels and
various phenomena (dirty read, phantom read etc.) that might occur.


/Lennart

SpreadTooThin <bjobrien62 (AT) gmail (DOT) com> wrote:
RTFM!

What is auto-commit and how to use explicite transactions:
http://dev.mysql.com/doc/refman/5.1/...var_autocommit
http://dev.mysql.com/doc/refman/5.1/en/commit.html

If auto-commit is good for performance:
http://dev.mysql.com/doc/refman/5.1/...db-tuning.html

how number of inserts per commit influences performance, page 4 here:
http://www.shinguz.ch/MySQL/transaction_performance.pdf


XL

On 6/21/2010 10:33 PM, Michael Vilain wrote:
If you're doing large batch adds, consider the LOAD DATA command.
It's many times faster than doing individual inserts. All the
index updating is done at one time, at the end of the data load,
instead of after each record. See
"http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html"

John Nagle