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

Polyglot Projects Percona Toolkit
1

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!