Mike,

On 8/10/06 4:09 AM, "Michael Stone" <mstone+postgres@mathom.us> wrote:

> On Wed, Aug 09, 2006 at 08:29:13PM -0700, Steve Poe wrote:
>> I tried as you suggested and my performance dropped by 50%. I went from
>> a 32 TPS to 16. Oh well.
>
> If you put data & xlog on the same array, put them on seperate
> partitions, probably formatted differently (ext2 on xlog).

If he's doing the same thing on both systems (Sun and HP) and the HP
performance is dramatically worse despite using more disks and having faster
CPUs and more RAM, ISTM the problem isn't the configuration.

Add to this the fact that the Sun machine is CPU bound while the HP is I/O
wait bound and I think the problem is the disk hardware or the driver
therein.

- Luke



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Thu, 2006-08-10 at 10:15, Luke Lonergan wrote:
> Mike,
>
> On 8/10/06 4:09 AM, "Michael Stone" <mstone+postgres@mathom.us> wrote:
>
> > On Wed, Aug 09, 2006 at 08:29:13PM -0700, Steve Poe wrote:
> >> I tried as you suggested and my performance dropped by 50%. I went from
> >> a 32 TPS to 16. Oh well.
> >
> > If you put data & xlog on the same array, put them on seperate
> > partitions, probably formatted differently (ext2 on xlog).
>
> If he's doing the same thing on both systems (Sun and HP) and the HP
> performance is dramatically worse despite using more disks and having faster
> CPUs and more RAM, ISTM the problem isn't the configuration.
>
> Add to this the fact that the Sun machine is CPU bound while the HP is I/O
> wait bound and I think the problem is the disk hardware or the driver
> therein.

I agree. The problem here looks to be the RAID controller.

Steve, got access to a different RAID controller to test with?

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Scott,

I *could* rip out the LSI MegaRAID 2X from my Sun box. This belongs to me
for testing. but I don't know if it will fit in the DL385. Do they have
full-heigth/length slots? I've not worked on this type of box before. I was
thinking this is the next step. In the meantime, I've discovered their no
email support for them so I am hoping find a support contact through the
sales rep that this box was purchased from.

Steve

On 8/10/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
>
> On Thu, 2006-08-10 at 10:15, Luke Lonergan wrote:
> > Mike,
> >
> > On 8/10/06 4:09 AM, "Michael Stone" <mstone+postgres@mathom.us> wrote:
> >
> > > On Wed, Aug 09, 2006 at 08:29:13PM -0700, Steve Poe wrote:
> > >> I tried as you suggested and my performance dropped by 50%. I went
> from
> > >> a 32 TPS to 16. Oh well.
> > >
> > > If you put data & xlog on the same array, put them on seperate
> > > partitions, probably formatted differently (ext2 on xlog).
> >
> > If he's doing the same thing on both systems (Sun and HP) and the HP
> > performance is dramatically worse despite using more disks and having
> faster
> > CPUs and more RAM, ISTM the problem isn't the configuration.
> >
> > Add to this the fact that the Sun machine is CPU bound while the HP is
> I/O
> > wait bound and I think the problem is the disk hardware or the driver
> > therein.
>
> I agree. The problem here looks to be the RAID controller.
>
> Steve, got access to a different RAID controller to test with?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

On Thu, Aug 10, 2006 at 07:09:38AM -0400, Michael Stone wrote:
> On Wed, Aug 09, 2006 at 08:29:13PM -0700, Steve Poe wrote:
> >I tried as you suggested and my performance dropped by 50%. I went from
> >a 32 TPS to 16. Oh well.
>
> If you put data & xlog on the same array, put them on seperate
> partitions, probably formatted differently (ext2 on xlog).

Got any data to back that up?

The problem with seperate partitions is that it means more head movement
for the drives. If it's all one partition the pg_xlog data will tend to
be interspersed with the heap data, meaning less need for head
repositioning.

Of course, if ext2 provided enough of a speed improvement over ext3 with
data=writeback then it's possible that this would be a win, though if
the controller is good enough to make putting pg_xlog on the same array
as $PGDATA a win, I suspect it would make up for most filesystem
performance issues associated with pg_xlog as well.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Jim,

I have to say Michael is onto something here to my surprise. I partitioned
the RAID10 on the SmartArray 642 adapter into two parts, PGDATA formatted
with XFS and pg_xlog as ext2. Performance jumped up to median of 98 TPS. I
could reproduce the similar result with the LSI MegaRAID 2X adapter as well
as with my own 4-disc drive array.

The problem lies with the HP SmartArray 6i adapter and/or the internal SCSI
discs. Putting the pg_xlog on it kills the performance.

Steve

