On Mon, Sep 21, 2009 at 1:19 PM, S Arvind <arvindwill@xxxxxxxxx> 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. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
