Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre expérimenté
    Avatar de Alexandre T
    Profil pro
    Inscrit en
    mai 2002
    Messages
    1 031
    Détails du profil
    Informations personnelles :
    Âge : 42
    Localisation : France, Meurthe et Moselle (Lorraine)

    Informations forums :
    Inscription : mai 2002
    Messages : 1 031
    Points : 1 543
    Points
    1 543

    Par défaut Update d'un champ sur une table de 21 millions de lignes

    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,
    Alexandre T.

    N'oubliez pas de consulter les FAQ Php et les cours et tutoriels Php

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 797
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 797
    Points : 43 955
    Points
    43 955

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  3. #3
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 797
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 797
    Points : 43 955
    Points
    43 955

    Par défaut

    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...
    Nom : Plan Update SQL Server.jpg
Affichages : 55
Taille : 64,9 Ko
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  4. #4
    Membre expérimenté
    Avatar de Alexandre T
    Profil pro
    Inscrit en
    mai 2002
    Messages
    1 031
    Détails du profil
    Informations personnelles :
    Âge : 42
    Localisation : France, Meurthe et Moselle (Lorraine)

    Informations forums :
    Inscription : mai 2002
    Messages : 1 031
    Points : 1 543
    Points
    1 543

    Par défaut

    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
    Alexandre T.

    N'oubliez pas de consulter les FAQ Php et les cours et tutoriels Php

  5. #5
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 797
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 797
    Points : 43 955
    Points
    43 955

    Par défaut

    Citation Envoyé par Alexandre T Voir le message
    [*]À 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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  6. #6
    Expert confirmé
    Avatar de pachot
    Homme Profil pro
    DBA au CERN (Genève), Oracle ACE Director, OCM 12c, Oak Table
    Inscrit en
    novembre 2007
    Messages
    1 681
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 48
    Localisation : Suisse

    Informations professionnelles :
    Activité : DBA au CERN (Genève), Oracle ACE Director, OCM 12c, Oak Table
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : novembre 2007
    Messages : 1 681
    Points : 5 963
    Points
    5 963

    Par défaut

    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.
    Franck Pachot - DBA au CERN - Oracle ACE Director - OCM 12c - Oak Table member - twitter: @FranckPachot - blog: blog.pachot.net

  7. #7
    Expert éminent sénior
    Avatar de Sve@r
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    février 2006
    Messages
    7 091
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : Aéronautique - Marine - Espace - Armement

    Informations forums :
    Inscription : février 2006
    Messages : 7 091
    Points : 20 145
    Points
    20 145
    Billets dans le blog
    1

    Par défaut

    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
    Nom : VirtualBox_Debian8_64b_04_04_2019_18_26_10.png
Affichages : 12
Taille : 300,6 Ko

    Ensuite, je rajoute une colonne booléenne. Moins de 34 secondes...
    Nom : VirtualBox_Debian8_64b_04_04_2019_18_27_46.png
Affichages : 12
Taille : 202,8 Ko

    Et enfin ta commande update. Moins de 4 minutes
    Nom : VirtualBox_Debian8_64b_04_04_2019_18_32_48.png
Affichages : 12
Taille : 145,8 Ko

    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...
    Mon Tutoriel sur la programmation «Shell»
    Sinon il y en a pleins d'autres. N'oubliez pas non plus les différentes faq disponibles sur ce site

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [WD 11] Champs sur une table
    Par jul52 dans le forum WinDev
    Réponses: 5
    Dernier message: 04/11/2008, 14h14
  2. 38 champs sur une table [mysql]
    Par crystaldope dans le forum Langage SQL
    Réponses: 2
    Dernier message: 20/07/2008, 22h54
  3. Requète complexe lier 2 champs sur une table
    Par charpeer dans le forum Requêtes
    Réponses: 4
    Dernier message: 20/06/2008, 15h28
  4. Update d'un champs d'une table
    Par pobrouwers dans le forum Langage SQL
    Réponses: 5
    Dernier message: 30/11/2006, 10h56
  5. selection comlexe de champs sur une table
    Par allowen dans le forum Langage SQL
    Réponses: 2
    Dernier message: 14/03/2006, 15h00

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo