Auto-Inc gaps - Id value explodes

Question

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?

Answer 1

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.