innodb_buffer_pool_size
如 果用Innodb，那么這是一個重要變量。相對于MyISAM來說，Innodb對于buffer size更敏感。MySIAM可能對于大數據量使用默認的key_buffer_size也還好，但Innodb在大數據量時用默認值就感覺在爬了。 Innodb的緩沖池會緩存數據和索引，所以不需要給系統的緩存留空間，如果只用Innodb，可以把這個值設為內存的70%-80%。和 key_buffer相同，如果數據量比較小也不怎么增加，那么不要把這個值設太高也可以提高內存的使用率。

innodb_additional_pool_size?
這個的效果不是很明顯，至少是當操作系統能合理分配內存時。但你可能仍需要設成20M或更多一點以看Innodb會分配多少內存做其他用途。

innodb_log_file_size
對于寫很多尤其是大數據量時非常重要。要注意，大的文件提供更高的性能，但數據庫恢復時會用更多的時間。我一般用64M-512M，具體取決于服務器的空間。

innodb_log_buffer_size?
默認值對于多數中等寫操作和事務短的運用都是可以的。如 果經常做更新或者使用了很多blob數據，應該增大這個值。但太大了也是浪費內存，因為1秒鐘總會 flush（這個詞的中文怎么說呢？）一次，所以不需要設到超過1秒的需求。8M-16M一般應該夠了。小的運用可以設更小一點。

innodb_flush_log_at_trx_commit? （這個很管用）?
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了調整這個值。默認值1的意思是每一次事務提交或事務外的指令都需要把日志寫入（flush）硬盤，這是很費時的。特別是使用電 池供電緩存（Battery backed up cache）時。設成2對于很多運用，特別是從MyISAM表轉過來的是可以的，它的意思是不寫入硬盤而是寫入系統緩存。日志仍然會每秒flush到硬 盤，所以你一般不會丟失超過1-2秒的更新。設成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務的數據。而值2只會在整個操作系統 掛了時才可能丟數據。?

innodb_flush_log_at_trx_commit

If the value of?innodb_flush_log_at_trx_commit?is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

The default value of this variable is 1 (prior to MySQL 4.0.13, the default is 0).

A value of 1 is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any?mysqldprocess crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However,?InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value.

Note

For the greatest possible durability and consistency in a replication setup using?InnoDB?with transactions, use?innodb_flush_log_at_trx_commit=1,?sync_binlog=1, and?innodb-safe-binlog?in your master server?my.cnf?file.

Caution

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tellmysqld?that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt theInnoDB?database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix commandhdparm?to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.

總結

以上是生活随笔為你收集整理的innodb_flush_log_at_trx_commit的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。