Postgres restart

--001636ed6b8b29962e04741a0f38
Content-Type: text/plain; charset=ISO-8859-1

Recently due to some problem(not yet diagnosed) , our DB server Postgres is
getting very slow after few hours. We didnt changed any settings for 6
months , so we dont know y its happening suddenly in this week. Our data
folder is 118GB with 160 DBs. System is 2 Quad core with RAM 4GB. In last
two days when it was restarted its performance is good but gradually going
down. So few planned to restart the posgres process daily. Is it advisable
to restart server daily ? since daily we can have 30 mins downtime. Please
advise is it advisable or not?

-Arvind S
*
"Many of lifes failure are people who did not realize how close they were to
success when they gave up."
-Thomas Edison*

--001636ed6b8b29962e04741a0f38
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Recently due to some problem(not yet diagnosed) , our DB server Postgres is=
getting very slow after few hours. We didnt changed any settings for 6 mon=
ths , so we dont know y its happening suddenly in this week. Our data folde=
r is 118GB with 160 DBs. System is 2 Quad core with RAM 4GB. In last two da=
ys when it was restarted its performance is good but gradually going down. =
So few planned to restart the posgres process daily. Is it advisable to res=
tart server daily ? since daily we can have 30 mins downtime. Please advise=
is it advisable or not? 

 -Arvind S "Many of lifes =
failure are people who did not realize how close they were to success when =
they gave up." -Thomas Edison 

--001636ed6b8b29962e04741a0f38--

There's an awful lot of information left out that would be very useful to
help advise you.

Restarting the postgres services on a daily basis is certainly nothing
that's going to corrupt your data or hurt your system, PROVIDED that it is
done correctly (ie: not killing the backend postmaster when something is
happening, not hard-booting the system while a RAID card is trying to write
its cache, etc.)

However, I think you'd be postponing the problem. The better answer might be
to define and resolve the issue.

"Its performance is good but gradually going down?" Well, is this a
dedicated database server? What O/S? What version of PG? Have you check out
memory usage? 4GB seems a bit low for the amount of data you're using. In a
similar environment, I've got 12GB, and from time to time I'm paging.

If "nothing changed" (TRULY, that is), you're most likely finding that
you're either CPU, memory, or I/O bound, and the most likely culprits are
the last 2 unless you've suddenly started some massive queries that didn't
happen a few weeks ago.

Also, when was the last time you vacuumed the database(s)? Is auto-vac on? I
know, I know, I'm not _supposed_ to have to perform a full vacuum and
analyze on my databases with auto-vac on, but if I don't, I run into
performance problems, so I do that once per week, myself.

-----Original Message-----
From: pgsql-admin-owner [at] postgresql.org
[mailto:pgsql-admin-owner [at] postgresql.org] On Behalf Of S Arvind
Sent: Monday, September 21, 2009 12:46 PM
To: pgsql-admin
Subject: [ADMIN] Postgres restart

Recently due to some problem(not yet diagnosed) , our DB server Postgres is
getting very slow after few hours. We didnt changed any settings for 6
months , so we dont know y its happening suddenly in this week. Our data
folder is 118GB with 160 DBs. System is 2 Quad core with RAM 4GB. In last
two days when it was restarted its performance is good but gradually going
down. So few planned to restart the posgres process daily. Is it advisable
to restart server daily ? since daily we can have 30 mins downtime. Please
advise is it advisable or not?

-Arvind S

"Many of lifes failure are people who did not realize how close they were to
success when they gave up."
-Thomas Edison

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--001636b42ebd1cb5d204741b5c91
Content-Type: text/plain; charset=ISO-8859-1

Thanks Scott.
1. Well, is this a dedicated database server?
YES, it runs only Postgres with some Back-up script for that DBs alone
daily.

2. What O/S?
CentOS (Linux version 2.6.18-8.1.4.el5 (mockbuild [at] builder6.centos.org) (gcc
version 4.1.1 20070105 (Red Hat 4.1.1-52)) )

3. What version of PG?
8.3.7

