3

My environment:

# cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 6.8 (Santiago)
# uname -a
Linux A.B.C 2.6.32-642.4.2.el6.x86_64 #1 SMP Mon Aug 15 02:06:41 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux
# rpm -q mysql-server percona-toolkit
mysql-server-5.1.73-7.el6.x86_64
percona-toolkit-2.2.19-1.noarch
# 

I'm running pt-table-checksum and I'm getting following error:

Skipping chunk X of Y.Z because MySQL chose no index instead of the word_sid_typeindex.

mysql> DESCRIBE search_index;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| word  | varchar(50)      | NO   | MUL |         |       |
| sid   | int(10) unsigned | NO   | MUL | 0       |       |
| type  | varchar(16)      | YES  |     | NULL    |       |
| score | float            | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS WHERE Name='search_index'\G;
*************************** 1. row ***************************
           Name: search_index
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3155432
 Avg_row_length: 51
    Data_length: 163250176
Max_data_length: 0
   Index_length: 415514624
      Data_free: 839909376
 Auto_increment: NULL
    Create_time: 2016-09-01 21:14:58
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.13 sec)

ERROR: 
No query specified

mysql> 

Please advice.

1

The message is both expected and disturbing.

WHY EXPECTED ?

I don't find this message disturbing at all. I would expect pt-table-checksum to bypass chunks since there is no defined uniqueness. Granted, InnoDB will create a row-based index called gen_clust_index when there is no PRIMARY KEY or UNIQUE INDEX. So, chunks read from the table have no rhyme or reason to its order other than order of insertion.

WHY DISTURBING ?

Since you have a replication tag on the question, now this becomes disturbing.

When you have many writes on a master, the slave may write rows in a different order than the master. If the search_index table had a PRIMARY KEY or a UNIQUE INDEX, that would stabilize checksum evaluation. Unfortunately, that cannot be said in the case of a table that has an internally generated index (gen_clust_index) as the only form of uniqueness with an InnoDB table ON BOTH MASTER AND SLAVE.

SOLUTION

  1. Define a PRIMARY KEY for search indexon the Master.
  2. mysqldump search_index from the Master and load it into the Slave
1

This is what I'd suggest:

  • run SHOW INDEXES FROM Y.Z\G
  • use the index with the highest Cardinality in --chunk-index option
  • use a different hash function --function=md5
  • use a smaller --chunk-size (eg. 200)

Optionally, run ANALYZE TABLE on the affected table to re-calculate its statistics. Make sure you're aware of the effects of running ANALYZE TABLE on a busy server.

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.