We are running out of primary keys for our table in production. We do not want that to occur again. So we need a mechanism to alert us when we are running out of primary keys based on the rate of insertion so that we can mitigate that.
I have written a Jenkins job that executes a query on
information_schema and extracts the
auto_increment from the MySQL database. But we need to keep track of the previous reading since we want to take
diff of it with the current in order to predict when the keys are going to be exhausted.
SELECT INDEX_LENGTH, TABLE_NAME, AUTO_INCREMENT, TABLE_ROWS FROM information_schema.TABLES ORDER BY index_length DESC;
I cannot find anything related to the rate of change in MySQL tables that should predict the rate at which the primary keys are being used up. I am also open to use any good alternate approach if anyone thinks of but keep in mind I have to get a query run by a cron job or Jenkins job that should tell that which tables will be exhausted at the end of this year.