Re: database slowdown

Purge meaning, stop postgresql, rm -rf $PGDATA, recreate the
environment, and start up postgresql again.

On Tue, Jan 18, 2011 at 8:32 AM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> In response to Mag Gam <magawake(at)gmail(dot)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(at)squeakycode(dot)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(at)postgresql(dot)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/
>