Re: pg_stat_get_backend_pid seems to be listing non existant

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(at)linux10320 root2]# kill -INT 27135
> bash: kill: (27135) - No such pid
> [root(at)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(at)postgresql(dot)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(at)Yahoo(dot)com #