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:
- Switching to innodb_autoinc_lock_mode = 0
- Alerting to BigInt in all tables auto-inc column
- 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?