- ~2 Million rows, 37G table
- inserts only table
- inserts 500~1000 rows per min
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
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 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
_the_table_new trx id 18459639428 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)
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?
It seems there is no need to hold auto-inc lock for
_the_table_newbecause the rows copying from
_the_table_oldwill always has the value for auto-increment column, right? any ways to prevent using auto-inc lock for the
any suggestions for solving the issue?