4. Have you check out memory usage?
When rebooted it has more then 2.5 GB free space but after few hours it
will reach 50MB. This is usual in our DB server, since this decrease never
affected our performance for past years. And also for 5 months we never
rebooted our system and also we had restart the postgres likely once in a
month, before this problem.

5. Also, when was the last time you vacuumed the database(s)?
As per advise from postgres team we are running full vaccum for every week
and frequently-used table(30) vacum daily. We have nearly 640 tables in each
DB.

6. Is auto-vac on?
Yes ( postgres: autovacuum launcher process running)

Is our problem is identifiable, from infrastructure side?

-Arvind S

"Many of lifes failure are people who did not realize how close they were to
success when they gave up."
-Thomas Edison

On Mon, Sep 21, 2009 at 11:33 PM, Scott Whitney <swhitney [at] journyx.com>wrote:

> There's an awful lot of information left out that would be very useful to
> help advise you.
>
> Restarting the postgres services on a daily basis is certainly nothing
> that's going to corrupt your data or hurt your system, PROVIDED that it is
> done correctly (ie: not killing the backend postmaster when something is
> happening, not hard-booting the system while a RAID card is trying to write
> its cache, etc.)
>
> However, I think you'd be postponing the problem. The better answer might
> be
> to define and resolve the issue.
>
> "Its performance is good but gradually going down?" Well, is this a
> dedicated database server? What O/S? What version of PG? Have you check out
> memory usage? 4GB seems a bit low for the amount of data you're using. In a
> similar environment, I've got 12GB, and from time to time I'm paging.
>
> If "nothing changed" (TRULY, that is), you're most likely finding that
> you're either CPU, memory, or I/O bound, and the most likely culprits are
> the last 2 unless you've suddenly started some massive queries that didn't
> happen a few weeks ago.
>
> Also, when was the last time you vacuumed the database(s)? Is auto-vac on?
> I
> know, I know, I'm not _supposed_ to have to perform a full vacuum and
> analyze on my databases with auto-vac on, but if I don't, I run into
> performance problems, so I do that once per week, myself.
>
> -----Original Message-----
> From: pgsql-admin-owner [at] postgresql.org
> [mailto:pgsql-admin-owner [at] postgresql.org] On Behalf Of S Arvind
> Sent: Monday, September 21, 2009 12:46 PM
> To: pgsql-admin
> Subject: [ADMIN] Postgres restart
>
> Recently due to some problem(not yet diagnosed) , our DB server Postgres is
> getting very slow after few hours. We didnt changed any settings for 6
> months , so we dont know y its happening suddenly in this week. Our data
> folder is 118GB with 160 DBs. System is 2 Quad core with RAM 4GB. In last
> two days when it was restarted its performance is good but gradually going
> down. So few planned to restart the posgres process daily. Is it advisable
> to restart server daily ? since daily we can have 30 mins downtime. Please
> advise is it advisable or not?
>
> -Arvind S
>
> "Many of lifes failure are people who did not realize how close they were
> to
> success when they gave up."
> -Thomas Edison
>
>
>

--001636b42ebd1cb5d204741b5c91
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Thanks Scott. 1. Well, is this a
dedicated database server? YES, it runs only Postgres with some Back-up =
script for that DBs alone daily. 2. What O/S? CentOS (Linux vers=
ion 2.6.18-8.1.4.el5 (<a href=3D"mailto:mockbuild [at] builder6.centos.org">mock=
build [at] builder6.centos.org</a>) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-5=
2)) ) 

 3. What version of PG? =A08.3.7 4.=A0 Have you check out
memory usage? =A0When rebooted it has more then 2.5 GB free space but a=
fter few hours it will reach 50MB. This is usual in our DB server, since th=
is decrease never affected our performance for past years. And also for 5 m=
onths we never rebooted our system and also we had restart the postgres lik=
ely once in a month, before this problem. 

 5. Also, when was the last time you vacuumed the database(s)? As per=
advise from postgres team we are running full vaccum for every week and fr=
equently-used table(30) vacum daily. We have nearly 640 tables in each DB.<=
br>

 6. Is auto-vac on? Yes ( postgres: autovacuum launcher process runni=
ng) Is our problem is identifiable, from infrastructure side? <br=
>-Arvind S "Many of lifes failure are peo=
ple who did not realize how close they were to success when they gave up.&q=
uot; 

