Обсуждение: postgres processes not reflected in pg_stat_activity

Поиск
Список
Период
Сортировка

postgres processes not reflected in pg_stat_activity

От
"Lee, Mija"
Дата:
Hello:
I have a server that has many more postgres processes (postgres -D
<datadir>, owned by postgres, listed with an ps -ef) than connections in
pg_stat_activity. How can I find out what those processes are?
I have been going through the docs and found a reference to if the
autovacuum launcher should show up in pg_stat_activity, but it looks
like that was implemented in 8.3.
I'm running 8.3.5 on solaris 10.
Thanks so much for the help!
Mija


CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain
confidential, proprietary, and/or privileged information protected by
law. If you are not the intended recipient, you may not use, copy, or
distribute this e-mail message or its attachments. If you believe you
have received this e-mail message in error, please contact the sender by
reply e-mail and destroy all copies of the original message.


Re: postgres processes not reflected in pg_stat_activity

От
Scott Marlowe
Дата:
On Thu, Sep 3, 2009 at 5:26 PM, Lee, Mija<mija@scharp.org> wrote:
> Hello:
> I have a server that has many more postgres processes (postgres -D
> <datadir>, owned by postgres, listed with an ps -ef) than connections in
> pg_stat_activity. How can I find out what those processes are?
> I have been going through the docs and found a reference to if the
> autovacuum launcher should show up in pg_stat_activity, but it looks
> like that was implemented in 8.3.
> I'm running 8.3.5 on solaris 10.
> Thanks so much for the help!
> Mija

What does:
$ ps ax|grep postgres
say?

I get something like this at idle:

  312 pts/0    S+     0:00 /usr/lib/postgresql/8.3/bin/psql
  313 ?        Ss     0:00 postgres: smarlowe smarlowe [local] idle
 2784 ?        S      0:01 /usr/lib/postgresql/8.3/bin/postgres -D
/var/lib/postgresql/8.3/main -c
config_file=/etc/postgresql/8.3/main/postgresql.conf
 2787 ?        Ss     0:03 postgres: writer process
 2788 ?        Ss     0:02 postgres: wal writer process
 2789 ?        Ss     0:00 postgres: autovacuum launcher process
 2790 ?        Ss     0:00 postgres: stats collector process
32621 pts/1    S+     0:00 grep postgres

Re: postgres processes not reflected in pg_stat_activity

От
"Lee, Mija"
Дата:
Hi -
Thanks for the response.
I'm on solaris so the ps ax doesn't work for me. I think the equivalent
is ps -ef which shows:
postgres  6939 22678   0 15:49:49 ?           1:33
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22678 16072   0   Aug 06 ?           5:22
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres  5074 22678   0   Aug 12 ?         710:08
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres  8677 22678   0   Aug 13 ?         894:39
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres  8347 22678   0   Aug 25 ?         357:27
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres   343 22678   0   Aug 13 ?         368:30
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres  8678 22678   0   Aug 13 ?         695:15
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 24793 22678   0   Aug 12 ?        2048:13
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22690 22678   0   Aug 06 ?         106:15
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22686 22678   0   Aug 06 ?           3:09
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22691 22678   0   Aug 06 ?        1145:42
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres   350 22678   0   Aug 13 ?         262:39
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 16593 22678   0   Aug 12 ?         731:57
/services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22688 22678   0   Aug 06 ?         104:59
/services/8.3.5/bin/postgres -D /services/pgdata2

If I'm wrong about the ps commands, please correct me.
When I cross reference this against pg_stat_activity, 5 of them don't
show up, and a top shows the same processes using 0% cpu but almost a
gig of memory. Are these internal postgres processes that don't make a
connection?
Mija





-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Thursday, September 03, 2009 7:54 PM
To: Lee, Mija
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] postgres processes not reflected in
pg_stat_activity

On Thu, Sep 3, 2009 at 5:26 PM, Lee, Mija<mija@scharp.org> wrote:
> Hello:
> I have a server that has many more postgres processes (postgres -D
> <datadir>, owned by postgres, listed with an ps -ef) than connections
in
> pg_stat_activity. How can I find out what those processes are?
> I have been going through the docs and found a reference to if the
> autovacuum launcher should show up in pg_stat_activity, but it looks
> like that was implemented in 8.3.
> I'm running 8.3.5 on solaris 10.
> Thanks so much for the help!
> Mija

