Forums PostgreSQL.fr

7uc0

Membre

Optimisation [suite] - lecture analyze

Bonjour la communauté,
En cours d'optimisation sur une requête, l'instruction suivante me laisse perplexe : executée une seule fois, son temps d'execution est long, executée à nouveau, le temps d'execution descend nettement.

explain analyze select count(*) as col_0_0_ from snsrBroadcast broadcast0_ where broadcast0_.gateway_id='52910199' limit 2

resultat #1

                                                              QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=816.50..816.51 rows=1 width=0) (actual time=4463.501..4463.504 rows=1 loops=1)
   ->  Aggregate  (cost=816.50..816.51 rows=1 width=0) (actual time=4463.496..4463.497 rows=1 loops=1)
         ->  Bitmap Heap Scan on snsrbroadcast broadcast0_  (cost=6.00..815.96 rows=213 width=0) (actual time=376.240..4463.001 rows=309 loops=1)
               Recheck Cond: (gateway_id = 52910199::bigint)
               ->  Bitmap Index Scan on idx_broadcast_gateway  (cost=0.00..5.94 rows=213 width=0) (actual time=376.175..376.175 rows=309 loops=1)
                     Index Cond: (gateway_id = 52910199::bigint)
 Total runtime: 4464.248 ms
(7 rows)

Executée une seconde fois, voici le résultat

                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=816.50..816.51 rows=1 width=0) (actual time=0.965..0.967 rows=1 loops=1)
   ->  Aggregate  (cost=816.50..816.51 rows=1 width=0) (actual time=0.962..0.963 rows=1 loops=1)
         ->  Bitmap Heap Scan on snsrbroadcast broadcast0_  (cost=6.00..815.96 rows=213 width=0) (actual time=0.159..0.771 rows=309 loops=1)
               Recheck Cond: (gateway_id = 52910199::bigint)
               ->  Bitmap Index Scan on idx_broadcast_gateway  (cost=0.00..5.94 rows=213 width=0) (actual time=0.100..0.100 rows=309 loops=1)
                     Index Cond: (gateway_id = 52910199::bigint)
 Total runtime: 1.015 ms
(7 rows)

Existe-t-il un "cache" de requêtes evitant au moteur les mêmes opérations que pour #1 ?

E.

rjuju

Administrateur

Re : Optimisation [suite] - lecture analyze

Bonjour.

Il n'y a pas un cache de requête mais un cache de donnée. Grosso modo, lors d'une requête, si les données se trouvent dans le cache alors postgres n'a pas besoin de les lire du disque, et la requête est donc plus rapide à exécuter, le changement de durée dépendant de la vitesse disques.

Pour voir cela, vous pouvez utiliser l'option buffers du explain, qui vous dira si les blocs sont lus en mémoire (hit) ou sur disque (read).

explain (analyze,buffers) select count(*) as col_0_0_ from snsrBroadcast broadcast0_ where broadcast0_.gateway_id='52910199' limit 2

Dernière modification par rjuju (22/10/2011 17:41:57)

---

Julien.
https://rjuju.github.io/

7uc0

Membre

Re : Optimisation [suite] - lecture analyze

avec un rapport 1/4000 ? D'une efficacité....

rjuju

Administrateur

Re : Optimisation [suite] - lecture analyze

Cela peut influer, autant peut être pas. Si vous êtes sur un portable avec des disques en 5400 tour ou du sas changera le coefficient. Après, il y avait peut être un verrou ou qqc qui bloquait.
Comme je vous disais l'option buffers est une première piste pour voir si c'est la seule chose qui influe.

---

Julien.
https://rjuju.github.io/

7uc0

Membre

Re : Optimisation [suite] - lecture analyze

Après quelques jours de congés je reviens sur la question. je tourne en postresql 8.4, comment puis-je investiguer le coup des buffers ?

rjuju

Administrateur

Re : Optimisation [suite] - lecture analyze

Vous pouvez utiliser l'option buffers du explain comme montré au dessus pour voir si les différences viennent de la lecture en cache ou sur disque.

Avez-vous la dernière version de postgresql 8.4 ? (8.4.9)

Sinon, le changement de temps d'exécution se reproduit-il à chaque fois que vous lancez une requête qui porte sur des données qui ne se trouvent pas dans le cache ou cela reste-t-il exceptionnel ? (dans ce cas peut-être d'autres traitements bloquaient la requête).

Et pour finir si vous n'avez toujours pas de réponse, un peu plus d'informations sur la configuration matérielle de votre serveur (disque, processeur ...), s'il est dédié et quelques valeurs du postgresql.conf (shared_buffers ...) et le système d'exploitation pourraient peut-être nous donner d'autres indices.

---

