On Mon, 5 Jul 2004 18:48:50 +0100 , Javier Diaz <[EMAIL PROTECTED]> wrote:
> > I really don't like the idea to set innodb_flush_log_at_trx_commit to 2, the > information in these tables is important. On the other hand there is nothing > I can do from the point of view of the number of transactions. Each process > run its own set of INSERTs and UPDATEs statements, so I can not reduce the > number of transactions being executed. > > Looking to the MySQL documentation: > >> Since the rotation speed of a disk is typically at most 167 > revolutions/second, that constrains the number of commits to the same > 167th/second > >> if the disk does not fool the operating system > > And that we are doing a LOT MORE INSERTs by second, I'm afraid maybe the > only solution is go back to MyISAM :-( > > By the way this figure of 167 revolutions/second is based on what kind of > hard disk? Well, if you are using myisam you already have even fewer guarantees about transactional integrity than innodb with innodb_flush_log_at_trx_commit set to 2. That is the only reason that myisam can perform as it does in the manner you are using it. So if that is all that is worrying you, no reason not to try innodb setup that way. You may want to look more closely at how you may be able to re architect your system to not require so many transactions, such as by having a middle tier that can aggregate information before committing it. Unfortunately, myisam tricks people into thinking disk based databases can safely handle the sort of operation you are doing, then leaves them in an unfortunate situation when they realize that myisam has no durability guarantees. A ballpark figure that applies to disk based databases is that you can do approximately one write operation per rotation, which translates into one transaction per rotation. This logic makes some assumptions and isn't exact with modern disks, but is a reasonable ballpark. 167 revolutions per second is a 10k RPM drive. You can improve this with the right type of RAID, you can improve it with faster disks, but it is still a fairly small number. You can improve it further with a battery backed disk controller that can cache writes, although the reliability of some of the cheaper options there isn't great. You can improve it with a database that doesn't commit to disk, such as mysql cluster however that is a whole different ballpark and a ways from being ready for prime time and has all sorts of issues of its own. Some databases can be smart and coalesce commits from multiple connections into one write to disk, but this is a fairly uncommon feature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
