Rafraichissement de vm_observations

[MathieuLagarde]

Bonjour, Suite à l'import de nouvelles données en base, je souhaite mettre à jour les vm sur Atlas. La fonction 'REFRESH MATERIALIZED VIEW atlas.vm_observations ;' n'aboutit pas en prod (1.3 M données), alors qu'elle ne pose pas de problème sur la préprod (100 000 données) de même configuration. Il y a quelques mois, cette même opération prenait une vingtaine de minutes pour un volume de données équivalent en prod. Je suis sur Atlas version 1.4.2. Avez-vous déjà été confronté au problème ? Qu'ai-je loupé ? Merci pour votre aide !

[DonovanMaillard]

Salut Mathieu,

Pourne pas tout rafraichir y compris le ref_geo etc, tu peux tester la fonction atlas.refresh_materialized_view_data() :)

[MathieuLagarde]

Salut Donovan, J'ai également fait tourner cette fonction. Même constat : elle fonctionne bien en preprod, mais n'abouti pas en prod.

[camillemonchicourt]

Oui, forcément. Si tu arrives à refresh une des VM, et que ça fonctionne pas, si tu lances une fonction qui l'a rafraîchit, ainsi que 5 autres, ça ne marchera pas mieux. Là c'est de l'administration PostgreSQL, plus qu'une question de GeoNature-atlas. Il faudrait voir si tu as des erreurs qui sont remontés quand tu essaies de refresh cette VM. Ou sinon peut-être que ton serveur PostgreSQL est dans les choux, et qu'un petit redémarrage lui ferait pas de mal ?

[jpm-cbna]

Dans le cadre de la mise en place de GeoNature pour les SINP PACA et AURA, j'ai du modifier le fonctionnement de la VM vm_observations de l'Atlas. J'ai du aussi simplifié la vue synthese.syntheseff car elle posait problème. Le code SQL et la doc : https://github.com/cbn-alpin/sinp-aura-data/tree/main/db-atlas

Le problème était principalement dû à un territoire trop grand (une région) et un nombre d'observation trop important (plusieurs millions). Le rafraîchissement n'aboutissait pas même après plus de 24h...

[MathieuLagarde]

Bonjour,

