On Mon, Sep 21, 2009 at 1:19 PM, S Arvind <arvindwill@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. 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.
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 each
> 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.