What does:
$ ps ax|grep postgres
say?

I get something like this at idle:

  312 pts/0    S+     0:00 /usr/lib/postgresql/8.3/bin/psql
  313 ?        Ss     0:00 postgres: smarlowe smarlowe [local] idle
 2784 ?        S      0:01 /usr/lib/postgresql/8.3/bin/postgres -D
/var/lib/postgresql/8.3/main -c
config_file=/etc/postgresql/8.3/main/postgresql.conf
 2787 ?        Ss     0:03 postgres: writer process
 2788 ?        Ss     0:02 postgres: wal writer process
 2789 ?        Ss     0:00 postgres: autovacuum launcher process
 2790 ?        Ss     0:00 postgres: stats collector process
32621 pts/1    S+     0:00 grep postgres

Re: postgres processes not reflected in pg_stat_activity

От
Scott Marlowe
Дата:
On Fri, Sep 4, 2009 at 10:32 AM, Lee, Mija<mija@scharp.org> wrote:
> Hi -
> Thanks for the response.
> I'm on solaris so the ps ax doesn't work for me. I think the equivalent
> is ps -ef which shows:
> postgres  6939 22678   0 15:49:49 ?           1:33
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22678 16072   0   Aug 06 ?           5:22
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  5074 22678   0   Aug 12 ?         710:08
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  8677 22678   0   Aug 13 ?         894:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  8347 22678   0   Aug 25 ?         357:27
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres   343 22678   0   Aug 13 ?         368:30
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  8678 22678   0   Aug 13 ?         695:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 24793 22678   0   Aug 12 ?        2048:13
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22690 22678   0   Aug 06 ?         106:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22686 22678   0   Aug 06 ?           3:09
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22691 22678   0   Aug 06 ?        1145:42
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres   350 22678   0   Aug 13 ?         262:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 16593 22678   0   Aug 12 ?         731:57
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22688 22678   0   Aug 06 ?         104:59
> /services/8.3.5/bin/postgres -D /services/pgdata2
>
> If I'm wrong about the ps commands, please correct me.
> When I cross reference this against pg_stat_activity, 5 of them don't
> show up, and a top shows the same processes using 0% cpu but almost a
> gig of memory. Are these internal postgres processes that don't make a
> connection?

Got me, I've never been real handy on Solaris.  There's likely a
command that'll show something closer to what I posted.  Note that in
the list I posted there are several processes that are NOT pg
backends, but are helper processes (stats collector etc) and it's
normal to have a handful of these processes on a pg server.

Re: postgres processes not reflected in pg_stat_activity

От
Alvaro Herrera
Дата:
Lee, Mija escribió:
> Hi -
> Thanks for the response.
> I'm on solaris so the ps ax doesn't work for me. I think the equivalent
> is ps -ef which shows:

hmm, did you try /usr/ucb/ps?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: postgres processes not reflected in pg_stat_activity

От
Emanuel Calvo Franco
Дата:
2009/9/4 Lee, Mija <mija@scharp.org>:
> Hi -
> Thanks for the response.
> I'm on solaris so the ps ax doesn't work for me. I think the equivalent
> is ps -ef which shows:
> postgres  6939 22678   0 15:49:49 ?           1:33
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22678 16072   0   Aug 06 ?           5:22
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  5074 22678   0   Aug 12 ?         710:08
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  8677 22678   0   Aug 13 ?         894:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  8347 22678   0   Aug 25 ?         357:27
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres   343 22678   0   Aug 13 ?         368:30
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  8678 22678   0   Aug 13 ?         695:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 24793 22678   0   Aug 12 ?        2048:13
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22690 22678   0   Aug 06 ?         106:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22686 22678   0   Aug 06 ?           3:09
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22691 22678   0   Aug 06 ?        1145:42
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres   350 22678   0   Aug 13 ?         262:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 16593 22678   0   Aug 12 ?         731:57
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22688 22678   0   Aug 06 ?         104:59
> /services/8.3.5/bin/postgres -D /services/pgdata2
>
> If I'm wrong about the ps commands, please correct me.
> When I cross reference this against pg_stat_activity, 5 of them don't
> show up, and a top shows the same processes using 0% cpu but almost a
> gig of memory. Are these internal postgres processes that don't make a
> connection?
> Mija
>

