Re: Some Solaris notes, and an invitation

Hi,

I currently have on loan to me a Sun StorEdge A5200. It's a 22-disk
array that I currently have configured with Veritas and RAID 1+0
(which is how our production environment is set). The A5200 is
attached by fibre.

I wanted to do some tests to find out where there might be
bottle-necks in our current config. Nothing too surprising, but I
thought I'd share these results as they might be useful to others.

I tested with 7.2.1 compiled as a 64-bit application with gcc 3.0.1.
We're using Solaris 7. The host machine is a Sun E450, 4 ways, with
2 Gig of RAM. I wasn't testing for memory settings, so I picked
(somewhat arbitrarily) a shared buffers setting of 16384. (Most of
the tests I was working on were write-only tests, where the buffers
wouldn't matter anyway.)

One problem we have is that benchmarking is still tricky. The OSDB
suite is nice, and now works without immediately dumping core on
64-bit Solaris (many, many thanks go to Andy Riebs for his work on
that project), but it does crash at the end of its run (I haven't had
a chance to dig into that any more). Using it for repeated one-off
tests is therefore not practical, although it's extremely handy for
larger-scale checks. Several people have noted that pgbench is not
terribly consistent, and so when you're dealing in sub-second trends,
its numbers aren't usually helpful. I did note that it's quite good
for exercising locking problems: 100 clients doing 100 transactions
will cause all kinds of UPDATE WAITING conditions, which are nice to
be able to cause if you're looking for problems to do with
concurrency.

In order to discover straight differences between one change and
another, I ended up using a combination of truss and time. This is a
way less-than-optimal way of testing, and I don't suggest it. But at
least if you alter one option, and can get consistently better
numbers for one setting than for the other, you can be reasonbly
certain that the better one really is better. (It also offers the
client's perspective, so you get to see whether a change in a setting
really helps. That at least tells you that, if the change has made a
difference on server performance but not on client, you haven't found
the magic bottleneck.)

I disovered, for what it's worth, that as might be expected,
open_datasync is the fastest, although only on the order of a few
milliseconds compared to open_sync. I was a little surprised at how
little the difference was, but I always expected open_datasync to be
the fastest. This is consistent with other tests I've done on
Solaris, too, so I think the fsync method for Solaris should probably
be open_datasync. It currently defaults to fsync, which is pretty
bad.

(Note that the A5200 doesn't have any intelligence, so turning off
fsync makes a difference still. For us, the speed penalty is worth
the additional safety.)

I also discovered that this disk is so much faster than other systems
that it doesn't matter where you put the pg_xlog (I was sort of
surprised by this one). I tried configuring another striped pair on
an internal SCSI controller, and could consistently get single-insert
times a few milliseconds slower than when the pg_xlog was in the main
database cluster. I thought this might just be due to disk speed and
controller speed, so we tried configuring another stripe on the same
array. That made no difference on average, but the resulting times
were not always consistent. My best guess is that the inconsistency
correlates with the moving of data from WAL to the main data area,
but I haven't figured out a way to prove it.

For our purposes, increasing the WAL_BUFFERS was useful. I have a
suspicion that this is one area which gets overlooked by a lot of
people, because I sometimes get blank looks when I ask about it. We
have always set this in order to avoid the overhead of creating new
segments. After the surprise of the xlog, though, I thought I'd
check to see whether this was a real problem. It is. With only 10
clients doing 10000 inserts each, I was able to cause new segments to
be created. The creation added as much as a second to run times.
The difference was enough to "feel", which indicates that WAL tuning
is important.

I also did some other tests, but they were all related to specific
things that I'd identified as issues with our app I wanted to
investigate.

I'm done with our own tests, and no-one has shown up to collect the
machine yet. I'm happy to do some other investigation if anyone has
anything they really want to know. I have been thinking of trying to
do some optimiser tweak tests, but every time I've done any tests
with that, my results have been pretty inconclusive. Any suggestions
are welcome.

A

--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M6K 3E3
+1 416 646 3304 x110

Andrew Sullivan wrote:
> Hi,
>
> I currently have on loan to me a Sun StorEdge A5200.
...
> but every time I've done any tests
> with that, my results have been pretty inconclusive. Any suggestions
> are welcome.
>
> A
>

Hi,

Are there any actual times you can provide on operations on
some big tables (if you have any of around 10-100 Gb say) - just
'select count(*)' kind of stuff, maybe a 'select *' where its doing a
sequential scan?

We're using commodity hardware - AMD XP's - and wonder
how what kind performance you can get with the 'big iron'.

Cheers, Kurt.

Hi Andrew,

Are you using PostgreSQL compiled with a fixed qsort() ?

:-)

Regards and best wishes,

Justin Clift

Kurt at iadvance wrote:
>
> Andrew Sullivan wrote:
> > Hi,
> >
> > I currently have on loan to me a Sun StorEdge A5200.
> ...
> > but every time I've done any tests
> > with that, my results have been pretty inconclusive. Any suggestions
> > are welcome.
> >
> > A
> >
>
> Hi,
>
> Are there any actual times you can provide on operations on
> some big tables (if you have any of around 10-100 Gb say) - just
> 'select count(*)' kind of stuff, maybe a 'select *' where its doing a
> sequential scan?
>
> We're using commodity hardware - AMD XP's - and wonder
> how what kind performance you can get with the 'big iron'.
>
> Cheers, Kurt.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

