Re: database slowdown

> 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
>>
>


On 1/18/2011 7:11 AM, Mag Gam wrote:
 > 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 ?
 >
 >
 >


When you say "Database performance is bad"... are you doing mostly read
or mostly write operations?

Do you vacuum?

Idle in transaction is probably bad.  It means a process has started a
transaction and not commit.  For any rows that transaction touched PG
must keep its row versions alive, plus any new versions of the same row.
  It can lead to excess memory usage, slow table reads (because not only
does PG have to scan the rows in a table, it has to scan the versions of
each row).  It'll also block vacuum's, so you wont be reclaiming disk
space and your database will just grow and grow.

I realize I never touched on your actual question about file systems.  I
dont think with a two disk mirror you are going to stress your FS too
much.  I have seen benchmarks give the edge to XFS.  I dont think it'll
even be in the 10's of percentages better.  I use XFS myself, so dont
really have any experience with ext3.  Its also important to have the
memory usage setup well so PG can cache as much data as possible.
(shared_buffers and effective_cache_size are probably most important)

select * from pg_locks;

it'll tell you if one process (that maybe has not commit?!) is holding a
lock that other processes are waiting for.

PG works best when you run small transactions, commit, and let
autovacuum do its magic.

-Andy