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:
>
> > 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.
>
> 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. :-)

whoops :)

>
> 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. :-(
>

It's big and reliable (and compared to lots of others, relatively
inexpensive) which is why we bought it. We bought it mostly as a huge
file store. The RAID groups on the SAN were set up for maximum
capacity rather than for performance. Using it for the databases just
came up recently.

Alex