Discussion :

[Alexandre T]

Bonjour,

Je viens vers vous en raison d'un étonnant problème de lenteur.

J'ai une base de données composées de deux tables reliées selon les règles de l'art par une clé étrangère qui pointe sur une clé primaire (un identifiant unique)

La première table contient les caractéristiques d'une caméra.
La seconde contient des informations sur les photos prises par les caméras.
Pour les photos, il y a 21 millions de lignes, mais je ne stocke absolument pas les photos. Je ne stocke que des données du type contraste, exposition.

Pour les photos prises par 4 caméras avant une certaine date, je dois rajouter un booléen à vrai, pour les prochaines photos et pour les autres caméras ce booléen sera à faux.

J'ai donc ajouté mon booléen avec une valeur non nulle : 5 heures de traitement ! étonnant pour seulement 21 millions de lignes dans la table photo...

Maintenant je souhaite faire ma mise à jour avec cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE data.te_photo set indicateur = (camera_id in (13,14,15,16))

Après 3 jours de calcul, ça tourne toujours... Aucun verrou et seulement 21.000.000 de lignes, je suis le seul utilisateur connecté sur ce serveur, il reste 90% d'espace libre, ça ne swappe pas, je consomme au maximum 8% de processeur dont 6% par postgresql. Il n'y a pas d'index sur cet indicateur, il y a un index sur camera_id puisque c'est une clé étrangère.

Pour 500.000 lignes sur mon malheureux ordinateur portable, la création de la colonne a duré moins d'une minute, la mise à jour de la table quelques secondes (2 ou 3, je ne sais plus).

Je pense lancer un insert ... select dans une nouvelle table et la renommer après avoir droppé la base d'origine pour résoudre ce souci, mais je reste très inquiet par ces latences car je vais devoir lancer un paquet de requête de ce type.

Je pensais rajouter un index sur indicateur par la suite, car je vais devoir programmer certains scripts du type

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE data.te_photo set indicateur = true WHERE indicateur is null

et certains jours, ce sera

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE data.te_photo set indicateur = false WHERE indicateur is null

J'ai peur que cela mette des plombes du coup.

Auriez-vous une idée de ce qui pourrait provoquer de tels délais ? Une façon de les diminuer...

Merci de votre attention,

[SQLpro]

La création d'une nouvelle colonne, oblige à une restructuration complète de la table avec copie des images avant pour gérer la transaction.

Si la table est très "lourde" (beaucoup de ligne, beaucoup de colonnes, bref, beaucup de volume) alors cela peut prendre du temps. D'autant plus que PosGreSQL contrairement à oracle ou SQL Server :

• ne gère pas directement le stockage sur disque
• ne sait pas faire du DDL (CREATE, ALTER, DROP) "online" ce qui veut dire que tous les utilisateurs voulant accéder à cette table seront bloqués
• ne sait pas faire une sauvegarde s'il y a une opération de DDL

De plus votre requête met à jour toutes les lignes !

Il aurait été plus sage de donner une valeur par défaut au moment de l'ALTER TABLE, par exemple "false"

et de faire la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
UPDATE data.te_photo 
SET indicateur = true
WHERE camera_id in (13,14,15,16)

A +

[SQLpro]

Pour information, test effectués sur une instance SQL Server 2017 :

1 - LES DONNÉES DU TEST

1.1 - la table des caméras

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE TABLE T_CAMERA_CMR
(CMR_ID                  INT IDENTITY PRIMARY KEY)
GO

1.2 - insertion de 1000 caméras

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
INSERT INTO T_CAMERA_CMR DEFAULT VALUES
GO 1000

1.3 - la table des photos

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE T_PHOTO_PHT
(PHT_ID                  INT IDENTITY PRIMARY KEY,
 CAM_ID                  INT NOT NULL REFERENCES T_CAMERA_CMR (CMR_ID),
 PHT_CONTRASTE           FLOAT NOT NULL,
 PHT_EXPOSITION          FLOAT NOT NULL)
GO

1.4 - insertion de 4583 photos

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO T_PHOTO_PHT
SELECT FLOOR(1 + RAND() * 1000), RAND(), RAND()
GO 4583

1.5 - insertion de 4583² = 21 003 889 photos

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
INSERT INTO T_PHOTO_PHT
SELECT 1 + ABS(CHECKSUM(NEWID())) % 1000, T1.PHT_CONTRASTE, T2.PHT_EXPOSITION
FROM   T_PHOTO_PHT AS T1
       CROSS JOIN T_PHOTO_PHT AS T2;

À ce stade il y a 21 008 472 lignes dans la table des photos.


2 - AJOUT D'UNE COLONNE BOOLÈENNE (PHT_INDICATEUR)
non nulle par défaut à 0 (false)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
ALTER TABLE T_PHOTO_PHT 
   ADD PHT_INDICATEUR BIT NOT NULL DEFAULT 0;
