Que dois-je faire (et certainement pas) si vous avez besoin de
mettre à
jour un grand nombre d'enregistrements dans la table PostgreSQL activement utilisée "plusieurs millions" - initialiser la valeur du nouveau champ ou corriger les erreurs dans les enregistrements existants? Et en même temps, économisez votre temps et ne perdez pas d'argent en raison des temps d'arrêt.
Préparez les données de test:
CREATE TABLE tbl(k text, v integer); INSERT INTO tbl SELECT chr(ascii('a'::text) + (random() * 26)::integer) k , (random() * 100)::integer v FROM generate_series(1, 1000000) i;
Supposons que nous voulions simplement augmenter la valeur de v de 1 pour tous les enregistrements avec k dans la plage «q» .. «z».
Mais, avant de commencer les expériences, nous allons enregistrer le jeu de données d'origine afin d'avoir des résultats «propres» à chaque fois:
CREATE TABLE _tbl AS TABLE tbl;
MISE À JOUR: un pour tous et tous pour un
L'option la plus simple qui vient immédiatement à l'esprit est de tout faire «en une seule MISE À JOUR»:
UPDATE tbl SET v = v + 1 WHERE k BETWEEN 'q' AND 'z';
[regardez expliquez.tensor.ru]Une opération assez simple, semble-t-il, sur des lignes complètement «courtes» a pris plus de 2,5 secondes. Et si votre expression est plus compliquée, la ligne est plus authentique, il y a plus d'enregistrements et même certains déclencheurs interviennent - le temps peut même augmenter jusqu'à quelques minutes, mais jusqu'à plusieurs heures. Supposons que vous soyez prêt à attendre, et le reste de votre système, lié à cette base, s'il a une charge OLTP active?
Le problème est que dès que UPDATE atteint un enregistrement particulier, il le
bloque jusqu'à la fin de l'exécution . Si simultanément avec le même enregistrement, il veut travailler sur une MISE À JOUR «spot» lancée parallèlement, il
«accrochera» toujours à attendre un blocage pour la demande de mise à jour, et s'affaissera jusqu'à la fin de son travail.
©
wumo.com/wumoLe pire des cas est celui des systèmes Web, où les connexions à la base de données sont créées selon les besoins - après tout, ces connexions "pendantes" s'accumulent et consommeront les ressources de la base de données et du client, si vous ne créez pas un mécanisme de défense distinct à partir de cela.
Transactions fractionnées
En général, tout n'est pas très bon si tout se fait en une seule demande. Oui, et même si nous divisons une grande MISE À JOUR en plusieurs petites, mais laissons tout cela fonctionner
dans une seule transaction , le problème de verrouillage restera le même, car les enregistrements mutables sont verrouillés jusqu'à la fin de la transaction entière.
Nous devons donc diviser une grande transaction en plusieurs. Pour ce faire, nous pouvons soit utiliser des moyens externes, et écrire une sorte de script qui génère des transactions distinctes, soit utiliser les opportunités que la base de données elle-même peut nous fournir.
APPEL et gestion des transactions
À partir de PostgreSQL 11, il est
possible de gérer les transactions directement dans le code de procédure:
Dans les procédures appelées par la commande CALL, ainsi que dans les blocs de code anonymes (dans la commande DO), vous pouvez effectuer des transactions en exécutant COMMIT et ROLLBACK. Une fois la transaction terminée par ces commandes, une nouvelle sera automatiquement lancée.
Mais cette version est loin de tout le monde, et travailler avec CALL a ses limites. Par conséquent, nous essaierons de résoudre notre problème sans moyens externes, et pour qu'il fonctionne sur toutes les versions actuelles, et même avec des changements minimes sur le serveur lui-même - afin qu'il ne soit pas nécessaire de compiler et de redémarrer quoi que ce soit.
Pour la même raison, nous ne considérerons pas l'option d'organiser
des transactions autonomes via pg_background .
Gérer les connexions «à l'intérieur» de la base
PostgreSQL a toujours utilisé différentes méthodes pour
émuler des transactions autonomes , générant des connexions supplémentaires distinctes - via des langages procéduraux supplémentaires ou le
module dblink standard. L'avantage de ce dernier est qu'il est inclus par défaut dans la plupart des distributions, et qu'une seule commande est requise pour l'activer dans la base de données:
CREATE EXTENSION dblink;
"... et beaucoup, beaucoup d'enfants dégoûtants amenés"
Mais avant de créer une liaison dblink, voyons d'abord comment un «développeur ordinaire» décompose un grand ensemble de données, qu'il doit mettre à jour, en petits.
Naive LIMIT ... OFFSET
La première idée est de faire une recherche de «pagination»:
«Sélectionnons les mille enregistrements suivants à chaque fois» en augmentant OFFSET à chaque nouvelle demande:
UPDATE tbl T SET v = Tv + 1 FROM ( SELECT k , v FROM tbl WHERE k BETWEEN 'q' AND 'z' ORDER BY
Avant de tester les performances de cette solution, nous allons restaurer l'ensemble de données:
TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tbl;
Comme nous l'avons vu dans le plan ci-dessus, nous devrons mettre à jour environ 384 000 enregistrements. Par conséquent, voyons immédiatement comment les mises à jour seront effectuées plus près de la fin -
dans la région de la 300e itération de 1000 entrées :
[regardez expliquez.tensor.ru]Oh ... La mise à jour de l'échantillon à la fin de tous les
enregistrements 1K nous coûtera presque autant de temps que la
version originale entière !
Ce n'est pas notre choix. Il peut toujours être utilisé d'une manière ou d'une autre si vous obtenez peu d'itérations et de petites valeurs OFFSET. Parce que le
LIMIT X OFFSET Y pour la base de données équivaut à "
soustraire / sélectionner / former les premiers enregistrements X + Y, puis jeter le premier Y dans la corbeille ", ce qui pour les grandes valeurs de Y semble tragique.
En fait, cette méthode
ne peut pas être appliquée du tout ! Non seulement nous nous appuyons sur des valeurs mises à jour pour la sélection, mais nous risquons également de sauter une partie des enregistrements et de mettre à jour l'autre partie deux fois si des blocs avec les mêmes clés atteignent la bordure de la page:
Dans cet exemple, nous avons mis à jour l'enregistrement vert deux fois et l'enregistrement rouge jamais. Tout simplement parce qu'avec les mêmes valeurs des clés de tri, l'ordre des enregistrements eux-mêmes à l'intérieur d'un tel bloc n'est pas fixe.
TRÈS ORDRE PAR ... LIMITE
Modifions un peu la tâche - ajoutons un nouveau champ dans lequel nous écrirons notre valeur v + 1:
ALTER TABLE tbl ADD COLUMN x integer;
Veuillez noter que cette conception fonctionne presque instantanément, sans réécrire la table entière. Mais si vous ajoutez une valeur DEFAULT, alors - seulement à
partir de la 11ème version .
Déjà enseigné par une expérience amère, créons immédiatement un index dans lequel seules les entrées non initialisées resteront:
CREATE INDEX CONCURRENTLY ON tbl(k, v) WHERE x IS NULL;
L'index CONCURRENTLY ne bloque pas le travail de lecture-écriture avec la table, alors qu'il roule lentement même sur un énorme ensemble de données.
Maintenant, l'idée est
"Sélectionnons à partir de cet index à chaque fois seulement les mille premiers enregistrements " :
UPDATE tbl T SET x = Tv + 1 FROM ( SELECT k, v FROM tbl WHERE k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1000
[regardez expliquez.tensor.ru]Déjà beaucoup mieux - la durée de chaque transaction individuelle est désormais plus courte d'environ 6 fois.
Mais voyons à nouveau en quoi le plan de la 200e itération va se transformer:
Update on tbl t (actual time=530.591..530.591 rows=0 loops=1) Buffers: shared hit=789337 read=1 dirtied=1
Le temps a encore empiré (seulement 25%, cependant) et les tampons ont augmenté - mais pourquoi?
Le fait est que
MVCC dans PostgreSQL laisse des «âmes mortes» dans l'index - des versions d'enregistrements déjà mis à jour, qui ne conviennent plus à l'index. Autrement dit, en ne prenant que les 1000 premiers enregistrements à la 200e itération, nous analysons
toujours , bien que plus tard, nous supprimions, les versions 199K précédentes des tuples ont déjà changé.
Si des itérations chez nous sont nécessaires non pas plusieurs centaines, mais plusieurs centaines de milliers, la dégradation sera plus visible à chaque exécution de requête suivante.
MISE À JOUR par segment
En fait, pourquoi sommes-nous si attachés à cette valeur de «1000 enregistrements»? Après tout, nous
n'avons aucune raison de choisir exactement 1000 ou un autre numéro spécifique. Nous voulions simplement «couper» l'ensemble complet de données en certains
segments disjoints , pas nécessairement égaux - utilisons donc notre index existant pour l'usage auquel il est destiné.
Une
paire indexée (k, v) est excellente pour notre tâche. Construisons une requête afin qu'elle puisse s'appuyer sur la dernière paire traitée:
WITH kv AS ( SELECT k, v FROM tbl WHERE (k, v) > ($1, $2) AND k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1 ) , upd AS ( UPDATE tbl T SET x = Tv + 1 WHERE (Tk, Tv) = (TABLE kv) AND Tx IS NULL RETURNING k, v ) TABLE upd LIMIT 1;
À la première itération, il nous suffit de définir les paramètres de la requête à la
valeur «zéro» ('', 0) , et pour chaque itération suivante, nous prenons le
résultat de la requête précédente .
[regardez expliquez.tensor.ru]Le temps de transaction / verrouillage est inférieur à une milliseconde, il n'y a pas de dégradation par le nombre d'itérations, une analyse préliminaire complète de toutes les données du tableau n'est pas requise. Super!
Mettre la version finale avec dblink DO $$ DECLARE k text = ''; v integer = 0; BEGIN PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port'));
Un avantage supplémentaire de cette méthode est la possibilité d'interrompre l'exécution de ce script à tout moment, puis de reprendre à partir du point souhaité.
Calculs complexes dans UPDATE
Je mentionnerai séparément la situation avec le calcul difficile de la valeur affectée - lorsque vous devez calculer quelque chose à partir des tables liées.
Le temps consacré à l'informatique augmente également la durée de la transaction. Par conséquent, la meilleure option serait
de prendre le processus de calcul de ces valeurs au-delà de UPDATE.
Par exemple, nous voulons remplir notre nouveau champ x avec le nombre d'enregistrements qui ont la même valeur (k, v). Créons une table «temporaire», dont la génération n'impose pas de verrous supplémentaires:
CREATE TABLE tmp AS SELECT k, v, count(*) x FROM tbl GROUP BY 1, 2; CREATE INDEX ON tmp(k, v);
Maintenant, nous pouvons itérer selon le modèle décrit ci-dessus selon ce tableau, en mettant à jour la cible:
UPDATE tbl T SET x = Sx FROM tmp S WHERE (Tk, Tv) = (Sk, Sv) AND (Sk, Sv) = ($1, $2);
Comme vous pouvez le voir, aucun calcul compliqué n'est requis.
N'oubliez pas de supprimer la table auxiliaire plus tard.