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

On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> 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. :-)

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