Re: database slowdown

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