-Thomas Edison 
 <div class=3D"gmail_quote">On Mon, Sep 21, 2009 at 11:33 PM, Scott =
Whitney <<a href=3D"mailto:swhitney [at] journyx.com">swhit=
ney [at] journyx.com</a>> wrote: <blockquote class=3D"gmail_quote" =
style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8=
ex; padding-left: 1ex;">

There's an awful lot of information left out that would be very useful =
to 
help advise you. 
 
Restarting the postgres services on a daily basis is certainly nothing 
that's going to corrupt your data or hurt your system, PROVIDED that it=
is 
done correctly (ie: not killing the backend postmaster when something is<br=
>
happening, not hard-booting the system while a RAID card is trying to write=
 
its cache, etc.) 
 
However, I think you'd be postponing the problem. The better answer mig=
ht be 
to define and resolve the issue. 
 
"Its performance is good but gradually going down?" Well, is this=
a 
dedicated database server? What O/S? What version of PG? Have you check out=
 
memory usage? 4GB seems a bit low for the amount of data you're using. =
In a 
similar environment, I've got 12GB, and from time to time I'm pagin=
g. 
 
If "nothing changed" (TRULY, that is), you're most likely fin=
ding that 
you're either CPU, memory, or I/O bound, and the most likely culprits a=
re 
the last 2 unless you've suddenly started some massive queries that did=
n't 
happen a few weeks ago. 
 
Also, when was the last time you vacuumed the database(s)? Is auto-vac on? =
I 
know, I know, I'm not _supposed_ to have to perform a full vacuum and<b=
r>
analyze on my databases with auto-vac on, but if I don't, I run into<br=
>
performance problems, so I do that once per week, myself. 
<div><div></div><div class=3D"h5"> 
-----Original Message----- 
From: <a href=3D"mailto:pgsql-admin-owner [at] postgresql.org">pgsql-admin-owner=
[at] postgresql.org</a> 
[mailto:<a href=3D"mailto:pgsql-admin-owner [at] postgresql.org">pgsql-admin-own=
er [at] postgresql.org</a>] On Behalf Of S Arvind 
Sent: Monday, September 21, 2009 12:46 PM 
To: pgsql-admin 
Subject: [ADMIN] Postgres restart 
 
Recently due to some problem(not yet diagnosed) , our DB server Postgres is=
 
getting very slow after few hours. We didnt changed any settings for 6 
months , so we dont know y its happening suddenly in this week. Our data<br=
>
folder is 118GB with 160 DBs. System is 2 Quad core with RAM 4GB. In last<b=
r>
two days when it was restarted its performance is good but gradually going<=
br>
down. So few planned to restart the posgres process daily. Is it advisable<=
br>
to restart server daily ? since daily we can have 30 mins downtime. Please<=
br>
advise is it advisable or not? 
 
-Arvind S 
 
"Many of lifes failure are people who did not realize how close they w=
ere to 
success when they gave up." 
-Thomas Edison 
 
 
</div></div></blockquote></div> 

--001636b42ebd1cb5d204741b5c91--

The 2.5GB -> 50MB "problem" could mean a number of things. It could also
mean nothing at all. It's much more about the amount of swap (paging) space
that's being used at the time of the slowdown.

The fact that it runs fine for a few hour then degrades significantly would
point me in the direction of watching paging space consumption. You may have
added just one too many rows, making one query just big enough to make your
server start paging more than it ever used to do.

Check your vmstat and iostat information at the time of the slowdown, and
see if you can determine whether you're paging or waiting on storage. I
suspect you'll find that throwing hardware (memory, as a guess) at this
problem will solve it.

-----Original Message-----
From: S Arvind [mailto:arvindwill [at] gmail.com]
Sent: Monday, September 21, 2009 2:19 PM
To: Scott Whitney; pgsql-admin
Subject: Re: [ADMIN] Postgres restart

