2 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

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)

InnoDB Plumbing

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 (http://dba.stackexchange.com/questions/194/how-do-you-tune-mysql-for-a-heavy-innodb-workload/1185#1185How do you tune MySQL for a heavy InnoDB workload?)

SUGGESTIONS

Please do one or more of the following

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)

InnoDB Plumbing

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 (http://dba.stackexchange.com/questions/194/how-do-you-tune-mysql-for-a-heavy-innodb-workload/1185#1185)

SUGGESTIONS

Please do one or more of the following

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)

InnoDB Plumbing

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

1
source | link

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)

InnoDB Plumbing

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 (http://dba.stackexchange.com/questions/194/how-do-you-tune-mysql-for-a-heavy-innodb-workload/1185#1185)

SUGGESTIONS

Please do one or more of the following