Thread: postgres processes not reflected in pg_stat_activity
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.
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
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
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.
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.
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
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
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?
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?
"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