Thanks Scott.
1. Well, is this a dedicated database server?
YES, it runs only Postgres with some Back-up script for that DBs alone
daily.

2. What O/S?
CentOS (Linux version 2.6.18-8.1.4.el5 (mockbuild [at] builder6.centos.org) (gcc
version 4.1.1 20070105 (Red Hat 4.1.1-52)) )

3. What version of PG?
8.3.7

4. Have you check out memory usage?
When rebooted it has more then 2.5 GB free space but after few hours it
will reach 50MB. This is usual in our DB server, since this decrease never
affected our performance for past years. And also for 5 months we never
rebooted our system and also we had restart the postgres likely once in a
month, before this problem.

5. Also, when was the last time you vacuumed the database(s)?
As per advise from postgres team we are running full vaccum for every week
and frequently-used table(30) vacum daily. We have nearly 640 tables in each
DB.

6. Is auto-vac on?
Yes ( postgres: autovacuum launcher process running)

Is our problem is identifiable, from infrastructure side?

-Arvind S

"Many of lifes failure are people who did not realize how close they were to
success when they gave up."
-Thomas Edison

On Mon, Sep 21, 2009 at 11:33 PM, Scott Whitney <swhitney [at] journyx.com>
wrote:

There's an awful lot of information left out that would be very
useful to
help advise you.

Restarting the postgres services on a daily basis is certainly
nothing
that's going to corrupt your data or hurt your system, PROVIDED that
it is
done correctly (ie: not killing the backend postmaster when
something is
happening, not hard-booting the system while a RAID card is trying
to write
its cache, etc.)

However, I think you'd be postponing the problem. The better answer
might be
to define and resolve the issue.

"Its performance is good but gradually going down?" Well, is this a
dedicated database server? What O/S? What version of PG? Have you
check out
memory usage? 4GB seems a bit low for the amount of data you're
using. In a
similar environment, I've got 12GB, and from time to time I'm
paging.

If "nothing changed" (TRULY, that is), you're most likely finding
that
you're either CPU, memory, or I/O bound, and the most likely
culprits are
the last 2 unless you've suddenly started some massive queries that
didn't
happen a few weeks ago.

Also, when was the last time you vacuumed the database(s)? Is
auto-vac on? I
know, I know, I'm not _supposed_ to have to perform a full vacuum
and
analyze on my databases with auto-vac on, but if I don't, I run into
performance problems, so I do that once per week, myself.

-----Original Message-----
From: pgsql-admin-owner [at] postgresql.org
[mailto:pgsql-admin-owner [at] postgresql.org] On Behalf Of S Arvind
Sent: Monday, September 21, 2009 12:46 PM
To: pgsql-admin
Subject: [ADMIN] Postgres restart

Recently due to some problem(not yet diagnosed) , our DB server
Postgres is
getting very slow after few hours. We didnt changed any settings for
6
months , so we dont know y its happening suddenly in this week. Our
data
folder is 118GB with 160 DBs. System is 2 Quad core with RAM 4GB. In
last
two days when it was restarted its performance is good but gradually
going
down. So few planned to restart the posgres process daily. Is it
advisable
to restart server daily ? since daily we can have 30 mins downtime.
Please
advise is it advisable or not?

-Arvind S

"Many of lifes failure are people who did not realize how close they
were to
success when they gave up."
-Thomas Edison

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Mon, Sep 21, 2009 at 1:19 PM, S Arvind <arvindwill [at] gmail.com> wrote:

First, check performance after reboot versus no reboot and ensure it
really is making things faster. It could just be that in the morning
the db is faster with less load and as load increases it gets slower.

> 4.=A0 Have you check out memory usage?
> =A0When rebooted it has more then 2.5 GB free space but after few hours it
> will reach 50MB. This is usual in our DB server, since this decrease never
> affected our performance for past years. And also for 5 months we never
> rebooted our system and also we had restart the postgres likely once in a
> month, before this problem.

As the other Scott says, that's pretty normal. I'd pay more attention
to the amount of swap used, what vmstat and iostat and possibly top
has to say. How much IO wait, is it climbing, etc.

