Re: 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
>
>
>