Mysql – Predicting the primary keys exhaustion in MySQL based on rate of insertion

information-schemalimitsMySQLprimary-key

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.

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.

Best Answer

  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.

Related Question