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@xxxxxxxxxxx> wrote: > I'm using postgres 7.4.5 > > Regards, > Vivek > > > > -----Original Message----- > From: Guido Barosio [mailto:gbarosio@xxxxxxxxx] > Sent: Tuesday, September 16, 2008 8:08 PM > To: Vivek_Sharan > Cc: Scott Marlowe; pgsql-admin@xxxxxxxxxxxxxx > Subject: Re: [ADMIN] Heavy postgres process > > On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <Vivek_Sharan@xxxxxxxxxxx> 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.marlowe@xxxxxxxxx] >> Sent: Monday, September 15, 2008 9:24 PM >> To: Vivek_Sharan >> Cc: pgsql-admin@xxxxxxxxxxxxxx >> 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@xxxxxxxxxxx> 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.marlowe@xxxxxxxxx] >>> Sent: Friday, September 12, 2008 11:18 PM >>> To: Vivek_Sharan >>> Cc: pgsql-admin@xxxxxxxxxxxxxx >>> Subject: Re: [ADMIN] Heavy postgres process >>> >>> On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx) >> 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
