Re: database slowdown

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.

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
>