為什麼增加 innodb_buffer_pool_size 會減慢 MySQL 的速度？
5.1.68-cll - CentOS 上的 MySQL 社區伺服器
該系統具有 32GB 的 RAM。
我將 innodb_buffer_pool_size 從 10240M 增加到 15360M（10GB -> 15GB）。
一系列相同操作所需的時間從 720 秒增加到 822 秒（增加了 14%）。
這是每個設置僅進行一次測試的結果。但是幾個月前進行的 4 次測試導致時間在 726 到 740 秒之間。
我剛剛嘗試用8GB再次執行它，所用時間為719s。
為什麼更多的記憶體會導致更慢的程序？
編輯：有關流程的更多詳細資訊
我正在測試的過程涉及清空一些表並從現有表中的數據重建它們。我不確定它是在使用
SELECT INSERT還是在SELECT使用數據，然後使用 PHP 創建長INSERT語句。如果這很重要，那麼我可以找出答案。沒有進行架構定義更改。
這是
numactl --hardware伺服器相對空閒時的輸出：root@server [~]# numactl --hardware available: 1 nodes (0) node 0 cpus: 0 1 2 3 4 5 6 7 node 0 size: 32740 MB node 0 free: 6216 MB node distances: node 0 0: 10和
free -mroot@server [~]# free -m total used free shared buffers cached Mem: 32081 25864 6216 0 2591 12791 -/+ buffers/cache: 10482 21599 Swap: 15994 16 15977由 RolandoMySQLDBA 編輯
請執行此查詢
SELECT InnoDBSpace / POWER(1024,1) InnoDB_KB, InnoDBSpace / POWER(1024,2) InnoDB_MB, InnoDBSpace / POWER(1024,3) InnoDB_GB FROM ( SELECT SUM(data_length+index_length) InnoDBSpace FROM information_schema.tables WHERE ENGINE='InnoDB' ) A;結果：
InnoDB_KB InnoDB_MB InnoDB_GB 8413536 8216.34375 8.02377319335938和這個
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages%';結果：
Innodb_buffer_pool_pages_data 410035 Innodb_buffer_pool_pages_dirty 204 Innodb_buffer_pool_pages_flushed 826954 Innodb_buffer_pool_pages_free 99231 Innodb_buffer_pool_pages_misc 15022 Innodb_buffer_pool_pages_total 524288在程序執行期間：
root@server [~]# numactl --hardware available: 1 nodes (0) node 0 cpus: 0 1 2 3 4 5 6 7 node 0 size: 32740 MB node 0 free: 5461 MB node distances: node 0 0: 10和：
total used free shared buffers cached Mem: 32081 26658 5423 0 2603 12948 -/+ buffers/cache: 11106 20975 Swap: 15994 16 15977
從你迄今為止給我的
CPU 具有訪問所有 32GB RAM 的傑出任務。
InnoDB 的 Buffer Pool 為 8GB (524288 * 16384)
- 524288 ( Innodb_buffer_pool_pages_total )
- 16384 ( Innodb_page_size ))
InnoDB 架構
猜想#1
由於 InnoDB 連續分配其緩衝池，因此只能想像緩衝池中是否存在任何形式的數據和索引頁碎片。還有什麼其他的東西？在合併到系統表空間文件（更廣為人知的名稱）之前，在緩衝池中首先發布的二級非唯一索引也有更新
ibdata1。請注意**InnoDB Architecture**.猜想#2
雖然較大的緩衝池可以減少磁碟 I/O，但太大的緩衝池可能會出現問題，例如我剛才提到的碎片問題。
也許稀疏的記憶體雖然是連續的，但也不是一件好事。我討論了一些類似的事情
Oct 22, 2012：mysql innodb_buffer_pool_size 應該有多大？我注意到緩衝池中的雜項頁面（ Innodb_buffer_pool_pages_misc ）。這佔用 235 MB (16384 * 15022)。只是索引和行鎖的一些成本。儘管如此，這可能會受到具有大量空白間隙的大型緩衝池的影響。
猜想#3
數據和索引頁並不是唯一佔用緩衝池的東西。他們必須與之共享空間的一個惱人的鄰居是插入緩衝區。緩衝池的那部分包含所有非唯一索引的更改。根據每個 InnoDB 表的索引數量，最多可以使用一半的緩衝池。這應該是一個很好的提醒，要查看所有 InnoDB 表並刪除冗餘索引
以下是描述如何尋找冗餘索引以及為什麼要擺脫它們的其他文章：
Jan 26, 2012: MySql - 查找冗餘索引Dec 10, 2011:我有重複的鍵索引嗎？Sep 07, 2011: MySQL CPU 使用率May 17, 2011：如果該欄位上的多列索引已經存在，我是否需要向表中添加新的單列索引？想一想：如果表有冗餘索引，對錶的 INSERT 和 UPDATE 將導致45% 的時間重新平衡樹頁面（在最壞的情況下）。對於具有大量索引的表，這種樹重新平衡會發生多次。刪除冗餘索引可以減少將這些更改寫入插入緩衝區以及每個 InnoDB 表的索引頁面所花費的時間。
如果 InnoDB 表的索引過多，尤其是冗餘索引，則增加緩衝池大小會為插入緩衝區留出執行的空間。您需要查找此類表的另一個跡像是執行此查詢：
SELECT table_schema,table_name,data_length,index_length FROM information_schema.tables WHERE engine='InnoDB' AND data_length/(data_length+index_length) < 0.8;這將使您大致了解哪些表需要查看索引以查找前導列的冗餘。
結語
請記住，緩衝池持有
- 數據頁
- 索引頁
- 為索引更改插入緩衝區
- 自適應雜湊索引
- 開桌資訊
- 雜項成本
- 請參閱**
InnoDB Architecture**緩衝池中浪費的空間可能需要更多時間來篩選，尤其是對於一個單獨負責所有 RAM 的核心。
我的建議是將innodb_buffer_pool_size保持在 10G。
我們曾經說過 innodb_buffer_pool_size 應該在總記憶體的 55% 到 65% 之間。也許您的系統沒有足夠的記憶體來正常工作（因為 MySQL 偷走了他所有的記憶體）並且您的系統始終具有優先權。
如果你的系統有問題，你的 MySQL 伺服器也會有問題 :)