What is the value of update_process_title in your postgresql.conf? By
default is on, so you must see the title  off the process.

what about ps auxw?

--
              Emanuel Calvo Franco
             DBA at:  www.siu.edu.ar
        www.emanuelcalvofranco.com.ar

Re: postgres processes not reflected in pg_stat_activity

От
"Lee, Mija"
Дата:
I got it once I pointed to the ucb ps per Alvaro's suggestion. Thanks! Here's what I see with a ps auwx :

postgres   343  4.2 16.627789922714952 ?        S   Aug 13 371:21 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres  8677  0.3 16.727967602735936 ?        S   Aug 13 895:14 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres 16593  0.2 10.617558721731344 ?        S   Aug 12 734:06 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22691  0.1  0.115096 9608 ?        S   Aug 06 1145:55 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22688  0.0  9.816178161609728 ?        S   Aug 06 105:03 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres  6939  0.0  9.816171921608128 ?        S 15:49:49  1:34 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22690  0.0  9.916256561615744 ?        S   Aug 06 106:16 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres   350  0.0 10.317070961678960 ?        S   Aug 13 262:39 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres  5074  0.0 10.216917041672928 ?        S   Aug 12 710:07 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres  8347  0.0 16.728063602733464 ?        S   Aug 25 357:26 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres  8678  0.0 10.216995281677848 ?        S   Aug 13 695:14 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22678  0.0  9.816164721609216 ?        S   Aug 06  5:22 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres 22686  0.0  0.0 6824 2176 ?        S   Aug 06  3:09 /services/8.3.5/bin/postgres -D /services/pgdata2
postgres 24793  0.0 16.928296322763624 ?        S   Aug 12 2050:48 /services/8.3.5/bin/postgres -D /services/pgdata2

Again, 5 extra processes. 6939 is one example. I'm not sure what you are looking for from this command that is
differentfrom ps -ef?  
Update_process_title is turned on.
Any other suggestions are greatly appreciated. I may have to kill these processes because they are eating up memory on
thismachine, but I would really prefer to know why/what they are before doing so. 
Mija

-----Original Message-----
From: Emanuel Calvo Franco [mailto:postgres.arg@gmail.com]
Sent: Friday, September 04, 2009 11:01 AM
To: Lee, Mija
Cc: Scott Marlowe; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] postgres processes not reflected in pg_stat_activity

2009/9/4 Lee, Mija <mija@scharp.org>:
> Hi -
> Thanks for the response.
> I'm on solaris so the ps ax doesn't work for me. I think the equivalent
> is ps -ef which shows:
> postgres  6939 22678   0 15:49:49 ?           1:33
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22678 16072   0   Aug 06 ?           5:22
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  5074 22678   0   Aug 12 ?         710:08
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  8677 22678   0   Aug 13 ?         894:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  8347 22678   0   Aug 25 ?         357:27
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres   343 22678   0   Aug 13 ?         368:30
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres  8678 22678   0   Aug 13 ?         695:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 24793 22678   0   Aug 12 ?        2048:13
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22690 22678   0   Aug 06 ?         106:15
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22686 22678   0   Aug 06 ?           3:09
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22691 22678   0   Aug 06 ?        1145:42
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres   350 22678   0   Aug 13 ?         262:39
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 16593 22678   0   Aug 12 ?         731:57
> /services/8.3.5/bin/postgres -D /services/pgdata2
> postgres 22688 22678   0   Aug 06 ?         104:59
> /services/8.3.5/bin/postgres -D /services/pgdata2
>
> If I'm wrong about the ps commands, please correct me.
> When I cross reference this against pg_stat_activity, 5 of them don't
> show up, and a top shows the same processes using 0% cpu but almost a
> gig of memory. Are these internal postgres processes that don't make a
> connection?
> Mija
>