> 5. Also, when was the last time you vacuumed the database(s)?
> As per advise from postgres team we are running full vaccum for every week
> and frequently-used table(30) vacum daily. We have nearly 640 tables in e=
ach
> DB.

Vacuum full hasn't been recommended for quite some time, and is even
being considered for removal from 8.5 (or renaming it to something
else, etc). Vacuum FULL can bloat indexes. But this wouldn't get
fixed by a reboot. Vacuum full followed by reindex on certain tables
with certain access patterns may be required, but almost NEVER on the
whole db.

> 6. Is auto-vac on?
> Yes ( postgres: autovacuum launcher process running)

Is it keeping up? I had a server where autovac couldn't keep up at
the default 20ms sleep and had to drop it down to 0. But again a
reboot won't fix that.

> Is our problem is identifiable, from infrastructure side?

Maybe. You need to get familiar with system monitoring tools so you
can watch what's happening to the system. tail system logs in
/var/log, run vmstat, iostat, top etc. Those tools can tell you a lot
about what's happening if you know how to read them.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--0016368e28dfc178fa0474288b81
Content-Type: text/plain; charset=ISO-8859-1

[postgres [at] database2 ~]$ vmstat 2 6
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id
wa st
2 8 672 32680 137840 3049972 0 0 274 1723 0 3 8 4 80
9 0
1 6 672 44704 137708 3036556 0 0 21248 27210 1717 3238 18 4 58
20 0
2 5 672 63144 137792 3043872 0 0 7234 13382 1361 2946 12 5 66
17 0
0 4 672 52600 137924 3056888 0 0 8614 38788 1636 2631 10 4 62
24 0
1 5 672 46388 138036 3056240 0 0 9772 26620 1417 4211 16 4 61
18 0
3 1 672 48484 138060 3058532 0 0 12360 428 1830 3565 13 4 60
22 0

This is the o/p of vmstat in average performance time. Is it seems any
problem as i am new to this statics?

Thanks,
Arvind S

"Many of lifes failure are people who did not realize how close they were to
success when they gave up."
-Thomas Edison

On Tue, Sep 22, 2009 at 12:53 AM, Scott Whitney <swhitney [at] journyx.com>wrote:

