Postgresql 9.3 embarqué dans un logiciel de supervision

Jfmm · 09/11/2017 23:04:52

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

rjuju · 10/11/2017 09:07:56

Le problème avec votre modèle de stockage est qu'une grande partie est redondante. Avez-vous essayé de stocker une seule ligne par timestamp ?

Vous pouvez créer un type composite pour pouvoir stocker jusqu'à 8 mesures par canal, et un autre type composite pour stocker jusqu'à 255 appareils.

Par exemple :

create type mes_channels AS (val1 float4, val2 float4, val3 float4, val4 float4, val5 float4, val6 float4, val7 float4, val8 float4);
create type mes_apps AS (app1 mes_channel, app2 mes_channel, app3 mes_channel, app4 mes_channel, app5 mes_channel, app6 mes_channel, app7 mes_channel, app8 mes_channel, app9 mes_channel,...);

create table mesure(mes_id bigint, mes_acquire timestamp, mes_apps mes_valeurs);

insert into mesure select 1, now(),  (
(10, 8, null, null, null, null, null, null, null, null)::mes_channels,
[...]
(-200, -18, null, null, null, null, null, null, null, null)::mes_channels,
(null, null, null, null, null, null, null, null, null, null)::mes_channels,
[..]
(null, null, null, null, null, null, null, null, null, null)::mes_channels
);

select sum((mes_valeurs).app1.val1) as "1.1", sum((mes_valeurs).app1.val2) as "1.2"... from mesures

La syntaxe est un peu lourde, il faut faut explicitement donner toutes les valeurs non présentes, mais il devrait y avoir un gain très important sur la volumétrie.

Jfmm · 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

Jfmm · 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 !

rjuju · 23/11/2017 17:43:33

Quelle est la requête associée ainsi que le plan d'exécution complet ? Les statistiques ont l'air fausses en tout cas, avez-vous essayé un vacuum analyze sur la table mesure_summary ?

Jfmm · 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

Jfmm · 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

Dernière modification par Jfmm (23/11/2017 18:54:30)

rjuju · 24/11/2017 00:51:30

Je ne comprends pourquoi vous avez ce type de plan. Quels changements avez-vous apportés dans la configuration de l'instance ? Avez-vous désactivé le enable_seqscan ?

En cas d'insertions uniquement (pas de mise à jour ou suppression) il me semble que le vacuum ne se déclenche pas. La configuration de l'autovacuum est sinon plutôt une affaire de combien de ligne sont modifiées parmi combien de lignes au total avant que la différence de statistiques implique un mauvais plan. Il n'est donc pas possible de donner une configuration pertinente sans avoir plus d'informations.

Jfmm · 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

Jfmm · 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 ?

gleu · 25/11/2017 10:47:08

À condition que ce soit bien des valeurs NULL, oui.

rjuju · 25/11/2017 18:42:18

J'ai regardé rapidement, mais j'ai l'impression que la table mesure_summary sera écrite de la manière suivante :

- 1 ligne insérée pour la première mesure du premier capteur
- puis autant d'update qu'il y a d'autres mesures

Si c'est le cas, c'est un très mauvais choix d'implémentation, parce que cela engendrera beaucoup de fragmentation, nécessitera beaucoup de tâches de maintenance supplémentaires qu'il n'aurait fallu si la ligne était insérée en une seule fois.

Cela ne répond pas non plus à la question des paramètres de configuration modifiés;

Jfmm · 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.

Dernière modification par Jfmm (26/11/2017 18:52:21)

Jfmm · 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

Jfmm · 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

rjuju · 02/12/2017 02:20:40

Le temps d'exécution est effectivement très mauvais. Sur mon pc portable, cette même requête tourne en 200 ms. Postgres sous windows n'est pas réputé pour être très rapide, mais pas à ce point. D'après ce jeu de test, la requête doit récupérer environ 65 Mo de données, soit une moyenne pour vous de 2 Mo/s. Même un simple disque dur mécanique SATA devrait être bien meilleur que ça. Peut-être y a-t-il un antivirus sur le poste, si c'est le cas il faut le désactiver pour le répertoire de données de PostgreSQL ainsi que le binaire. Il faudrait sinon un explain (analyze, buffers) de la requête pour avoir plus d'informations.

Quelques remarques en vrac :

- la version 9.2 n'est plus supportée, il faudrait passer au moins sur la 9.3, idéalement une version beaucoup plus récente (9.6 ou 10)
- sur windows il est conseillé de désactiver les paramètre update_process_title ainsi que track_io_timing
- vous devriez positionner le paramètre wal_buffers à 16MB
- la configuration du checkpoint est celle par défaut, en fonction de l'activité en écriture sur le serveur c'est probablement trop bas

Jfmm · 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

Dernière modification par Jfmm (02/12/2017 11:23:12)

rjuju · 02/12/2017 12:02:34

Oui pour les données. Pour le binaire, vous pouvez également exclure le répertoire en question, mais certains antivirus vérifie également ce que font les processus en cours d'exécution, et il faut donc également le configurer pour qu'il ne surveille pas le processus postgres.

Jfmm · 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

Dernière modification par Jfmm (04/12/2017 15:28:46)

rjuju · 04/12/2017 17:57:01

Je ne suis pas certain de comprendre. La requête avec explain (analyze, buffers) à pris 85 ms. Vous avez rebooté et le même explain analyze à pris 21 secondes ? Peux-t-on voir le résultat de l'explain ?

Si la requête prend 21 second en l'éxécutant sans explain c'est probablement uniquement l'affichage d'un million de ligne qui est lent.

Jfmm · 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

gleu · 04/12/2017 19:44:28

C'est l'affichage dans pgadmin qui est particulièrement lent, pas la requête.

#1 09/11/2017 23:04:52

Postgresql 9.3 embarqué dans un logiciel de supervision

#2 10/11/2017 09:07:56

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#3 11/11/2017 00:20:18

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#4 23/11/2017 15:39:38

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#5 23/11/2017 17:43:33

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#6 23/11/2017 18:50:00

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#7 23/11/2017 18:51:41

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#8 24/11/2017 00:51:30

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#9 24/11/2017 11:09:59

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#10 24/11/2017 17:01:31

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#11 25/11/2017 10:47:08

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#12 25/11/2017 18:42:18

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#13 25/11/2017 19:28:27

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#14 27/11/2017 11:19:43

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#15 01/12/2017 13:30:39

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#16 02/12/2017 02:20:40

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#17 02/12/2017 10:56:16

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#18 02/12/2017 12:02:34

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#19 04/12/2017 15:18:10

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#20 04/12/2017 17:57:01

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#21 04/12/2017 18:20:11

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

#22 04/12/2017 19:44:28

Re : Postgresql 9.3 embarqué dans un logiciel de supervision

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux