Project

General

Profile

Development #91418

Requête coûteuse identifiée en base

Added by Paul Marillonnet about 1 month ago. Updated about 1 month ago.

Status:
Nouveau
Priority:
Normal
Assignee:
-
Category:
-
Target version:
-
Start date:
04 June 2024
Due date:
% Done:

0%

Estimated time:
Patch proposed:
No
Planning:
No

Description

Cette requête qui a trait à la journalisation spécifique à GLC et qui, d’après notre DBA, est coûteuse et gagnerait à être optimisée.

SELECT DATE_TRUNC('month', "journal_event"."timestamp" AT TIME ZONE 'Europe/Paris') AS "month", ("journal_event"."data" ->> 'service_name') AS "service_name", COUNT("journal_event"."id") AS "count" FROM "journal_event" WHERE "journal_event"."type_id" = 3 GROUP BY DATE_TRUNC('month', "journal_event"."timestamp" AT TIME ZONE 'Europe/Paris'), ("journal_event"."data" ->> 'service_name') ORDER BY "month" ASC;

Je vais regarder à quoi ça correspond dans le code du plugin.

History

#1

Updated by Paul Marillonnet about 1 month ago

  • Project changed from Plugin GLC to Authentic 2

C’est une requête de constitution de statistiques, rien de spécifique au plugin GLC.

#2

Updated by Benjamin Dauvergne about 1 month ago

  • Status changed from Nouveau to Information nécessaire
  • Assignee set to Pierre Ducroquet

Sur glc la table contient quasiment 2 millions de lignes, c'est lent et sans pré-aggrégation je ne suis pas certain qu'on puisse aller bien loin.

Je vois que PierreD a déjà tenté d'ajouter un index :

    "test__pierred_journal_group" btree (date_trunc('month'::text, timezone('Europe/Paris'::text, "timestamp"))) WHERE type_id = 3

mais qui ne me semble pas assez couvrant pour cette requêtes :
glc_authentic=# explain analyze SELECT DATE_TRUNC('month', "journal_event"."timestamp" AT TIME ZONE 'Europe/Paris') AS "month", ("journal_event"."data" ->> 'service_name') AS "service_name", COUNT(*) AS "count" FROM "journal_event" WHERE "journal_event"."type_id" = 3 GROUP BY DATE_TRUNC('month', "journal_event"."timestamp" AT TIME ZONE 'Europe/Paris'), ("journal_event"."data" ->> 'service_name') ORDER BY "month" ASC;
                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.56..308960.66 rows=1565343 width=48) (actual time=127.720..10734.095 rows=516 loops=1)
   Group Key: (date_trunc('month'::text, timezone('Europe/Paris'::text, "timestamp"))), ((data ->> 'service_name'::text))
   ->  Incremental Sort  (cost=0.56..269827.08 rows=1565343 width=40) (actual time=127.225..8331.946 rows=1593775 loops=1)
         Sort Key: (date_trunc('month'::text, timezone('Europe/Paris'::text, "timestamp"))), ((data ->> 'service_name'::text))
         Presorted Key: (date_trunc('month'::text, timezone('Europe/Paris'::text, "timestamp")))
         Full-sort Groups: 31  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB
         Pre-sorted Groups: 31  Sort Method: quicksort  Average Memory: 5996kB  Peak Memory: 8584kB
         ->  Index Scan using test__pierred_journal_group on journal_event  (cost=0.43..199386.64 rows=1565343 width=40) (actual time=0.049..3356.702 rows=1593775 loops=1)
 Planning Time: 0.187 ms
 Execution Time: 10734.889 ms
(10 lignes)

avec un index plus couvrant on gagne 5 pauvres secondes sur 10:
glc_authentic=# -- create index concurrently test__bdauvergne_journal_group on journal_event (DATE_TRUNC('month', "journal_event"."timestamp" AT TIME ZONE 'Europe/Paris'), ("journal_event"."data" ->> 'service_name')) where type_id = 3;
glc_authentic=# explain analyze SELECT DATE_TRUNC('month', "journal_event"."timestamp" AT TIME ZONE 'Europe/Paris') AS "month", ("journal_event"."data" ->> 'service_name') AS "service_name", COUNT(*) AS "count" FROM "journal_event" WHERE "journal_event"."type_id" = 3 GROUP BY DATE_TRUNC('month', "journal_event"."timestamp" AT TIME ZONE 'Europe/Paris'), ("journal_event"."data" ->> 'service_name') ORDER BY "month" ASC;
                                                                               QUERY PLAN                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.43..222045.82 rows=1619601 width=48) (actual time=0.869..5594.033 rows=516 loops=1)
   Group Key: date_trunc('month'::text, timezone('Europe/Paris'::text, "timestamp")), (data ->> 'service_name'::text)
   ->  Index Scan using test__bdauvergne_journal_group on journal_event  (cost=0.43..181555.80 rows=1619601 width=40) (actual time=0.033..3373.175 rows=1593860 loops=1)
 Planning Time: 0.218 ms
 Execution Time: 5594.760 ms
