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
>