Re: Postgres restart

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



[postgres@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@xxxxxxxxxxx> 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@xxxxxxxxx]
Sent: Monday, September 21, 2009 2:19 PM
To: Scott Whitney; pgsql-admin
Subject: 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@xxxxxxxxxxxxxxxxxxx) (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@xxxxxxxxxxx>
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@xxxxxxxxxxxxxx
       [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of S Arvind
       Sent: Monday, September 21, 2009 12:46 PM
       To: pgsql-admin
       Subject: 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








[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux