Projet

Général

Profil

Development #57637

Trop de requêtes SQL sur la tables roles pendant une seule requête HTTP

Ajouté par Benjamin Dauvergne il y a plus de 2 ans. Mis à jour il y a environ 2 ans.

Statut:
Fermé
Priorité:
Normal
Assigné à:
-
Version cible:
-
Début:
06 octobre 2021
Echéance:
% réalisé:

0%

Temps estimé:
Patch proposed:
Non
Planning:
Non

Description

Suite encore au ticket client #56562.

On a 85 requêtes sur les rôles :

# grep roles log3
2021-10-06 11:15:40.467  SELECT ... FROM roles WHERE id = 'cc3164213ff84343b8429e05dcfd26ab' 
2021-10-06 11:15:40.468  SELECT ... FROM roles WHERE id = '2c68cbb76fd347678bc753eb7c376614' 
2021-10-06 11:15:40.469  SELECT ... FROM roles WHERE id = 'cc3164213ff84343b8429e05dcfd26ab' 
2021-10-06 11:15:40.469  SELECT ... FROM roles WHERE id = '2c68cbb76fd347678bc753eb7c376614' 
2021-10-06 11:15:40.469  SELECT ... FROM roles WHERE id = '391176ccb1864956a3479d8a61bd19fe' 
2021-10-06 11:15:40.469  SELECT ... FROM roles WHERE id = 'b53aeea3b4d549c595940279c67202a0' 
2021-10-06 11:15:40.470  SELECT ... FROM roles WHERE id = '6b2f98ddb77843978bbc7ab0761015c8' 
2021-10-06 11:15:40.470  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.470  SELECT ... FROM roles WHERE id = '4ed5ca7d3b144efc910bc7942e6a9682' 
2021-10-06 11:15:40.471  SELECT ... FROM roles WHERE id = '27265c45e0814909a3cdd305542cc432' 
2021-10-06 11:15:40.471  SELECT ... FROM roles WHERE id = '2c68cbb76fd347678bc753eb7c376614' 
2021-10-06 11:15:40.472  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.472  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.472  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.473  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.473  SELECT ... FROM roles WHERE id = '391176ccb1864956a3479d8a61bd19fe' 
2021-10-06 11:15:40.473  SELECT ... FROM roles WHERE id = 'b53aeea3b4d549c595940279c67202a0' 
2021-10-06 11:15:40.474  SELECT ... FROM roles WHERE id = '6b2f98ddb77843978bbc7ab0761015c8' 
2021-10-06 11:15:40.474  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.554  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.556  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.556  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.556  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.557  SELECT ... FROM roles WHERE id = '391176ccb1864956a3479d8a61bd19fe' 
2021-10-06 11:15:40.557  SELECT ... FROM roles WHERE id = 'b53aeea3b4d549c595940279c67202a0' 
2021-10-06 11:15:40.557  SELECT ... FROM roles WHERE id = '6b2f98ddb77843978bbc7ab0761015c8' 
2021-10-06 11:15:40.558  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.802  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.804  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.815  SELECT ... FROM roles WHERE id = 'cc3164213ff84343b8429e05dcfd26ab' 
2021-10-06 11:15:40.815  SELECT ... FROM roles WHERE id = '2c68cbb76fd347678bc753eb7c376614' 
2021-10-06 11:15:40.815  SELECT ... FROM roles WHERE id = 'cc3164213ff84343b8429e05dcfd26ab' 
2021-10-06 11:15:40.816  SELECT ... FROM roles WHERE id = '2c68cbb76fd347678bc753eb7c376614' 
2021-10-06 11:15:40.816  SELECT ... FROM roles WHERE id = '391176ccb1864956a3479d8a61bd19fe' 
2021-10-06 11:15:40.816  SELECT ... FROM roles WHERE id = 'b53aeea3b4d549c595940279c67202a0' 
2021-10-06 11:15:40.816  SELECT ... FROM roles WHERE id = '6b2f98ddb77843978bbc7ab0761015c8' 
2021-10-06 11:15:40.817  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.817  SELECT ... FROM roles WHERE id = '4ed5ca7d3b144efc910bc7942e6a9682' 
2021-10-06 11:15:40.817  SELECT ... FROM roles WHERE id = '27265c45e0814909a3cdd305542cc432' 
2021-10-06 11:15:40.818  SELECT ... FROM roles WHERE id = '2c68cbb76fd347678bc753eb7c376614' 
2021-10-06 11:15:40.851  SELECT ... FROM roles WHERE id = 'cc3164213ff84343b8429e05dcfd26ab' 
2021-10-06 11:15:40.853  SELECT ... FROM roles WHERE id = '2c68cbb76fd347678bc753eb7c376614' 
2021-10-06 11:15:40.853  SELECT ... FROM roles WHERE id = 'cc3164213ff84343b8429e05dcfd26ab' 
2021-10-06 11:15:40.853  SELECT ... FROM roles WHERE id = '2c68cbb76fd347678bc753eb7c376614' 
2021-10-06 11:15:40.854  SELECT ... FROM roles WHERE id = '391176ccb1864956a3479d8a61bd19fe' 
2021-10-06 11:15:40.854  SELECT ... FROM roles WHERE id = 'b53aeea3b4d549c595940279c67202a0' 
2021-10-06 11:15:40.854  SELECT ... FROM roles WHERE id = '6b2f98ddb77843978bbc7ab0761015c8' 
2021-10-06 11:15:40.855  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.855  SELECT ... FROM roles WHERE id = '4ed5ca7d3b144efc910bc7942e6a9682' 
2021-10-06 11:15:40.855  SELECT ... FROM roles WHERE id = '27265c45e0814909a3cdd305542cc432' 
2021-10-06 11:15:40.855  SELECT ... FROM roles WHERE id = '2c68cbb76fd347678bc753eb7c376614' 
2021-10-06 11:15:40.856  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.857  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.857  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.857  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.857  SELECT ... FROM roles WHERE id = '391176ccb1864956a3479d8a61bd19fe' 
2021-10-06 11:15:40.858  SELECT ... FROM roles WHERE id = 'b53aeea3b4d549c595940279c67202a0' 
2021-10-06 11:15:40.858  SELECT ... FROM roles WHERE id = '6b2f98ddb77843978bbc7ab0761015c8' 
2021-10-06 11:15:40.858  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.937  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.938  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.939  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.939  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:40.939  SELECT ... FROM roles WHERE id = '391176ccb1864956a3479d8a61bd19fe' 
2021-10-06 11:15:40.940  SELECT ... FROM roles WHERE id = 'b53aeea3b4d549c595940279c67202a0' 
2021-10-06 11:15:40.940  SELECT ... FROM roles WHERE id = '6b2f98ddb77843978bbc7ab0761015c8' 
2021-10-06 11:15:40.940  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.178  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.180  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.193  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.194  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.194  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.194  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.194  SELECT ... FROM roles WHERE id = '391176ccb1864956a3479d8a61bd19fe' 
2021-10-06 11:15:41.195  SELECT ... FROM roles WHERE id = 'b53aeea3b4d549c595940279c67202a0' 
2021-10-06 11:15:41.195  SELECT ... FROM roles WHERE id = '6b2f98ddb77843978bbc7ab0761015c8' 
2021-10-06 11:15:41.195  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.197  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.197  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.199  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.200  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d' 
2021-10-06 11:15:41.200  SELECT ... FROM roles WHERE id = '391176ccb1864956a3479d8a61bd19fe' 
2021-10-06 11:15:41.200  SELECT ... FROM roles WHERE id = 'b53aeea3b4d549c595940279c67202a0' 
2021-10-06 11:15:41.201  SELECT ... FROM roles WHERE id = '6b2f98ddb77843978bbc7ab0761015c8' 
2021-10-06 11:15:41.201  SELECT ... FROM roles WHERE id = '57c41e4e30f9449598e5fac577f6bd0d'

