We have just migrated a huge installation to InnoDB from MyISAM.
While I can feel the power and potential and appreciate this
sophistication of what I have at my disposal, I am having some
performance issues that are most likely due to my ignorance.
Answers to the following questions should help me greatly. Forgive
me, I'm sure these are answered in the manual as a whole but I'm
having trouble pulling it all together as I am operating without sleep.
Let AUTOCOMMIT=1
INSERT INTO foo (id,b,c) VALUES (3,'foo','buh'), (4,'baz','bar');
Separate transaction for each inserted row or one for the entire statement?
Likewise:
UPDATE foo SET c = 'gwah' WHERE id = 3 OR id = 4
Separate transaction for each updated row or one transaction?
I can make good guesses as to what happens, but it'd quell my fears
to have a definitive answer.
Also, since we handle session management in the mysql database, there
are a disproportionately high number of INSERTs which can cause a COMMIT
for literally every page hit (of which there can be about 75 per second)
With the option:
innodb_flush_log_at_trx_commit=1
am I correct in assuming that each COMMIT generates disk I/O and will not
return until it's written to the log? Whereas setting:
innodb_flush_log_at_trx_commit=0
will cause COMMIT to return immediately, and hit the disk much less
frequently with the understanding that in a crash we would be
out about a second's worth of data?
My thanks in advance.
--
Michael Bacarella | Netgraft Corporation
| 545 Eighth Ave #401
Systems Analysis | New York, NY 10018
Technical Support | 212 946-1038 | 917 670-6982
Managed Services | [EMAIL PROTECTED]
---------------------------------------------------------------------
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
