PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 04/12/2017 18:20:11

Je me suis mal fait comprendre, la requête mais 21 secondes à retourner les données dans pg_admin3

#2 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 04/12/2017 15:18:10

J'ai fais désactiver kapersky et malgré cela j'ai toujours ces temps ?


explain (analyse,buffers)
SELECT c1, c2
  FROM foo;

"Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.040..66.897 rows=1000000 loops=1)"
"  Buffers: shared hit=32 read=8302"
"Total runtime: 85.730 ms"

Mais 21 secondes pour retourner toutes les lignes après reboot

#3 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 02/12/2017 10:56:16

Il y a en effet un antivirus sur mon poste; chez mes clients ce sera la même chose. Quant vous dites "il faut le désactiver pour le répertoire de données de PostgreSQL ainsi que le binaire": cela veut dire exclure le répertoire data de mon antivirus; pour la partie binaire voulez vous dire exclure le répertoire c:\programme files\postgresql\...\bin  ?
Pour migrer vers 9.6, j'ai potentiellement un nombre important de clients cela va être difficile.
Pour les autres conseils, je vois ça lundi.

Merci pour ces conseils

#4 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 01/12/2017 13:30:39

Bonjour,

Je reviens vers vous car j'essaie d'optimiser la configuration de mon instance et les temps de réponse de mes requêtes en utilisant le tuto "Comprendre EXPLAIN" de Dalibo.
J'ai crée la table foo:
CREATE TABLE foo (c1 integer, c2 text);
INSERT INTO foo
SELECT i, md5(random()::text)
FROM generate_series(1, 1000000) AS i;

explain SELECT
  foo.c1,
  foo.c2
FROM
  public.foo;

"Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37)"

Les stats sont bonnes mais il faut + de 27 secondes à postgresql pour retourner les lignes de cette requête,
ça me semble beaucoup !

J'ai utilisé pgtune pour configurer mon instance:

# DB Version: 9.2
# OS Type: windows
# DB Type: desktop
# Total Memory (RAM): 3 GB
# Number of Connections: 5

max_connections = 5
shared_buffers = 192MB
effective_cache_size = 768MB
work_mem = 32MB
maintenance_work_mem = 192MB
checkpoint_segments = 3
checkpoint_completion_target = 0.5
wal_buffers = 5898kB
default_statistics_target = 100

#5 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 27/11/2017 11:19:43

Un modèle du type :

Liste de dates:
--drop table DATES
CREATE TABLE DATES
(
    id             bigserial primary key,
    acquire      TIMESTAMP
);
CREATE UNIQUE INDEX dates_acquire_idx ON DATES (acquire);

et une table par appareil avec toutes les voies avec cle étrangère =id de la table dates:



