What are the causes of InnoDB Index Corruption?

I'm running into issues where many of my database servers are experiencing repeat index corruption. The index corruption across several physical hosts, and many different tables. Whenever I restore a new server, it goes corrupt several days latter.

Normally I see index corruption on my read only slave, followed by the master less than a day later.

I am running Percona 5.5.51-38.1 on CentOS 6, on a VMWare host.

My errors mostly look like this (although I've also seen other errors):

```
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 1399

When you said InnoDB Index Corruption, I immediately thought of the InnoDB Buffer Pool

Let's start with what InnoDB Buffer Pool actually holds. Please take a look at the upper left hand corner of this Pictorial Representation of InnoDB (courtesy of Percona TCO Vadim Tkachenko)

The InnoDB Buffer Pool has a section called the Change Buffer (a.k.a. Insert Buffer, which is dedicated to updating changes to non-unique indexes. Note how those changes are moved from the Buffer Pool into the System Tablespace (ibdata1). A lot of work adjusting non-unique indexes is involved. Note the MySQL Documentation Clustered and Secondary Indexes under the subheading How Secondary Indexes Relate to the Clustered Index :

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

CONJECTURE #1

If you have large PRIMARY KEYs, I suspect the Change Buffer becoming a bit of a hog within the Buffer Pool. Changes can reach up to 50% of the Buffer Pool. You can tune that down with innodb_ibuf_max_size. The default is half the buffer pool. In your case, that would be 4096M (4G). Perhaps lowering it could throttle the amount of index maintenance needed.
CONJECTURE #2

I don't see innodb_buffer_pool_instances configured. For MySQL 5.5, the default is 1. You have innodb_buffer_pool_size set at 8192M (8G). If the Buffer Pool is more that half the installed RAM, YIKES !!! You will experience lots of swap. I recommend setting it to 2 or 4 or the number of cores assigned to the VM. I mentioned this back on Feb 12, 2011 (How do you tune MySQL for a heavy InnoDB workload?)
SUGGESTIONS

Please do one or more of the following

• Give each VM more RAM

• Raise innodb_buffer_pool_instances

• Reduce the Change Buffer size (innodb_ibuf_max_size) as a last resort

• Use smaller PRIMARY KEYs

• Check the Hypervisor's Physical RAM

StackExchange Database Administrators Q#151781, answer score: 2