167 small transactions per second on MySQL 4.1 / InnoDB / FreeBSD / FFS / SCSI, which looks like one per rotation.
Presumably the time required to write a block at the
end of the write-ahead log.
5000 per second with --innodb_flush_log_at_trx_commit=0, which only writes the log to disk once per second.
The MySQL documentation claims that this configuration does crash-recovery correctly, though you may lose the last second's worth of transactions.
BTW, in case anyone is thinking, "why would I need more than 167 transactions per second", imagine that you have a web site that displays everyone's "last active" time (for chat maybe). If users are performing some activity once every 30 sec, then you will be pretty much be at your limit with about 5000 simultaneous users.
Robert, did you test MySQL with a parallel load? To give it the best chance at maximizing tps, it will need to be able to combine multiple pending transactions in a single write. Also, can you tell if it's updating it's b-trees? (if not, it may not be able to sustain these rates)
I ran the test with 1,000,000 INSERTs (taking 200 seconds),
with the innodb cache size set to only 200,000 bytes.
So there's a fair chance it updated the b-trees on disk.
5000 per second with --innodb_flush_log_at_trx_commit=0, which only writes the log to disk once per second.
The MySQL documentation claims that this configuration does crash-recovery correctly, though you may lose the last second's worth of transactions.