Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
Pages : 1
#1 Re : Optimisation » Optimisation [suite] - lecture analyze » 22/11/2011 18:32:45
l'environnement est Xenifié (Infra Gandi). Je plonge dans l'article. Merci ruju
#2 Re : Optimisation » Optimisation [suite] - lecture analyze » 22/11/2011 16:15:47
rjuju a écrit :
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 raid10cô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 = 256MBMerci en tout cas
#3 Re : Optimisation » Optimisation [suite] - lecture analyze » 22/11/2011 13:26:06
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 ?
#4 Re : Optimisation » Optimisation [suite] - lecture analyze » 22/10/2011 23:54:50
avec un rapport 1/4000 ? D'une efficacité....
#5 Optimisation » Optimisation [suite] - lecture analyze » 22/10/2011 14:40:12
- 7uc0
- Réponses : 8
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 2resultat #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.
#6 Re : Optimisation » Optimisation [Suite] - Impact order by » 04/10/2011 12:00:11
Merci pour ce temps de réponse qui me permet de réduire le temps de réponse !
#7 Optimisation » Optimisation [Suite] - Impact order by » 04/10/2011 10:57:43
- 7uc0
- Réponses : 2
Bonjour,
je poursuis la série des best practices pour tenter de mieux comprende le fonctionnement du moteur sur des cas d'ordonnancement. Je m'explique:
la requête suivante porte sur une table d'à peu près (500.0000 enregistrements), le temps de réponse sans order by est "normal"
explain analyze select broadcast0_.id as id40_ from snsrbroadcast as broadcast0_ where broadcast0_.gateway_id=61172076 limit 20;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..76.16 rows=20 width=8) (actual time=0.050..0.234 rows=20 loops=1)
-> Index Scan using idx_broadcast_gateway on snsrbroadcast broadcast0_ (cost=0.00..11192.09 rows=2939 width=8) (actual time=0.049..0.209 rows=20 loops=1)
Index Cond: (gateway_id = 61172076)
Total runtime: 0.270 ms
(4 rows)En revanche, l'ajout d'une contrainte d'ordonnancement rend l'execution beaucoup plus longue.
explain analyze select broadcast0_.id as id40_ from snsrbroadcast as broadcast0_ where broadcast0_.gateway_id=61172076 order by broadcast0_.receptiondate desc limit 20;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..593.77 rows=20 width=16) (actual time=106120.505..106131.201 rows=20 loops=1)
-> Index Scan Backward using idx_broadcast_receptiondate on snsrbroadcast broadcast0_ (cost=0.00..87254.69 rows=2939 width=16) (actual time=106120.502..106131.169 rows=20 loops=1)
Filter: (gateway_id = 61172076)
Total runtime: 106131.253 msCe qui me surprend encore plus, c'est que la bascule en asc conduit à un temps plus long que la requête initiale, mais négligeable par rapport à l'execution en desc
explain analyze select broadcast0_.id as id40_ from snsrbroadcast as broadcast0_ where broadcast0_.gateway_id=61172076 order by broadcast0_.receptiondate asc limit 20;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..593.77 rows=20 width=16) (actual time=631.043..1650.628 rows=20 loops=1)
-> Index Scan using idx_broadcast_receptiondate on snsrbroadcast broadcast0_ (cost=0.00..87254.69 rows=2939 width=16) (actual time=631.041..1650.586 rows=20 loops=1)
Filter: (gateway_id = 61172076)
Total runtime: 1650.688 msQuelque chose m'échappe sur le comportement du moteur, d'ou ce post. Ah, j'oubliais les caractéristique de la table sollicitée :
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_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)-E.
#9 Re : Optimisation » Investigation [suite] - Temps d'execution » 23/09/2011 11:03:54
Effectivementn, du Bitmap Heap et Bitmap Index (sans index, j'aurais eu droit à du Full / Seq scan ?)
explain analyze select count(*) from snsrrecord where recorddate between '2011-09-22 00:00:00' and '2011-09-23 00:00:00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=486244.42..486244.43 rows=1 width=0) (actual time=111493.738..111493.739 rows=1 loops=1)
-> Bitmap Heap Scan on snsrrecord (cost=8645.59..485492.17 rows=300897 width=0) (actual time=44681.949..110085.654 rows=2190730 loops=1)
Recheck Cond: ((recorddate >= '2011-09-22 00:00:00'::timestamp without time zone) AND (recorddate <= '2011-09-23 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on idx_record_recorddate (cost=0.00..8570.36 rows=300897 width=0) (actual time=44650.388..44650.388 rows=2221609 loops=1)
Index Cond: ((recorddate >= '2011-09-22 00:00:00'::timestamp without time zone) AND (recorddate <= '2011-09-23 00:00:00'::timestamp without time zone))
Total runtime: 111495.077 msJe rebondis sur un de tes posts précedents pour creuser :
"On pourrait déjà commencer par passer les stats sur les tables. Elles ne sont pas à jour du tout"
Que signifie le terme stats ? l'execution d'anlyze ? vacuum ? les deux ? pas du tout
#10 Re : Optimisation » Investigation [suite] - Temps d'execution » 23/09/2011 10:42:07
Le statut des traitements automatisés
select
current_database(),
schemaname || '.' || relname as table,
to_char(last_vacuum,'DD-MM-YYYY HH24:mi') as lastvacuum,
to_char(last_autovacuum,'DD-MM-YYYY HH24:mi') as lastautovacuum,
to_char(last_analyze,'DD-MM-YYYY HH24:mi') as lastanalyze,
to_char(last_autoanalyze,'DD-MM-YYYY HH24:mi') as lastautoanalyze
from
pg_stat_user_tables
order by
2 current_database | table | lastvacuum | lastautovacuum | lastanalyze | lastautoanalyze
------------------+------------------------------+------------------+------------------+------------------+------------------
sensordb1 | public.databasechangelog | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.databasechangeloglock | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.patchlevel | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrbinding | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrbroadcast | 06-09-2011 08:00 | | 22-09-2011 18:09 | 20-09-2011 19:08
sensordb1 | public.snsrcfgvar | 06-09-2011 08:00 | | 06-09-2011 08:00 | 30-08-2011 15:01
sensordb1 | public.snsrcmd | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrcmd_args | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrcomdqueue | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrconsolidation | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrcustomer | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrdevice | 06-09-2011 08:00 | 23-09-2011 01:00 | 22-09-2011 18:09 | 23-09-2011 00:46
sensordb1 | public.snsrdevicecfg | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrdevicetypeassoc | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrexport | 06-09-2011 08:00 | | 06-09-2011 08:00 | 22-09-2011 01:52
sensordb1 | public.snsrfactorytest | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrfactorytest_tests | 06-09-2011 08:00 | | 06-09-2011 08:00 | 19-09-2011 17:34
sensordb1 | public.snsrmember | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrmemberprofile | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrmetric | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrmetricvar | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrmodeldef | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrnode | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsroperation | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrorganisation | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrpackage | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrpermassign | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrpicture | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrprobalert | 06-09-2011 08:00 | | 06-09-2011 08:00 | 15-09-2011 09:48
sensordb1 | public.snsrprobcond | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrprobe | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrprobecfg | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrproduct | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrprofile | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrprop | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrrecord | 06-09-2011 08:07 | 20-09-2011 23:11 | 22-09-2011 17:52 | 18-09-2011 01:52
sensordb1 | public.snsrrelease | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrsite | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrsiteway | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrstatusvar | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrsystem | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrtargetmetric | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrtypedef | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrvariable | 06-09-2011 08:00 | | 22-09-2011 18:09 |
sensordb1 | public.snsrvendor | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrversion | 06-09-2011 08:00 | | 06-09-2011 08:00 |#11 Re : Optimisation » Investigation [suite] - Temps d'execution » 23/09/2011 10:36:59
@Marc : FS Au taquet durant l'execution de la requete avant analyze, CPU au taquet durant analyze
L'analyse réduit considérablement le temps de requête, N'y a-t-il pas un démon similaire à l'autovacuum, chargé d'orchestrer les analyses sur une base ?
Merci en tout cas
#12 Optimisation » Investigation [suite] - Temps d'execution » 22/09/2011 17:34:02
- 7uc0
- Réponses : 12
[RE] bonjour,
Dans la continuité de mes investigations sur des comportements lents et / ou bloquants, j'observe des temps de réponses hyper longs (plusieurs minutes) sur une requête suivante contre une table assez volumineuse (entre 50 et 60 M d'enregistrements au total) :
explain analyze select count(*) from snsrrecord where recorddate between '2011-09-21 00:00:00' and '2011-09-23 00:00:00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=17.10..17.11 rows=1 width=0) (actual time=268580.842..268580.843 rows=1 loops=1)
-> Index Scan using idx_record_recorddate on snsrrecord (cost=0.00..17.10 rows=1 width=0) (actual time=45.583..267006.647 rows=2389648 loops=1)
Index Cond: ((recorddate >= '2011-09-21 00:00:00'::timestamp without time zone) AND (recorddate <= '2011-09-23 00:00:00'::timestamp without time zone))
Total runtime: 268580.889 ms
(4 rows)
count
---------
2387745explain analyze select count(*) from snsrrecord where recorddate between '2011-09-22 00:00:00' and '2011-09-23 00:00:00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=17.10..17.11 rows=1 width=0) (actual time=15314.732..15314.733 rows=1 loops=1)
-> Index Scan using idx_record_recorddate on snsrrecord (cost=0.00..17.10 rows=1 width=0) (actual time=23.264..15218.793 rows=153268 loops=1)
Index Cond: ((recorddate >= '2011-09-22 00:00:00'::timestamp without time zone) AND (recorddate <= '2011-09-23 00:00:00'::timestamp without time zone))
Total runtime: 15314.781 ms
count
--------
151326La machine n'est pas "au taquet"
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1541 postgres 20 0 50640 37m 35m D 1 3.7 0:28.32 postgresJ'aimerais trouver le moyen de réduire significativement ce temps, mais j'avoue ne pas savoir quel levier activer (réduire le nombre d'enregistrements ?)
E.
PG 8.4.7, ubuntu 9.10
#13 Re : Optimisation » Investigation :: Bonnes pratiques » 22/09/2011 15:58:17
Noté, Merci encore.
E.
#14 Re : Optimisation » Investigation :: Bonnes pratiques » 22/09/2011 15:09:51
OK si je résume l'approche sur les comportements bloquants :
1 - A plus haut niveau, verifier les sessions en cours via ps
2 - Regarder la table pg_locks à la recherche de verrous non attribués
3 - Identifier la session ayant acquis le verrou et chercher d'éventuelles raisons de blocage au sein de la même transaction en se calant sur la colonne mode
4 - Identifier les éventuelles relations lockées durant la même session pour en déduire une ou plusieurs instructions bloquantes durant la transaction fonctionnelle
5 - faire évoluer le code (deux tx differentes, update après select) pour eliminer ce cas de blocage.
6 - Une fois les comportements bloquants éliminés -la transaction fonctionnelle peut-être longue, mais elle se termine (cf logs postgres avec log_min_duration)-, on peut se concentrer sur les optimisations des requêtes lentes à coup de explain et de refacto fonctionelle / positionnement d'index / évolution de configuration
Merci de m'apprendre à pêcher.
E.
#15 Re : Optimisation » Investigation :: Bonnes pratiques » 22/09/2011 14:45:35
@Marc, tu conclus sur l'éxecution d'une autre instruction qu'un select à cause de l'acquisition d'un verrou RowExclusiveLock durant la même session ?
je tente de determiner la relation en interrogant le champ pg_class, une fois connecté sur la base....
Merci en tout cas
#16 Re : Optimisation » Investigation :: Bonnes pratiques » 22/09/2011 11:56:03
Merci Marc, je tombe les pg_lock alors
J'ai un select et 3 inserts waiting
postgres 17687 1 0 Sep21 ? 00:00:02 /usr/lib/postgresql/8.4/bin/postgres -D /srv/sdhost-app/pgdata/main -c config_file=/etc/postgresql/8.4/main/postgresql.conf
postgres 17689 17687 0 Sep21 ? 00:00:17 postgres: writer process
postgres 17690 17687 0 Sep21 ? 00:00:05 postgres: wal writer process
postgres 17691 17687 0 Sep21 ? 00:00:03 postgres: autovacuum launcher process
postgres 17692 17687 0 Sep21 ? 00:00:06 postgres: stats collector process
postgres 17706 17687 0 Sep21 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(49816) idle
postgres 17707 17687 0 Sep21 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(49817) idle
postgres 17710 17687 0 Sep21 ? 00:00:15 postgres: sensor sensordb1 127.0.0.1(49820) INSERT waiting
postgres 17711 17687 0 Sep21 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(49821) idle
postgres 17713 17687 0 Sep21 ? 00:00:55 postgres: sensor sensordb1 127.0.0.1(49823) idle
postgres 17715 17687 0 Sep21 ? 00:00:32 postgres: sensor sensordb1 127.0.0.1(49825) idle
postgres 17719 17687 0 Sep21 ? 00:03:53 postgres: sensor sensordb1 127.0.0.1(49829) idle
postgres 25278 17687 0 09:03 ? 00:00:11 postgres: sensor sensordb1 127.0.0.1(60064) idle
postgres 25279 17687 0 09:03 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(60065) idle
postgres 25280 17687 0 09:03 ? 00:00:02 postgres: sensor sensordb1 127.0.0.1(60066) idle
postgres 25281 17687 0 09:03 ? 00:00:04 postgres: sensor sensordb1 127.0.0.1(60067) SELECT
postgres 25634 25579 0 09:39 pts/2 00:00:00 /usr/lib/postgresql/8.4/bin/psql
postgres 25636 17687 0 09:39 ? 00:00:01 postgres: sensor sensordb1 127.0.0.1(54385) INSERT waiting
postgres 25637 17687 0 09:39 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(54386) idle
postgres 25638 17687 0 09:39 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(54387) idle
postgres 25639 17687 0 09:39 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(54388) INSERT waiting
postgres 25651 17687 0 09:41 ? 00:00:00 postgres: postgres sensordb1 [local] idle locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------
relation | 26166 | 27586 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26543 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26553 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27551 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26379 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26555 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26361 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26551 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26358 | | | | | | | | 1/5474 | 25639 | RowExclusiveLock | t
relation | 26166 | 1528075 | | | | | | | | 1/5474 | 25639 | RowExclusiveLock | t
virtualxid | | | | | 5/136 | | | | | 5/136 | 25716 | ExclusiveLock | t
relation | 26166 | 27536 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26507 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26259 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26259 | | | | | | | | 6/144 | 17710 | RowShareLock | t
relation | 26166 | 26491 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27563 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 1528078 | | | | | | | | 1/5474 | 25639 | RowExclusiveLock | t
relation | 26166 | 26543 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26549 | | | | | | | | 1/5474 | 25639 | RowExclusiveLock | t
relation | 26166 | 26553 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27551 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26361 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26557 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26557 | | | | | | | | 14/4861 | 25281 | RowExclusiveLock | t
relation | 26166 | 26507 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26259 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26259 | | | | | | | | 18/75 | 25636 | RowShareLock | t
relation | 26166 | 26491 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27563 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26367 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26373 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26385 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26385 | | | | | | | | 6/144 | 17710 | RowShareLock | t
relation | 26166 | 27617 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27550 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27514 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27547 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27557 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 1528076 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27548 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 1528077 | | | | | | | | 6/144 | 17710 | RowExclusiveLock | t
relation | 26166 | 27558 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27559 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26521 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27592 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 16385 | 1259 | | | | | | | | 8/2579 | 25717 | AccessShareLock | t
relation | 26166 | 26385 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26385 | | | | | | | | 18/75 | 25636 | RowShareLock | t
relation | 26166 | 27538 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27550 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27615 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
virtualxid | | | | | 18/75 | | | | | 18/75 | 25636 | ExclusiveLock | t
relation | 26166 | 27616 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26280 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26513 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
transactionid | | | | | | 2275997 | | | | 1/5474 | 25639 | ExclusiveLock | t
relation | 26166 | 26346 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26298 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 1528077 | | | | | | | | 18/75 | 25636 | RowExclusiveLock | t
relation | 26166 | 27558 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27559 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
transactionid | | | | | | 2276003 | | | | 18/75 | 25636 | ExclusiveLock | t
relation | 26166 | 1528078 | | | | | | | | 18/75 | 25636 | RowExclusiveLock | t
relation | 26166 | 27586 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26543 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26549 | | | | | | | | 6/144 | 17710 | RowExclusiveLock | t
relation | 26166 | 27551 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26555 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26551 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
transactionid | | | | | | 2276025 | | | | 6/144 | 17710 | ExclusiveLock | t
relation | 26166 | 26391 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26358 | | | | | | | | 18/75 | 25636 | RowExclusiveLock | t
relation | 26166 | 27546 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 1528075 | | | | | | | | 18/75 | 25636 | RowExclusiveLock | t
relation | 26166 | 27543 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26507 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26259 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26259 | | | | | | | | 1/5474 | 25639 | RowShareLock | t
relation | 26166 | 26491 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27563 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 1528078 | | | | | | | | 6/144 | 17710 | RowExclusiveLock | t
relation | 26166 | 27586 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 1528073 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26559 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26549 | | | | | | | | 18/75 | 25636 | RowExclusiveLock | t
relation | 26166 | 26553 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26555 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26361 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26551 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
virtualxid | | | | | 8/2579 | | | | | 8/2579 | 25717 | ExclusiveLock | t
relation | 26166 | 26358 | | | | | | | | 6/144 | 17710 | RowExclusiveLock | t
relation | 26166 | 1528075 | | | | | | | | 6/144 | 17710 | RowExclusiveLock | t
relation | 26166 | 26346 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
transactionid | | | | | | 2275983 | | | | 14/4861 | 25281 | ExclusiveLock | t
relation | 26166 | 27539 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27558 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27559 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
virtualxid | | | | | 6/144 | | | | | 6/144 | 17710 | ExclusiveLock | t
relation | 26166 | 27538 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27550 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27615 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27535 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26505 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27557 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26346 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 1528077 | | | | | | | | 1/5474 | 25639 | RowExclusiveLock | t
relation | 26166 | 26265 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27537 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27545 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26385 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26385 | | | | | | | | 1/5474 | 25639 | RowShareLock | t
relation | 26166 | 27538 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27615 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26295 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27557 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 1528074 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27549 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26373 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26385 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26385 | | | | | | | | 14/4861 | 25281 | RowExclusiveLock | t
relation | 26166 | 27617 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26295 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27547 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 1528074 | | | | | | | | 1/5474 | 25639 | RowExclusiveLock | t
relation | 26166 | 1528076 | | | | | | | | 6/144 | 17710 | RowExclusiveLock | t
relation | 26166 | 27616 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27548 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27549 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 1528077 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26265 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27537 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26521 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27545 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 11564 | 10969 | | | | | | | | 5/136 | 25716 | AccessShareLock | t
relation | 26166 | 26229 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26373 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27617 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27550 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26497 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27535 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 28020 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26505 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27547 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 1528076 | | | | | | | | 18/75 | 25636 | RowExclusiveLock | t
relation | 26166 | 27616 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27548 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
transactionid | | | | | | 2275983 | | | | 1/5474 | 25639 | ShareLock | f
relation | 26166 | 27539 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27558 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27559 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26521 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26229 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 1528073 | | | | | | | | 1/5474 | 25639 | RowExclusiveLock | t
relation | 26166 | 26559 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26553 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26379 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26361 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27587 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
tuple | 26166 | 26385 | 8 | 67 | | | | | | 6/144 | 17710 | ShareLock | t
relation | 26166 | 27543 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26557 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27536 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26507 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26259 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26491 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27563 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26241 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26241 | | | | | | | | 1/5474 | 25639 | RowShareLock | t
relation | 26166 | 27552 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26367 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26543 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27551 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27551 | | | | | | | | 14/4861 | 25281 | RowExclusiveLock | t
relation | 26166 | 26379 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26391 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27546 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
tuple | 26166 | 26385 | 8 | 67 | | | | | | 18/75 | 25636 | ShareLock | t
relation | 26166 | 26557 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27536 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26367 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26346 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
transactionid | | | | | | 2275983 | | | | 6/144 | 17710 | ShareLock | f
relation | 26166 | 27539 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26265 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27537 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27545 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27593 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27538 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26497 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27615 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26295 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27535 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26505 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 1528074 | | | | | | | | 18/75 | 25636 | RowExclusiveLock | t
relation | 26166 | 27616 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27549 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
transactionid | | | | | | 2275983 | | | | 18/75 | 25636 | ShareLock | f
relation | 26166 | 27539 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26265 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27537 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26521 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27545 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26229 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26373 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27617 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26497 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26295 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27535 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26505 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27547 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27557 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 1528074 | | | | | | | | 6/144 | 17710 | RowExclusiveLock | t
relation | 26166 | 1528076 | | | | | | | | 1/5474 | 25639 | RowExclusiveLock | t
relation | 26166 | 27548 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 27549 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
virtualxid | | | | | 14/4861 | | | | | 14/4861 | 25281 | ExclusiveLock | t
relation | 26166 | 27587 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 27543 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26557 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26241 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26241 | | | | | | | | 18/75 | 25636 | RowShareLock | t
relation | 26166 | 27552 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26367 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 1528073 | | | | | | | | 18/75 | 25636 | RowExclusiveLock | t
relation | 26166 | 26559 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26549 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26391 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27546 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27587 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
tuple | 26166 | 26385 | 8 | 67 | | | | | | 1/5474 | 25639 | ShareLock | t
relation | 26166 | 27543 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 27536 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 1528078 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26241 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26241 | | | | | | | | 6/144 | 17710 | RowShareLock | t
relation | 26166 | 27552 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
virtualxid | | | | | 1/5474 | | | | | 1/5474 | 25639 | ExclusiveLock | t
relation | 26166 | 1528073 | | | | | | | | 6/144 | 17710 | RowExclusiveLock | t
relation | 26166 | 26559 | | | | | | | | 6/144 | 17710 | AccessShareLock | t
relation | 26166 | 26379 | | | | | | | | 1/5474 | 25639 | AccessShareLock | t
relation | 26166 | 26555 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26551 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26523 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 26391 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 26358 | | | | | | | | 14/4861 | 25281 | AccessShareLock | t
relation | 26166 | 27546 | | | | | | | | 18/75 | 25636 | AccessShareLock | t
relation | 26166 | 1528075 | | | | | | | | 14/4861 | 25281 | AccessShareLock | tUn explain sur le select me donne ceci
sensordb1=# explain verbose select record0_.id as id13_, record0_.broadcast_id as broadcast4_13_, record0_.owner_id as owner5_13_, record0_.recorddate as recorddate13_, record0_.recordvalue as recordva3_13_, record0_.recordvar_id as recordvar6_13_, record0_.target_id as target7_13_ from snsrRecord record0_ where record0_.recordvar_id='52' and record0_.owner_id='465714329' and (record0_.recorddate between '2011-09-21 00:00:00' and '2011-09-22 00:00:00') order by record0_.recorddate asc
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_record_recorddate on snsrrecord record0_ (cost=0.00..17.08 rows=1 width=53)
Output: id, broadcast_id, owner_id, recorddate, recordvalue, recordvar_id, target_id
Index Cond: ((recorddate >= '2011-09-21 00:00:00'::timestamp without time zone) AND (recorddate <= '2011-09-22 00:00:00'::timestamp without time zone))
Filter: ((recordvar_id = 52::bigint) AND (owner_id = 465714329::bigint))L'explain analyze est en cours....
#17 Re : Optimisation » Investigation :: Bonnes pratiques » 22/09/2011 11:17:55
Bonjour Guillaume,
pour des questions de lisibilité, les requêtes sont partielles
select procpid, waiting, xact_start, case when length(current_query) > 40 then substring(current_query for 20) || '...' || substring(current_query from (length(current_query) - 19) for 20) else current_query end as query,query_start, backend_start from pg_stat_activity;
---------+---------+-------------------------------+---------------------------------------------+-------------------------------+-------------------------------
24994 | f | | <IDLE> | 2011-09-22 08:50:55.843264+00 | 2011-09-22 08:40:39.864567+00
17706 | f | | <IDLE> | 2011-09-21 16:10:59.431737+00 | 2011-09-21 16:10:59.219198+00
17707 | f | | <IDLE> | 2011-09-21 16:10:59.431568+00 | 2011-09-21 16:10:59.222115+00
17708 | f | | <IDLE> | 2011-09-21 16:10:59.437048+00 | 2011-09-21 16:10:59.433722+00
17709 | f | | <IDLE> | 2011-09-21 16:10:59.439149+00 | 2011-09-21 16:10:59.436508+00
17710 | f | | <IDLE> | 2011-09-21 16:10:59.442018+00 | 2011-09-21 16:10:59.4399+00
17711 | f | | <IDLE> | 2011-09-22 01:54:12.438279+00 | 2011-09-21 16:10:59.442741+00
17713 | f | | <IDLE> | 2011-09-22 08:40:30.040956+00 | 2011-09-21 16:10:59.447902+00
17715 | f | | <IDLE> | 2011-09-22 08:40:27.187039+00 | 2011-09-21 16:10:59.451399+00
17719 | f | | <IDLE> | 2011-09-22 01:54:14.501713+00 | 2011-09-21 16:10:59.460876+00
24989 | f | | <IDLE> | 2011-09-22 08:40:39.851391+00 | 2011-09-22 08:40:39.7986+00
24990 | f | | <IDLE> | 2011-09-22 08:40:39.851561+00 | 2011-09-22 08:40:39.801836+00
24991 | f | 2011-09-22 08:50:31.70157+00 | select record0_.id a...ord0_.recorddate asc | 2011-09-22 08:50:31.814156+00 | 2011-09-22 08:40:39.804197+00
24992 | t | 2011-09-22 08:51:34.493436+00 | insert into snsrReco... $3, $4, $5, $6, $7) | 2011-09-22 08:51:36.27359+00 | 2011-09-22 08:40:39.852488+00
24993 | f | | <IDLE> | 2011-09-22 08:40:40.254967+00 | 2011-09-22 08:40:39.854626+00les deux requêtes en détails :
select record0_.id as id13_, record0_.broadcast_id as broadcast4_13_, record0_.owner_id as owner5_13_, record0_.recorddate as recorddate13_, record0_.recordvalue as recordva3_13_, record0_.recordvar_id as recordvar6_13_, record0_.target_id as target7_13_ from snsrRecord record0_ where record0_.recordvar_id=$1 and record0_.owner_id=$2 and (record0_.recorddate between $3 and $4) order by record0_.recorddate ascEt
insert into snsrRecord (broadcast_id, owner_id, recorddate, recordvalue, recordvar_id, target_id, id) values ($1, $2, $3, $4, $5, $6, $7)Structurellement, la table snsrRecord est liée à plusieurs tables :
Column | Type | Modifiers
--------------+-----------------------------+-----------
id | bigint | not null
recorddate | timestamp without time zone |
recordvalue | character varying(255) |
broadcast_id | bigint |
owner_id | bigint |
recordvar_id | bigint |
target_id | bigint |
Indexes:
"snsrrecord_pkey" PRIMARY KEY, btree (id)
"idx_record_broadcast" btree (broadcast_id)
"idx_record_bug117" btree (owner_id, recordvar_id, recorddate)
"idx_record_owner" btree (owner_id)
"idx_record_recorddate" btree (recorddate)
"idx_record_target" btree (target_id)
"idx_record_var" btree (recordvar_id)
Foreign-key constraints:
"fk_record_broadcast" FOREIGN KEY (broadcast_id) REFERENCES snsrbroadcast(id)
"fk_record_owner" FOREIGN KEY (owner_id) REFERENCES snsrdevice(id)
"fk_record_target" FOREIGN KEY (target_id) REFERENCES snsrdevice(id)
"fk_record_var" FOREIGN KEY (recordvar_id) REFERENCES snsrvariable(id)Et côté pg_locks, voici ce que me sort la requête suivante (select (*) me renvoie plus de 300 enregistrements):
select
locktype, database, relation, page, tuple, transactionid, virtualtransaction, mode, granted,
relname, relkind,
datname, usename,
case
when length(current_query) > 40
then substring(current_query for 20) || '...'
|| substring(current_query from (length(current_query) - 19) for 20)
else current_query
end as query,
waiting, query_start, client_addr, client_port
from pg_locks
left outer join pg_class on oid = relation
left outer join pg_stat_activity on pid = procpid
order by pid;Résultat (ne porte que sur le select, j'ai du procéder à un nouveau "déblocage"):
locktype | database | relation | page | tuple | transactionid | virtualtransaction | mode | granted | relname | relkind | datname | usename | query | waiting | query_start | client_addr | client_port
---------------+----------+----------+------+-------+---------------+--------------------+------------------+---------+---------+---------+-----------+---------+---------------------------------------------+---------+-------------------------------+-------------+-------------
relation | 26166 | 26346 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27593 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27538 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27615 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27557 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26549 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 1528078 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26555 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26551 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26523 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26358 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 1528075 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26385 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26385 | | | | 1/5452 | RowExclusiveLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 1528077 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
transactionid | | | | | 2275951 | 1/5452 | ExclusiveLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27550 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 28020 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27558 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27559 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26553 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26361 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26507 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26259 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26491 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
virtualxid | | | | | | 1/5452 | ExclusiveLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27563 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26543 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27551 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27551 | | | | 1/5452 | RowExclusiveLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26391 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27546 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27543 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 1528073 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26559 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27539 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27535 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26505 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26265 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27537 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27545 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26295 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 1528074 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27549 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26379 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27536 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26557 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26557 | | | | 1/5452 | RowExclusiveLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26367 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26373 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27617 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27514 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27547 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 1528076 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27548 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26521 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27592 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 27616 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26280 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26513 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068
relation | 26166 | 26298 | | | | 1/5452 | AccessShareLock | t | | | sensordb1 | sensor | select record0_.id a...ord0_.recorddate asc | f | 2011-09-22 09:05:13.962259+00 | 127.0.0.1 | 60068Mon interprétation
En l'état, la lecture des données de la table snsrRecord prend un temps infini et est à l'origine du blocage progressif des autres requêtes (insertion, maj). J'avais dans l'idée de partir sur un explain de la requête en question pour comprendre l'origine de la lenteur, qu'est ce que je dois comprendre de la sortie précedente concernant les nombreux locks rien que sur la lecture ?
Merci encore
#18 Re : Optimisation » Investigation :: Bonnes pratiques » 21/09/2011 17:40:45
Merci pour ces retours,
J'ai en ce moment même la sortie ps qui me rend ceci
postgres 11328 1 0 08:48 ? 00:00:01 /usr/lib/postgresql/8.4/bin/postgres -D /srv/sdhost-app/pgdata/main -c config_file=/etc/postgresql/8.4/main/postgresql.conf
postgres 11331 11328 0 08:48 ? 00:00:06 postgres: writer process
postgres 11332 11328 0 08:48 ? 00:00:01 postgres: wal writer process
postgres 11333 11328 0 08:48 ? 00:00:01 postgres: autovacuum launcher process
postgres 11334 11328 0 08:48 ? 00:00:03 postgres: stats collector process
postgres 11710 11328 0 09:01 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(35923) idle
postgres 11711 11328 0 09:01 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(35924) idle
postgres 11914 11328 0 09:26 ? 00:00:52 postgres: sensor sensordb1 127.0.0.1(51881) idle
postgres 11915 11328 0 09:26 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(51882) idle
postgres 11917 11328 0 09:26 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(51884) idle
postgres 11918 11328 0 09:26 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(51885) idle
postgres 11920 11328 0 09:26 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(51887) idle
postgres 13297 11328 0 12:42 ? 00:00:06 postgres: sensor sensordb1 127.0.0.1(39026) idle
postgres 13298 11328 0 12:42 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(39027) idle
postgres 13299 11328 0 12:42 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(39028) idle
postgres 13302 11328 0 12:42 ? 00:00:07 postgres: sensor sensordb1 127.0.0.1(39031) idle
postgres 13304 11328 0 12:42 ? 00:00:48 postgres: sensor sensordb1 127.0.0.1(39033) idle
postgres 16205 11328 0 13:22 ? 00:00:00 postgres: sensor sensordb1 127.0.0.1(39690) idle
postgres 16206 11328 0 13:22 ? 00:00:31 postgres: sensor sensordb1 127.0.0.1(39691) INSERT waiting
postgres 16207 11328 0 13:22 ? 00:00:26 postgres: sensor sensordb1 127.0.0.1(39692) SELECTJ'en conclus après plusieurs secondes d'attente qu'un cas de blocage est en cours
@Arthurr
-pas d'erreur de deadlock dans les logs
-what is %wa ? un top me sort ceci
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16207 postgres 20 0 51864 39m 35m D 4 3.8 0:30.26 postgresVoici ce qui ressort de la consultation de pg_stat_activity
select procpid, waiting, xact_start, query_start, backend_start from pg_stat_activity;
procpid | waiting | xact_start | query_start | backend_start
---------+---------+-------------------------------+-------------------------------+-------------------------------
13304 | f | | 2011-09-21 15:17:22.03169+00 | 2011-09-21 12:42:37.973344+00
16963 | f | 2011-09-21 15:17:57.914497+00 | 2011-09-21 15:17:57.914497+00 | 2011-09-21 15:14:11.88781+00
13297 | f | | 2011-09-21 15:12:19.089292+00 | 2011-09-21 12:42:37.892173+00
13298 | f | | 2011-09-21 13:22:58.327495+00 | 2011-09-21 12:42:37.895526+00
13299 | f | | 2011-09-21 13:22:58.326579+00 | 2011-09-21 12:42:37.898187+00
16206 | t | 2011-09-21 15:09:43.672439+00 | 2011-09-21 15:09:45.756467+00 | 2011-09-21 13:22:57.775099+00
16207 | f | 2011-09-21 14:57:49.827326+00 | 2011-09-21 14:57:50.002233+00 | 2011-09-21 13:22:57.775471+00
16297 | f | | 2011-09-21 14:19:38.789846+00 | 2011-09-21 13:38:07.210547+00
13302 | f | | 2011-09-21 13:22:57.883614+00 | 2011-09-21 12:42:37.952311+00
16205 | f | | 2011-09-21 13:22:57.930659+00 | 2011-09-21 13:22:57.773747+00
11711 | f | | 2011-09-21 09:26:04.449413+00 | 2011-09-21 09:01:04.774461+00
11710 | f | | 2011-09-21 09:26:04.439868+00 | 2011-09-21 09:01:04.774133+00
11914 | f | | 2011-09-21 12:42:13.09521+00 | 2011-09-21 09:26:03.21994+00
11915 | f | | 2011-09-21 09:26:04.43964+00 | 2011-09-21 09:26:03.221895+00
11917 | f | | 2011-09-21 09:26:04.444564+00 | 2011-09-21 09:26:03.356444+00
11918 | f | | 2011-09-21 09:26:03.366232+00 | 2011-09-21 09:26:03.363535+00
11920 | f | | 2011-09-21 09:26:03.369445+00 | 2011-09-21 09:26:03.366658+00
procpid 16963 : select procpid, waiting, xact_start, query_start...
procpid 16206 : insert into snsrRecord
procpid 16207 : select record0_.id as ..... from snsrRecord where record0_ where ... (plusieurs critères, dont deux sur des tables liées et une temporelle - recorddate between X an Y)Enfin le statut sur l'état des operations de maintenance interne
current_database | table | lastvacuum | lastautovacuum | lastanalyze | lastautoanalyze
------------------+------------------------------+------------------+------------------+------------------+------------------
sensordb1 | public.databasechangelog | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.databasechangeloglock | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.patchlevel | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrbinding | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrbroadcast | 06-09-2011 08:00 | | 06-09-2011 08:00 | 20-09-2011 19:08
sensordb1 | public.snsrcfgvar | 06-09-2011 08:00 | | 06-09-2011 08:00 | 30-08-2011 15:01
sensordb1 | public.snsrcmd | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrcmd_args | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrcomdqueue | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrconsolidation | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrcustomer | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrdevice | 06-09-2011 08:00 | 21-09-2011 00:08 | 06-09-2011 08:00 | 21-09-2011 05:37
sensordb1 | public.snsrdevicecfg | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrdevicetypeassoc | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrexport | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrfactorytest | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrfactorytest_tests | 06-09-2011 08:00 | | 06-09-2011 08:00 | 19-09-2011 17:34
sensordb1 | public.snsrmember | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrmemberprofile | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrmetric | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrmetricvar | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrmodeldef | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrnode | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsroperation | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrorganisation | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrpackage | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrpermassign | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrpicture | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrprobalert | 06-09-2011 08:00 | | 06-09-2011 08:00 | 15-09-2011 09:48
sensordb1 | public.snsrprobcond | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrprobe | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrprobecfg | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrproduct | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrprofile | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrprop | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrrecord | 06-09-2011 08:07 | 20-09-2011 23:11 | 06-09-2011 08:10 | 18-09-2011 01:52
sensordb1 | public.snsrrelease | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrsite | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrsiteway | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrstatusvar | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrsystem | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrtargetmetric | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrtypedef | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrvariable | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrvendor | 06-09-2011 08:00 | | 06-09-2011 08:00 |
sensordb1 | public.snsrversion | 06-09-2011 08:00 | | 06-09-2011 08:00 |Les récentes occurence de vaccum / analyse semblent en phase avec les tables les plus régulièrement sollicitées au niveau applicatif
Je modifie la conf pour tracer les requêtes de plus de X ms
E.
#19 Optimisation » Investigation :: Bonnes pratiques » 21/09/2011 16:05:55
- 7uc0
- Réponses : 14
Bonjour la communauté,
Pour mon premier post sur le forum que je suis de plus en plus près, je voudrais en savoir plus sur les bonnes pratiques à adopter sur une base postgresql en souffrance.
J'ai une bdd en version 8.4.7 sous ubuntu 9.10 dont le dump répresente 1.5 Go (de taille normale imho),
Après l'installation de base, j'ai isolé l'ensemble des data sur une partition disque différente de la partition système (c'est d'ailleur la seule modif apportée au fichier postgresql.conf)
Le contexte
-l'applicatif tourne sur la même machine (gandi 2 coeurs 1 Go de ram, 512 Mo pour l'appli JEE) et exploite c3p0 pour pooler les connections
-la base est sollicitée quotidiennement sur une période relativement courte (23h00 - 3h00) notamment pour de l'injection de données dans une table (en moyenne 5 - 9M d'enregistrements quotidien sur cette table, avec une rotation de 2 semaines, soit à peu près 110 - 120 M d'enregistrements)
La maintenance opérationnelle
Les opérations suivantes sont réalisées quotidiennement ou hebdomadairement
- dump complet (via backup-manager)
- observation des logs (rien à observer à part des evènements fataux, la configuration étant vierge)
- sur la base des stats applicatives (temps min / max /moyen de traitement d'une requête http) => isolation des n requêtes http les plus lentes => recensement des requêtes SQL => explain sur ces requêtes => correctif applicatif (reformulation de la requête, découpage fonctionnel différent) ou ajout d'index.
Les symptômes
La stack appli / BDD tourne "normalement" (temps de réponse utilisateur acceptable) depuis 18 mois maintenant, j'observe cependant depuis quelques jours des timeouts http sur des accès en consultation de données. En regardant de plus près, je constate que l'activité appli est nulle, et l'activité bdd un plus importante 3.8 - 4% cpu max, sans plus.
l'affichage des processus postgres (ps -ef ...) me retourne plusieurs (mais pas tous) processus en INSERT waiting et un ou deux en SELECT
l'affichage détaillé sous psql (http://www.lunatech-research.com/archiv … greSQL-tid) m'informe des transactions en cours, mais j'avoue que je ne sais pas trop quoi en conclure du point de vue bdd
Je constate enfin que l'utilisation de pg_cancel_backend (http://www.postgresonline.com/journal/a … -Ends.html) me permet de débloquer la situation, temporairement.
Merci de m'avoir lu jusque là !
Alors je me permets de poster pour :
1 - avoir vos commentaires conseils sur la methodo suivie pour déployer et maintenir la base en l'état.
2 - connaître l'approche la plus efficace pour addresser ce type de comportements observés, mais du point de vue DB (rendre les logs plus verbeux, executer des traitements de maintenance, embaucher un vrai dba...)
Je tiens à préciser que je ne suis pas dbA, ni développeur, ni ingé sys, mais un peu de tout ca au sein de ma petite pme.
Merci pour vos commentaires et conseils
E.
Pages : 1