Immortal backend .... (kill -9 ?..) - Mailing list pgsql-admin

Hi

i have just discovered  a query in my database system that would take
extremely long durations to finish. the query plan is like:

explain 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
tradein_clients-# ;
NOTICE:  QUERY PLAN:
Merge Join  (cost=145610.94..147300.41 rows=112596 width=16)
  ->  Sort  (cost=145597.57..145597.57 rows=112596 width=4)
        ->  Subquery Scan subsel  (cost=130520.56..136150.35 rows=112596
 width=4) ->  Aggregate  (cost=130520.56..136150.35 rows=112596 width=4) ->
 Group  (cost=130520.56..133335.46 rows=1125957 width=4) ->  Sort
 (cost=130520.56..130520.56 rows=1125957 width=4) ->  Seq Scan on
 email_source  (cost=0.00..17346.57 rows=1125957 width=4) ->  Sort
 (cost=13.37..13.37 rows=213 width=4)
        ->  Seq Scan on sources  (cost=0.00..5.13 rows=213 width=4)
EXPLAIN

unfortunately it has been running since  yesterday and i am
discovering it now.

now even kill -9 <pid of backend> does not releases it below is
the current status of backends:

postgres  8594  0.0  0.0 139416 148 ?        S    May11   0:25
 /usr/local/pgsql/bin/postmaster postgres  8595  1.3  0.1 140408 1040 ?
 S    May11  78:47 postgres: stats buffer process postgres  8597  1.1  0.0
 139704 324 ?        S    May11  65:18 postgres: stats collector process
 postgres 32444  0.0  0.0 238700   0 ?        DW   May14   0:20 postgres:
 tradein tradein_clients 130.94.20.27 SELECT postgres 32482  0.0  0.0 139808
  0 ?        DW   May14   0:00 postgres: checkpoint subprocess postgres 16907
  0.0  0.3 140296 3912 ?       S    17:51   0:00 postgres: postgres
 tradein_clients 203.196.129.235 idle postgres 20382  1.3  4.1 140816 42588 ?
      S    18:27   0:01 postgres: tradein tradein_clients 130.94.20.27 idle
 in transaction postgres 20388  0.5  6.7 141200 68940 ?      S    18:27
 0:00 postgres: tradein tradein_clients 130.94.20.27 idle in transaction

The pid of the backend that is executing the query is 32444 .
also note that pid 32482 is also (running?) is since may 14
 and 32444 also  is (running?) since may 14.
(is there any relation between them?)
what is checkpoint subprocess?

the load average is
[root@linux10320 root2]# uptime
  6:30pm  up 4 days,  6:26,  1 user,  load average: 11.08, 10.54, 10.78
[root@linux10320 root2]#

can anyone tell me what might me causing so much load in the system,
or someway to get see what is going on to make such a load?


also if i were to bring down postgresql what should have been
a safe (and effective) way.

Disk I/O is like:

# iostat -d 2 6
Linux 2.4.9 (linux10320)        05/15/02

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
dev8-0           32.25       450.01       445.92  166025844  164517760
dev8-1           34.26       434.75       640.21  160395794  236199552

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
dev8-0            2.50        24.00        12.00         48         24
dev8-1            1.00         0.00        12.00          0         24

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
dev8-0           13.00         0.00       112.00          0        224
dev8-1           13.00         0.00       112.00          0        224

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
dev8-0           23.50         0.00       200.00          0        400
dev8-1           23.50         0.00       200.00          0        400

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
dev8-0            3.00         0.00        44.00          0         88
dev8-1            3.00         0.00        44.00          0         88

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
dev8-0            3.00         0.00        36.00          0         72
dev8-1            3.00         0.00        36.00          0         72
[root@linux10320 root2]#

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.