From: Jan Wieck <janwieck@yahoo.com>
To: Rajesh Kumar Mallah. <mallah@trade-india.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: pg_stat_get_backend_pid seems to be listing non existant
Date: Fri, 10 May 2002 06:48:09 -0400 (EDT)
Message-ID: <200205101048.g4AAmAK03594@saturn.janwieck.net> (raw)
In-Reply-To: <200205101556.40047.mallah@trade-india.com>
Rajesh Kumar Mallah. wrote:
> Hi Folks,
> please help ,
>
> therse seems to be too much lag between the access collector
> and system status. even the pids of backend does not seems to be matching.
The delay is on average 250 milliseconds for a busy database
(1/4 second). The controlling definition is in
src/include/pgstat.h:
#define PGSTAT_STAT_INTERVAL 500
This means, from the moment ANY statistic packet has arrived
in the collector, it waits 500 milliseconds before writing
out all information. Thus, the above 250 milliseconds
average is only true assuming a constant flow of packets.
And, before you discover this one: The backends send their
statistic collection information via UDP packets. In the case
of heavy database load, some of these packets can get lost so
that the statistics will not be 100% accurate. This is a
wanted feature and implemented on purpose! It is because
counting the number of scans isn't considered as much
important as responding to the client as fast as possible
during the rushhour.
Jan
>
> tradein_clients=# SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
> pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT
> pg_stat_get_backend_idset() AS backendid) s;
>
> procpid | current_query
> ---------+-------------------------------
> 27134 | <IDLE> in transaction
> 26958 | <IDLE> in transaction
> 26953 | <IDLE> in transaction
> 26960 | <IDLE> in transaction
> 27008 | <IDLE> in transaction
> 12839 | <IDLE>
> 26977 | <IDLE> in transaction
> 27012 | <IDLE> in transaction
> 31354 | <IDLE>
> 27014 | <IDLE> in transaction
> 27015 | <IDLE> in transaction
> 26978 | <IDLE> in transaction
> 26985 | <IDLE> in transaction
> 27135 | select count(*) from ( select distinct on (email_id)
> email_id,email,contact from email_bank a join (select email_id from
> email_export_category where category_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
> 12, 14, 16, 17, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 1
> 12262 | SELECT source_id , cnt from (SELECT source_id,count(source_id) as
> cnt from email_source group by source_id ) subsel join sources
> using(source_id) order by source_id
> 27136 | <IDLE> in transaction
> (16 rows)
>
>
>
> tradein_clients=#
> why does the above not match with the "top" output at
> the same time:
>
> ==============================================================================
> 4:10pm up 2 days, 23:06, 2 users, load average: 6.21, 6.06, 5.60
> 69 processes: 66 sleeping, 3 running, 0 zombie, 0 stopped
> CPU states: 55.6% user, 2.0% system, 0.0% nice, 42.3% idle
> Mem: 1028484K av, 980320K used, 48164K free, 0K shrd, 3744K buff
> Swap: 971004K av, 102532K used, 868472K free 912724K
> cached
>
> PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND
> 5456 postgres 17 0 59456 57M 57156 R 0 99.1 5.7 3:12 postmaster
> 6601 postgres 9 0 79964 77M 78328 S 0 3.3 7.7 0:01 postmaster
> 6779 postgres 9 0 88412 86M 86752 S 0 1.9 8.5 0:00 postmaster
> 6703 postgres 9 0 81668 79M 80276 S 0 1.7 7.9 0:01 postmaster
> 6943 postgres 9 0 78732 76M 77520 S 0 1.7 7.6 0:01 postmaster
> 6940 postgres 9 0 44180 42M 42668 S 0 0.5 4.2 0:00 postmaster
> 6776 postgres 9 0 121M 121M 119M S 0 0.3 12.0 0:01 postmaster
> 5597 postgres 8 0 624 248 216 S 0 0.0 0.0 0:24 postmaster
> 5598 postgres 9 0 1440 4 4 D 0 0.0 0.0 2:31 postmaster
> 5599 postgres 9 0 2052 4 4 S 0 0.0 0.0 28:05 postmaster
> 12262 postgres 9 0 88564 4 4 D 0 0.0 0.0 0:19 postmaster
> 13039 postgres 9 0 656 4 4 D 0 0.0 0.0 0:00 postmaster
> 29440 postgres 9 0 20928 19M 20332 S 0 0.0 1.9 0:01 postmaster
> 1652 postgres 9 0 3356 2324 2144 S 0 0.0 0.2 3:21 postmaster
> 2219 postgres 9 0 2744 2120 2068 S 0 0.0 0.2 0:00 postmaster
> 6772 postgres 9 0 100M 100M 99.3M S 0 0.0 9.9 0:00 postmaster
> 6805 postgres 9 0 4440 4168 3532 S 0 0.0 0.4 0:00 postmaster
> 6809 postgres 9 0 35280 34M 33948 S 0 0.0 3.4 0:00 postmaster
> 6846 postgres 9 0 98.9M 98M 99804 S 0 0.0 9.8 0:01 postmaster
> 6931 postgres 9 0 21744 20M 20428 S 0 0.0 2.0 0:02 postmaster
> 6934 postgres 9 0 19020 18M 17868 S 0 0.0 1.8 0:00 postmaster
> 6941 postgres 9 0 63280 61M 61756 S 0 0.0 6.1 0:01 postmaster
> ================================================================================
>
>
> [root@linux10320 root2]# kill -INT 27135
> bash: kill: (27135) - No such pid
> [root@linux10320 root2]#
>
>
> and # kill -INT 12262 does not actually kills it ??
>
> regds
> mallah.
>
>
> --
> Rajesh Kumar Mallah,
> Project Manager (Development)
> Infocom Network Limited, New Delhi
> phone: +91(11)6152172 (221) (L) ,9811255597 (M)
>
> Visit http://www.trade-india.com ,
> India's Leading B2B eMarketplace.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: janwieck@yahoo.com, mallah@trade-india.com
Subject: Re: pg_stat_get_backend_pid seems to be listing non existant
In-Reply-To: <200205101048.g4AAmAK03594@saturn.janwieck.net>
References: <200205101556.40047.mallah@trade-india.com> <200205101048.g4AAmAK03594@saturn.janwieck.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox