Partage

'Icône Linkedin 'Icône Twitter 'Icône Facebook

Arrivés au max des id INT 2147483647...


Empilement de valises grises

Arrivés au max des id INT 2147483647...

Arrivés au max des id INT 2147483647...

En tant que développeur ou Devops, si cela ne vous est jamais arrivé, vous ne perdez rien pour attendre et vous retrouver dans une panique de l’espace, le fameux 2 147 483 647, le maximum de la valeur INT en SQL !!

Vous allez dire que ce nombre est inatteignable pour le commun des mortels, et des projets, et c’est vrai. Mais cela arrive dans pas mal de projets, à partir du moment où votre projet commence à être bien visible et que vous produisez beaucoup de données.

Par exemple, une table de logs avec les modifications de bdd, les parcours users, les emails etc… peut facilement être dans ce cas.

2 147 483 647 c’est le nombre possible de ligne avec un ID positif, et le double si vous aviez placé votre auto_increment à - 2 147 483 647 dès le début, ce que personne ne fait.

Conseils aprés s’être brulé:

  1. Tuer un SELECT / INSERT / ALTER ne libère pas immédiatement les ressources. Aprés avoir essayé de faire de grandes copies et des modifications de table sur place, les performances en ont souffert. Et même en tuant manuellement les threads, na performance ne s'est pas complètement rétablie. Car lorsque vous abandonnez une requête comme celle-ci, MySQL effectue une restauration, et il prendra des ressources précieuses. Dans un exemple de confrère, cela a pris plus de 48 heures pour une restauration completes, même avec des ressources MySQL monstrueuses à portée de main.

  2. Un grand SELECT / INSERT bloque les insertions. En fait MySQL verrouille par défaut les lignes en cours de lecture en fonction du "niveau d'isolement". Voir cet excellent article pour une bien meilleure description. Il y a plus de facteurs, donc même lorsque vous utilisez l'option la moins contraignante (SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;), les performances souffriront toujours des grandes tables ou tables utilisées pendant la copie.

Comment migrer ce INT vers un BIGINT ?

Solution 1, déplacer les ID, pour les petites tables (< 1Go)

  • Régler le niveau d’isolation des transactions de lecture à “Sans Engagement”
  • Ignorer les vérifications de clés étrangere SET FOREIGN_KEY_CHECKS = 0;
  • Copiez les données de la table afin que nous impactions le moins possible le fonctionnement. ALTER TABLE table_with_id_hitting_max_int CHANGE id`` id INT (11) UNSIGNED NOT NULL AUTO_INCREMENT, ALGORITHME = COPIE, VERROUILLAGE = PARTAGE;
  • L'ID minimum est maintenant 0 et le maximum est 4 294 967 294
  • Prend généralement 1 à 5 minutes.

Solutions 2, Passer en BIGINT, pour les petites tables (< 1Go)

  • Régler le niveau d’isolation des transactions de lecture à “Sans Engagement”
  • Ignorer les vérifications de clés étrangere SET FOREIGN_KEY_CHECKS = 0;
  • Copiez les données de la table afin que nous impactions le moins possible le fonctionnement ALTER TABLE table_with_id_hitting_max_int CHANGE id`` id BIGINT (12) UNSIGNED NOT NULL AUTO_INCREMENT, ALGORITHME = COPIE, VERROUILLAGE = PARTAGE;
  • Avec ~ 1 To, cela peut prendre environ 48 heures, selon la charge.
  • L'ID minimum est maintenant 0 et le maximum est 4 294 967 294

Solution 3, Échanger les tables, (Table 10 Go).

  • Régler le niveau d’isolation des transactions de lecture à “Sans Engagement”
  • Ignorer les vérifications de clés étrangere SET FOREIGN_KEY_CHECKS = 0;
  • Créer une nouvelle_table comme l’ancienne_table CREATE nom_table_v2 LIKE nom_table;
  • Modifier la “nouvelle table” ALTER TABLE nouvelle_table CHANGE id`` id BIGINT (12) UNSIGNED NOT NULL AUTO_INCREMENT;
  • Modifier l’auto_increment ALTER TABLE nouvelle_table AUTO_INCREMENT = 2147483648;
  • RENAME TABLE ancienne_tableTO ancienne_table_v1, nouvelle_table TO table;
  • Vous devriez être de nouveau opérationnel, mais bien sûr, vos anciennes données sont manquantes…
  • Copier les données de l'ancienne vers la nouvelle table, avec un script en python et en faisant des boucles de 10 000 lignes INSERT INTO table SELECT * FROM ancienne_table limit 10 000;