GO

Temps d'analyse et de compilation de SQL Server :
Temps UC = 0 ms, temps écoulé = 14 ms.
Temps d'exécution*de SQL Server :
Temps UC = 15 ms, temps écoulé = 28 ms.
Bref 14 ms pour trouver un plan d'exécution de la requête et 28 ms pour l'exécution...

3 - REQUÊTE QUI MET A JOUR TOUTES LES LIGNES

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
UPDATE T_PHOTO_PHT 
SET    PHT_INDICATEUR = CASE 
                        WHEN CAM_ID in (13,14,15,16) THEN 1 
                        ELSE 0 
                     END;

Temps d'analyse et de compilation de SQL Server :
Temps UC = 0 ms, temps écoulé = 11 ms.
Temps d'exécution*de SQL Server :
Temps UC = 38625 ms, temps écoulé = 45192 ms.

Bref 45 secondes pour la mise à jour de la table complète.

3 - REQUÊTE QUI NE MET A JOUR UNIQUEMENT LES LIGNES DES CAMÉRAS 13 à 16

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
UPDATE T_PHOTO_PHT 
SET    PHT_INDICATEUR = 1
WHERE   (CAM_ID in (13,14,15,16))

Temps d'analyse et de compilation de SQL Server :
Temps UC = 176 ms, temps écoulé = 176 ms.
Temps d'exécution*de SQL Server :
Temps UC = 8199 ms, temps écoulé = 551 ms.

Bref une demi second pour mettre à jour toutes les lignes...

Notez le parallélisme en action : 8,2 seconde de temps CPU pour un temps effectif chrono de 0,5 secondes, ce qui veut dire que le serveur a utilisé un niveau de parallélisme de plus de 16 cœurs...

Les petites flèches dans un rond jaune indique que cette étape du plan est effectuée en parallèle. En l'occurrence SQL Server a utilisé 24 cœurs pour cette requête sur les 48 disponibles.


Problème... PostGreSQL ne sait pas faire de parallélisme pour les ordres du DDL (CREATE, ALTER, DROP...) ni pour la mise à jour (INSERT, UPDATE, DELETE...).

Version utilisée de SQL Server : 2017 Developper (gratuit pour le dev, les tests...) Build 14.0.2002.14 (X64) sur Windows 10 pro. machine avec 128 Go de RAM et 48 cœurs.

A +

[Alexandre T]

Bonjour et merci beaucoup Frédéric,

Toujours aussi précis ! J'apprécie beaucoup les tests que tu as réalisés sur SQL Server et de m'avoir parlé de ce parallélisme. Je constatai moi aussi que mon processeur était sous exploité. Je cherche actuellement à mesurer si mes disques durs saturent en lecture/écriture.

En conclusion, voici les solutions que nous allons mettre en place avec l'équipe :

