Skip site navigation (1) Skip section navigation (2)

Re: Seeking performance advice and explanation for high I/O on 8.3

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Scott Otis <scott(dot)otis(at)intand(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Seeking performance advice and explanation for high I/O on 8.3
Date: 2009-09-03 17:12:37
Message-ID: 4A9FF905.6080603@squeakycode.net (view raw or flat)
Thread:
Lists: pgsql-performance
Scott Otis wrote:
> I agree that they don't make sense - part of the reason I am looking for 
> help :)
> 
> I am using iostat to get those numbers ( which I specify to average over 
> 5 min then collect to display in Cacti ).
> 
> 2 processes are taking up a good deal of CPU - the postgres stats 
> collector and autovacuum ones.  Both of those are using a lot of 1 core 
> each.
> 
> I am not familiar with a dd test - what is that?
> 
> Thanks,
> 
> Scott
> 
> On Sep 3, 2009, at 8:03 AM, "Andy Colson" <andy(at)squeakycode(dot)net> wrote:
> 
>> Scott Otis wrote:
>>> Would love to get some advice on how to change my conf settings / 
>>> setup to get better I/O performance.
>>> Total I/O (these number are pretty constant throughout the day):
>>> Reads: ~ 100 / sec for about 2.6 Mb/sec
>>> Writes: ~ 400 /sec for about 46.1Mb/sec
>>> Most of the SQL happening is selects – very little inserts, updates 
>>> and deletes comparatively.
>>
>> Maybe I'm wrong, but those two don't seem to jive.  You say its mostly 
>> selects, but you show higher writes per second.
>>
>> Does freebsd have a vmstat or iostat?  How did you get the numbers 
>> above?  How's the cpu's look?  (are they pegged?)
>>
>> The io stats above seem low  (reading 2 meg a second is a tiny 
>> fraction of what your system should be capable of).  Have you tried a 
>> dd test?
>>
>> -Andy

Please keep the list included so others may help.


the dd test:

http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm


I think Ivan is right, the 2 meg a second is probably because most of the reads are from cache.  But he and I looked at the writes differently.  If we ignore the 400/sec, and just read 46 meg a second (assuming you meant megabyte and not megabit) then, that's pretty slow (for sequential writing) -- which the dd test will measure your sequential read and write speed.

Ivan asked a good question:
By the way, why do you think your setup is slow? Is your application slow and you think your database is the reason?


-Andy

In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2009-09-03 18:25:44
Subject: Re: Slow select times on select with xpath
Previous:From: Kenneth CoxDate: 2009-09-03 16:13:36
Subject: Re: partition query using Seq Scan even when index is present

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group