[postg...@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 <swhit...@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:arvindw...@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 (mockbu...@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 <swhit...@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-ow...@postgresql.org > [mailto:pgsql-admin-ow...@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 > > > > > > >