> The 2.5GB -> 50MB "problem" could mean a number of things. It could also
> mean nothing at all. It's much more about the amount of swap (paging) space
> that's being used at the time of the slowdown.
>
> The fact that it runs fine for a few hour then degrades significantly would
> point me in the direction of watching paging space consumption. You may
> have
> added just one too many rows, making one query just big enough to make your
> server start paging more than it ever used to do.
>
> Check your vmstat and iostat information at the time of the slowdown, and
> see if you can determine whether you're paging or waiting on storage. I
> suspect you'll find that throwing hardware (memory, as a guess) at this
> problem will solve it.
>
> -----Original Message-----
> From: S Arvind [mailto:arvindwill [at] gmail.com]
> Sent: Monday, September 21, 2009 2:19 PM
> To: Scott Whitney; pgsql-admin
> Subject: Re: [ADMIN] Postgres restart
>
> Thanks Scott.
> 1. Well, is this a dedicated database server?
> YES, it runs only Postgres with some Back-up script for that DBs alone
> daily.
>
> 2. What O/S?
> CentOS (Linux version 2.6.18-8.1.4.el5 (mockbuild [at] builder6.centos.org)
> (gcc
> version 4.1.1 20070105 (Red Hat 4.1.1-52)) )
>
> 3. What version of PG?
> 8.3.7
>
> 4. Have you check out memory usage?
> When rebooted it has more then 2.5 GB free space but after few hours it
> will reach 50MB. This is usual in our DB server, since this decrease never
> affected our performance for past years. And also for 5 months we never
> rebooted our system and also we had restart the postgres likely once in a
> month, before this problem.
>
> 5. Also, when was the last time you vacuumed the database(s)?
> As per advise from postgres team we are running full vaccum for every week
> and frequently-used table(30) vacum daily. We have nearly 640 tables in
> each
> DB.
>
> 6. Is auto-vac on?
> Yes ( postgres: autovacuum launcher process running)
>
> Is our problem is identifiable, from infrastructure side?
>
> -Arvind S
>
>
>
> "Many of lifes failure are people who did not realize how close they were
> to
> success when they gave up."
> -Thomas Edison
>
>
>
> On Mon, Sep 21, 2009 at 11:33 PM, Scott Whitney <swhitney [at] journyx.com>
> wrote:
>
>
> There's an awful lot of information left out that would be very
> useful to
> help advise you.
>
> Restarting the postgres services on a daily basis is certainly
> nothing
> that's going to corrupt your data or hurt your system, PROVIDED that
> it is
> done correctly (ie: not killing the backend postmaster when
> something is
> happening, not hard-booting the system while a RAID card is trying
> to write
> its cache, etc.)
>
> However, I think you'd be postponing the problem. The better answer
> might be
> to define and resolve the issue.
>
> "Its performance is good but gradually going down?" Well, is this a
> dedicated database server? What O/S? What version of PG? Have you
> check out
> memory usage? 4GB seems a bit low for the amount of data you're
> using. In a
> similar environment, I've got 12GB, and from time to time I'm
> paging.
>
> If "nothing changed" (TRULY, that is), you're most likely finding
> that
> you're either CPU, memory, or I/O bound, and the most likely
> culprits are
> the last 2 unless you've suddenly started some massive queries that
> didn't
> happen a few weeks ago.
>
> Also, when was the last time you vacuumed the database(s)? Is
> auto-vac on? I
> know, I know, I'm not _supposed_ to have to perform a full vacuum
> and
> analyze on my databases with auto-vac on, but if I don't, I run into
> performance problems, so I do that once per week, myself.
>
>
> -----Original Message-----
> From: pgsql-admin-owner [at] postgresql.org
> [mailto:pgsql-admin-owner [at] postgresql.org] On Behalf Of S Arvind
> Sent: Monday, September 21, 2009 12:46 PM
> To: pgsql-admin
> Subject: [ADMIN] Postgres restart
>
> Recently due to some problem(not yet diagnosed) , our DB server
> Postgres is
> getting very slow after few hours. We didnt changed any settings for
> 6
> months , so we dont know y its happening suddenly in this week. Our
> data
> folder is 118GB with 160 DBs. System is 2 Quad core with RAM 4GB. In
> last
> two days when it was restarted its performance is good but gradually
> going
> down. So few planned to restart the posgres process daily. Is it
> advisable
> to restart server daily ? since daily we can have 30 mins downtime.
> Please
> advise is it advisable or not?
>
> -Arvind S
>
> "Many of lifes failure are people who did not realize how close they
> were to
> success when they gave up."
> -Thomas Edison
>
>
>
>
>
>
>

--0016368e28dfc178fa0474288b81
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

[postgres [at] database2 ~]$ vmstat 2 6 procs -----------memory---------- ---=
swap-- -----io---- --system-- -----cpu------ =A0r=A0 b=A0=A0 swpd=A0=A0 =
free=A0=A0 buff=A0 cache=A0=A0 si=A0=A0 so=A0=A0=A0 bi=A0=A0=A0 bo=A0=A0 in=
=A0=A0 cs us sy id wa st =A02=A0 8=A0=A0=A0 672=A0 32680 137840 3049972=
=A0=A0=A0 0=A0=A0=A0 0=A0=A0 274=A0 1723=A0=A0=A0 0=A0=A0=A0 3=A0 8=A0 4 80=
=A0 9=A0 0 

=A01=A0 6=A0=A0=A0 672=A0 44704 137708 3036556=A0=A0=A0 0=A0=A0=A0 0 21248 =
27210 1717 3238 18=A0 4 58 20=A0 0 =A02=A0 5=A0=A0=A0 672=A0 63144 13779=
2 3043872=A0=A0=A0 0=A0=A0=A0 0=A0 7234 13382 1361 2946 12=A0 5 66 17=A0 0<=
br>=A00=A0 4=A0=A0=A0 672=A0 52600 137924 3056888=A0=A0=A0 0=A0=A0=A0 0=A0 =
8614 38788 1636 2631 10=A0 4 62 24=A0 0 

