MySQL 5.1 InnoDB 配置/24GB RAM - 雙至強高負載

我正在執行一個 Facebook 應用程序，該應用程序目前有 300 - 600 個並髮使用者（並且還在增長）。為了讓硬體為增長做好準備，我將 i7 / 12gb ram / 2x 80gb intel x25 ssd (debian 5.0 / mysql 5.0 / 64bit) 更改為 bi-xeon / 24gb ram / 2x 120gb intel 320 ssd (ubuntu 10.10 / mysql 5.1 / 64 位）。

現在我面臨的問題是性能比“小盒子”更差。在兩台伺服器上，我一直在使用 nginx / php fcgi 來提供內容。

我只使用 innodb，讀取/寫入大約 65% / 35%。大約 800 - 1000 qps，但所有查詢都很簡單，並且永遠不會加入超過 1 個額外的表。設置了所有索引，並且慢日誌（> 2s）中沒有記錄單個查詢。目前我有大約 400mb 的數據（大約 1gb 的索引）預計它每個月都會翻一番。

我很喜歡所有能給我提示的人，他們應該改變什麼以使其執行更順暢。

i7 機器上的舊配置是這樣的（混合 myisam / innodb），在多達 800 多個使用者時表現相當不錯。

老我的.cnf

  key_buffer              = 3000M
  max_allowed_packet      = 128M
  thread_stack            = 192K
  thread_cache_size       = 8
  max_connections        = 400
  table_cache            = 8000
  thread_concurrency     = 16
  query_cache_limit       = 8M
  query_cache_size        = 128M
  wait_timeout            = 10
  interactive_timeout     = 10
  connect_timeout         = 600
  low_priority_updates    = 1
  join_buffer_size        = 8M
  read_buffer_size        = 2M
  sort_buffer_size        = 3M
  myisam_sort_buffer_size = 32M
  read_rnd_buffer_size    = 4M
  innodb_buffer_pool_size = 3G
  innodb_log_buffer_size  = 8M

bi-xeon盒子上的新配置是這樣的（純innodb），導致300+使用者的高負載。大約 30 個 mysql 程序位於程序列表的頂部。

磁碟 I/O：

   avg-cpu:  %user   %nice %system %iowait  %steal   %idle
             36.28    0.00    1.60    0.17    0.00   61.95

我的.cnf

   key_buffer              = 64M
   max_allowed_packet      = 1M
   thread_stack            = 192K
   thread_cache_size       = 128
   max_connections        = 500
   table_cache            = 512
   #thread_concurrency     = 10
   sort_buffer_size        = 256K
   read_buffer_size        = 256K
   read_rnd_buffer_size    = 256K
   tmp_table_size          = 32M
   max_heap_table_size     = 32M
   query_cache_limit       = 1M
   query_cache_size        = 128M
   query_cache_type        = 1

   innodb_file_per_table = 1
   innodb_data_file_path = ibdata1:1000M:autoextend
   innodb_buffer_pool_size = 16384M
   innodb_additional_mem_pool_size = 8M
   innodb_flush_log_at_trx_commit = 1
   innodb_support_xa = 0
   innodb_lock_wait_timeout = 50
   innodb_flush_method=O_DIRECT
   innodb_log_files_in_group = 2
   innodb_log_file_size = 128M
   innodb_log_buffer_size = 8M
   innodb_thread_concurrency = 12

我在 StackExchnage 上寫了一些文章

1. 為 InnoDB 和 MyISAM 調整 MySQL
2. 如何控制 InnoDB Diskspace
3. MySQL磁碟空間管理的另一個觀點
4. InnoDB 優化的觀點
5. InnoDB 微調

請閱讀這些以獲得您需要的指導。

現在，對於更緊迫的問題：您提到您有 400MB 的數據，1GB 的索引。你的索引比數據大 50%，這讓我感到害怕。但是，由於您的所有數據都是 InnoDB 並且您對目前的查詢性能感到滿意，因此您的設置綽綽有餘，尤其是 16384MB 的 innodb_buffer_pool_size。那是16GB。你們都準備好了。可是等等 ！！！你的 innodb_log_file_size 是 128M 嗎？考慮到 16GB 緩衝池，太小了。您應該調整 ib_logfile 文件的大小（將 innodb_log_file_size 設置為 2047M）。

您可能會遇到基於每個執行緒的負載。嘗試設置連接緩衝區（join_buffer_size、sort_buffer_size、read_buffer_size、read_rnd_buffer_size）

來自我：為什麼 MySQL 說我記憶體不足？

來自@DTest：你如何計算 mysql max_connections 變數？

試一試 ！！！

• 您是否將某些表從 MyISAM 轉換為 InnoDB？

如果是這樣，請檢查 http://mysql.rjweb.org/doc.php/myisam2innodb中的細微性能改進/降級

• innodb_flush_log_at_trx_commit = 1
  – 在每次事務 後寫入日誌。考慮使用= 2.
• max_connections– SHOW GLOBAL STATUS LIKE 'max_used_connections'

– 這將告訴你自啟動以來你需要多少。

• 查詢記憶體：

query_cache_size        = 128M
query_cache_type        = 1

這些可能會造成傷害。上面說，50MQC 在維護上花費了太多時間。擁有它ON也可能是浪費。做SHOW GLOBAL STATUS LIKE 'Qc%'檢查有效性。

引用自：https://dba.stackexchange.com/questions/3163