Hi! Good questions!
----- Original Message ----- From: "Michael Bacarella" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, May 16, 2002 6:41 PM Subject: MySQL/InnoDB questions from a MyISAM background > 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? AUTOCOMMIT=1 means that a COMMIT is automatically executed after each SQL statement. It does NOT mean a commit after each row, if the statement happens to modify several rows. > Likewise: > > UPDATE foo SET c = 'gwah' WHERE id = 3 OR id = 4 > > Separate transaction for each updated row or one transaction? Only one transaction because this a single SQL statement, even if it would update 1000 000 rows. > 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? Yes, InnoDB will in this case at a COMMIT call fsync (== physical write to disk) on the log file. The fsync can in some Linux versions take even much longer than the 10 milliseconds required for a disk rotation. Some disks fool the operating system, and consequently fsync may return even in 2 milliseconds, though a physical write to a disk is not possible in that time. >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? That is correct. Note that the MySQL binlog is written to a file after every commit, though MySQL never calls fsync for the binlog file. Thus, if the operating system does not crash, the binlog file (physically residing in the operating system file cache in main memory) may contain all the transactions which were committed during that second, and no transaction is really lost if you pipe the remaining binlog into MySQL after a crash. In 3.23.52 it is my intention that InnoDB will write the log buffer to the log file at each COMMIT, regardless of the value of innodb_flush_log_at_trx_commit. But it will only call fsync (a physical disk write) as before. Thus, if the operating system does not crash, the InnoDB automatic crash recovery will normally recover every transaction, regardless of the value of ..._trx_commit. It is also possible that I will make this strategy of using the operating system file cache as 'non-volatile' storage optional, that is, there will be a value 2 which means a compromise between the strategies specified by the current options 0 and 1. We see from the above that making a committed transaction 'durable' is a somewhat gradual process. Since we cannot fully trust disks or operating systems, we cannot with absolute certainty say that at some point the data became permanent on disk. And even if it goes to disk, it is possible that the computer is later destroyed in a fire. One could as well define that a transaction is 'durable' only when there is a backup of the database, or the binlog, stored in a fireproof safe. > 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] Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com --------------------------------------------------------------------- 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