=A01=A0 5=A0=A0=A0 672=A0 46388 138036 3056240=A0=A0=A0 0=A0=A0=A0 0=A0 977=
2 26620 1417 4211 16=A0 4 61 18=A0 0 =A03=A0 1=A0=A0=A0 672=A0 48484 138=
060 3058532=A0=A0=A0 0=A0=A0=A0 0 12360=A0=A0 428 1830 3565 13=A0 4 60 22=
=A0 0 This is the o/p of vmstat in average performance time. Is it s=
eems any problem as i am new to this statics? 

 Thanks, Arvind S "Many of lifes failur=
e are people who did not realize how close they were to success when they g=
ave up." -Thomas Edison 
 <div class=3D"gmail_quote">On Tue, Sep 22, 2009 at 12:53 AM, Scott =
Whitney <<a href=3D"mailto:swhitney [at] journyx.com">swhit=
ney [at] journyx.com</a>> wrote: <blockquote class=3D"gmail_quote" =
style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8=
ex; padding-left: 1ex;">

The 2.5GB -> 50MB "problem" could mean a number of things. It =
could also 
mean nothing at all. It's much more about the amount of swap (paging) s=
pace 
that's being used at the time of the slowdown. 
 
The fact that it runs fine for a few hour then degrades significantly would=
 
point me in the direction of watching paging space consumption. You may hav=
e 
added just one too many rows, making one query just big enough to make your=
 
server start paging more than it ever used to do. 
 
Check your vmstat and iostat information at the time of the slowdown, and<b=
r>
see if you can determine whether you're paging or waiting on storage. I=
 
suspect you'll find that throwing hardware (memory, as a guess) at this=
 
problem will solve it. 
<div><div></div><div class=3D"h5"> 
-----Original Message----- 
From: S Arvind [mailto:<a href=3D"mailto:arvindwill [at] gmail.com">arvindwill [at] g=
mail.com</a>] 
Sent: Monday, September 21, 2009 2:19 PM 
To: Scott Whitney; pgsql-admin 
Subject: Re: [ADMIN] Postgres restart 
 
Thanks Scott. 
1. Well, is this a dedicated database server? 
YES, it runs only Postgres with some Back-up script for that DBs alone 
daily. 
 
2. What O/S? 
CentOS (Linux version 2.6.18-8.1.4.el5 (<a href=3D"mailto:mockbuild [at] builder=
6.centos.org">mockbuild [at] builder6.centos.org</a>) (gcc 
version 4.1.1 20070105 (Red Hat 4.1.1-52)) ) 
 
3. What version of PG? 
=A08.3.7 
 
4. =A0Have you check out memory usage? 
=A0When rebooted it has more then 2.5 GB free space but after few hours it<=
br>
will reach 50MB. This is usual in our DB server, since this decrease never<=
br>
affected our performance for past years. And also for 5 months we never 
rebooted our system and also we had restart the postgres likely once in a<b=
r>
month, before this problem. 
 
5. Also, when was the last time you vacuumed the database(s)? 
As per advise from postgres team we are running full vaccum for every week<=
br>
and frequently-used table(30) vacum daily. We have nearly 640 tables in eac=
h 
DB. 
 
6. Is auto-vac on? 
Yes ( postgres: autovacuum launcher process running) 
 
Is our problem is identifiable, from infrastructure side? 
 
-Arvind S 
 
 
 
"Many of lifes failure are people who did not realize how close they w=
ere to 
success when they gave up." 
-Thomas Edison 
 
 
 
On Mon, Sep 21, 2009 at 11:33 PM, Scott Whitney <<a href=3D"mailto:swhit=
ney [at] journyx.com">swhitney [at] journyx.com</a>> 
wrote: 
 
 
=A0 =A0 =A0 =A0There's an awful lot of information left out that would=
be very 
useful to 
=A0 =A0 =A0 =A0help advise you. 
 