(5 lignes)

--

Plusieurs choses :
  • plutôt que de regarder uniquement le temps des requêtes il faudrait croiser ça avec leur fréquence, a priori regarder les statistiques est une action peu fréquente qu'un simple cache rendrait indolore pour l'agent qui se balade sur les pages statistiques du portail agent,
  • on peut ajouter ce nouvel index, mais je ne sais pas si ça vaut le coup de ralentir l'écriture dans journal_event pour gagner 5s très infréquemment (pour chaque index il y a de l'amplification d'écriture, i.e. au lieu d'écriture une page dans le WAL on en écrit 2 ou plus, ce n'est vraiment rentable à terme que si une requête est très fréquente),
  • si vraiment il faut que ça aille très vite tout le temps il n'y a que la pré-aggrégation qui fonctionne, i.e. ajouter une nouvelle table avec les décomptes rangés par type_id, mois et service. on aurait même plus besoin d'index.
#3

Updated by Pierre Ducroquet about 1 month ago

Benjamin Dauvergne a écrit :

Sur glc la table contient quasiment 2 millions de lignes, c'est lent et sans pré-aggrégation je ne suis pas certain qu'on puisse aller bien loin.
Plusieurs choses :
  • plutôt que de regarder uniquement le temps des requêtes il faudrait croiser ça avec leur fréquence, a priori regarder les statistiques est une action peu fréquente qu'un simple cache rendrait indolore pour l'agent qui se balade sur les pages statistiques du portail agent,

Ça tombe bien, je ne traite que des requêtes qui remontent à cause de leur fréquence. La prochaine fois que je la vois passer, je tenterai de rejoindre avec les logs des fronts pour voir si c'est un utilisateur agacé par le temps de chargement et qui tape sur F5, ou si c'est un usage "normal".

Si un cache est acceptable pour l'application, alors il faut sérieusement l'envisager.

  • on peut ajouter ce nouvel index, mais je ne sais pas si ça vaut le coup de ralentir l'écriture dans journal_event pour gagner 5s très infréquemment (pour chaque index il y a de l'amplification d'écriture, i.e. au lieu d'écriture une page dans le WAL on en écrit 2 ou plus, ce n'est vraiment rentable à terme que si une requête est très fréquente),

On se fiche de l'amplification d'écriture dans notre cas, on est sur de l'application avec trop peu d'écritures pour que cela soit significatif, alors qu'un blocage d'un processus en frontal pendant X secondes est beaucoup plus coûteux. (Et la première cible pour les index inutiles serait Django)

  • si vraiment il faut que ça aille très vite tout le temps il n'y a que la pré-aggrégation qui fonctionne, i.e. ajouter une nouvelle table avec les décomptes rangés par type_id, mois et service. on aurait même plus besoin d'index.

Genre ça:

glc_authentic=# create materialized view monthly_journal as SELECT type_id, DATE_TRUNC('month', "journal_event"."timestamp" AT TIME ZONE 'Europe/Paris') AS "month", ("journal_event"."data" ->> 'service_name') AS "service_name", COUNT(*) AS "count" FROM "journal_event" GROUP BY 1, 2, 3;
glc_authentic=# create unique index concurrently on monthly_journal (type_id, month, service_name);

Et périodiquement, un refresh materialized view concurrently monthly_journal ; qui prend 5 secondes.
Mais à nouveau, il faut que ce soit acceptable/compréhensible au niveau de la présentation des données aux clients.

#4

Updated by Benjamin Dauvergne about 1 month ago

  • Status changed from Information nécessaire to Nouveau
  • Assignee deleted (Pierre Ducroquet)

Pierre Ducroquet a écrit :

On se fiche de l'amplification d'écriture dans notre cas, on est sur de l'application avec trop peu d'écritures pour que cela soit significatif, alors qu'un blocage d'un processus en frontal pendant X secondes est beaucoup plus coûteux. (Et la première cible pour les index inutiles serait Django)

journal_event c'est un peu la table la plus écrite sur authentic je pense, c'est certainement moins que base_resourcelog sur passerelle mais quand même.

Genre ça:

[...]

Et périodiquement, un refresh materialized view concurrently monthly_journal ; qui prend 5 secondes.
Mais à nouveau, il faut que ce soit acceptable/compréhensible au niveau de la présentation des données aux clients.

Oui pré-aggréger. On peut faire ça en SQL et ce sera imbittable pour tout le monde ou en Python/Django, c'est pareil. On a 4 dimensions:
  • le temps en jour, moi ou année
  • le type d'authent,
  • le service,
  • l'ou du service (mais ça peut changer)

On pourrait aggréger par jour, type d'authentification, et service_pk et on est bon pour couvrir tout ce que fait l'API.

Also available in: Atom PDF