pour seulement 8 rôles différents :

# grep roles log3 | sed 's/.*id =//' | sort | uniq
 '27265c45e0814909a3cdd305542cc432' 
 '2c68cbb76fd347678bc753eb7c376614' 
 '391176ccb1864956a3479d8a61bd19fe' 
 '4ed5ca7d3b144efc910bc7942e6a9682' 
 '57c41e4e30f9449598e5fac577f6bd0d' 
 '6b2f98ddb77843978bbc7ab0761015c8' 
 'b53aeea3b4d549c595940279c67202a0' 
 'cc3164213ff84343b8429e05dcfd26ab'

Ça fait beaucoup. Les rôles étant quasiment immuables (l'appartenance est stockée sur les objets user) on pourrait les mettre en cache de manière plus agressive au moins pour la durée d'une requête.

 # sur toodego
In [20]: uuids = [role.id for role in pub.role_class.select(limit=85)]

In [21]: %timeit len([pub.role_class.get(id=uuid) for uuid in uuids[:8]])
100 loops, best of 5: 3.11 ms per loop <- mise en cache au premier accès

In [22]: %timeit len([pub.role_class.get(id=uuid) for uuid in uuids])
10 loops, best of 5: 28.9 ms per loop <- pas de mise en cache

In [23]: %timeit len(pub.role_class.select())
10 loops, best of 5: 164 ms per loop <-tout mettre en cache est un peu cher

Demandes liées

Lié à w.c.s. - Development #57623: sql: ne pas itérer ligne par ligne sur un SqlKlass.select(iterator=False)Rejeté05 octobre 2021

Actions
Lié à w.c.s. - Development #59882: contexte lazy pour le rendu des pages et des widgetsFermé18 décembre 2021

Actions
Lié à w.c.s. - Development #59891: lazy loading des rôles associés aux catégoriesFermé19 décembre 2021

Actions

Historique

#1

Mis à jour par Benjamin Dauvergne il y a plus de 2 ans

  • Lié à Development #57623: sql: ne pas itérer ligne par ligne sur un SqlKlass.select(iterator=False) ajouté
#3

Mis à jour par Frédéric Péters il y a plus de 2 ans

  • Lié à Development #59882: contexte lazy pour le rendu des pages et des widgets ajouté
#4

Mis à jour par Frédéric Péters il y a plus de 2 ans

J'ai passé un peu de temps sur les origines du chargement des rôles, différentes choses :

  • les rôles sont chargés quand les catégories sont chargées, lors de la désérialisation des attributs *_roles (export_roles, statistics_roles, management_roles); la plupart du temps on charge les catégories sans être intéressé par ces attributs, le patch de la branche gère ça en faisant les requêtes uniquement lors de l'accès aux attributs (peut-être à sortir dans son propre ticket, ce ticket faisant juste chapeau);
  • pour accentuer ça les catégories elles-mêmes étaient beaucoup trop chargées, en front, c'est en partie très bête #59881
  • les rôles étaient aussi pas mal chargés pour les variables form_role_…, et il y avait encore de la génération statique de celles-ci lors des rendus des widgets et des pages, c'est #59882. (qui doit venir après #59883)
  • côté objet User l'attribut roles est une liste d'identifiants, ça ne pose pas problème. (il y a cependant une utilisation dans can_go_in_backoffice à optimiser/mettre en cache, pas encore de ticket).

Il y a toute une série de court-circuits en place quand l'utilisateur est admin, je n'ai pas encore passé du temps avec un agent pas admin.

#5

Mis à jour par Frédéric Péters il y a plus de 2 ans

#6

Mis à jour par Frédéric Péters il y a environ 2 ans

  • Statut changé de Nouveau à Fermé

Les tickets liés sont intégrés depuis.

Formats disponibles : Atom PDF