Обсуждение: Heavy postgres process
Hi Admin,
I’m new to this I have few queries as listed below
1) Number of connections made with a particular database.
2) And how can I check which process (PID) is responsible for the connection and
3) what all can make a postgres process as heavy as 70-80 MB in size
Need to know these answers ASAP so I would highly appreciate if you can find time to answer my questions.
Regards,
~Vivek
**************** 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*** |
On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@infosys.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.
Thanks for your reply but how would I calculate which processes are eating up more memory, When I check process list, itsonly postgres and apache processes running on my system and only postgres processes are heavy. System runs out of memoryquickly. Regards, ~Vivek -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: Friday, September 12, 2008 11:18 PM To: Vivek_Sharan Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Heavy postgres process On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@infosys.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***
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@infosys.com> wrote: > Thanks for your reply but how would I calculate which processes are eating up more memory, When I check process list, itsonly postgres and apache processes running on my system and only postgres processes are heavy. System runs out of memoryquickly. > > Regards, > ~Vivek > > > -----Original Message----- > From: Scott Marlowe [mailto:scott.marlowe@gmail.com] > Sent: Friday, September 12, 2008 11:18 PM > To: Vivek_Sharan > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Heavy postgres process > > On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@infosys.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*** >
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
havealready scanned pg_stat_activity and pg_listener table but could get any clue. Pg_stat_activity shows list of all
idleprocesses 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
anythingbecause this is not available with top on this server. Following is the output of top if filtered for only
postgresuser
*****************************************************************************
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@gmail.com]
Sent: Monday, September 15, 2008 9:24 PM
To: Vivek_Sharan
Cc: pgsql-admin@postgresql.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@infosys.com> wrote:
> Thanks for your reply but how would I calculate which processes are eating up more memory, When I check process list,
itsonly postgres and apache processes running on my system and only postgres processes are heavy. System runs out of
memoryquickly.
>
> Regards,
> ~Vivek
>
>
> -----Original Message-----
> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Sent: Friday, September 12, 2008 11:18 PM
> To: Vivek_Sharan
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Heavy postgres process
>
> On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@infosys.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***
>
On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <Vivek_Sharan@infosys.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 havealready scanned pg_stat_activity and pg_listener table but could get any clue. Pg_stat_activity shows list of all idleprocesses 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 anythingbecause this is not available with top on this server. Following is the output of top if filtered for only postgresuser > > ***************************************************************************** > 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@gmail.com] > Sent: Monday, September 15, 2008 9:24 PM > To: Vivek_Sharan > Cc: pgsql-admin@postgresql.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@infosys.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 ofmemory quickly. >> >> Regards, >> ~Vivek >> >> >> -----Original Message----- >> From: Scott Marlowe [mailto:scott.marlowe@gmail.com] >> Sent: Friday, September 12, 2008 11:18 PM >> To: Vivek_Sharan >> Cc: pgsql-admin@postgresql.org >> Subject: Re: [ADMIN] Heavy postgres process >> >> On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@infosys.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@postgresql.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
On Tue, Sep 16, 2008 at 8:37 AM, Guido Barosio <gbarosio@gmail.com> wrote: > On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <Vivek_Sharan@infosys.com> wrote: >> Thanks for the information so far OK, I'm not BSD expert (Tom Lane might help out here) but I'm guessing that what we see in the following lines of top: >> 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 Is that the number on the left, like 83636K is the total memory used, and the number to the right 76068K is how much is shared buffers etc... which would mean that each of those processes above are using about 7 megs a piece, and the rest is repeated.
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@infosys.com> wrote: > I'm using postgres 7.4.5 > > Regards, > Vivek > > > > -----Original Message----- > From: Guido Barosio [mailto:gbarosio@gmail.com] > Sent: Tuesday, September 16, 2008 8:08 PM > To: Vivek_Sharan > Cc: Scott Marlowe; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Heavy postgres process > > On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <Vivek_Sharan@infosys.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 havealready scanned pg_stat_activity and pg_listener table but could get any clue. Pg_stat_activity shows list of all idleprocesses 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 anythingbecause this is not available with top on this server. Following is the output of top if filtered for only postgresuser >> >> ***************************************************************************** >> 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@gmail.com] >> Sent: Monday, September 15, 2008 9:24 PM >> To: Vivek_Sharan >> Cc: pgsql-admin@postgresql.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@infosys.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 ofmemory quickly. >>> >>> Regards, >>> ~Vivek >>> >>> >>> -----Original Message----- >>> From: Scott Marlowe [mailto:scott.marlowe@gmail.com] >>> Sent: Friday, September 12, 2008 11:18 PM >>> To: Vivek_Sharan >>> Cc: pgsql-admin@postgresql.org >>> Subject: Re: [ADMIN] Heavy postgres process >>> >>> On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@infosys.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@postgresql.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
On Wed, Sep 17, 2008 at 7:18 AM, Vivek_Sharan <Vivek_Sharan@infosys.com> wrote: > Yes that's true and that's planned. We will migrate to Oracle. But as of now need some pointers on solving the problemin hand. Well, updating to the latest 7.4 version would be a very very good move. There are some very real and nasty data loss bugs in 7.4.5. It's an in place update that requires only a few minutes of downtime. I would guess you've got something like sort_mem set too high. P.s. If you think PostgreSQL is hard to troubleshoot, I can't wait to see you running Oracle.
I'm using postgres 7.4.5 Regards, Vivek -----Original Message----- From: Guido Barosio [mailto:gbarosio@gmail.com] Sent: Tuesday, September 16, 2008 8:08 PM To: Vivek_Sharan Cc: Scott Marlowe; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Heavy postgres process On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <Vivek_Sharan@infosys.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 havealready scanned pg_stat_activity and pg_listener table but could get any clue. Pg_stat_activity shows list of all idleprocesses 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 anythingbecause this is not available with top on this server. Following is the output of top if filtered for only postgresuser > > ***************************************************************************** > 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@gmail.com] > Sent: Monday, September 15, 2008 9:24 PM > To: Vivek_Sharan > Cc: pgsql-admin@postgresql.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@infosys.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 ofmemory quickly. >> >> Regards, >> ~Vivek >> >> >> -----Original Message----- >> From: Scott Marlowe [mailto:scott.marlowe@gmail.com] >> Sent: Friday, September 12, 2008 11:18 PM >> To: Vivek_Sharan >> Cc: pgsql-admin@postgresql.org >> Subject: Re: [ADMIN] Heavy postgres process >> >> On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@infosys.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@postgresql.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
Yes that's true and that's planned. We will migrate to Oracle. But as of now need some pointers on solving the problem inhand. Regards, Vivek -----Original Message----- From: Guido Barosio [mailto:gbarosio@gmail.com] Sent: Wednesday, September 17, 2008 6:39 PM To: Vivek_Sharan Cc: Scott Marlowe; pgsql-admin@postgresql.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@infosys.com> wrote: > I'm using postgres 7.4.5 > > Regards, > Vivek > > > > -----Original Message----- > From: Guido Barosio [mailto:gbarosio@gmail.com] > Sent: Tuesday, September 16, 2008 8:08 PM > To: Vivek_Sharan > Cc: Scott Marlowe; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Heavy postgres process > > On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <Vivek_Sharan@infosys.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 havealready scanned pg_stat_activity and pg_listener table but could get any clue. Pg_stat_activity shows list of all idleprocesses 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 anythingbecause this is not available with top on this server. Following is the output of top if filtered for only postgresuser >> >> ***************************************************************************** >> 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@gmail.com] >> Sent: Monday, September 15, 2008 9:24 PM >> To: Vivek_Sharan >> Cc: pgsql-admin@postgresql.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@infosys.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 ofmemory quickly. >>> >>> Regards, >>> ~Vivek >>> >>> >>> -----Original Message----- >>> From: Scott Marlowe [mailto:scott.marlowe@gmail.com] >>> Sent: Friday, September 12, 2008 11:18 PM >>> To: Vivek_Sharan >>> Cc: pgsql-admin@postgresql.org >>> Subject: Re: [ADMIN] Heavy postgres process >>> >>> On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@infosys.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@postgresql.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
On Wed, Sep 17, 2008 at 12:31 PM, Kenneth Marshall <ktm@rice.edu> wrote: > Gee, > > Going to Oracle does seem a bit like throwing the baby out with > the bath water. Especially considering the performance increase from pgsql 7.4 to 8.3 is humongous. I'd say most operations are 2 to 4 times as fast and some operations are many 10 to 100 times faster. But for Vivek the real, first priority is to get his 7.4.5 server updated to the latest 7.4 version to make sure his data stays safe.
Getting to the latest 7.4 server involves the same as going to the latest 8.x server, AFAIK, if we take in consideration that 7.4.8 requires a dump & restore (meaning downtime). I would rather go after the latest 8.x server 2 cents. (It seems that Vivek works for Infosys, and that may explain the reason for an Oracle migration in the future. They are prolly plenty of Oracle DBA's working there, cheaper and reusable for sure if you think that often the kind of clients they handle are already oracle'ized ) gb.- On Wed, Sep 17, 2008 at 3:58 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Wed, Sep 17, 2008 at 12:31 PM, Kenneth Marshall <ktm@rice.edu> wrote: >> Gee, >> >> Going to Oracle does seem a bit like throwing the baby out with >> the bath water. > > Especially considering the performance increase from pgsql 7.4 to 8.3 > is humongous. I'd say most operations are 2 to 4 times as fast and > some operations are many 10 to 100 times faster. > > But for Vivek the real, first priority is to get his 7.4.5 server > updated to the latest 7.4 version to make sure his data stays safe. > -- http://www.linkedin.com/in/gbarosio
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 problemin hand. > > Regards, > Vivek > > > > -----Original Message----- > From: Guido Barosio [mailto:gbarosio@gmail.com] > Sent: Wednesday, September 17, 2008 6:39 PM > To: Vivek_Sharan > Cc: Scott Marlowe; pgsql-admin@postgresql.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@infosys.com> wrote: > > I'm using postgres 7.4.5 > > > > Regards, > > Vivek > > > > > > > > -----Original Message----- > > From: Guido Barosio [mailto:gbarosio@gmail.com] > > Sent: Tuesday, September 16, 2008 8:08 PM > > To: Vivek_Sharan > > Cc: Scott Marlowe; pgsql-admin@postgresql.org > > Subject: Re: [ADMIN] Heavy postgres process > > > > On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <Vivek_Sharan@infosys.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. Ihave already scanned pg_stat_activity and pg_listener table but could get any clue. Pg_stat_activity shows list of all idleprocesses 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 anythingbecause this is not available with top on this server. Following is the output of top if filtered for only postgresuser > >> > >> ***************************************************************************** > >> 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@gmail.com] > >> Sent: Monday, September 15, 2008 9:24 PM > >> To: Vivek_Sharan > >> Cc: pgsql-admin@postgresql.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@infosys.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 ofmemory quickly. > > >>> > >>> Regards, > >>> ~Vivek > >>> > >>> > >>> -----Original Message----- > >>> From: Scott Marlowe [mailto:scott.marlowe@gmail.com] > >>> Sent: Friday, September 12, 2008 11:18 PM > >>> To: Vivek_Sharan > >>> Cc: pgsql-admin@postgresql.org > >>> Subject: Re: [ADMIN] Heavy postgres process > >>> > >>> On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@infosys.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@postgresql.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
On Wed, Sep 17, 2008 at 1:43 PM, Guido Barosio <gbarosio@gmail.com> wrote:
> Getting to the latest 7.4 server involves the same as going to the
> latest 8.x server, AFAIK, if we take in consideration that 7.4.8
> requires a dump & restore (meaning downtime).
7.4.7 to 7.4.8 does NOT require a complete dump and restore. Been
there, done it and got the tshirt. There's a minor fix in the
catalogs that you can get by a single SQL command.
> I would rather go after the latest 8.x server
>
> 2 cents.
Me too. But the update to 7.4.21 or whatever the latest version is
does NOT require a dump reload, and unless you need that one line sql
fix, you don't even have to do that.
I quote the 7.4.8 release notes:
A dump/restore is not required for those running 7.4.X. However, it is
one possible way of handling two significant security problems that
have been found in the initial contents of 7.4.X system catalogs. A
dump/initdb/reload sequence using 7.4.8's initdb will automatically
correct these problems.
and further down
If you wish not to do an initdb, perform the following procedures
instead. As the database superuser, do:
BEGIN;
UPDATE pg_proc SET proargtypes[3] = 'internal'::regtype
WHERE pronamespace = 11 AND pronargs = 5
AND proargtypes[2] = 'cstring'::regtype;
-- The command should report having updated 90 rows;
-- if not, rollback and investigate instead of committing!
COMMIT;
Next, if you have installed contrib/tsearch2, do:
and so on.
> (It seems that Vivek works for Infosys, and that may explain the
> reason for an Oracle migration in the future. They are prolly plenty
> of Oracle DBA's working there, cheaper and reusable for sure if you
> think that often the kind of clients they handle are already
> oracle'ized )
especially if you can piggy back on someone else's oracle server
that's already running.
Where can I buy that t-shirt? :) Hmm, you are right. My intention was to explain that the case for a 7.4.21 (wich AFAIK is the latest one of the 7.4 series) is the same as for the v8 series of the server. 7.4.8 seems to be the latest one before a dump & restore would be a good idea. That was my point. Apologies for the noise! Gb.- On Wed, Sep 17, 2008 at 5:07 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Wed, Sep 17, 2008 at 1:43 PM, Guido Barosio <gbarosio@gmail.com> wrote: >> Getting to the latest 7.4 server involves the same as going to the >> latest 8.x server, AFAIK, if we take in consideration that 7.4.8 >> requires a dump & restore (meaning downtime). > > 7.4.7 to 7.4.8 does NOT require a complete dump and restore. Been > there, done it and got the tshirt. There's a minor fix in the > catalogs that you can get by a single SQL command. > >> I would rather go after the latest 8.x server >> >> 2 cents. > > Me too. But the update to 7.4.21 or whatever the latest version is > does NOT require a dump reload, and unless you need that one line sql > fix, you don't even have to do that. > > I quote the 7.4.8 release notes: > > A dump/restore is not required for those running 7.4.X. However, it is > one possible way of handling two significant security problems that > have been found in the initial contents of 7.4.X system catalogs. A > dump/initdb/reload sequence using 7.4.8's initdb will automatically > correct these problems. > > and further down > > If you wish not to do an initdb, perform the following procedures > instead. As the database superuser, do: > > BEGIN; > UPDATE pg_proc SET proargtypes[3] = 'internal'::regtype > WHERE pronamespace = 11 AND pronargs = 5 > AND proargtypes[2] = 'cstring'::regtype; > -- The command should report having updated 90 rows; > -- if not, rollback and investigate instead of committing! > COMMIT; > > Next, if you have installed contrib/tsearch2, do: > > and so on. > >> (It seems that Vivek works for Infosys, and that may explain the >> reason for an Oracle migration in the future. They are prolly plenty >> of Oracle DBA's working there, cheaper and reusable for sure if you >> think that often the kind of clients they handle are already >> oracle'ized ) > > especially if you can piggy back on someone else's oracle server > that's already running. > -- http://www.linkedin.com/in/gbarosio
On Wed, Sep 17, 2008 at 2:23 PM, Guido Barosio <gbarosio@gmail.com> wrote: > Where can I buy that t-shirt? :) > > Hmm, you are right. My intention was to explain that the case for a > 7.4.21 (wich AFAIK is the latest one of the 7.4 series) is the same as > for the v8 series of the server. 7.4.8 seems to be the latest one > before a dump & restore would be a good idea. That was my point. > > Apologies for the noise! No! it's a very valid point. it's just that you don't have to dump restore after 7.4.7 to get the security fixes in the catalog, there's a sql only way to avoid that. Plus, if you don't do it, you're only missing one small security update. The data eating bugs in early 7.4 releases are plentiful and ugly. I was just scanning through the release notes and there were data eating bugs in 7.4.6 through 7.4.17. Lots of them. It's more important to upgrade to squash those and forget about the security bug than to let the security bug hold you back on the update.
"Guido Barosio" <gbarosio@gmail.com> writes:
> Getting to the latest 7.4 server involves the same as going to the
> latest 8.x server, AFAIK, if we take in consideration that 7.4.8
> requires a dump & restore (meaning downtime).
(a) it does *not* require a dump and restore
(b) if you ignore the recommendations in the 7.4.8 notes and just
upgrade without doing anything else, then what you will have is a
7.4 system that still contains two security vulnerabilities.
Vulnerabilities that are in your 7.4.5 system today. How are you
worse off?
This is about the lamest excuse for not updating I've ever heard.
regards, tom lane
Hi,
Try to monitor the o/p of following command.
psql > select * from pg_Stat_activity;
It gives all the connection detail.
On Fri, 2008-09-12 at 18:12 +1000, Vivek_Sharan wrote:
Try to monitor the o/p of following command.
psql > select * from pg_Stat_activity;
It gives all the connection detail.
On Fri, 2008-09-12 at 18:12 +1000, Vivek_Sharan wrote:
Hi Admin,
I’m new to this I have few queries as listed below
1) Number of connections made with a particular database.
2) And how can I check which process (PID) is responsible for the connection and
3) what all can make a postgres process as heavy as 70-80 MB in size
Need to know these answers ASAP so I would highly appreciate if you can find time to answer my questions.
Regards,
~Vivek
| Thanks & Regards, Suresh Borse (DBA) _________________________________________________________________________________________ DIRECTION SOFTWARE SOLUTIONS 5, Brady Gladys Plaza, 1/447, Senapati Bapat Marg, Lower Parel, Mumbai - 400 013 Tel. : 91 22 66615000 (Ext: 350) Fax : 91 22 24911046 Cell : 91 9422 239338 Site : www.direction.biz _________________________________________________________________________________________ |
VACUUM activiy can make any process size so much heavy... Suresh Borse wrote: > Hi, > > Try to monitor the o/p of following command. > > psql > select * from pg_Stat_activity; > > It gives all the connection detail. > > > On Fri, 2008-09-12 at 18:12 +1000, Vivek_Sharan wrote: >> Hi Admin, >> >> >> >> I’m new to this I have few queries as listed below >> >> >> >> 1) Number of connections made with a particular database. >> >> 2) And how can I check which process (PID) is responsible for the >> connection and >> >> 3) what all can make a postgres process as heavy as 70-80 MB in size >> >> >> >> Need to know these answers ASAP so I would highly appreciate if you >> can find time to answer my questions. >> >> >> >> Regards, >> >> * ~Vivek * >> >> >> >> >> **************** 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*** >> >> > Thanks & Regards, > > Suresh Borse > (D BA ) > ______________________________________________________ > ________________________________ _ __ > * DIRECTION SOFTWARE SOLUTIONS * > 5, Brady Gladys Plaza, 1/447, Senapati Bapat Marg, Lower Parel, M > umbai - 400 013 > Tel. : 91 22 66615000 (Ext: 35 0 ) Fax : 91 22 > 24911046 > Cell : 91 9 4 2 2 239338 Site : > www.direction.biz <http://www.direction.biz/> > ___________________________________________________ > _____________________________________ _ >