Le Contexte du besoin

Il arrive tôt ou tard dans une vie de développeur ou de Devops, d'arriver au fameux maximum des INT de votre base d'index pour une table particulièrement bien utilisée. Et la, c'est la panique. Comment s'y prend-on ?

Vous allez dire que ce nombre reste inatteignable pour le commun des projets, et c’est vrai. Mais cela arrive régulièrement, il faut savoir quoi faire. Car cela peut aller très vite, dés lors que votre projet commence à être bien visible et que vous produisez beaucoup de données.

Par exemple, si vous loggez des modifications de BDD, si vous envoyez beaucoup de mails, cela peut vite arriver.

NB: 2 147 483 647 ou 2^32 - 1, 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.

Précautions à prendre après s'être brulé

Kill un process Mysql SELECT, INSERT ou 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 killant manuellement les threads, la performance ne sera 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 complète, même avec des ressources MySQL monstrueuses à portée de main.

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". 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 INT vers un BIGINT ?

C'est le vrai sujet de fond, comment passer de 2^32 -1 à 2^64-1. Nous avons 3 solutions à vous proposer suivant le temps que vous pouvez y consacrer et la taille de votre table en base de donnée. Dans les 3 cas, nous vous invitons à bien effectuer un script sur des bases de tests, afin de bien vérifier son fonctionnement et effectuer les tests avec une machine de puissance égale à votre production. Vous en serez d'autant averti si la requete est un peu longue.

Pour ceux qui ne connaissaient pas les niveaux d'isolations de transaction chez mysql, nous vous invitons à bien lire la documentation en amont.

Dans chacune des propositions, vous devrez effectuer ces 2 manipulations.

Ne verrouille pas les lignes pendant que je les copies

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Réduit la charge, en ignorant la vérification de clés externes

SET FOREIGN_KEY_CHECKS = 0;

Solution 1:, déplacer les ID (Table < 1Go)

Une première solution consiste à déplacer l'auto incrément de la base de donnée. C'est à dire qu'on va configurer le int pour qu'il ne commence pas à -2^32-1 et termine à 2^32-1 mais à commence à 0 et termine autour de 2^33. On va donc modifier la table qui déborde.

ALTER TABLE table CHANGE `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,ALGORITHM=COPY, LOCK=SHARED;

Avec ça, nous avons un peu de répit. Rien de foufou, mais simple et ça décale le problème pour quelques temps. On arrive en effet à un maximum de 4 294 967 294 lignes.

Il faudra compter entre 1 et 5 minutes selon votre table

Solutions 2, Passer en BIGINT (Tables < 1Go)

Une autre solution, consiste à modifier la colonne en elle meme, et la passer directement en BIGINT avec la commande:

ALTER TABLE table `id` `id` BIGINT(12) UNSIGNED NOT NULL AUTO_INCREMENT, ALGORITHM=COPY, LOCK=SHARED;

Le concept est simple aussi, et pour des petites tailles de base de donnée, c'est peut etre une solution. Mais ce n'est pas trés efficace, car cela prend trop de temps. Pour des grosses tables, on peut meme aller jusqu'à quelques heures. Et c'est pour ça que vous devez tester sur une base de donnée de recette. Si le temps de chargement est acceptable pour votre cas, ne vous en faites pas, sinon il vous faudra une autre méthode.

Solution 3, Échanger les colonnes (Table qqls Go)

Il existe une méthode, dans laquelle on va créer une nouvelle colonne new_id BIGINT, dans laquelle on va copier toutes les valeurs de la colonne id, puis simplement on déplace l'index et les clés externes vers la nouvelle colonne avant de supprimer id et de renommer new_id en id.

C'est parti, on créé la colonne

ALTER TABLE table ADD `new_id` BIGINT(12) UNSIGNED NOT NULL ;

Puis on copie les données par paquet, pour ne pas surcharger le serveur, vous pouvez faire un script pour en lancer un nouveau quand le premier est terminé:

UPDATE TABLE table set new_id = id where 1 LIMIT 10 000;

Apres avoir mis l'auto incrément sur new_id, et initialiser sa valeur, on peut renommer id en old_id et new_id en id, puis on peut lui ajouter la clés primaire qu'on a préalablement retiré de la colonne old_id, et mettre à jour les clés secondaires des autres tables et enfin.

Et enfin on poura supprimer la colonne old_id. Notre table sera toute neuve avec un bigint à disposition.

Solution 4, Échanger les tables, (Table 10 Go et +).

Pour des cas de table, un peu volumineuse, nous pouvons utiliser une autre technique. Nous allons créer une nouvelle table avec la même structure que celle que vous voulez migrer, sauf votre index qui aura été changer. Puis nous allons insérer les données de l'ancienne table dans la nouvelle. Bien entendu, il faudra régler l'auto_increment pour ne pas qu'il y ait de doublon, mais le principe est la.

CREATE table_v2 LIKE table; ALTER TABLE table_v2 CHANGE id`` id BIGINT (12) UNSIGNED NOT NULL AUTO_INCREMENT;

Si vous n’êtes pas familier avec la sélection du dernier incrément, voici un exemple.

select auto_increment from information_schema.TABLES where TABLE_NAME =’table’ and TABLE_SCHEMA=’database’;

Puis nous l'insérons dans la table_v2: ALTER TABLE nouvelle_table AUTO_INCREMENT = 2147483648; Avec cette valeur, vous etes certain de ne pas avoir de doublon et c'est plus court. Mais ça vous retire potentiellement quelques valeurs.

A ce stade, il ne nous reste plus qu'à renommer table en table_old et table_v2 en table, et votre table est de nouveau opérationnelle pour de l'insertion. Il ne reste plus qu'à insérer l'ancienne donnée dans la nouvelle table.

Je vous suggère d'y aller par paquet dans votre insertion afin de ne pas encombrer trop votre production. Effectuez un script qui va faire un boucle pour insérer la donnée par groupe de ligne.

INSERT INTO table SELECT * FROM table_v2 limit 10 000;

Contactez Efficience IT pour plus d'informations.