Not the answer you need?
Register and ask your own question!

pt-online-schema-change causing transactions wait for AUTO-INC lock and crash server

cool_coderscool_coders EntrantCurrent User Role Beginner
Fact:
1. ~2 Million rows, 37G table
2. inserts only table
3. inserts 500~1000 rows per min

Problem:
try to use pt-online-schema-change tool to add a column to an existing index but it is causing lots of transactions waiting for auto-inc lock and evantually causing server overloaded and shuts down.

we tried make chunk-time smaller but not help. the parameters we use are: --no-check-replication-filters --chunk-time 0.05 --sleep 1


Processlist:
LATEST DETECTED DEADLOCK
2016-07-28 21:00:16 2b06d3430700TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION


*** TRANSACTION:
TRANSACTION 18459639428, ACTIVE 19 sec setting auto-inc lock
mysql tables in use 2, locked 2
3 lock struct(s), heap size 360, 0 row lock(s), undo log entries 2


REPLACE INTO xxxx

*** WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `my_db`.`_the_table_new` trx id 18459639428 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)


Question:
1. From processlist, we found there's a bulk insert ("INSERT LOW_PRIORITY IGNORE INTO ") that inserting about 800 rows but takes 173 secs. not sure why it take that long. any explanations?

2. It seems there is no need to hold auto-inc lock for `_the_table_new` because the rows copying from `_the_table_old` will always has the value for auto-increment column, right? any ways to prevent using auto-inc lock for the `_the_table_new`?

3. any suggestions for solving the issue?

Thanks!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.