Solution 4, Echanger les tables via une procédure stockée (Table > 100Go)

Le principe est le même qu’en solution 2, sauf que l’on passe par une procédure stockée plutot que par un script python, mais le principe est le même, il est sans doute plus puissant qu’avec une surcouche de python.

CREATE TABLE `migrate_tmp` (
`id` int (11) unsigned NOT NULL AUTO_INCREMENT,
`key` varchar (255) DEFAULT NULL,
`val` varchar (255) DEFAULT NULL,
CLÉ PRIMAIRE (`id`),
CLÉ UNIQUE `clé` (` clé`)
) MOTEUR = InnoDB AUTO_INCREMENT = 0 DEFAULT CHARSET = latin1;
  • Supprimez cette procédure stockée au cas où nous la mettions à jour. Puis recréez.
  DROP PROCEDURE IF EXISTS migrate_v1_to_v2;
  DELIMITER //
  CRÉER UNE PROCÉDURE migrate_v1_to_v2 (row_count INT (11), sleep_seconds INT (11))
  COMMENCER
  DECLARE start_id BIGINT;
  DECLARE end_id BIGINT;
  DECLARE max_id BIGINT;
  DECLARE heure_début VARCHAR;
  SET start_id = (SELECT val FROM migrate_tmp WHERE `key` = 'next_id');
  SET max_id = (SELECT id FROM nom_table_v1 ORDER BY id DESC LIMIT 1);
  SET FOREIGN_KEY_CHECKS = 0;
  WHILE start_id <= max_id DO
  SET heure_début = MAINTENANT ();
  SET end_id = start_id + row_count;
    - migrate_v1_to_v2
      INSERT IGNORE INTO nom_table_v2
      CHOISIR *
      FROM nomtable_v1 INDEX DE FORCE (PRIMAIRE)
      OÙ
      id> = id_démarrage
      AND id <end_id
      LIMIT row_count;

  SET id_début = id_fin;

  INSERT INTO migrate_tmp (`clé`,` val`)
  VALEURS
  ('last_row_count', ROW_COUNT ()),
  ('next_id', start_id),
  ('last_insert_finished', MAINTENANT ()),
  ('last_insert_started', heure_début);
  SELECT 'migrate_v1_to_v2', SLEEP (sleep_seconds);
  END WHILE;
  SET FOREIGN_KEY_CHECKS = 1;
  FINIR//
  DELIMITER;
  • Ensuite, exécutez quelque chose comme ça pour copier des lots de 10k lignes
  • Et faites une pause d'une demi-seconde après chaque jusqu'à ce que vous ayez terminé. APPEL migrate_v1_to_v2 (10000, .5);

Solution 2147483648, Anticiper les problèmes à venir

SÉLECTIONNER
TABLE_SCHEMA AS 'DB',
TABLE_NAME AS "table",
COLUMN_NAME AS 'colonne',
DATA_TYPE AS 'data_type',
COLUMN_TYPE AS 'type_colonne',
SI(
LOCATE ('non signé', COLUMN_TYPE)> 0,
1,
0
) COMME 'est_unsigné',
(
CASE DATA_TYPE
QUAND 'tinyint' ALORS 255
QUAND 'smallint' PUIS 65535
QUAND 'mediumint' PUIS 16777215
QUAND 'int' PUIS 4294967295
QUAND 'bigint' PUIS 18446744073709551615
END >> IF (LOCATE ('unsigned', COLUMN_TYPE)> 0, 0, 1)
) COMME 'valeur_max',
AUTO_INCREMENT AS 'auto_increment',
INCRÉMENTATION AUTOMATIQUE / (
CASE DATA_TYPE
QUAND 'tinyint' ALORS 255
QUAND 'smallint' PUIS 65535
QUAND 'mediumint' PUIS 16777215
QUAND 'int' PUIS 4294967295
QUAND 'bigint' PUIS 18446744073709551615
END >> IF (LOCATE ('unsigned', COLUMN_TYPE)> 0, 0, 1)
) AS 'ratio'
DE
INFORMATION_SCHEMA.COLUMNS
INNER JOIN INFORMATION_SCHEMA.TABLES UTILISANT (TABLE_SCHEMA, TABLE_NAME)
OÙ
TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema')
AND EXTRA = 'auto_increment'
COMMANDER PAR 9 DESC
;

Cela peut être exécuté quotidiennement par un OPS pour voir à quel point les tables sont proches de leurs valeurs maximales. Considérez cela comme l'équivalent DB de la vérification de l'espace disque disponible.

Bon courage à vous

Poursuivez votre lecture sur ce(s) sujet(s) :