Re: Postgres restart

[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(dot)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(dot)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(dot)centos(dot)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(dot)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(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)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
>
>
>
>
>
>
>