Julien.
https://rjuju.github.io/

7uc0

Membre

Re : Optimisation [suite] - lecture analyze

Vous pouvez utiliser l'option buffers du explain comme montré au dessus pour voir si les différences viennent de la lecture en cache ou sur disque.

Avez-vous la dernière version de postgresql 8.4 ? (8.4.9)

Sinon, le changement de temps d'exécution se reproduit-il à chaque fois que vous lancez une requête qui porte sur des données qui ne se trouvent pas dans le cache ou cela reste-t-il exceptionnel ? (dans ce cas peut-être d'autres traitements bloquaient la requête).

Et pour finir si vous n'avez toujours pas de réponse, un peu plus d'informations sur la configuration matérielle de votre serveur (disque, processeur ...), s'il est dédié et quelques valeurs du postgresql.conf (shared_buffers ...) et le système d'exploitation pourraient peut-être nous donner d'autres indices.

Bonjour rjuju,

j'ai bien peur que la version de la version psql m'empeche l'emploi de l'instruction explain (analyze,buffers) dans l'explain plan, d'ou mon retour précedent. Le changement de temps d'excution n'est pas exceptionnel, j'ai du désactivé l'appel au code executant la requete pour retrouver de la fluidité au niveau navigation.

pour être performante, je dois utiliser le cache, mais pour exploiter le cache il faut passer par une premiere execution hyper longue pour une simple requête, avec une table de 700 000 enregistrements...

               Table "public.snsrbroadcast"
     Column     |            Type             | Modifiers 
----------------+-----------------------------+-----------
 id             | bigint                      | not null
 analysisdate   | timestamp without time zone | 
 analysisresult | character varying(255)      | 
 analysisstatus | character varying(255)      | 
 content        | bytea                       | 
 contentsize    | integer                     | 
 format         | character varying(255)      | 
 fullheader     | boolean                     | 
 receptiondate  | timestamp without time zone | 
 requestid      | character varying(255)      | 
 gateway_id     | bigint                      | 
Indexes:
    "snsrbroadcast_pkey" PRIMARY KEY, btree (id)
    "id_broadcast_result" btree (analysisresult)
    "id_broadcast_status" btree (analysisstatus)
    "idx_broadcast_analysisdate" btree (analysisdate)
    "idx_broadcast_gateway" btree (gateway_id)
    "idx_broadcast_gw_reception_date" btree (gateway_id, receptiondate)
    "idx_broadcast_receptiondate" btree (receptiondate)
Foreign-key constraints:
    "fk_broadcast_owner" FOREIGN KEY (gateway_id) REFERENCES snsrdevice(id)
Referenced by:
    TABLE "snsrexport" CONSTRAINT "fk_export_broadcast" FOREIGN KEY (broadcast_id) REFERENCES snsrbroadcast(id)
    TABLE "snsrrecord" CONSTRAINT "fk_record_broadcast" FOREIGN KEY (broadcast_id) REFERENCES snsrbroadcast(id)

la base 8.4.7-0  tourne sous Ubuntu 9.10  virtualisé 2.6.27-xenU-4265-i386 dont les caractéristiques hardware sont les suivantes :

cpu:                                                            
                       Intel(R) Xeon(R) CPU           L5520  @ 2.27GHz, 2260 MHz
                       Intel(R) Xeon(R) CPU           L5520  @ 2.27GHz, 2260 MHz

memory : 1024 mb

filesystem : ext3

disk : SAS raid10

côté postgresql.conf, voici les infos de conf modifiées par rapport aux valeurs par défaut

log_destination = 'syslog'              # Valid values are combinations of
                                        # stderr, csvlog, syslog and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.
#redirect_stderr = off
silent_mode = on
log_min_duration_statement = 3000
shared_buffers = 128MB
effective_cache_size = 256MB

Merci en tout cas

rjuju

Administrateur

Re : Optimisation [suite] - lecture analyze

Effectivement, l'option buffers n'est accessible qu'à partir de la 9.0. Mais si ce problème est systématique, il est à peu près sur que cela vient du temps d'accès disque.

La configuration matérielle de votre machine ne semble pas poser de problème, mais si c'est une machine virtualisée il faut peut-être creuser de ce coté là.

Est-ce une virtualisation vmWare ?
Si oui, un utilisateur a justement fait un topo il y a quelques mois suite à un problème de lenteur disque sous vmware, peut-être cela pourrait vous aider à résoudre votre problème ?

http://forums.postgresql.fr/viewtopic.php?id=1643

---

Julien.
https://rjuju.github.io/

7uc0

Membre

Re : Optimisation [suite] - lecture analyze

l'environnement est Xenifié (Infra Gandi). Je plonge dans l'article. Merci ruju