Re: strange performance regression between 7.4 and 8.1

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.

Alex