基于插入率的MySQL主键耗尽预测

Question

我们在prod中的表的主键快用完了。我们不希望这种情况再次发生。因此，我们需要一种机制，当我们耗尽主键时，基于插入速率来提醒我们，以便我们可以缓解这种情况。

我已经编写了一个Jenkins作业，该作业在Information schema上运行和执行查询，并从MySQL数据库中提取索引长度、表行和auto_increment。但我们需要跟踪之前的读数，因为我们想要与当前的读数不同，以便预测密钥何时将耗尽。

        SELECT 
        INDEX_LENGTH, TABLE_NAME, AUTO_INCREMENT, TABLE_ROWS 
        FROM 
        information_schema.TABLES 
        ORDER BY 
        index_length DESC;

我在Mysql表中找不到任何与更改速率相关的东西，可以预测主键被使用的速率。我也愿意使用任何好的替代方法，如果有人想到一种方法，但请记住，我必须得到一个由cron作业或Jenkins作业运行的查询，它应该告诉您哪些表将在今年年底耗尽。

Answer 1

找到相关查询，获取自动增量用完的百分比。请参阅this链接和注释部分。

SELECT table_schema, table_name, column_name, column_type, auto_increment,max_int,ROUND(auto_increment/max_int*100,2) AS “USED %” FROM
    (SELECT table_schema, table_name, column_name, column_type, auto_increment,
    pow(2, case data_type
    when ‘tinyint’ then 7
    when ‘smallint’ then 15
    when ‘mediumint’ then 23
    when ‘int’ then 31
    when ‘bigint’ then 63
    end+(column_type like ‘% unsigned’))-1 as max_int
    FROM information_schema.tables t
    JOIN information_schema.columns c USING (table_schema,table_name)
    WHERE t.table_schema not in (‘mysql’,’information_schema’,’performance_schema’) and t.table_type = ‘base table’
    and c.extra = ‘auto_increment’ AND t.auto_increment IS NOT NULL order by auto_increment desc limit 10) TMP;

Answer 2

您可以简单地创建一个表来存储指标、当前正在检索的值以及时间戳

    INSERT INTO table_metrics(index_length, table_name, auto_increment, row_count, when)
    SELECT INDEX_LENGTH, TABLE_NAME, AUTO_INCREMENT, TABLE_ROWS, now()
    FROM information_schema.TABLES 
    ;

然后对该数据运行查询

SELECT table_name
    , il_change / daysPassed AS ilGrowthRate
    , ([literal ceiling value] - il_current) / (il_change / daysPassed) AS daysRemaining
    , ...
FROM (
SELECT table_name
    , MAX(index_length) AS il_current
    , MAX(index_length) - MIN(index_length) AS il_change
    , ....
    , DATEDIFF(MAX(when), MIN(when)) AS daysPassed
GROUP BY table_name
) AS subQ

这假设您在截断某个表时删除了该表的数据，以便在该表截断时指标重新开始；当然，您可以添加条件以仅查看前一天、前一周、前一个月等的指标……(在这种情况下，清除后修剪度量表就变得不那么重要了)。