1
mysql> desc user_donate_request;
+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| donate_id         | varchar(128) | NO   | PRI |         |       |
| uid1              | bigint(20)   | YES  |     | NULL    |       |
| uid2              | bigint(20)   | YES  |     | NULL    |       |
| type              | int(11)      | YES  |     | NULL    |       |
| call_index_string | varchar(64)  | YES  |     | NULL    |       |
| vgift_type        | int(11)      | YES  |     | NULL    |       |
| vgift_count       | int(11)      | YES  |     | NULL    |       |
| vm_typeid         | int(11)      | YES  |     | NULL    |       |
| vm_count          | int(11)      | YES  |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+

and the donate_id is

    mysql> select donate_id from user_donate_request limit 10;
+------------------------------------------------------+
| donate_id                                            |
+------------------------------------------------------+
| 0_20150531111943_3257668255_3239391718_1433042383740 |
| 0_20150531112019_3257668255_3239391718_1433042419477 |
| 0_20150531112030_3257668255_3239391718_1433042430360 |
| 0_20150531114105_3257668255_3257563917_1433043665442 |
| 0_20150531114619_3257668255_3256955987_1433043979643 |
| 0_20150531134327_3257668255_3249316396_1433051007791 |
| 0_20150531134331_3257668255_3246491322_1433051011254 |
| 0_20150531134340_3257668255_3247980835_1433051020169 |
| 0_20150531134340_3257668255_3249725755_1433051020127 |
| 0_20150531134340_3257668255_3249725755_1433051020169 |
+------------------------------------------------------+

I use this command in master

pt-table-checksum h=XXX,P=XXX,u=checksums,p=123456 --no-check-binlog-format --nocheck-replication-filters --recursion-method=dsn=h=xxx,D=test,t=dsns --databases=XXXX

pt-table-sync --print --sync-to-master h=XXX,P=XXX,u=checksums,p=123456 

and pt-checksum-table make my slave lock a whole table for 2555 secondes

 ---TRANSACTION 1F5026074, ACTIVE 2555 sec, process no 1339, OS thread id 140431652148992
mysql tables in use 1, locked 1
612227 lock struct(s), heap size 62716344, 68171907 row lock(s)
MySQL thread id 15637930, query id 1808437527 106.120.184.71 checksums Sending data
   SELECT /*rows in chunk*/ `donate_id`,
`uid1`,
`uid2`,
`type`,
`call_index_string`,
`vgift_type`,
`vgift_count`,
`vm_typeid`,
`vm_count`,
CRC32(CONCAT_WS('#', `donate_id`, `uid1`, `uid2`, `type`, `call_index_string`, `vgift_type`, `vgift_count`, `vm_typeid`, `vm_count`, CONCAT(ISNULL(`uid1`), ISNULL(`uid2`), ISNULL(`type`), ISNULL(`call_index_string`), ISNULL(`vgift_type`), ISNULL(`vgift_count`), ISNULL(`vm_typeid`), ISNULL(`vm_count`)))) AS __crc 
FROM `new_huanju`.`user_donate_request`
FORCE INDEX (`PRIMARY`) WHERE (`donate_id` < '2')
ORDER BY `donate_id`
LOCK IN SHARE MODE

And I explain this SQL I found rows are too big! 21512952 rows!

+----+-------------+---------------------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table               | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+---------------------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | user_donate_request | range | PRIMARY       | PRIMARY | 386     | NULL | 21512952 | Using where |
+----+-------------+---------------------+-------+---------------+---------+---------+------+----------+-------------+

every insert SQL I can't execute because of the locks

    Oct 26 13:18:02 warning hmhj_giftexchange_d[26003]: 26016 exec error:Lock wait timeout exceeded; try restarting transaction
Oct 26 13:18:02 warning hmhj_giftexchange_d[26003]: 26016 exec error:Lock wait timeout exceeded; try restarting transaction
  • How many rows have donate_id < '2'? – Rick James Oct 27 '16 at 1:08
  • @RickJames almost the whole table rows .......... the checksum SQL is created from the checksum tool..... – LawrenceLi Oct 27 '16 at 1:31
  • Sounds like the checksum tool is not smart enough to chunk varchars cleanly. – Rick James Oct 27 '16 at 1:51
  • @RickJames Is there a way to print out the total chunk SQL in order to judge ? yesterday DB accident occur and I know this problem... too late .. – LawrenceLi Oct 27 '16 at 2:03
  • Sorry, I barely know of the existence of the tool. Since I have done varchar and multi-column chunking for another situation, I understand the difficulties. (Hence my question about chunk size.) It's almost as if they treated donate_id as numeric. – Rick James Oct 27 '16 at 2:08

Your Answer

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

Browse other questions tagged or ask your own question.