Re: strange performance regression between 7.4 and 8.1

On 3/1/07, Jeff Frost <jeff(at)frostconsultingllc(dot)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