Hello everybody.
I have a very weird behavior of my database which I tried to move to RAID-0 array consisting of two SSD drives.
So I have two “Team Group L3 EVO 120Gb” drives in my PowerEdge R710 server which is using PERC H700 controller.
I’ve installed drives and created RAID-0 (all defaults) array for larger space and faster RW and because of RAID-1 has no redundancy win with SSD.
I’ve created only one partition for the whole space and formatted it to ext4.
Then I’ve stopped Percona Server, moved mysql folder to SSD array, started server and got a lot of errors in the mysql error log looking like:
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 47123.
InnoDB: You may have to recover from a backup.
2017-03-16 03:38:04 7f5db15ae700 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex 994f02880000b8130000000000000000000000231813383a00 0a0000000000000000000000cf00000f68ffffffffd29bd18b d0bbd0b0d0b9d0b4d18b2c20d0b0d188d0b0d0b4d18b2c20d1 96d0b7d0b4d0b5d0bdd0b5d0b4d1962e$
InnoDB: End of page dump
2017-03-16 03:38:04 7f5db15ae700 InnoDB: uncompressed page, stored checksum in field1 2572092040, calculated checksums for field1: crc32 2618809217, innodb 3732643505, none 3735928559, stored checks$
InnoDB: Page may be a BLOB page
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 47123.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.6/…-recovery.html
InnoDB: about forcing recovery.
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 47123.
InnoDB: You may have to recover from a backup.
…
2017-03-16 03:38:04 7f5db15ae700 InnoDB: Assertion failure in thread 140040384210688 in file buf0buf.cc line 4480
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.6/…-recovery.html
InnoDB: about forcing recovery.
21:38:04 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/
key_buffer_size=8388608
read_buffer_size=262144
max_used_connections=39
max_threads=602
thread_count=32
connection_count=32
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 19896992 K bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f5db0100000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7f5db15add00 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x8d7fdc]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x65a441]
/lib64/libpthread.so.0[0x3c1720f7e0]
/lib64/libc.so.6(gsignal+0x35)[0x3c16e325e5]
/lib64/libc.so.6(abort+0x175)[0x3c16e33dc5]
/usr/sbin/mysqld[0xab3899]
/usr/sbin/mysqld[0xaca661]
/usr/sbin/mysqld[0xaad279]
/usr/sbin/mysqld[0xa8eecc]
/usr/sbin/mysqld[0xa9cae3]
/usr/sbin/mysqld[0x568b4e]
/usr/sbin/mysqld[0x568d53]
/usr/sbin/mysqld[0xa3775e]
/usr/sbin/mysqld[0x9871d9]
/usr/sbin/mysqld(_ZN7handler17ha_index_read_mapEPhPKhm16ha_r key_function+0xb6)[0x5a3486]
/usr/sbin/mysqld[0x6bd36c]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0xdd)[0x6ba82d]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x3e8)[0x6b9bb8]
/usr/sbin/mysqld(Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4 ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select resultP18st_select_lex_unitP13st_select_lex+0x275)[0x703ec5]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x1 95)[0x704755]
/usr/sbin/mysqld[0x55d138]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1626)[0x6dcd36]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x5b 8)[0x6e2a88]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3 THDPcj+0xff0)[0x6e4210]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1a2)[0x6b0932]
/usr/sbin/mysqld(handle_one_connection+0x40)[0x6b09d0]
/usr/sbin/mysqld(pfs_spawn_thread+0x146)[0x90f5d6]
/lib64/libpthread.so.0[0x3c17207aa1]
/lib64/libc.so.6(clone+0x6d)[0x3c16ee8aad]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f5dc141b010): is an invalid pointer
Connection ID (thread ID): 799
Status: NOT_KILLED
And something like that. After that I’ve used innodb_force_recovery = 1, dump data, drop database, import to non-SSD drive. Try other options. Repeat.
I’ve tried with LVM and without, default mount options and non-default (with discard also), tried xfs instead of ext4 and the result was always the same. All data was correct every time so I can assume the problem was with indexes. Each time I’ve tried to use SSD there were problems with random tables.
Actually my problem described here: https://bugs.mysql.com/bug.php?id=69476 but it had no answer.
Percona Server version - 5.6.35-80, Centos 6.8 x64. Database size is ~25G.
I’m looking forward for any help or advice. I’m totally stuck.
Thanks in advance.