What is the value of update_process_title in your postgresql.conf? By
default is on, so you must see the title  off the process.

what about ps auxw?

--
              Emanuel Calvo Franco
             DBA at:  www.siu.edu.ar
        www.emanuelcalvofranco.com.ar

Re: postgres processes not reflected in pg_stat_activity

От
Scott Marlowe
Дата:
On Fri, Sep 4, 2009 at 12:17 PM, Lee, Mija<mija@scharp.org> wrote:
> I got it once I pointed to the ucb ps per Alvaro's suggestion. Thanks! Here's what I see with a ps auwx :

So what does ps ax|grep postgres say?

Re: postgres processes not reflected in pg_stat_activity

От
"Lee, Mija"
Дата:
  343 ?        S 372:35 /services/8.3.5/bin/postgres -D
/services/pgdata2
   350 ?        S 262:39 /services/8.3.5/bin/postgres -D
/services/pgdata2
  5074 ?        S 710:07 /services/8.3.5/bin/postgres -D
/services/pgdata2
  6939 ?        S  1:35 /services/8.3.5/bin/postgres -D
/services/pgdata2
  8347 ?        S 357:26 /services/8.3.5/bin/postgres -D
/services/pgdata2
  8677 ?        S 897:01 /services/8.3.5/bin/postgres -D
/services/pgdata2
  8678 ?        S 695:14 /services/8.3.5/bin/postgres -D
/services/pgdata2
 16593 ?        O 736:45 /services/8.3.5/bin/postgres -D
/services/pgdata2
 22678 ?        S  5:23 /services/8.3.5/bin/postgres -D
/services/pgdata2
 22686 ?        S  3:09 /services/8.3.5/bin/postgres -D
/services/pgdata2
 22688 ?        S 105:04 /services/8.3.5/bin/postgres -D
/services/pgdata2
 22690 ?        S 106:16 /services/8.3.5/bin/postgres -D
/services/pgdata2
 22691 ?        S 1146:00 /services/8.3.5/bin/postgres -D
/services/pgdata2
 24793 ?        S 2050:48 /services/8.3.5/bin/postgres -D
/services/pgdata2


-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Friday, September 04, 2009 11:21 AM
To: Lee, Mija
Cc: Emanuel Calvo Franco; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] postgres processes not reflected in
pg_stat_activity

On Fri, Sep 4, 2009 at 12:17 PM, Lee, Mija<mija@scharp.org> wrote:
> I got it once I pointed to the ucb ps per Alvaro's suggestion. Thanks!
Here's what I see with a ps auwx :

So what does ps ax|grep postgres say?

Re: postgres processes not reflected in pg_stat_activity

От
Tom Lane
Дата:
"Lee, Mija" <mija@scharp.org> writes:
> Again, 5 extra processes. 6939 is one example. I'm not sure what you are looking for from this command that is
differentfrom ps -ef?  
> Update_process_title is turned on.

5 extra processes would be exactly what I'd expect in an 8.3
installation.  I don't remember the magic incantation to get Solaris'
ps to show you useful descriptions, but on most platforms what you'd
see is something like this:

postgres  8522     1  0 13:37:37 pts/2     0:00 postmaster
postgres  8525  8522  0 13:37:37 ?         0:00 postgres: writer process
postgres  8526  8522  0 13:37:37 ?         0:00 postgres: wal writer process
postgres  8527  8522  0 13:37:37 ?         0:00 postgres: autovacuum launcher process
postgres  8528  8522  0 13:37:37 ?         0:00 postgres: stats collector process

> Any other suggestions are greatly appreciated. I may have to kill these processes because they are eating up memory
onthis machine, but I would really prefer to know why/what they are before doing so. 

You can't kill them without taking down the database.  I will bet large
odds that they aren't "eating memory", either --- you have probably got
Postgres' shared memory area set to about 1GB, and you are looking at
the output of tools that report all of that as belonging to each process
that's connected to the shared memory segment.

            regards, tom lane