--DROP TABLE D_11_KIMO_CP30;
CREATE TABLE D_11_KIMO_CP30
(
id             bigserial primary key,

V1 float4 default null,
V2 float4 default null,
V3 float4 default null,
V4 float4 default null,
V5 float4 default null,
V6 float4 default null,
V7 float4 default null,
V8 float4 default null,
V9 float4 default null,
V10 float4 default null,
V11 float4 default null,
V12 float4 default null,
V13 float4 default null,
V14 float4 default null,
V15 float4 default null,
V16 float4 default null,
dates_id   bigserial,
CONSTRAINT dates_id_fk FOREIGN KEY (dates_id)
      REFERENCES dates (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)     

ne serait-il pas une alternative possible sachant que le logiciel est limité à afficher 100 voies maximum et que je réalise un échantillonnage sur 500000 points  ?


Je pourrai faire une requête du type :


select A.ACQUIRE
,D11.V1,D11.V2,D11.V3,D11.V4,D11.V5,D11.V6,D11.V7,D11.V8,D11.V9,D11.V10,D11.V11,D11.V12,D11.V13,D11.V14,D11.V15,D11.V16
,D12.V1,D12.V2,D12.V3,D12.V4,D12.V5,D12.V6,D12.V7,D12.V8,D12.V9,D12.V10,D12.V11,D12.V12,D12.V13,D12.V14,D12.V15,D12.V16
,D13.V1,D13.V2,D13.V3,D13.V4,D13.V5,D13.V6,D13.V7,D13.V8,D13.V9,D13.V10,D13.V11,D13.V12,D13.V13,D13.V14,D13.V15,D13.V16
,D14.V1,D14.V2,D14.V3,D14.V4,D14.V5,D14.V6,D14.V7,D14.V8,D14.V9,D14.V10,D14.V11,D14.V12,D14.V13,D14.V14,D14.V15,D14.V16
,D15.V1,D15.V2,D15.V3,D15.V4,D15.V5,D15.V6,D15.V7,D15.V8,D15.V9,D15.V10,D15.V11,D15.V12,D15.V13,D15.V14,D15.V15,D15.V16
,D16.V1,D16.V2,D16.V3,D16.V4,D16.V5,D16.V6,D16.V7,D16.V8,D16.V9,D16.V10,D16.V11,D16.V12,D16.V13,D16.V14,D16.V15,D16.V16
from dates A
INNER JOIN D_11_KIMO_CP30 D11 on A.ID=D11.dates_id
INNER JOIN D_12_KIMO_CP30 D12 on A.ID=D12.dates_id
INNER JOIN D_13_KIMO_CP30 D13 on A.ID=D13.dates_id
INNER JOIN D_14_KIMO_CP30 D14 on A.ID=D14.dates_id
INNER JOIN D_15_KIMO_CP30 D15 on A.ID=D15.dates_id
INNER JOIN D_16_KIMO_CP30 D16 on A.ID=D16.dates_id
where A.ID % 96 = 0

#6 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 25/11/2017 19:28:27

Oui, c'est bien comme ça que le trigger est implémenté.

Pour l'insertion en une seule fois, comme il s'agit d'un insert dynamique, le texte de la requête va sans doute être trop long. (255 appareils * 16 voies comme contraintes)
Je préférerai éviter un trigger. De plus, certains appareils (enregistreurs autonomes) ne sont pas déchargés toutes les secondes et ne remontent pas leurs valeurs en temps réel.

#7 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 24/11/2017 17:01:31

J'ai analysé la taille de mes tables; il semblerait que la table "mesure_summary" grossit beaucoup plus vite que ma table mesures ...
Les nulls qui sont ajoutés pour chaque voie sans valeur sont-ils interprétés vraiment comme des nulls ?

#8 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 24/11/2017 11:09:59

Bonjour Julien, je vous donne plus d'informations

I- Ma config postgresql.conf:

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

#autovacuum = on            # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.
#log_autovacuum_min_duration = -1    # -1 disables, 0 logs all actions and
                    # their durations, > 0 logs only
                    # actions running at least this number
                    # of milliseconds.
#autovacuum_max_workers = 3        # max number of autovacuum subprocesses
                    # (change requires restart)
#autovacuum_naptime = 1min        # time between autovacuum runs
#autovacuum_vacuum_threshold = 50    # min number of row updates before
                    # vacuum
#autovacuum_analyze_threshold = 50    # min number of row updates before
                    # analyze
#autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1    # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000    # maximum XID age before forced vacuum
                    # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                    # autovacuum, in milliseconds;
                    # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for
                    # autovacuum, -1 means use
                    # vacuum_cost_limit

(c'est celle de base, il me semble)

II- Les tables:

CREATE TABLE public.mesures (
                mes_id BIGINT NOT NULL DEFAULT nextval('public.mesures_mes_id_seq'),
                mes_date TIMESTAMP,
                mes_acquire TIMESTAMP,
                mes_valeur REAL,
                mes_on_alarme BIT,
                mes_numslave INTEGER,
                mes_numchannel INTEGER,
                mes_hashcode VARCHAR(255),
                mes_type INTEGER,
                mes_unite VARCHAR(20),
                mes_ap_code VARCHAR(50),
                mes_valeurorigine REAL ,
                mes_supprime BIT NOT NULL,
                mes_refsonde INTEGER,
                mes_precision INTEGER,
                mes_unitecodes VARCHAR(255) NOT NULL,
                mes_persist BIT NOT NULL,
                mes_numfunction BIGINT,
                alarmes_al_id BIGINT,
                acquisitions_acq_id BIGINT NOT NULL,
                CONSTRAINT mesures_pk PRIMARY KEY (mes_id)
);

CREATE TABLE mesure_summary
(
  mes_acquire         timestamp NOT NULL,
  mes_persist          bit NOT NULL        ,
  mes_hashcode          varchar   
);


ALTER TABLE mesure_summary
    SET (autovacuum_vacuum_scale_factor = 0.0);
    
    ALTER TABLE mesure_summary
    SET (autovacuum_vacuum_threshold = 50);
    
    ALTER TABLE mesure_summary
    SET (autovacuum_analyze_scale_factor = 0.0);
    
    ALTER TABLE mesure_summary
    SET (autovacuum_analyze_threshold = 50); 

car je souhaiterai réaliser des mises à jour de statistiques toutes les 50 lignes ajoutées.

|||- Les ajouts de mesures :

A chaque seconde, j' ajoute les mesures de 255 appareils dans la table principale mesures via un trigger en INSERT sur la table mesures

Ce trigger appelle la fonction maint_mesure_summary():

                                           
CREATE OR REPLACE FUNCTION maint_mesure_summary()
  RETURNS trigger AS
$BODY$
/*--------------------------------------------------------------------------------------------------
            Fonction pour la maintenance de la table mesure_summary
           
            SELECT     * from public.fc_mesure_insertmulti    (array[10524,10524]::bigint[]
            ,(E'2017-08-16 14:07:47.877452')::timestamp
            ,(array[E'KIMO_CP30',E'KIMO_CP30'])::character varying[]
            ,(array[E'-200',E'0'])::real[]
            ,(array[E'-200',E'0'])::real[]
            ,(array[0,0])::bit[]
            ,(array[11,11])::integer[]
            ,(array[1,2])::integer[]
            ,(array[E'',E'']):: character varying[]
            ,500::integer
            ,(array[E'°C',E'Pa'])::character varying[]
            ,(array[E'2017-08-16 14:07:44.518185',E'2017-08-16 14:07:44.518185'])::timestamp[]
            ,(B'0')::bit
            ,(array[0,0])::integer[]
            ,(array[-1,-1])::bigint[]
            ,(array[1,0])::integer[]
            ,(array[E'U7;°C',E'U11;PA']):: character varying[]
            ,(B'1')::bit
            ,(array[0,0])::bigint[]);

           
----------------------------------------------------------------------------------------------------*/

DECLARE
    schemaname  varchar ='';    -- Nom schema
    tablename    varchar ='mesure_summary';    -- Nom de la table resume mesure
    colname     varchar ='';            -- Nom de la colonne
    isfxchannel    bit=B'0';                -- Indicateur voie calculee
    schemacolname     varchar ='';            -- Nom de la colonne lu dans la bdd
    insert_sql    varchar ='';            -- Texte querie d'insertion
    delete_sql    varchar ='';            -- Texte querie d'insertion
    update_sql    varchar ='';            -- Texte querie de mise à jour de la table
    alter_sql    varchar ='';            -- Texte querie ajout colonne de la table
    coltest_sql    varchar ='';            -- Texte querie test presence colonne
    rowcount    int=0;                -- Nombre de ligne modifie
   
   
BEGIN

    --raise notice 'Start Trigger maint_mesure_summary';
   
   
    --  Ajout d'1 mesure
    IF (TG_OP = 'INSERT') THEN
   
       
        schemaname = TG_TABLE_SCHEMA;   
       
    --    raise notice 'Schema:%',schemaname;
        --raise notice 'TG_OP:%',TG_OP;

        -- Test valeur et date non null
        IF NEW.mes_valeur IS NULL OR  NEW.mes_acquire IS NULL THEN
            RETURN NEW;
        END IF;
       
        -- Determine le nom de la colonne
        IF NEW.mes_numfunction=0 THEN
            -- Format Voie : N°esclave
            colname := CONCAT ('D',NEW.mes_numslave)  ;
            isfxchannel :=B'0';
        ELSE
            -- Format Voie calculee : N°fonction
            isfxchannel :=B'1';
            colname := CONCAT('F',NEW.mes_numfunction) ;
        END IF;
       
        --raise notice 'INSERTION esclave:%,%',NEW.mes_numslave,NEW.mes_numchannel;
        -- CONSTITUTION QUERY INSERT
        insert_sql:='INSERT INTO ' || schemaname || '.'|| tablename || ' (mes_acquire,mes_persist,mes_hashcode, "' || colname
                             || '") SELECT ' || quote_literal(NEW.mes_acquire)
                             || ',' || quote_literal(NEW.mes_persist)
                             || ',' || quote_literal(NEW.mes_hashcode)        
                             || ',' || CONCAT('ROW(', NEW.mes_numslave,',',quote_literal(NEW.mes_ap_code),
                            -- GESTION VOIE CLASSIQUE/VOIE CALCULEE SUR LA PREMIERE VOIE (POUR LES VOIES CALCULEE ON A 1 SEULE VOIE ET LA VOIE FOURNIE PAR LE SERVICE EST A ZERO)
                            CASE WHEN isfxchannel=B'0' THEN
                                ',row(1,'|| COALESCE(CASE WHEN NEW.mes_numchannel=1 THEN quote_literal(NEW.mes_valeur) END,'NULL')||')'
                            ELSE
                                ',row(1,'|| COALESCE(CASE WHEN NEW.mes_numchannel=0 THEN quote_literal(NEW.mes_valeur) END,'NULL')||')'
                            END     ,',',
                                        'row(2,'|| COALESCE(CASE WHEN NEW.mes_numchannel=2 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(3,'|| COALESCE(CASE WHEN NEW.mes_numchannel=3 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(4,'|| COALESCE(CASE WHEN NEW.mes_numchannel=4 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(5,'|| COALESCE(CASE WHEN NEW.mes_numchannel=5 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(6,'|| COALESCE(CASE WHEN NEW.mes_numchannel=6 THEN quote_literal(NEW.mes_valeur) END,'NULL')||')',',',
                                        'row(7,'|| COALESCE(CASE WHEN NEW.mes_numchannel=7 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(8,'|| COALESCE(CASE WHEN NEW.mes_numchannel=8 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(9,'|| COALESCE(CASE WHEN NEW.mes_numchannel=9 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(10,'|| COALESCE(CASE WHEN NEW.mes_numchannel=10 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(11,'|| COALESCE(CASE WHEN NEW.mes_numchannel=11 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(12,'|| COALESCE(CASE WHEN NEW.mes_numchannel=12 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(13,'|| COALESCE(CASE WHEN NEW.mes_numchannel=13 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(14,'|| COALESCE(CASE WHEN NEW.mes_numchannel=14 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(15,'|| COALESCE(CASE WHEN NEW.mes_numchannel=15 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(16,'|| COALESCE(CASE WHEN NEW.mes_numchannel=16 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(17,'|| COALESCE(CASE WHEN NEW.mes_numchannel=17 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(18,'|| COALESCE(CASE WHEN NEW.mes_numchannel=18 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(19,'|| COALESCE(CASE WHEN NEW.mes_numchannel=19 THEN quote_literal(NEW.mes_valeur) END,'NULL') ||')',',',
                                        'row(20,'|| COALESCE(CASE WHEN NEW.mes_numchannel=20 THEN quote_literal(NEW.mes_valeur) END,'NULL'),
                                        '))::public.device_Propertie;');
                       
        --raise notice 'insert_sql:%',insert_sql;
       
        update_sql:= 'UPDATE '|| schemaname  || '.' || tablename
                        ||  ' SET ' || CONCAT('"',colname,'".V',
                                                    CASE WHEN isfxchannel=B'0' THEN
                                                        NEW.mes_numchannel
                                                    ELSE
                                                        '1'
                                                    END
                                                ,'.mes_valeur' ) || ' =  ' || NEW.mes_valeur
                        ||  ' WHERE MES_ACQUIRE=''' || NEW.mes_acquire  || '''';--|| ' AND  "' || colname || '" IS NULL;';

        --raise notice 'update_sql:%',update_sql;

        EXECUTE update_sql;
       
        GET DIAGNOSTICS rowcount = ROW_COUNT;
       
        raise notice 'update rowcount:%',rowcount;
       
        IF rowcount = 0 THEN
            raise notice 'insert_sql:%',insert_sql;
            EXECUTE insert_sql;
        END IF;
    
  END IF;

 
  RETURN NEW;

EXCEPTION
    WHEN SQLSTATE '42703'  THEN
    BEGIN
        RAISE NOTICE 'ERROR';

         -- TEST EXISTENCE COLONNE
         coltest_sql = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS '
                  ||' WHERE TABLE_SCHEMA = ' || quote_literal(TG_TABLE_SCHEMA)
                  ||' AND TABLE_NAME=' || quote_literal(tablename)
                  ||' AND COLUMN_NAME='|| quote_literal(colname)   ;
                  
        --raise NOTICE 'coltest_sql:%' ,coltest_sql;

        EXECUTE  coltest_sql INTO schemacolname;

        --RAISE NOTICE 'AVANT AJOUT COLONNE';
       
        IF(schemacolname IS NULL ) THEN
       
            -- AJOUT COLONNE SI NON EXISTENCE
            --RAISE NOTICE 'AJOUT COLONNE';
           
            alter_sql = 'ALTER TABLE ' ||schemaname|| '.'|| tablename || ' ADD COLUMN "'|| colname || '"  public.device_Propertie ' ;

            --RAISE NOTICE 'alter_sql:%',alter_sql;
           
            EXECUTE alter_sql ;

            --raise notice 'insert_sql:%',coalesce (insert_sql,'<NULL>');
            --raise notice 'insert_sql error:%',coalesce (insert_sql,'<NULL>');
           
            EXECUTE insert_sql;

            RETURN NEW;
           
        END IF;

        RETURN NEW;
       
    END;
   
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

  Explication de la fonction :
 
Quant j'ajoute dans la table mesures un enregistrement, j' ajoute (ou modifie) un enregistrement dans la table mesure_summary.

Si la colonne d'un appareil n'existe pas dans la table mesure_summary, je crée la colonne pour cet appareil.

Une fois la colonne existante,
    SI la date n'existe pas dans la table mesure_summary
        j' ajoute un enregistrement pour l' appareil (ajouté dans la table mesures)
    SINON
        je mets à jour l'enregistrement pour un autre appareil (ajouté dans la table mesures)

IV- Nouveau SELECT avec son plan d'execution:

explain analyse SELECT mes_id
  FROM w201747.mesure_summary;

"Bitmap Heap Scan on mesure_summary  (cost=20435.72..25845.45 rows=1384 width=8) (actual time=1150.544..39824.118 rows=2071 loops=1)"
"  ->  Bitmap Index Scan on mesure_summary_pk  (cost=0.00..20435.37 rows=1384 width=0) (actual time=1147.198..1147.198 rows=948368 loops=1)"
"Total runtime: 39824.949 ms"


La table de resume : mesure_summary comporte 443 colonnes pour 2082 lignes.
La table principal mesures comporte 3653952 lignes.

Merci pour votre aide

#9 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 23/11/2017 18:51:41

explain analyse SELECT mes_id
--,ROW_NUMBER ()  OVER ( ) ROWID
FROM w201747.mesure_summary

"Bitmap Heap Scan on mesure_summary  (cost=4926.88..6849.14 rows=491 width=8) (actual time=234.197..7196.321 rows=571 loops=1)"
"  ->  Bitmap Index Scan on mesure_summary_pk  (cost=0.00..4926.75 rows=491 width=0) (actual time=231.270..231.270 rows=231212 loops=1)"
"Total runtime: 7196.577 ms"

Faut -il configurer l'autovacuum d'1 certaine façon sachant que je simule une insertion toutes les secondes pour tous les appareils ?

Merci

#10 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 23/11/2017 18:50:00

il s'agissait d'une simple requête :
SELECT mes_id
--,ROW_NUMBER ()  OVER ( ) ROWID
FROM w201747.mesure_summary
--ORDER BY MES_id desc
limit 3000

#11 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 23/11/2017 15:39:38

Merci pour ta réponse rjuju,

J'ai essayé de tester ta solution mais je suis limité par le nombre de colonnes autorisées par postgresql.
J'ai 255 appareils possibles * 16 = 4080 colonnes
En plus, j'ai un ensemble de logiciel déja en prod.

Aussi j'ai essayé de trouver une solution intermédiaire:
J'ai conservé la table mesures et j'ai crée une table mesure_summary :


CREATE SEQUENCE mesure_summary_id_seq;

CREATE TABLE mesure_summary
(
  mes_id BIGINT NOT NULL DEFAULT nextval('mesure_summary2_id_seq'),     
  mes_acquire         TIMESTAMP NOT NULL,
  mes_persist          BIT NOT NULL        ,
  mes_hashcode          VARCHAR   
);

CREATE INDEX IX_FK_MES_ID ON MESURE_SUMMARY(MES_ID);
CREATE INDEX IX_FK_MES_ACQUIRE ON MESURE_SUMMARY(MES_ACQUIRE);


    ALTER TABLE mesure_summary
    SET (autovacuum_vacuum_scale_factor = 0.0);
    
    ALTER TABLE mesure_summary
    SET (autovacuum_vacuum_threshold = 1000);
    
    ALTER TABLE mesure_summary
    SET (autovacuum_analyze_scale_factor = 0.0);
    
    ALTER TABLE mesure_summary
    SET (autovacuum_analyze_threshold = 1000); 
   

qui stocke dans chaque colonne un appareil et permet de stocker toutes les mesures des appareils à une date donnée.

Les types :

CREATE TYPE public.device_Propertie AS (mes_numslave integer,mes_ap_code character varying
                                    ,V1 public.channel_Propertie
                                    ,V2 public.channel_Propertie
                                    ,V3 public.channel_Propertie
                                    ,V4 public.channel_Propertie
                                    ,V5 public.channel_Propertie
                                    ,V6 public.channel_Propertie
                                    ,V7 public.channel_Propertie
                                    ,V8 public.channel_Propertie
                                    ,V9 public.channel_Propertie
                                    ,V10 public.channel_Propertie
                                    ,V11 public.channel_Propertie
                                    ,V12 public.channel_Propertie
                                    ,V13 public.channel_Propertie
                                    ,V14 public.channel_Propertie
                                    ,V15 public.channel_Propertie
                                    ,V16 public.channel_Propertie
                                    ,V17 public.channel_Propertie
                                    ,V18 public.channel_Propertie
                                    ,V19 public.channel_Propertie
                                    ,V20 public.channel_Propertie
                                    );
CREATE TYPE public.channel_Propertie AS (    mes_numchannel     smallint ,
                                            mes_valeur         FLOAT4);

J'alimente la table mesure_summary via un trigger qui ajoute  au fur et à mesure de l'insertion des données dans ma table mesures 'réelle' , les appareils manquants dans la table mesure_summary .
L'insertion se passe bien, mais ce type  de requête :

"Limit  (cost=31090.01..63842.51 rows=10000 width=8) (actual time=1793.662..63078.897 rows=1726 loops=1)"
"  ->  Bitmap Heap Scan on mesure_summary  (cost=31090.01..236618.51 rows=62752 width=8) (actual time=1793.660..63078.003 rows=1726 loops=1)"
"        ->  Bitmap Index Scan on ix_fk_mes_id  (cost=0.00..31074.32 rows=62752 width=0) (actual time=1767.902..1767.902 rows=1727171 loops=1)"
"Total runtime: 63079.697 ms"

et super long  !

#12 Re : Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 11/11/2017 00:20:18

Non, je n'ai pas essayer de faire comme ça mais je vais y réfléchir.
Merci pour la réponse

#13 Optimisation » Postgresql 9.3 embarqué dans un logiciel de supervision » 09/11/2017 23:04:52

Jfmm
Réponses : 21

Bonjour,

J'embarque Postresql 9.3 dans un logiciel de supervision qui est installé sur différents sites.

Je viens lire des mesures d'1 à 255 appareils de mesures de températures. Chaque appareil peut avoir de 1 à 8 voies de mesures
La base est constituée de schemas qui constitue les semaines.

Pour chaque semaine, il y a une table de MESURE qui est constituée des colonnes:

mes_id bigint, mes_acquire timestamp, mes_numslave int,mes_numchannel int,mes_valeur float.

Les mesures sont ajoutées toutes les secondes dans cette table.

Exemple: mes_id , mes_acquire         , mes_numapp       ,     mes_numchannel  ,mes_valeur .

               1          2017-11-08 08:01         1                           1                           10
               2          2017-11-08 08:01         1                           2                           8
             
...
              100       2017-11-08 08:01         50                         1                           -200
              101       2017-11-08 08:01         50                         2                           -18
...           102       2017-11-08 08:02         1                           1                           9
              103       2017-11-08 08:02         1                           2                           8
              104       2017-11-08 08:02         50                         1                           -201
              105       2017-11-08 08:02         50                         2                           -15

J'affiche toute les secondes dans mon logiciel une vue dynamique:
             
              Date                            1.1              1.2 ...       50.1                50.2         

              2017-11-08 08:01         10              8    ...       -200                -18
              2017-11-08 08:02         9                8    ...       -201                -15

grâce à une requête dynamique du type:

SELECT SUM(CASE WHEN mes_numapp=1 and   mes_numchannel  =1 THEN mes_valeur ELSE null END "1.1"
          ,SUM(CASE WHEN mes_numapp=1 and   mes_numchannel  =2 THEN mes_valeur ELSE null END "1.2"     
...
         ,SUM(CASE WHEN mes_numapp=50 and   mes_numchannel  =1 THEN mes_valeur ELSE null END "50.1"
          ,SUM(CASE WHEN mes_numapp=50 and   mes_numchannel  =2 THEN mes_valeur ELSE null END "50.2"
...
FROM w201745.MESURES   
WHERE MES_ACQUIRE>=date1 AND MES_ACQUIRE<=date1
GROUP BY MES_ACQUIRE

La liste des voies sélectionnées peut changer.

Existe-il d'autres façon plus efficace de faire cet affichage ?

J'observe des dégradations de performances sur des pc windows (dont je ne peux pas maîtriser la config minimum, config préconnise : intel 3 coeurs ,2 Go de ram) quant les tables MESURES commencent à dépasser 2 millions d'enregistrements par semaine et qu'il faut réaliser des recherches sur plusieurs semaines.

Je parcours alors les semaines et stockent les informations dans une table temporaire.
Si les stats indiquent qu'il y a plus de 500000 lignes dans toutes les tables MESURES (des différentes semaines), j'effectue alors un échantillonnage (modulo/row_number()).

Merci d'avance pour votre aide

Pied de page des forums

Propulsé par FluxBB