0

I'm having a recurring issue with a MySQL database where one index in particular has been getting corrupted. Originally I saw a corruption event about every 2-3 weeks and this week I've seen it happen twice. The corruption also only happens on one index and the data itself seems fine.

The composite index in question spans two BigInt foreign key ID Columns on a table with 5M rows. I'm using InnoDB for all tables and running MySQL 5.6.23 on Amazon RDS. I looked to see if I could find anything in mysql-error but haven't found any entries in it.

To verify that the index corrupted, I run the following EXPLAIN:

mysql> explain SELECT * FROM  student INNER JOIN school ON school.id = student.school_id WHERE student.student_status_id IN (3, 4, 7) ORDER BY student.id desc LIMIT 0, 25;

+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
| id | select_type | table      | type | possible_keys                                                   | key                       | key_len | ref                   | rows | Extra                           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+---------------------------------+
|  1 | SIMPLE      | school     | ALL  | PRIMARY                                                         | NULL                      | NULL    | NULL                  | 3690 | Using temporary; Using filesort |
|  1 | SIMPLE      | student    | ref  | student_status_id_idx,school_id_idx,schoolStatusIndex_idx       | schoolStatusIndex_idx     | 8       | school.id             |  178 | Using index condition           |
+----+-------------+------------+------+-----------------------------------------------------------------+---------------------------+---------+-----------------------+------+————————————————+

When it’s “fixed” the rows are roughly 48/1 instead of 3690/178

To fix it, it requires some combination of these 3 steps:

  1. alter table student drop KEY schoolStatusIndex_idx; alter table student add key schoolStatusIndex_idx (school_id,student_status_id);
  2. alter table school engine=innodb; (rebuilds all indexes)
  3. alter table student engine=innodb;

Still, this has me baffled. What could be causing this kind of index corruption and how can I prevent it from happening moving forward?

Here's the trimmed output of SHOW CREATE TABLE for student, student_status, and school:

CREATE TABLE `student_status` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `school_id` bigint(20) NOT NULL,
  `phone` varchar(10) NOT NULL,
  `student_status_id` bigint(20) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `student_status_id_idx` (`student_status_id`),
  KEY `school_id_idx` (`school_id`),
  KEY `schoolStatusIndex_idx` (`school_id`,`student_status_id`),
  CONSTRAINT `student_student_status_id_student_status_id` FOREIGN KEY (`student_status_id`) REFERENCES `student_status` (`id`),
  CONSTRAINT `student_school_id_school_id` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=2582686 DEFAULT CHARSET=latin1;


CREATE TABLE `school` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=4350 DEFAULT CHARSET=latin1;

And some additional output that might be helpful:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 5753536512 |
+-------------------------+------------+
  • 2
    ANALYZE TABLE would probably also "fix" it. This doesn't look like "corruption," merely inaccurate index statistics. – Michael - sqlbot Mar 5 '16 at 2:46
  • 1
    Please provide SHOW CREATE TABLE for both tables. Does the EXPLAIN look different in other areas when it is 'corrupt'? Does the query run slower or faster when 'corrupt'? What version of MySQL are you running? – Rick James Mar 6 '16 at 23:59
  • @RickJames: I've updated the post to include the CREATE TABLE output. The EXPLAIN only seems to change here. In a normal state, the query takes around 35 ms. When it's in the 'corrupt' state, the query takes an average 3500 ms or more. I'm on MySQL 5.6.23. – Kevin London Mar 7 '16 at 17:44
  • SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; – Rick James Mar 7 '16 at 17:48
  • 1
    Please provide EXPLAIN FORMAT=JSON SELECT...;. And try to catch both variants. Something funny may be going on with the "costs". – Rick James Mar 7 '16 at 17:55
0

I suspect that the MySQL query optimizer occasionally choose a sub-optimal path. I'm not certain why it would sometimes use the index and sometimes not, nor why it seemed related to the index in question.

I think the query itself could not be fully covered by the index (it included an ORDER BY that I did not cover). I made a change to the query to ORDER BY student.created_at and added an index on (created_at, school_id, student_status_id) and everything looks good now.

  • 1
    Putting created_at (the ORDER BY column) first mean that it won't get to the other columns. So I would argue that this is not as good as putting it last. – Rick James Mar 7 '16 at 23:29
  • Interesting! I checked my dataset and it looks like it prefers to use the (created_at, school_id, student_status_id) over any other permutation of index I've tried, including (school_id, student_status_id, created_at). I also read over your post on MySQL performance (mysql.rjweb.org/doc.php/index1) so thanks for posting it! – Kevin London Mar 9 '16 at 18:08

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.