=A0 =A0 =A0 =A0Restarting the postgres services on a daily basis is certai=
nly 
nothing 
=A0 =A0 =A0 =A0that's going to corrupt your data or hurt your system, =
PROVIDED that 
it is 
=A0 =A0 =A0 =A0done correctly (ie: not killing the backend postmaster when=
 
something is 
=A0 =A0 =A0 =A0happening, not hard-booting the system while a RAID card is=
trying 
to write 
=A0 =A0 =A0 =A0its cache, etc.) 
 
=A0 =A0 =A0 =A0However, I think you'd be postponing the problem. The b=
etter answer 
might be 
=A0 =A0 =A0 =A0to define and resolve the issue. 
 
=A0 =A0 =A0 =A0"Its performance is good but gradually going down?&quo=
t; Well, is this a 
=A0 =A0 =A0 =A0dedicated database server? What O/S? What version of PG? Ha=
ve you 
check out 
=A0 =A0 =A0 =A0memory usage? 4GB seems a bit low for the amount of data yo=
u're 
using. In a 
=A0 =A0 =A0 =A0similar environment, I've got 12GB, and from time to ti=
me I'm 
paging. 
 
=A0 =A0 =A0 =A0If "nothing changed" (TRULY, that is), you're=
most likely finding 
that 
=A0 =A0 =A0 =A0you're either CPU, memory, or I/O bound, and the most l=
ikely 
culprits are 
=A0 =A0 =A0 =A0the last 2 unless you've suddenly started some massive =
queries that 
didn't 
=A0 =A0 =A0 =A0happen a few weeks ago. 
 
=A0 =A0 =A0 =A0Also, when was the last time you vacuumed the database(s)? =
Is 
auto-vac on? I 
=A0 =A0 =A0 =A0know, I know, I'm not _supposed_ to have to perform a f=
ull vacuum 
and 
=A0 =A0 =A0 =A0analyze on my databases with auto-vac on, but if I don'=
t, I run into 
=A0 =A0 =A0 =A0performance problems, so I do that once per week, myself.<b=
r>
 
 
=A0 =A0 =A0 =A0-----Original Message----- 
=A0 =A0 =A0 =A0From: <a href=3D"mailto:pgsql-admin-owner [at] postgresql.org">p=
gsql-admin-owner [at] postgresql.org</a> 
=A0 =A0 =A0 =A0[mailto:<a href=3D"mailto:pgsql-admin-owner [at] postgresql.org"=
>pgsql-admin-owner [at] postgresql.org</a>] On Behalf Of S Arvind 
=A0 =A0 =A0 =A0Sent: Monday, September 21, 2009 12:46 PM 
=A0 =A0 =A0 =A0To: pgsql-admin 
=A0 =A0 =A0 =A0Subject: [ADMIN] Postgres restart 
 
=A0 =A0 =A0 =A0Recently due to some problem(not yet diagnosed) , our DB se=
rver 
Postgres is 
=A0 =A0 =A0 =A0getting very slow after few hours. We didnt changed any set=
tings for 
6 
=A0 =A0 =A0 =A0months , so we dont know y its happening suddenly in this w=
eek. Our 
data 
=A0 =A0 =A0 =A0folder is 118GB with 160 DBs. System is 2 Quad core with RA=
M 4GB. In 
last 
=A0 =A0 =A0 =A0two days when it was restarted its performance is good but =
gradually 
going 
=A0 =A0 =A0 =A0down. So few planned to restart the posgres process daily. =
Is it 
advisable 
=A0 =A0 =A0 =A0to restart server daily ? since daily we can have 30 mins d=
owntime. 
Please 
=A0 =A0 =A0 =A0advise is it advisable or not? 
 
=A0 =A0 =A0 =A0-Arvind S 
 
=A0 =A0 =A0 =A0"Many of lifes failure are people who did not realize =
how close they 
were to 
=A0 =A0 =A0 =A0success when they gave up." 
=A0 =A0 =A0 =A0-Thomas Edison 
 
 
 
 
 
 
</div></div></blockquote></div> 

--0016368e28dfc178fa0474288b81--

Postgres restart

Re: Postgres restart

Re: Postgres restart

Re: Postgres restart

Re: Postgres restart

Re: Postgres restart