Re: Any advice on debugging hanging postgresql-8.1.21 (many postmaster's)

Specifically look into setting..... 

log_min_duration_statement (integer)

in the postgresql.conf file.

-----Original Message-----
From: Robert Gravsjö [mailto:tekniksupport@blogg.se] 
Sent: Monday, October 04, 2010 7:22 AM
To: Alexander Farber
Cc: pgsql-general@postgresql.org
Subject: Re: Any advice on debugging hanging postgresql-8.1.21 (many postmaster's)



Alexander Farber skrev 2010-10-04 11.48:
> I wish I could see those hanging queries, what SQL do they try to execute:

To see the query you need to enable stats_command_string in your 
postgresql.conf. See: 
http://www.postgresql.org/docs/8.1/interactive/runtime-config-statistics.html

You might also benefit from chapter 24:
http://www.postgresql.org/docs/8.1/interactive/monitoring.html

Regards,
roppert

>
> pref=>  select * from pg_stat_activity;
>   datid | datname | procpid | usesysid | usename |        current_query
>         | query_start |         backend_start         | client_addr |
> client_port
>
-------+---------+---------+----------+---------+------------------------------+-------------+-------------------------------+-------------+-------------
>   16384 | pref    |    2681 |    16385 | pref    |<command string not
> enabled>  |             | 2010-10-04 10:22:53.051483+02 |             |
>          -1
> .........
>
> I have usually just one Postgres connection from my game
> (a perl script running as daemon). And then I have several
> connections from phpBB. But when I restart httpd,
> the spinning postmaster's don't disappear:
>
> top - 11:48:11 up  1:28,  1 user,  load average: 9.85, 8.68, 6.25
> Tasks: 126 total,  12 running, 114 sleeping,   0 stopped,   0 zombie
> Cpu0  : 36.1%us,  0.5%sy,  0.0%ni, 63.0%id,  0.4%wa,  0.0%hi,  0.0%si,  0.0%st
> Cpu1  : 37.1%us,  0.8%sy,  0.0%ni, 61.6%id,  0.1%wa,  0.0%hi,  0.5%si,  0.0%st
> Cpu2  : 61.4%us,  1.3%sy,  0.0%ni, 35.7%id,  1.2%wa,  0.0%hi,  0.5%si,  0.0%st
> Cpu3  : 26.6%us,  0.6%sy,  0.0%ni, 72.1%id,  0.1%wa,  0.0%hi,  0.5%si,  0.0%st
> Mem:   4019028k total,  1428256k used,  2590772k free,    22324k buffers
> Swap:  2104496k total,        0k used,  2104496k free,  1199036k cached
>
>    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
>   3263 postgres  16   0  122m  14m   9m R 116.0  0.4  16:16.83 postmaster
>   3208 postgres  16   0  122m  14m   9m R 112.0  0.4  35:16.08 postmaster
>   3275 postgres  16   0  121m  13m   9m R 110.6  0.4  11:34.32 postmaster
>   3315 postgres  16   0  121m  13m   9m R 100.5  0.4   7:16.93 postmaster
>   3193 postgres  16   0  122m  14m   9m R 96.4  0.4  38:44.78 postmaster
>   3233 postgres  16   0  122m  14m   9m R 68.8  0.4  29:28.90 postmaster
>   3243 postgres  16   0  122m  14m   9m R 53.3  0.4  25:13.96 postmaster
>   3256 postgres  16   0  122m  14m   9m R 34.4  0.4  19:23.93 postmaster
>