Re: Heavy postgres process

Gee,

Going to Oracle does seem a bit like throwing the baby out with
the bath water. For pretty much any use, we found that Oracle requires
many more hardware and management resources than PostgreSQL needs for
the same performance. Make certain that you load test your Oracle "upgrade"
to ensure that you can meet your service requirements.

On the performance problem, I think that the 83MB is the shared_buffers
for postgres and is shared between all backends. According to the FreeBSD
site, sbwait happens when a thread is trying to send or receive data on
a blocking socket. I would try a couple of sample queries that your app
generates, to time them, but it may be your Apache process that is using
the lion's share of your memory.

Cheers,
Ken

On Wed, Sep 17, 2008 at 11:18:24PM +1000, Vivek_Sharan wrote:
> Yes that's true and that's planned. We will migrate to Oracle. But as of now need some pointers on solving the problem in hand.
>
> Regards,
> Vivek
>
>
>
> -----Original Message-----
> From: Guido Barosio [mailto:gbarosio(at)gmail(dot)com]
> Sent: Wednesday, September 17, 2008 6:39 PM
> To: Vivek_Sharan
> Cc: Scott Marlowe; pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] Heavy postgres process
>
> Well, the answer is shor Vivekt:
>
> Upgrade that postgresql ASAP, it's too way old.
>
> gb.-
>
> On Wed, Sep 17, 2008 at 9:29 AM, Vivek_Sharan <Vivek_Sharan(at)infosys(dot)com> wrote:
> > I'm using postgres 7.4.5
> >
> > Regards,
> > Vivek
> >
> >
> >
> > -----Original Message-----
> > From: Guido Barosio [mailto:gbarosio(at)gmail(dot)com]
> > Sent: Tuesday, September 16, 2008 8:08 PM
> > To: Vivek_Sharan
> > Cc: Scott Marlowe; pgsql-admin(at)postgresql(dot)org
> > Subject: Re: [ADMIN] Heavy postgres process
> >
> > On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <Vivek_Sharan(at)infosys(dot)com> wrote:
> >> Thanks for the information so far
> >> My Application runs on FreeBSd box and main technological component are Apache and mod Perl, database is postgres. I have already scanned pg_stat_activity and pg_listener table but could get any clue. Pg_stat_activity shows list of all idle processes but command (current_query) column is empty. So I cannot make out what these processes are doing.
> >> TOP on this server doesn't have any option available to further break down processes. And hitting 'M; did change anything because this is not available with top on this server. Following is the output of top if filtered for only postgres user
> >>
> >> *****************************************************************************
> >> last pid: 92308; load averages: 0.00, 0.03, 0.05
> >> 78 processes: 2 running, 76 sleeping
> >> CPU states: 1.6% user, 0.0% nice, 3.4% system, 0.0% interrupt, 94.9% idle
> >> Mem: 413M Active, 2122M Inact, 534M Wired, 140M Cache, 199M Buf, 533M Free
> >> Swap: 4096M Total, 3880K Used, 4092M Free
> >>
> >> PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
> >> 90976 postgres 2 0 83568K 76016K sbwait 2 0:32 2.83% 2.83% postgres
> >> 90963 postgres 2 0 83396K 75876K sbwait 2 0:25 1.37% 1.37% postgres
> >> 90919 postgres 2 0 83808K 76244K sbwait 1 0:32 0.39% 0.39% postgres
> >> 87341 postgres 2 0 6388K 756K select 3 2:35 0.00% 0.00% postgres
> >> 87340 postgres 2 0 7200K 1224K select 0 1:41 0.00% 0.00% postgres
> >> 90961 postgres 2 0 83580K 76008K sbwait 0 0:30 0.00% 0.00% postgres
> >> 90920 postgres 2 0 83636K 76068K sbwait 0 0:29 0.00% 0.00% postgres
> >> 90934 postgres 2 0 83664K 76012K sbwait 0 0:27 0.00% 0.00% postgres
> >> 90924 postgres 2 0 83408K 75872K sbwait 0 0:25 0.00% 0.00% postgres
> >> 90915 postgres 2 0 79292K 72664K sbwait 0 0:23 0.00% 0.00% postgres
> >> 90955 postgres 2 0 79644K 73040K sbwait 0 0:22 0.00% 0.00% postgres
> >> 90979 postgres 2 0 78904K 72260K sbwait 0 0:17 0.00% 0.00% postgres
> >> 87339 postgres 2 0 74756K 672K select 1 0:12 0.00% 0.00% postgres
> >> 90921 postgres 2 0 75504K 59848K sbwait 3 0:01 0.00% 0.00% postgres
> >> 90927 postgres 2 0 75540K 59296K sbwait 3 0:01 0.00% 0.00% postgres
> >> 90962 postgres 2 0 75524K 56960K sbwait 0 0:01 0.00% 0.00% postgres
> >> 90923 postgres 2 0 75540K 57584K sbwait 1 0:01 0.00% 0.00% postgres
> >> 90914 postgres 2 0 75552K 57776K sbwait 1 0:01 0.00% 0.00% postgres
> >> 90917 postgres 2 0 75524K 57256K sbwait 3 0:01 0.00% 0.00% postgres
> >> 90922 postgres 2 0 75504K 57352K sbwait 1 0:01 0.00% 0.00% postgres
> >> 90918 postgres 2 0 75508K 57748K sbwait 3 0:01 0.00% 0.00% postgres
> >> 90933 postgres 2 0 75540K 53728K sbwait 2 0:01 0.00% 0.00% postgres
> >> 90926 postgres 2 0 75484K 54928K sbwait 3 0:01 0.00% 0.00% postgres
> >> 90931 postgres 2 0 75512K 20880K sbwait 3 0:00 0.00% 0.00% postgres
> >> 90977 postgres 2 0 75512K 20584K sbwait 0 0:00 0.00% 0.00% postgres
> >> 91005 postgres 2 0 75512K 19956K sbwait 0 0:00 0.00% 0.00% postgres
> >> 90966 postgres 2 0 75488K 19056K sbwait 1 0:00 0.00% 0.00% postgres
> >> 90986 postgres 2 0 75512K 19348K sbwait 1 0:00 0.00% 0.00% postgres
> >> 90973 postgres 2 0 75512K 18140K sbwait 1 0:00 0.00% 0.00% postgres
> >> 90989 postgres 2 0 75512K 18668K sbwait 2 0:00 0.00% 0.00% postgres
> >> 90956 postgres 2 0 75488K 18320K sbwait 2 0:00 0.00% 0.00% postgres
> >> 90998 postgres 2 0 75512K 17564K sbwait 3 0:00 0.00% 0.00% postgres
> >> 90925 postgres 2 0 75488K 17412K sbwait 1 0:00 0.00% 0.00% postgres
> >> 88881 postgres 2 0 75528K 7920K sbwait 0 0:00 0.00% 0.00% postgres
> >> *****************************************************************************
> >>
> >> Output of vmstat command
> >>
> >> procs memory page disks faults cpu
> >> r b w avm fre flt re pi po fr sr da0 da1 in sy cs us sy id
> >> 0 0 0 423492 688492 40 0 0 0 52 57 0 0 50 11 50 53 47 -0
> >>
> >> *****************************************************************************
> >> Output of systat command
> >>
> >>> systat
> >>
> >>
> >> /0 /1 /2 /3 /4 /5 /6 /7 /8 /9 /10
> >> Load Average |
> >>
> >> /0 /10 /20 /30 /40 /50 /60 /70 /80 /90 /100
> >> postgres postgres X
> >> *****************************************************************************
> >> entries in pg_stat_activities
> >>
> >> datid | datname | procpid | usesysid | usename | current_query | query_start
> >> -------+---------+---------+----------+----------+---------------+-------------
> >> 17142 | wasdb | 90914 | 103 | was | |
> >> 17142 | wasdb | 90917 | 103 | was | |
> >> 17142 | wasdb | 90915 | 103 | was | |
> >> 17142 | wasdb | 90918 | 103 | was | |
> >> 17142 | wasdb | 90919 | 103 | was | |
> >> 17142 | wasdb | 90920 | 103 | was | |
> >> 17142 | wasdb | 90921 | 103 | was | |
> >> 17142 | wasdb | 90922 | 103 | was | |
> >> 17142 | wasdb | 90923 | 103 | was | |
> >> 17142 | wasdb | 90924 | 103 | was | |
> >> 17142 | wasdb | 90925 | 104 | audit | |
> >> 17142 | wasdb | 90926 | 103 | was | |
> >> 17142 | wasdb | 90927 | 103 | was | |
> >> 17142 | wasdb | 90955 | 103 | was | |
> >> 17142 | wasdb | 90956 | 104 | audit | |
> >> 17142 | wasdb | 90961 | 103 | was | |
> >> 17142 | wasdb | 90931 | 104 | audit | |
> >> 17142 | wasdb | 90933 | 103 | was | |
> >> 17142 | wasdb | 90934 | 103 | was | |
> >> 17142 | wasdb | 90962 | 103 | was | |
> >> 17142 | wasdb | 90963 | 103 | was | |
> >> 17142 | wasdb | 90966 | 104 | audit | |
> >> 17142 | wasdb | 90973 | 104 | audit | |
> >> 17142 | wasdb | 90976 | 103 | was | |
> >> 17142 | wasdb | 90977 | 104 | audit | |
> >> 17142 | wasdb | 90979 | 103 | was | |
> >> 17142 | wasdb | 90986 | 104 | audit | |
> >> 17142 | wasdb | 90989 | 104 | audit | |
> >> 17142 | wasdb | 92353 | 1 | postgres | |
> >> 17142 | wasdb | 90998 | 104 | audit | |
> >> 17142 | wasdb | 88881 | 1 | postgres | |
> >> 17142 | wasdb | 91005 | 104 | audit | |
> >> (32 rows)
> >>
> >> *****************************************************************************
> >> Regards,
> >> Vivek Sharan
> >>
> >>
> >>
> >> -----Original Message-----
> >> From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
> >> Sent: Monday, September 15, 2008 9:24 PM
> >> To: Vivek_Sharan
> >> Cc: pgsql-admin(at)postgresql(dot)org
> >> Subject: Re: [ADMIN] Heavy postgres process
> >>
> >> Run top, hit M and the attach the output to a reply here and we'll take a look.
> >>
> >> On Mon, Sep 15, 2008 at 5:33 AM, Vivek_Sharan <Vivek_Sharan(at)infosys(dot)com> wrote:
> >>> Thanks for your reply but how would I calculate which processes are eating up more memory, When I check process list, its only postgres and apache processes running on my system and only postgres processes are heavy. System runs out of memory quickly.
>
> >>>
> >>> Regards,
> >>> ~Vivek
> >>>
> >>>
> >>> -----Original Message-----
> >>> From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
> >>> Sent: Friday, September 12, 2008 11:18 PM
> >>> To: Vivek_Sharan
> >>> Cc: pgsql-admin(at)postgresql(dot)org
> >>> Subject: Re: [ADMIN] Heavy postgres process
> >>>
> >>> On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan(at)infosys(dot)com> wrote:
> >>>> Hi Admin,
> >>>>
> >>>> I'm new to this I have few queries as listed below
> >>>>
> >>>> 1) Number of connections made with a particular database.
> >>>
> >>> Wait, how to find out how many connections there are, or how many can
> >>> a particular db handle.
> >>>
> >>> For this kind of thing, look at the admin functions in the pgsql-sql docs:
> >>>
> >>> http://www.postgresql.org/docs/8.3/interactive/functions-admin.html
> >>>
> >>> specifically you want something like:
> >>>
> >>> select datname from pg_stat_activity;
> >>> select datname, count(datname) from pg_stat_activity group by datname;
> >>>
> >>>> 2) And how can I check which process (PID) is responsible for the
> >>>> connection and
> >>>
> >>> That table up there ^^^
> >>>
> >>>> 3) what all can make a postgres process as heavy as 70-80 MB in size
> >>>
> >>> you may not be measuring properly. When you say it's using 70-80 MB
> >>> how do you know this? The numbers you see in top aren't necessarily
> >>> what some folks think they ar.
> >>>
> >>> **************** CAUTION - Disclaimer *****************
> >>> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
> >>> for the use of the addressee(s). If you are not the intended recipient, please
> >>> notify the sender by e-mail and delete the original message. Further, you are not
> >>> to copy, disclose, or distribute this e-mail or its contents to any other person and
> >>> any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
> >>> every reasonable precaution to minimize this risk, but is not liable for any damage
> >>> you may sustain as a result of any virus in this e-mail. You should carry out your
> >>> own virus checks before opening the e-mail or attachment. Infosys reserves the
> >>> right to monitor and review the content of all messages sent to or from this e-mail
> >>> address. Messages sent to or from this e-mail address may be stored on the
> >>> Infosys e-mail system.
> >>> ***INFOSYS******** End of Disclaimer ********INFOSYS***
> >>>
> >>
> >> --
> >> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-admin
> >>
> >
> > Vivek, which version of postgres you are using?
> >
> > gb.-
> >
> > --
> > http://www.linkedin.com/in/gbarosio
> >
>
>
>
> --
> http://www.linkedin.com/in/gbarosio
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>