2

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 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/

And here is 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        

I'm mostly trying to get a list of possible causes so I can keep investigating. Initially we were running 5.5.31 with Antelope, but we upgraded to the latest 5.5 and changed to Barracuda, but that did not help.

I suspect we're hitting an edge case in MySQL, but we must be doing something to trigger it.

  • How much RAM?... – Rick James Oct 8 '16 at 16:43
  • 1
    The troubling behavior you describe should require one of the following: defective memory/disk/hardware, improper handling of data files (such as using snapshot or tarball backups of tablespaces with mysqld still running), or actual bugs in InnoDB or the OS. It should be quite impossible to trigger these errors by normal usage or (mis)configuration. Also, there's no reasonable way for structural corruption like this to propagate across a replication connection. "Whenever I restore a new server, it goes corrupt several days latter." How are you doing your backup and restore (what tools)? – Michael - sqlbot Oct 9 '16 at 16:24
  • 1
    Another question that needs to be asked is whether these failures are preceded by any other, seemingly less severe or noteworthy, crashes or anomalies, which perhaps may have gone unnoticed but should be evident in the error logs. – Michael - sqlbot Oct 9 '16 at 16:26
  • 1
    Dump/import meaning mysqldump? If so, that is good, because all of the indexes are built fresh. If not, please elaborate. Using snapshots made without quiescing the database not a winning proposition, so there's little point in backing up a MySQL server that way... although I would hope the act of taking a snapshot couldn't cause corruption of data on disk, if that's what you're thinking... – Michael - sqlbot Oct 10 '16 at 1:17
  • 1
    @brandon Wamboldt We are now 2+ years after your initial question. Are you still plagued with corruption of indexes? If so, please post to pastebin.com A) SHOW GLOBAL STATUS; after a typical workday B) SHOW GLOBAL VARIABLES; C) RAM available at this time; D) entire report from MySQLTuner.pl E) from linux ulimit -a report, F) htop or top FIRST page, G) iostat -xm 5 3 text report, H) your complete my.cnf for analysis. – Wilson Hauck Dec 13 '18 at 21:32
1

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

  • Thank you, I really appreciate all the info, however those sound like performance issues, not something that would cause corruption. Am I misunderstanding? – Brandon Wamboldt Oct 8 '16 at 21:09
  • No, @BrandonWamboldt, you are correct. These are performance considerations. If these things could allow the actual data structures to become corrupt, that would point to serious defects in the InnoDB code itself. – Michael - sqlbot Oct 9 '16 at 16:09
0

Actually on physical HW with 8GB of memory and only 8GB swap file limitations, with default my.cnf, heavy queries can crash the process and corrupt InnoDB. I cannot assess that there exists good code in MariaDB version of MySQL then to prevent corruption of InnoDB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.