On 8/14/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
>
> On Thu, Aug 10, 2006 at 07:09:38AM -0400, Michael Stone wrote:
> > On Wed, Aug 09, 2006 at 08:29:13PM -0700, Steve Poe wrote:
> > >I tried as you suggested and my performance dropped by 50%. I went from
> > >a 32 TPS to 16. Oh well.
> >
> > If you put data & xlog on the same array, put them on seperate
> > partitions, probably formatted differently (ext2 on xlog).
>
> Got any data to back that up?
>
> The problem with seperate partitions is that it means more head movement
> for the drives. If it's all one partition the pg_xlog data will tend to
> be interspersed with the heap data, meaning less need for head
> repositioning.
>
> Of course, if ext2 provided enough of a speed improvement over ext3 with
> data=writeback then it's possible that this would be a win, though if
> the controller is good enough to make putting pg_xlog on the same array
> as $PGDATA a win, I suspect it would make up for most filesystem
> performance issues associated with pg_xlog as well.
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

On Mon, Aug 14, 2006 at 10:38:41AM -0500, Jim C. Nasby wrote:
>Got any data to back that up?

yes. that I'm willing to dig out? no.

>The problem with seperate partitions is that it means more head movement
>for the drives. If it's all one partition the pg_xlog data will tend to
>be interspersed with the heap data, meaning less need for head
>repositioning.

The pg_xlog files will tend to be created up at the front of the disk
and just sit there. Any affect the positioning has one way or the other
isn't going to be measurable/repeatable. With a write cache for pg_xlog
the positioning isn't really going to matter anyway, since you don't
have to wait for a seek to do the write.

From what I've observed in testing, I'd guess that the issue is that
certain filesystem operations (including, possibly, metadata operations)
are handled in order. If you have xlog on a seperate partition there
will never be anything competing with a log write on the server side,
which won't necessarily be true on a shared filesystem. Even if you have
a battery backed write cache, you might still have to wait a relatively
long time for the pg_xlog data to be written out if there's already a
lot of other stuff in a filesystem write queue.

Mike Stone

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Mon, Aug 14, 2006 at 08:51:09AM -0700, Steve Poe wrote:
> Jim,
>
> I have to say Michael is onto something here to my surprise. I partitioned
> the RAID10 on the SmartArray 642 adapter into two parts, PGDATA formatted
> with XFS and pg_xlog as ext2. Performance jumped up to median of 98 TPS. I
> could reproduce the similar result with the LSI MegaRAID 2X adapter as well
> as with my own 4-disc drive array.
>
> The problem lies with the HP SmartArray 6i adapter and/or the internal SCSI
> discs. Putting the pg_xlog on it kills the performance.

Wow, interesting. IIRC, XFS is lower performing than ext3, so if your
previous tests were done with XFS, that might be part of it. But without
a doubt, if you don't have a good raid controller you don't want to try
combining pg_xlog with PGDATA.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Mon, Aug 14, 2006 at 12:05:46PM -0500, Jim C. Nasby wrote:
>Wow, interesting. IIRC, XFS is lower performing than ext3,

For xlog, maybe. For data, no. Both are definately slower than ext2 for
xlog, which is another reason to have xlog on a small filesystem which
doesn't need metadata journalling.

Mike Stone

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Mon, Aug 14, 2006 at 01:03:41PM -0400, Michael Stone wrote:
> On Mon, Aug 14, 2006 at 10:38:41AM -0500, Jim C. Nasby wrote:
> >Got any data to back that up?
>
> yes. that I'm willing to dig out? no.

Well, I'm not digging hard numbers out either, so that's fair. But it
would be very handy if people posted results from any testing they're
doing as part of setting up new hardware. Actually, a wiki would
probably be ideal for this...

> >The problem with seperate partitions is that it means more head movement
> >for the drives. If it's all one partition the pg_xlog data will tend to
> >be interspersed with the heap data, meaning less need for head
> >repositioning.
>
> The pg_xlog files will tend to be created up at the front of the disk
> and just sit there. Any affect the positioning has one way or the other
> isn't going to be measurable/repeatable. With a write cache for pg_xlog
> the positioning isn't really going to matter anyway, since you don't
> have to wait for a seek to do the write.

Certainly... my contention is that if you have a good controller that's
caching writes then drive layout basically won't matter at all, because
the controller will just magically make things optimal.

> From what I've observed in testing, I'd guess that the issue is that
> certain filesystem operations (including, possibly, metadata operations)
> are handled in order. If you have xlog on a seperate partition there
> will never be anything competing with a log write on the server side,
> which won't necessarily be true on a shared filesystem. Even if you have
> a battery backed write cache, you might still have to wait a relatively
> long time for the pg_xlog data to be written out if there's already a
> lot of other stuff in a filesystem write queue.

Well, if the controller is caching with a BBU, I'm not sure that order
matters anymore, because the controller should be able to re-order at
will. Theoretically. But this is why having some actual data posted
somewhere would be great.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Mon, Aug 14, 2006 at 01:09:04PM -0400, Michael Stone wrote:
> On Mon, Aug 14, 2006 at 12:05:46PM -0500, Jim C. Nasby wrote:
> >Wow, interesting. IIRC, XFS is lower performing than ext3,
>
> For xlog, maybe. For data, no. Both are definately slower than ext2 for
> xlog, which is another reason to have xlog on a small filesystem which
> doesn't need metadata journalling.

Are 'we' sure that such a setup can't lose any data? I'm worried about
files getting lost when they get written out before the metadata does.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match