How InnoDB flush the log buffer

When InnoDB flush the log buffer to the log file on disk, it locks the buffer with a mutex, flushes up it to a desired point, and then moves any remaining entries to the front of the buffer.

If you care more about performance than durability, you can change innodb_flush_log_at_trx_commit to control where and how often the log buffer is flushed.

0: write the log buffer to the log file and flush the log file every second, and do nothing at transaction commit.

1:write the log buffer to the log file and flush it to durable storage every time a transaction commits.

2: write the log buffer to the log file at every commit, but do not flush it.

In most operating systems, writing the buffer to the log simply moves the data from InnoDB’s memory buffer to the operating system’s cache, which is also in memory.

In contrast, flushing the log to durable storage means InnoDB asks the operating system to flush the data out of the cache and ensure it is written to the disk.

Sometime the disk controller or operating system can fask flush by put the data to yet another cache.

Setting innodb_flush_log_at_trx_commit anything other than 1 can cause you to lose transactions.

the best configuration for high performance transactional needs is to leave innodb_flush_log_at_trx_commit set to 1 and place the log files on a raid volumn backed by a battery backed cache.

PErconer Server extends … to make is a per-session variable, instead of global for a whole server.