Re: database slowdown

From: Mag Gam <magawake(at)gmail(dot)com>
To: Andy Colson <andy(at)squeakycode(dot)net>
Cc: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: Re: database slowdown
Date: 2011-01-18 13:11:58
Message-ID: AANLkTikLUvUNtFnSgA5CtaS_m_gTnUodSCHWUWpagULw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2011-01-18 13:32:52 Re: database slowdown
Previous Message Dmitriy Igrishin 2011-01-18 11:55:36 Re: libpq: multiple commands within single query