innodb的innodb_buffer_pool_size和MyISAM的key_buffer_size

一. key_buffer_size 對MyISAM表來說非常重要。

如果隻是使用MyISAM表，可以把它設定為可用記憶體的 30-40%。合理的值取決于索引大小、資料量以及負載 -- 記住，MyISAM表會使用作業系統的緩存來緩存資料，是以需要留出部分記憶體給它們，很多情況下資料比索引大多了。盡管如此，需要總是檢查是否所有的 key_buffer 都被利用了 -- .MYI 檔案隻有 1GB，而 key_buffer 卻設定為 4GB 的情況是非常少的。這麼做太浪費了。如果你很少使用MyISAM表，那麼也保留低于 16-32MB 的 key_buffer_size 以适應給予磁盤的臨時表索引所需。

key_buffer_size = 2000M

或者

key_buffer_size = 1G

二. innodb_buffer_pool_size 對Innodb表來說非常重要。

Innodb相比MyISAM表對緩沖更為敏感。MyISAM可以在預設的 key_buffer_size 設定下運作的可以，然而Innodb在預設的 innodb_buffer_pool_size 設定下卻跟蝸牛似的。由于Innodb把資料和索引都緩存起來，無需留給作業系統太多的記憶體，是以如果隻需要用Innodb的話則可以設定它高達 70-80% 的可用記憶體。一些應用于 key_buffer 的規則有 -- 如果你的資料量不大，并且不會暴增，那麼無需把 innodb_buffer_pool_size 設定的太大了。

innodb_buffer_pool_size = 8000M

或者

innodb_buffer_pool_size = 15G

其他牛人的解釋

#InnoDB存儲資料字典、内部資料結構的緩沖池，16MB 已經足夠大了。

innodb_additional_mem_pool_size = 16M

#InnoDB用于緩存資料、索引、鎖、插入緩沖、資料字典等

#如果是專用的DB伺服器，且以InnoDB引擎為主的場景，通常可設定實體記憶體的50%

#如果是非專用DB伺服器，可以先嘗試設定成記憶體的1/4，如果有問題再調整

#預設值是8M，非常坑X，這也是導緻很多人覺得InnoDB不如MyISAM好用的緣故

innodb_buffer_pool_size = 4G

#InnoDB共享表空間初始化大小，預設是 10MB，也非常坑X，改成 1GB，并且自動擴充

innodb_data_file_path = ibdata1:1G:autoextend

#如果不了解本選項，建議設定為1，能較好保護資料可靠性，對性能有一定影響，但可控

innodb_flush_log_at_trx_commit = 1

#抱 怨Innodb比MyISAM慢 100倍？那麼你大概是忘了調整這個值。預設值1的意思是每一次事務送出或事務外的指令都需要把日志寫入（flush）硬碟，這是很費時的。特别是使用電 池供電緩存（Battery backed up cache）時。設成2對于很多運用，特别是從MyISAM表轉過來的是可以的，它的意思是不寫入硬碟而是寫入系統緩存。日志仍然會每秒flush到硬 盤，是以你一般不會丢失超過1-2秒的更新。設成0會更快一點，但安全方面比較差，即使MySQL挂了也可能會丢失事務的資料。而值2隻會在整個作業系統 挂了時才可能丢資料

#InnoDB的log buffer，通常設定為 64MB 就足夠了

innodb_log_buffer_size = 64M

#InnoDB redo log大小，通常設定256MB 就足夠了

innodb_log_file_size = 256M

#InnoDB redo log檔案組，通常設定為 2 就足夠了

innodb_log_files_in_group = 2

#啟用InnoDB的獨立表空間模式，便于管理

innodb_file_per_table = 1

#啟用InnoDB的status file，便于管理者檢視以及監控等

innodb_status_file = 1

#設定事務隔離級别為 READ-COMMITED，提高事務效率，通常都滿足事務一緻性要求

transaction_isolation = READ-COMMITTED 

[mysqld]

#禁止開啟自動事務

init_connect='SET autocommit=0'

innodb_buffer_pool_size = 4G

key_buffer_size = 1G

user            = mysql

socket          = /var/run/mysqld/mysqld.sock

port            = 3306

basedir         = /usr

datadir         = /var/lib/mysql

tmpdir          = /tmp

skip-external-locking

#bind-address            = 127.0.0.1

#

# * Fine Tuning

#

key_buffer              = 16M

max_allowed_packet      = 16M

thread_stack            = 192K

thread_cache_size       = 8

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

myisam-recover         = BACKUP

#max_connections        = 100

#table_cache            = 64

#thread_concurrency     = 10

# * Query Cache Configuration

query_cache_limit       = 1M

query_cache_size        = 16M

#general_log_file        = /var/log/mysql/mysql.log

#general_log             = 1

log_error                = /var/log/mysql/error.log

# Here you can see queries with especially long duration

log_slow_queries        = /var/log/mysql/mysql-slow.log

long_query_time = 0.05

#log-queries-not-using-indexes

expire_logs_days        = 10

max_binlog_size         = 100M

# * InnoDB

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

# Read the manual for more InnoDB related options. There are many!

# chroot = /var/lib/mysql/

——————————————————————————————————————————————————

innodb_pool_buffer_size對innodb性能的影響

innodb_pool_buffer_size是影響innodb性能最重要的參數，指的是innodb緩沖的大小。一般來說，它越大Innodb的吞吐量(機關： tps)就越高。是以，在不影響伺服器上其他程式正常工作的情況下，這個值總是越大越好。

但是當innodb做crash recovery的時候，大的pool buffer會讓recovery奇慢無比。 一種折衷的解決方法就是：啟動的時候用小的pool buffer，恢複完成以後改用大的pool bufer。

為什麼小的pool buffer能加快恢複的速度？ 

用Oprofile跟蹤恢複的過程發現，Flush_List中頁的插入排序耗掉了大多的時間，大的Pool buffer會讓Flush list中的髒頁越來越多，而插入排序的時間複雜度是O(N^2)。 相反，小的pool buffer會讓flush list的髒page及時flush，這樣flush list裡面的髒頁數目總是不會很大，有新的髒資料按序插入就不會有大的開銷。