On Tue, Jun 25, 2002 at 04:05:55PM +0930, Justin Clift wrote:
> Hi Andrew,
>
> Are you using PostgreSQL compiled with a fixed qsort() ?

Yes.

A

--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M6K 3E3
+1 416 646 3304 x110

On Tue, Jun 25, 2002 at 12:08:54PM +1200, Kurt at iadvance wrote:

> Are there any actual times you can provide on operations on
> some big tables (if you have any of around 10-100 Gb say) - just
> 'select count(*)' kind of stuff, maybe a 'select *' where its doing a
> sequential scan?

Ok, let me see what I can do today.

A

> We're using commodity hardware - AMD XP's - and wonder
> how what kind performance you can get with the 'big iron'.

Keep in mind that it's not always speed you're getting from the big
iron. In this case, for instance, part of the advantage is
redundancy.

A

--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M6K 3E3
+1 416 646 3304 x110

On Tue, Jun 25, 2002 at 12:08:54PM +1200, Kurt at iadvance wrote:
> Hi,
>
> Are there any actual times you can provide on operations on
> some big tables (if you have any of around 10-100 Gb say) - just
> 'select count(*)' kind of stuff, maybe a 'select *' where its doing a
> sequential scan?

Sorry, I intended to reply to this sooner. I went to perform such a
test on 28 June, and (alas) the temporary license I had for Veritas
had expired. So I couldn't run this. I'll have another opportunity,
I believe, to run some tests in August, so I'll keep this on my TODO.

A

--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M6K 3E3
+1 416 646 3304 x110

The following diff adds the NetBSD qsort() for all Solaris platforms.
If Sun ever fixes it, we will have to test version numbers somehow, but
for now, it is unconditionally used on Solaris for 7.3.

---------------------------------------------------------------------------

Andrew Sullivan wrote:
> On Tue, Jun 25, 2002 at 12:08:54PM +1200, Kurt at iadvance wrote:
> > Hi,
> >
> > Are there any actual times you can provide on operations on
> > some big tables (if you have any of around 10-100 Gb say) - just
> > 'select count(*)' kind of stuff, maybe a 'select *' where its doing a
> > sequential scan?
>
> Sorry, I intended to reply to this sooner. I went to perform such a
> test on 28 June, and (alas) the temporary license I had for Veritas
> had expired. So I couldn't run this. I'll have another opportunity,
> I believe, to run some tests in August, so I'll keep this on my TODO.
>
> A
>
> --
> ----
> Andrew Sullivan 87 Mowat Avenue
> Liberty RMS Toronto, Ontario Canada
> <andrew(at)libertyrms(dot)info> M6K 3E3
> +1 416 646 3304 x110
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> The following diff adds the NetBSD qsort() for all Solaris platforms.

Do we know that Solaris is the only platform with the problem?

In the back of my mind is the thought that if we used this qsort on
*all* platforms, we'd eliminate some platform-to-platform sort ordering
differences that occasionally cause regression test problems. And I
see little downside to using it everywhere.

regards, tom lane

On Fri, Jul 19, 2002 at 02:15:40PM -0400, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > The following diff adds the NetBSD qsort() for all Solaris platforms.
>
> Do we know that Solaris is the only platform with the problem?
>
> In the back of my mind is the thought that if we used this qsort on
> *all* platforms, we'd eliminate some platform-to-platform sort ordering
> differences that occasionally cause regression test problems. And I
> see little downside to using it everywhere.

What if someone else's qsort is faster than NetBSD's? NetBSD is not
famous for its high speeds, although I haven't any reason ot suppose
that this library is any differnt than FreeBSD's (or any other
Berkeley-derived system), since I haven't checked. Just asking,
really.

A

--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M6K 3E3
+1 416 646 3304 x110

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > The following diff adds the NetBSD qsort() for all Solaris platforms.
>
> Do we know that Solaris is the only platform with the problem?
>
> In the back of my mind is the thought that if we used this qsort on
> *all* platforms, we'd eliminate some platform-to-platform sort ordering
> differences that occasionally cause regression test problems. And I
> see little downside to using it everywhere.

That's a tough call. I am hesitant to replace qsort on platforms where
it is known to be working. If an OS has some platform-specific
optimized qsort, our replacing it could cause problems. Not sure how
likely that is to have happened, but Solaris clearly broke theirs so
someone must be modifying libc qsort somewhere. In fact, some qsorts
are not even quicksort, so I tend to defer to the libc authors to
provide the best qsort for their platform. In fact, this email suggests
there is quite a bit of variation in qsort implementations:

http://www.dietlibc.org/mailinglists/dietlibc/2001/12-2001/msg00024.html

I don't think we can replace library functions unless we know they are
broken or missing, or if several people using the OS agree with the
replacement, as they have on Solaris.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026