Re: strange performance regression between 7.4 and 8.1 - Mailing list pgsql-performance

On Thu, 1 Mar 2007, Alex Deucher wrote:

> On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
>> On Thu, 1 Mar 2007, Alex Deucher wrote:
>>
>> >> >> Postgresql might be choosing a bad plan because your
>> >> effective_cache_size
>> >> >> is
>> >> >> way off (it's the default now right?).  Also, what was the block
>> >> read/write
>> >> >
>> >> > yes it's set to the default.
>> >> >
>> >> >> speed of the SAN from your bonnie tests?  Probably want to tune
>> >> >> random_page_cost as well if it's also at the default.
>> >> >>
>> >> >
>> >> >                   ------Sequential Output------ --Sequential Input-
>> >> > --Random-
>> >> >                   -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
>> >> > --Seeks--
>> >> > Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP
>> >> /sec
>> >> > %CP
>> >> > luna12-san   16000M 58896  91 62931   9 35870   5 54869  82 145504 13
>> >> 397.7
>> >> > 0
>> >> >
>> >>
>> >> So, you're getting 62MB/s writes and 145MB/s reads.  Just FYI, that
>> write
>> >> speed is about the same as my single SATA drive write speed on my
>> >> workstation,
>> >> so not that great.  The read speed is decent, though and with that sort
>> of
>> >> read performance, you might want to lower random_page_cost to something
>> >> like
>> >> 2.5 or 2 so the planner will tend to prefer index scans.
>> >>
>> >
>> > Right, but the old box was getting ~45MBps on both reads and writes,
>> > so it's an improvement for me :)  Thanks for the advice, I'll let you
>> > know how it goes.
>>
>> Do you think that is because you have a different interface between you and
>> the SAN?  ~45MBps is pretty slow - your average 7200RPM ATA133 drive can do
>> that and costs quite a bit less than a SAN.
>>
>> Is the SAN being shared between the database servers and other servers?
>> Maybe
>> it was just random timing that gave you the poor write performance on the
>> old
>> server which might be also yielding occassional poor performance on the new
>> one.
>>
>
> The direct attached scsi discs on the old database server we getting
> 45MBps not the SAN.  The SAN got 62/145Mbps, which is not as bad.  We
> have 4 servers on the SAN each with it's own 4 GBps FC link via an FC
> switch.  I'll try and re-run the numbers when the servers are idle
> this weekend.

Sorry, I thought the old server was also attached to the SAN.  My fault for
not hanging onto the entire email thread.

I think you're mixing and matching your capitol and lower case Bs in your
sentence above though. :-)

I suspect what you really mean is The SAN got 62/145MBps (megabytes/sec) and
teh FC link is 4Gbps (gigabits/sec) or 500MBps.  Is that correct?  If so, and
seeing that you think there are 105 spindles on the SAN, I'd say you're either
maxxing out the switch fabric of the SAN with your servers or you have a
really poorly performing SAN in general, or you just misunderstood the .

As a comparison With 8 WD Raptors configured in a RAID10 with normal ext3 I
get about 160MB/s write and 305MB/s read performance.  Hopefully the SAN has
lots of other super nifty features that make up for the poor performance. :-(

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954