• À court terme, initialiser avec une valeur par défaut les nouvelles colonnes,
• Toujours à court terme, dupliquer les tables temporairement avec du create as select
• À moyen terme, nous envisageons de retravailler cette table pour avoir des tuples à longueur fixe, les répertoires de stockage et le nom de images ont toujours le même nombre de caractères, (et selon moi, les répertoires devraient être dans une autre table avec une jointure dans les règles de l'art)
• À moyen terme, nous envisageons aussi de travailler sur les disques durs de nos serveurs. En effet, je pense qu'on pourrait réfléchir à équiper nos serveurs de plusieurs natures de disques durs, y compris le SSD. Nous créons deux espaces de données par base de données. L'un pour les index, l'autre pour les données. Je pense que pour les données, nous devrions réfléchir à un espace de données par type/vitesse de disque dur. Ensuite, en fonction de la volumétrie et des usages de la table, nous décidons sur quel espace / quel disque la placer.
• À long terme, nous allons envisager de comparer différents moteurs de base de données pour ces traitements. Cela fait plusieurs fois que SQL Server détrône largement Postgresql.

Je reviendrai donner les résultats quand nous aurons optimisé la table pour que les tuples ait une longueur fixe.

Mon serveur n'a pas le dimensionnement du tien, mais nous avons un double parfait de ce serveur. Nous allons y installer un windows 10 pro et un SQL Server 2017, y migrer la base de données et tester pour argumenter auprès des décideurs.

Encore merci Frédéric

Alexandre

[SQLpro]

[*]À court terme, initialiser avec une valeur par défaut les nouvelles colonnes,

Et oui, important !

[*]Toujours à court terme, dupliquer les tables temporairement avec du create as select

pas sur que cela ait un intérêt... En fait PostgreSQL stocke les tables temporaires dans le même espace que les tables de la base...

[*]À moyen terme, nous envisageons de retravailler cette table pour avoir des tuples à longueur fixe, les répertoires de stockage et le nom de images ont toujours le même nombre de caractères, (et selon moi, les répertoires devraient être dans une autre table avec une jointure dans les règles de l'art)

Oui... Pour info dans certaines GBDR comme SQL Server on peut dissocier le stockage des lobs des données relationnelles dans dans espaces de stockage différents... Et dans SQL Server il y a le FILESTREAM qui permet de stocker les fichiers (dans ton cas image) sous le contrôle du serveur, mais à titre de fichiers et transactionné (avec un journal dédié). Cela permet d'avoir une sauvegarde cohérente, impossible à faire sans cela dans les autres SGBDR

[*]À moyen terme, nous envisageons aussi de travailler sur les disques durs de nos serveurs. En effet, je pense qu'on pourrait réfléchir à équiper nos serveurs de plusieurs natures de disques durs, y compris le SSD. Nous créons deux espaces de données par base de données. L'un pour les index, l'autre pour les données. Je pense que pour les données, nous devrions réfléchir à un espace de données par type/vitesse de disque dur. Ensuite, en fonction de la volumétrie et des usages de la table, nous décidons sur quel espace / quel disque la placer.

On ne le dira jamais assez, le stockage est l'une des choses les plus importantes. mais la dissociation index/data, n'a aucune intérêt sur le plan des performances. Cela se faisait du temps ou les disques étaient cher et claquaient souvent. Ce qui est important, c'est que je JOURNAL DE TRANSACTIONS soit sur un disque ULTRA RAPIDE en écriture (SSD Write Intensive). C'est très cher, mais TRÈS TRÈS TRÈS rapide :
https://www.cdw.com/product/hpe-1.6t...ed-ssd/4827891
Les disques SSD ordinaire (READ INTENSIVE) ne vous ferons rien gagner !

[*]À long terme, nous allons envisager de comparer différents moteurs de base de données pour ces traitements. Cela fait plusieurs fois que SQL Server détrône largement Postgresql.

Il n'y a pas de miracle.... Lorsque c'est gratuit, c'est limité. Par exemple PostGreSQL ne permet pas des tables "In Memory" ni de l'indexation verticale (columstore) indispensable aujourdh'ui pour être rapide sur de gros volumes...

A +

[pachot]

Bonjour,

PostgreSQL gère les update comme in insert+delete: toute la ligne est copiée même si un seul booléen est changé. Et tous les index doivent être mis à jour pour pointer vers la nouvelle version. Et l'ancienne version est mise à jour pour pointer sur la nouvelle. Et plus tard, vaccum va devoir nettoyer toutes les anciennes versions.
En plus, ces modification vont copier tout le bloc dans le WAL (cf. https://blog.dbi-services.com/full-p...es-and-oracle/) donc même si un faible pourcentage est modifié, mais que les lignes sout réparties dans toutes la table, ça génère autant de logging que de modifier toute la table....
Donc ça c'est pour l'explication. Pour la suite, il est peut-être préférable de mettre ces booléens dans une autre table. Et à la lecture de faire la jointure entre une grosse table statique et cette table plus dynamique moins large.

Et pour les modifications en bloc, si elles peuvent se faire pendant l'arrêt de l'application, alors oui il vaud mieux créer une autre table re recopiant tout.
Le mieux est de créer une vue qui fait la modification de manière dynamique (comme l'indicateur pour cerntains camera id). Ca c'est rapide. Puis lors d'une plage de maintenance, créer une autre table à partir de cette vue, puis pointer dessus.

Cordialement,
Franck.

[Sve@r]

Bonjour

J'ai fait des tests analogues à ceux de SQLPro sous Postgres (9.6.8) et j'ai eu des temps de réponse tout à fait corrects.

Tout d'abord, je crée et remplis la table. Je suis passé par un code Python pour la remplir et ça m'a pris 4h mais au final, la table contient 21 008 472 lignes


Ensuite, je rajoute une colonne booléenne. Moins de 34 secondes...


Et enfin ta commande update. Moins de 4 minutes


Et tout ça sur une machine virtuelle (linux debian). Donc une machine qui n'utilise pas directement le processeur physique mais qui passe par un intermédiaire (le logiciel de virtualisation) pour travailler.

Donc ton souci de 3 jours de délai ne vient pas de la table. D'ailleurs 3 jours ça me paraissait franchement bien excessif. Tu aurais dit "ça me prend 15mn" j'aurais pensé "ok c'est relativement cohérent" mais 3 jours pour 21 millions de lignes ça veut dire que le bouzin ne traite que 81 lignes par seconde. C'est impensable pour un ordi.
Peut-être le fichier "postgresql.conf" à vérifier surtout dans tout ce qui concerne les check points. Je me souviens une fois (sous pg9.4) j'avais un dump que je chargeais en 40mn. Puis j'ai bidouillé des paramètres dans ce fichier (pas beaucoup, juste deux ou trois mais justement au niveau de ces check points) et le chargement suivant a pris près de 8h...