Re: database slowdown

In response to Mag Gam <magawake@gmail.com>:

> Hi Andy,
>
> No, I don't shrink the database. I simply purge the whole thing and
> then let it populate again. The data isn't too critical.

What does "purge" mean?  Are you doing an SQL DELETE, or a TRUNCATE,
or dropping the DB and recreating?

Each of these functions differently behind the scenes, in particular
DELETE will not actually free up the space used by the records you
deleted right away, and depending on many other factors, may be the
cause of your problems.

>
> The disks I have are internal SAS disks. I get around 150MB/sec write
> and 250MB/sec read. Its a RAID1 .
>
> ps does show idle in transactions. I've never checked pg_locks. How
> would I do that ?
>
>
>
> On Sun, Jan 16, 2011 at 12:28 PM, Andy Colson <andy@squeakycode.net> wrote:
> > On 01/16/2011 10:44 AM, Mag Gam wrote:
> >>
> >> I am running Redhat 5.2 Linux with Postgresql 8.4.4;
> >>
> >> When my disk space is 90% free the database performance is very good.
> >> However, when it reaches close to 20% free the database performance is
> >> bad. I know its bad because I see a lot of 'D' next to 'postgresql'
> >> process when running top and I see a lot 'WAITING' in the 'ps' tree. I
> >> was wondering if there are any filesystem tweaks I can do to make
> >> postgresql run smoother. I am currently using ext3. Should I consider
> >> using a different file system for my database?
> >>
> >
> > I doubt its a disk space problem.  Sounds like a cache/disk io problem.
> >  When you are using 80% space, do you do something to get it back to 90%
> > free (ie shrink the db)?
> >
> > When the db is small, it fits in ram, and read's will be very quick.  Once
> > your db gets bigger than cache it'll have to start using a lot more disk io.
> >
> > What kind of disk IO throughput do yo have?  What kind of disk system is
> > this? (raid, scsi, etc)
> > Does ps ever show 'idle in transaction'?
> > Have you checked pg_locks to see what you are waiting on?
> > Have you watched vmstat while its at 80% full vs when its at 80% free?
> >  (does the iowait go up?)
> >
> >
> > -Andy
> >
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/