Suite à des problèmes rencontrés récemment en lien avec la vm_observations (REFRESH MATERIALIZED VIEW atlas.vm_observations ; n’aboutissant pas et saturant le serveur – cf. lock) (https://github.com/PnX-SI/GeoNature-atlas/issues/283), nous avons fait appel à notre prestataire informatique (Géofit) pour résoudre le problème.

En remontant le fil, entre geonature2db et geonatureatlas, nous (@pbarille et moi) avions pu constater que :

• la table étrangère synthese.synthese de geonatureatlas comporte bien le même nombre de données que la table gn_synthese.synthese de geonature2db ;
• la visualisation des données de cette table donne un message d'erreur :

ERROR:` ERREUR: la colonne « id_nomenclature_obs_meth » n'existe pasCONTEXT: Remote SQL command: SELECT id_synthese, unique_id_sinp, unique_id_sinp_grp, id_source, id_module, entity_source_pk_value, id_dataset, id_nomenclature_geo_object_nature, id_nomenclature_grp_typ, id_nomenclature_obs_meth, id_nomenclature_obs_technique, [...] last_action FROM gn_synthese.syntheseSQL state: 42703

• la vue 'synthese.syntheseff' de geonatureatlas pose des problèmes (count, visualisation impossibles sur pgAdmin) ;
• la mise à jour de la 'vm_observations' n'aboutit pas (REFRESH MATERIALIZED VIEW atlas.vm_observations ;).

Nous avons ensuite entrepris quelques tests :

• création d'une nouvelle table étrangère (synthese_test) pour contourner le problème de colonne ; la table se crée et s'ouvre (au moins les 100 premières/dernières lignes);
• nous avons recréé la vue syntheseff (syntheseff_test) en nous basant sur la table synthese_test mais là ça ne s'ouvre pas;
• enfin nous avons essayé de lancer des morceaux de la requête de création de vue syntheseff_test pour essayer d'identifier le problème. La première partie du WITH ne pose pas de problème, en revanche la deuxième partie n'aboutit pas (on l'a laissé tourner plusieurs minutes).

Puis nous avons remonté la table gn_synthese.synthese (et toutes les tables dont elle a besoin pour fonctionner) sur une base test sur un "petit" serveur interne (1CPU + 4Go de RAM).
Il y a eu une erreur sur un trigger (fct_trig_insert_in_cor_area_synthese_on_each_statement) lors de la remontée de la table l'appel de la fonction ST_GeometryType n'était pas préfixé par "public". Nous supposons que sur les bases prod et pre-prod de geonature, public est mentionné comme schéma par défaut et que ça ne pose pas de problème.
Nous avons laissé la table cor_area_synthese se reconstruire toute seule grâce à l'import de données dans la table synthese.
Nous avons essayé de décomposer la requête syntheseff pour voir d'où pouvait provenir les lenteurs.
Nous nous sommes aperçus que le champ centroid de la table ref_geo.lib_areas était vide pour de nombreux enregistrements.
Nous avons fait un update de la table pour le remplir.

UPDATE ref_geo.l_areas SET centroid = public.ST_PointOnSurface(geom) WHERE public.ST_GeometryType(centroid) is null ;

Nous avons décomposé la requête syntheseff pour essayer de trouver ce qui pose problème dans son exécution. Le "CASE" a été identifié comme l'élément qui fait ralentir la requête et qui fait qu'elle n'aboutit pas, le reste s'exécutant en 30 secondes environ.
En prenant les éléments du "CASE" un par un les temps d'exécution sont bons également.
En ne prenant que les 3 "CASE WHEN" sans le "ELSE" les temps d'exécution explosent à nouveau (requête stoppée avant la fin au bout de plusieurs minutes d'attente).
Nous avons trouvé une requête optimisée sur le git de geonature : https://github.com/PnX-SI/GeoNature-atlas/issues/250#issuecomment-685727265

SELECT DISTINCT ON (id_synthese) / Pour ne garder qu'un tuple par id_synthese quand plusieurs remontent (cas de données à la limite de deux mailles / s.id_synthese, s.cd_nom, s.date_min AS dateobs, s.observers AS observateurs, (s.altitude_min + s.altitude_max) / 2 AS altitude_retenue, st_transform(COALESCE(areas.centroid, the_geom_point),3857) AS the_geom_point, s.count_min AS effectif_total, insee_com::varchar(25) AS insee, dl.cd_nomenclature::int as diffusion_level FROM synthese.synthese s JOIN ref_geo.l_areas c ON st_intersects(the_geom_local, c.geom) JOIN ref_geo.li_municipalities m ON c.id_area = m.id_area LEFT JOIN synthese.t_nomenclatures dl ON s.id_nomenclature_diffusion_level = dl.id_nomenclature AND id_nomenclature_diffusion_level IS NOT NULL AND dl.cd_nomenclature <> '4' LEFT JOIN LATERAL ( SELECT centroid FROM synthese.cor_area_synthese sa JOIN ref_geo.l_areas a ON sa.id_area = a.id_area JOIN ref_geo.bib_areas_types t ON a.id_type = t.id_type WHERE type_code IN ('M10', 'COM', 'DEP') AND sa.id_synthese = s.id_synthese AND type_code = CASE WHEN dl.cd_nomenclature = '1' THEN 'COM' WHEN dl.cd_nomenclature = '2' THEN 'M10' WHEN dl.cd_nomenclature = '3' THEN 'DEP' END ) areas ON trueWHERE id_nomenclature_observation_status = ( /sous-requête bizarrement plus efficace qu'une jointure dans la requête principale /
SELECT id_nomenclature FROM synthese.t_nomenclatures JOIN synthese.bib_nomenclatures_types USING(id_type) WHERE bib_nomenclatures_types.mnemonique = 'STATUT_OBS' AND cd_nomenclature = 'Pr') / Pour avoir un comportement de DISTINCT ON prévisible / ORDER BY id_synthese, st_astext(COALESCE(areas.centroid, the_geom_point)) ;

Avec cette requête ça fonctionne, la vue syntheseff s'ouvre en un peu plus d'une minute.
Nous avons recréé la vue matérialisée vm_observation et là aussi en un peu plus d'une minute tout est exécuté et la vue s'affiche en environ 1 minute également.
Info contexte supplémentaire : tout s'est fait sur la même base sans lien vers des tables étrangères comme entre geonature2db et geonatureatlas.

Reprise des tests sur la pre prod (miroir de la prod avec un volume d’observations moindre)

Satisfaits de ces résultats nous avons essayé de reproduire tout ça sur la préprod.
Nous avons calculé les centroides sur ref_geo.l_areas.
Nous avons recalculé la table gn_synthese.cor_area_synthese dans le doute (core_area_synthese_2) - 40 minutes de traitement environ.
Nous avons lancé la requête syntheseff optimisée (syntheseff2) dans atlas.
La requête n'aboutit pas...
Nous avons essayé de relancer la requête syntheseff classique en la décomposant comme fait sur le serveur test.
C'est encore une fois le "CASE" qui pose problème sauf que cette fois en ne lançant qu'une des clauses when du case nous sommes à 5 minutes d'exécution et non plus 30 secondes.

Tests sur la prod

Nous avons également fait quelques tests sur la prod.
Essai de la requête optimisée sans succès (syntheseff_test_mathieubossaert - https://github.com/PnX-SI/GeoNature-atlas/issues/250#issuecomment-685727265)
Essai de la requête optimisée en se branchant sur une autre table étrangère synthese (synthese_test) pour palier au problème de champ id_nomenclature_obs_meth déclaré dans la table étrangère synthese mais qui n'est plus présent dans la table source. Là aussi sans succès.
Essai de la requête optimisée directement sur la base geonature2db (synthese.v_synthese_ff_test), ça fonctionne en environ une minute.
Je précise que nous n'avons pas rempli la table l_areas avec les centroïdes manquant sur la prod (je pense toutefois que ça peut permettre d'améliorer les perfs).
Nous n'avons pas recalculé non plus la table cor_area_synthese sur la prod (ne semble pas nécessaire).

Matthieu Etourneau de Géofit a donc été sollicité à ce stade pour trouver une solution.

Il propose une réécriture de la vue syntheseff:

CREATE VIEW synthese.syntheseff AS WITH obs_data AS ( SELECT DISTINCT s.id_synthese, s.cd_nom, s.date_min AS dateobs, s.observers AS observateurs, (s.altitude_min + s.altitude_max) / 2 AS altitude_retenue, CASE WHEN dl.cd_nomenclature = '1' THEN st_transform(sa_com.centroid, 3857) WHEN dl.cd_nomenclature = '2' THEN st_transform(sa_m10.centroid, 3857) WHEN dl.cd_nomenclature = '3' THEN st_transform(sa_dep.centroid, 3857) ELSE st_transform(s.the_geom_point, 3857) END AS the_geom_point, s.count_min AS effectif_total, dl.cd_nomenclature::int as diffusion_level FROM synthese.synthese s LEFT JOIN ( select sa_com.id_synthese, centroid FROM synthese.cor_area_synthese sa_com INNER JOIN ref_geo.l_areas a_com ON sa_com.id_area = a_com.id_area INNER JOIN ref_geo.bib_areas_types t_com ON a_com.id_type = t_com.id_type AND t_com.type_code = 'COM' INNER JOIN atlas.l_communes c_com ON st_intersects(st_transform(a_com.centroid, 3857), c_com.the_geom) ) as sa_com ON sa_com.id_synthese = s.id_synthese LEFT JOIN ( select sa_dep.id_synthese, centroid FROM synthese.cor_area_synthese sa_dep INNER JOIN ref_geo.l_areas a_dep ON sa_dep.id_area = a_dep.id_area INNER JOIN ref_geo.bib_areas_types t_dep ON a_dep.id_type = t_dep.id_type AND t_dep.type_code = 'DEP' INNER JOIN atlas.l_communes c_dep ON st_intersects(st_transform(a_dep.centroid, 3857), c_dep.the_geom) ) as sa_dep ON sa_dep.id_synthese = s.id_synthese LEFT JOIN ( select sa_m10.id_synthese, centroid FROM synthese.cor_area_synthese sa_m10 INNER JOIN ref_geo.l_areas a_m10 ON sa_m10.id_area = a_m10.id_area INNER JOIN ref_geo.bib_areas_types t_m10 ON a_m10.id_type = t_m10.id_type AND t_m10.type_code = 'M10' INNER JOIN atlas.l_communes c_m10 ON st_intersects(st_transform(a_m10.centroid, 3857), c_m10.the_geom) ) as sa_m10 ON sa_m10.id_synthese = s.id_synthese LEFT OUTER JOIN synthese.t_nomenclatures dl ON s.id_nomenclature_diffusion_level = dl.id_nomenclature LEFT OUTER JOIN synthese.t_nomenclatures st ON s.id_nomenclature_observation_status = st.id_nomenclature --1346194 WHERE (NOT dl.cd_nomenclature = '4'::text OR id_nomenclature_diffusion_level IS NULL) -- Filtre données non diffusable code "4" ou pas de diffusion spécifiée AND st.cd_nomenclature = 'Pr'-- seulement les données présentes (status_observation = ) )
SELECT d.id_synthese, d.cd_nom, d.dateobs, d.observateurs, d.altitude_retenue, d.the_geom_point, d.effectif_total, c.insee, diffusion_level FROM obs_data d JOIN atlas.l_communes c ON st_intersects(d.the_geom_point, c.the_geom);

Il propose également quelques reindex (ce que nous avions déjà effectué) :
VACUUM FULL VERBOSE ANALYZE ; REINDEX SCHEMA gn_synthese ; REINDEX SCHEMA ref_geo ; REINDEX SCHEMA ref_nomenclatures ;

Côté conf postgres:

Nouvelle configuration de postgresql :

max_connections = 100
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 20
work_mem = 10485kB
min_wal_size = 2GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

Suite à cela, nous avons repris nos investigations pour comprendre l’origine du problème initial.

La conclusion semble être la suivante: les jointures, notamment spatiales, entre différentes tables étrangères ont un impact non négligeable sur les performances (perte des index lors de ces appels ?).

Proposition: remplacement de la requête syntheseff par celle proposée ci-dessus par Mathieu Etourneau ou par celle de Mathieu Bossaert en remplaçant

JOIN ref_geo.l_areas c ON st_intersects(s.the_geom_local, c.geom) JOIN ref_geo.li_municipalities m ON c.id_area = m.id_area

Par :

JOIN atlas.l_communes c ON st_intersects(st_transform(s.the_geom_point, 3857), c.the_geom)

Qui permettent toutes les deux de limiter les appels aux tables étrangères en s’appuyant sur la table atlas.l_communes

@jpm-cbna nous serions intéressé par votre retour sur le test de ces deux requêtes puisque vous avez été confronté au même problème que le nôtre : : https://github.com/cbn-alpin/sinp-aura-data/tree/main/db-atlas

Configurations:
Prod géonature: Géonature version 2.6.2 / Atlas version 1.4.2.
Debian 10 – 8 CPU – 30 Go RAM
1346194 données en synthèse