InnoDB 索引損壞的原因是什麼？
我遇到了許多數據庫伺服器都遇到重複索引損壞的問題。跨多個物理主機和許多不同表的索引損壞。每當我恢復新伺服器時，它都會在幾天后損壞。
通常我會在我的只讀從屬設備上看到索引損壞，不到一天后主設備就會出現損壞。
我在 VMWare 主機上的 CentOS 6 上執行 Percona 5.5.51-38.1。
我的錯誤大多看起來像這樣（儘管我也看到了其他錯誤）：
InnoDB: End of page dump 161008 10:25:47 InnoDB: Page checksum 371733204 (32bit_calc: 1567583928), prior-to-4.0.14-form checksum 1175312553 InnoDB: stored checksum 1215686486, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 23 2865603967, low 4 bytes of lsn at page end 0 InnoDB: Page number (if stored to page already) 176, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 3784 InnoDB: Page may be an index page where index id is 10061 InnoDB: (index "key2" of table "my_database"."my_table") InnoDB: Corruption of an index tree: table "my_database"."my_table", index "key2", InnoDB: father ptr page no 9116, child page no 9118 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex cc7dc1b0; asc } ;; 1: len 4; hex 8003f1ec; asc ;; 2: len 4; hex 80000031; asc 1;; 3: len 4; hex 80117f84; asc ;; n_owned: 0; heap_no: 2; next rec: 146 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex cc7c7030; asc |p0;; 1: len 4; hex 8001172e; asc .;; 2: len 4; hex 80000031; asc 1;; 3: len 4; hex 80117d0a; asc } ;; 4: len 4; hex 0000239c; asc # ;; n_owned: 6; heap_no: 483; next rec: 12200 InnoDB: You should dump + drop + reimport the table to fix the InnoDB: corruption. If the crash happens at the database startup, see InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html about InnoDB: forcing recovery. Then dump + drop + reimport. 161008 10:25:47 InnoDB: Assertion failure in thread 139950214960896 in file btr0btr.c line 1330 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 13:25:47 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Please help us make Percona Server better by reporting any bugs at http://bugs.percona.com/這是my.cnf：
[client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] general_log = on user = mysql read_only = 0 port = 3306 socket = /var/lib/mysql/mysql.sock datadir = /var/lib/mysql symbolic-links = 0 skip-external-locking key_buffer_size = 32M max_allowed_packet = 128M table_open_cache = 10000 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 log-bin = mysql-bin innodb_buffer_pool_size = 8192M innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql innodb_additional_mem_pool_size = 20M innodb_log_file_size = 1000M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 50 tmpdir = /var/lib/mysql pid-file = mysql.pid log-error = mysql.err max_binlog_size = 100M log_bin_trust_function_creators = 1 expire_logs_days = 3 max_connections = 2000 max_connect_errors = 10000 lower_case_table_names = 1 default-storage-engine = innodb innodb_file_format = Barracuda innodb_file_per_table = 1 innodb_status_file = 1 innodb_flush_method = O_DIRECT slow_query_log_file = slow-query.log slow_query_log = 0 long_query_time = 10 skip-networking = 0 relay_log = relay-bin server-id = 2 read-only = 1 skip-slave-start = 1我主要是想列出可能的原因，以便繼續調查。最初我們使用 Antelope 執行 5.5.31，但我們升級到最新的 5.5 並更改為 Barracuda，但這並沒有幫助。
我懷疑我們在 MySQL 中遇到了一個邊緣情況，但我們必須做一些事情來觸發它。
當你說 InnoDB Index Corruption 時，我立刻想到了 InnoDB Buffer Pool
讓我們從 InnoDB Buffer Pool 實際包含的內容開始。請查看此 InnoDB 圖示的左上角（由 Percona TCO Vadim Tkachenko 提供）
InnoDB 緩衝池有一個稱為更改緩衝區（又名插入緩衝區，專門用於更新對非唯一索引的更改。注意這些更改是如何從緩衝池移動到系統表空間（ibdata1）的部分。很多工作涉及調整非唯一索引。注意副標題下的MySQL 文件聚集索引和二級索引
How Secondary Indexes Relate to the Clustered Index：除聚集索引外的所有索引都稱為二級索引。在 InnoDB 中，二級索引中的每條記錄都包含行的主鍵列，以及為二級索引指定的列。InnoDB 使用這個主鍵值來搜尋聚集索引中的行。
如果主鍵長，二級索引佔用的空間就更多，所以主鍵短是有利的。
猜想#1
如果您有大的主鍵，我懷疑更改緩衝區在緩衝池中變得有點麻煩。更改最多可達到緩衝池的 50%。您可以使用innodb_ibuf_max_size將其調低。預設為緩衝池的一半。在您的情況下，這將是 4096M (4G)。也許降低它可以減少所需的索引維護量。
猜想#2
我沒有看到配置了 innodb_buffer_pool_instances。對於 MySQL 5.5，預設值為 1。您將innodb_buffer_pool_size設置為 8192M (8G)。如果緩衝池超過已安裝 RAM 的一半，YIKES !!! 你會經歷很多交換。我建議將其設置為 2 或 4 或分配給 VM 的核心數。我在 2011 年 2 月 12 日提到了這一點（您如何調整 MySQL 以應對繁重的 InnoDB 工作負載？）
建議
請執行以下一項或多項操作
- 為每個 VM 提供更多 RAM
- 提高innodb_buffer_pool_instances
- 減少更改緩衝區大小 ( innodb_ibuf_max_size ) 作為最後的手段
- 使用較小的主鍵
- 檢查 Hypervisor 的物理 RAM
實際上在具有 8GB 記憶體和只有 8GB 交換文件限制的物理硬體上，使用預設的 my.cnf，繁重的查詢可能會導致程序崩潰並損壞 InnoDB。我無法評估 MySQL 的 MariaDB 版本中是否存在良好的程式碼以防止 InnoDB 損壞。