We have a big issue in production environment, many tables were created with Int Auto-Inc Primary Key.

Adding to the fact that our insert mechanizes includes using Insert Ignore/Insert.. On Duplicate Update we have reached the Int.MaxValue In a few tables.

I am considering my options and would like to consult:

  1. Switching to innodb_autoinc_lock_mode = 0
  2. Alerting to BigInt in all tables auto-inc column
  3. Switching all our application inserts to Select + Insert/Update

I am considering option 1 as this is an On-Premise DB and the migration work here is very costly.

As far as I understand from my researching of using innodb_autoinc_lock_mode=0 is that it as somewhat of a performance downgrade when handling concurrent inserts. I am planning a benchmark to understand the performance difference, what do you guys think about this option?


I call this "burning ids". I think it happens with all flavors of INSERT, including INSERT IGNORE, IODKU, REPLACE, etc.

Here is a workaround: http://mysql.rjweb.org/doc.php/staging_table#normalization

That describes how to use 2 SQLs to "normalize" a batch of names and get their ids in an efficient way, and without burning ids. If you have only a single name to insert/lookup, it is still 2 sqls.

INSERT allocates as many ids as it might need, then burns the ones it did not need.

  1. I think the autoinc_mode is global, and hurts performance.
  2. Sure, BIGINT will work, but I hate to use 8 bytes.
  3. And, yeah, my recommendation is similar to your #3. But look closely; there may be some useful differences, especially if you can batch things.
  • Isn't using option 3 bad for performance? Isn't it better to just use the extra 4 bytes and work with the IODKU? – omri Nov 12 at 8:07
  • The perennial tradeoff in computers: speed versus space. For batching, my code is probably faster. For a single row, you are probably correct -- slower. I offer a workaround; you can choose to use it or not. – Rick James Nov 12 at 18:47

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.