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 index_length, table_rows and 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.

  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.

  • Seems like index_length is far too crude to trust. table_rows is only an estimate for InnoDB. – Rick James Jun 1 '19 at 3:07
  • 1
    Why not use bigint for all primary keys and don't worry about "burning" values? – a_horse_with_no_name Jun 1 '19 at 3:43

Rather than delete records or re-assign primary keys you could use the datatype of bigint assuming it is currently int. This will stop you running out but I am led to believe this may affect performance. Do you have a test environment to test query speeds?

  • yeah, we are moving to bigint for some tables, but for the rest of the databases, we want to have alert mechanism to alert us like for example a year before we run out of the primary keys. pt online schema is used to migrate all the records to a new table with the changed datatype. Simply we just want to know beforehand we are ruuning out of primary keys based on the rate of insertion – ali May 31 '19 at 10:23
  • @ali If the tables you're inserting into have an associated date_added, date_inserted, etc. column, you could do a query with a count and group by on the date added to find the rate of insertion. If they don't have a column like that, you would likely need to create an additional table to store the date that each column is added so you will have something to query, or add a date_added column. – raphael75 Jun 7 '19 at 16:28
  1. Find tables that have an AUTO_INCREMENT column.
  2. Discover the datatype of that column, including SIGNED vs UNSIGNED.
  3. Use that to establish the drop-dead limit.
  4. SELECT MAX(id) FROM tbl; to see where you are now.

At that point, you can go one of two directions.

Plan A: Compute what percent of the limit the id already is, and sort on that.

Plan B: Check MAX(id) a week later, then compute how many weeks before the limit will be hit.

When you do find a table that is threatening to hit the limit, find out if the ids are being "burned". Do this by comparing COUNT(*) versus MAX(id). Either the table is really big (compared to the datatype used), or there are a lot of gaps.

If there are a lot of gaps, then dig into what the code does. There are many flavors of INSERT that "burn" ids. REPLACE burns a id because it is effectively DELETE + INSERT. INSERT IGNORE, if the row is present, burns the id that it otherwise would use. IODKU burns when it updates. Etc.

If you are burning ids due to Normalization, see this.

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.