Thread: Re: [GENERAL] Re : Solaris Performance - Profiling (Solved)
Hi Tom,
How about we include this and have configure somehow ensure the Solaris
users get it automatically?
There are a *bunch* of Solaris users out there.
:-)
Regards and best wishes,
Justin Clift
Mark kirkwood wrote:
>
> On Wed, 2002-04-03 at 04:02, Tom Lane wrote:
> >
> > Hmm. Where exactly did you get those numbers from? I see 4118.54 sec
> > as the total CPU accounted for in the profile.
> >
> odd ...the call graph has 4047.53 and the flat graph has 4118.54
> >
> > Hmm. Assuming that the profile data is trustworthy and the queries are
> > indeed the same (did you compare EXPLAIN output?), it seems that
> > Solaris' problem is a spectacularly bad qsort() implementation.
> >
> A bit surfing finds heaps of unhappy Solaris qsort users... apparently
> it cannot sort lists with many repeated items... so our GROUP BY will be
> causing it grief here
> >
> > It might be entertaining to snarf a qsort off the net (from glibc,
> > perhaps) and link it into Postgres to see if you get better results.
> >
> > regards, tom lane
> >
> Indeed it is - obtained qsort.c from Freebsd CVS and rebuilt Postgresql :
> The query now takes 6 seconds instead of 1 hour ! Thanks for an
> excellent suggestion.
>
> For those in need to a quick fix :
>
> I did a cheap and dirty mod to src/backend/utils/sort/Makefile
>
> changed OBJS = logtape.o -> OBJS = qsort.o logtape.o
>
> and copied qsort.c into this directory
>
> (had to comment out a couple of lines to compile under Solaris :
>
> /*#include <sys/cdefs.h>
> __FBSDID("$FreeBSD: src/lib/libc/stdlib/qsort.c,v 1.11 2002/03/22
> 21:53:10 obrien Exp $");
> */
>
> )
>
> What do you think about providing something like this for the Solaris
> port ? (since its clearly quicker...)
>
> regards
>
> Mark
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
Just curious - why is solaris qsort that way? Any good reasons? I saw a very old post by a solaris guy, but it didn't seem very convincing. By the way are there faster sorts which Postgresql can use for its sorting other than quick sort? e.g. BSD 4.4 radixsort (which DJB seems to keep going on about :)). Would it make a significant improvement in performance? Cheerio, Link. p.s. We have postgresql on solaris too ;). At 05:59 PM 4/3/02 +1000, you wrote: >Hi Tom, > >How about we include this and have configure somehow ensure the Solaris >users get it automatically? > >There are a *bunch* of Solaris users out there. > >:-)
Justin Clift <justin@postgresql.org> writes:
> Mark kirkwood wrote:
>> Indeed it is - obtained qsort.c from Freebsd CVS and rebuilt Postgresql :
>> The query now takes 6 seconds instead of 1 hour ! Thanks for an
>> excellent suggestion.
> How about we include this and have configure somehow ensure the Solaris
> users get it automatically?
> There are a *bunch* of Solaris users out there.
Hmm. I suppose there'd be no license issues with borrowing a BSD qsort.
But I can't see any reasonable way for configure to decide automatically
whether we should replace the system qsort. I think we'd have to put
a USE_PRIVATE_QSORT symbol definition into src/template/solaris.
Can anyone see a problem with doing it that way --- are there any
versions of Solaris where this'd be a bad idea?
regards, tom lane
Tom Lane wrote: > > Justin Clift <justin@postgresql.org> writes: > > Mark kirkwood wrote: > >> Indeed it is - obtained qsort.c from Freebsd CVS and rebuilt Postgresql : > >> The query now takes 6 seconds instead of 1 hour ! Thanks for an > >> excellent suggestion. > > > How about we include this and have configure somehow ensure the Solaris > > users get it automatically? > > > There are a *bunch* of Solaris users out there. > > Hmm. I suppose there'd be no license issues with borrowing a BSD qsort. > But I can't see any reasonable way for configure to decide automatically > whether we should replace the system qsort. I think we'd have to put > a USE_PRIVATE_QSORT symbol definition into src/template/solaris. > > Can anyone see a problem with doing it that way --- are there any > versions of Solaris where this'd be a bad idea? I noticed poor performance on Solaris, does one see this problem when compiling PostgreSQL with gcc on solaris? As a suggestion, why not find the *best* version of qsort available, anywhere, and always use that version on all platforms?
On Wed, Apr 03, 2002 at 10:23:41AM -0500, Tom Lane wrote:
> Justin Clift <justin@postgresql.org> writes:
> > How about we include this and have configure somehow ensure the Solaris
> > users get it automatically?
>
> Hmm. I suppose there'd be no license issues with borrowing a BSD qsort.
> But I can't see any reasonable way for configure to decide automatically
> whether we should replace the system qsort. I think we'd have to put
> a USE_PRIVATE_QSORT symbol definition into src/template/solaris.
>
> Can anyone see a problem with doing it that way --- are there any
> versions of Solaris where this'd be a bad idea?
Would it be possible instead to make it a --configure option, or just
to add a note to the Solaris FAQ about adding an option to CFLAGS?
I'd be leery of automatically replacing system libraries, if only
because it might surprise people. Clearly the improvement is a win
in this case, but if Sun fixes their library, it might be yet faster.
(By the way, I've been following this thread, and noticed that the
problem shows up with gcc 2.95; AFAIK, 2.95 couldn't generate 64 bit
Solaris binaries, so we can be fairly certain the problem is in the
32 bit library. Maybe the 64 bit one is better? I _may_ have time
to check this week, but it's looking unlikely. If no-one else does,
I'll try it out as soon as I can.)
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
mlw <markw@mohawksoft.com> writes: > I noticed poor performance on Solaris, does one see this problem > when compiling PostgreSQL with gcc on solaris? Since it's libc that's the culprit, I would imagine so. > As a suggestion, why not find the *best* version of qsort available, > anywhere, and always use that version on all platforms? Because qsort() is *supposed* to be optimized by the vendor for their platform, perhaps even written in assembler. It makes sense to trust the vendor except when their implementation is provably pessimized. -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache LinuxBSD...
Doug McNaught wrote: > > mlw <markw@mohawksoft.com> writes: > > > I noticed poor performance on Solaris, does one see this problem > > when compiling PostgreSQL with gcc on solaris? > > Since it's libc that's the culprit, I would imagine so. Thanks, that explains what I have seen. > > > As a suggestion, why not find the *best* version of qsort available, > > anywhere, and always use that version on all platforms? > > Because qsort() is *supposed* to be optimized by the vendor for their > platform, perhaps even written in assembler. It makes sense to trust > the vendor except when their implementation is provably pessimized. Perhaps *supposed* to be optimized, but, in reality, are they? Is it a realistic expectation? qsort() is a great sort for very random data, when data is mostly in the correct order, it is very bad. Perhaps replacing it with an alternate sort would improve performance in general.
mlw <markw@mohawksoft.com> writes: > > Because qsort() is *supposed* to be optimized by the vendor for their > > platform, perhaps even written in assembler. It makes sense to trust > > the vendor except when their implementation is provably pessimized. > > Perhaps *supposed* to be optimized, but, in reality, are they? Is it a > realistic expectation? I think most vendors do a pretty good job. Don't forget, optimizing a routine like that depends a lot on the cache size and behavior of the CPU and other architecture-dependent stuff. > qsort() is a great sort for very random data, when data is mostly in the > correct order, it is very bad. Perhaps replacing it with an alternate sort > would improve performance in general. Actually, the C standard says nothing about what algorithm should be used for qsort(); it's simply supposed to be a fast in-memory sort. The qsort() name is just a historical artifact. -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache LinuxBSD...
Doug McNaught <doug@wireboard.com> writes:
> Actually, the C standard says nothing about what algorithm should be
> used for qsort(); it's simply supposed to be a fast in-memory sort.
> The qsort() name is just a historical artifact.
In practice I believe qsort usually is a quicksort; it's just too good
of a general-purpose algorithm. However you do need a good heuristic
for selecting the median value to split on, or you can get burnt by
corner cases. I'm guessing that Sun was careless and got burnt ...
regards, tom lane
Doug McNaught wrote: > I think most vendors do a pretty good job. Don't forget, optimizing a > routine like that depends a lot on the cache size and behavior of the > CPU and other architecture-dependent stuff. >> qsort() is a great sort for very random data, when data is mostly in the >> correct order, it is very bad. Perhaps replacing it with an alternate sort >> would improve performance in general. > Actually, the C standard says nothing about what algorithm should be > used for qsort(); it's simply supposed to be a fast in-memory sort. > The qsort() name is just a historical artifact. Perhaps so, but maybe that is the issue with Solaris, it actually may use qsort algorithm. I am not too sure how one would optimize the qsort() API on a platform basis. The sorting algorithm uses a callback function, this precludes any meaningful optimization. Yea, you can play with memory page sizes, and so on, but you still have to do a function call for some multiple of the number of elements in the array.
Andrew Sullivan <andrew@libertyrms.info> writes:
> On Wed, Apr 03, 2002 at 10:23:41AM -0500, Tom Lane wrote:
>> But I can't see any reasonable way for configure to decide automatically
>> whether we should replace the system qsort. I think we'd have to put
>> a USE_PRIVATE_QSORT symbol definition into src/template/solaris.
> Would it be possible instead to make it a --configure option, or just
> to add a note to the Solaris FAQ about adding an option to CFLAGS?
I think the default should be to replace, but we could probably have a
configure option to prevent it --- or to force it, in case people want
to try a non-system qsort on other platforms besides Solaris. Whenever
I see something like this, I wonder whether the problem is more
widespread than we know.
> ... so we can be fairly certain the problem is in the
> 32 bit library. Maybe the 64 bit one is better?
Good point. Please check it out and let us know.
regards, tom lane
Tom Lane wrote: > > Doug McNaught <doug@wireboard.com> writes: > > Actually, the C standard says nothing about what algorithm should be > > used for qsort(); it's simply supposed to be a fast in-memory sort. > > The qsort() name is just a historical artifact. > > In practice I believe qsort usually is a quicksort; it's just too good > of a general-purpose algorithm. However you do need a good heuristic > for selecting the median value to split on, or you can get burnt by > corner cases. I'm guessing that Sun was careless and got burnt ... quicksort is a pretty poor algorithm if your data is in some kind of order already. If you are sorting a list that is mostly in the order in which you want, it will perform very badly indeed. If you could choose the sorting algorithm based on knowledge of the order of the data, it may improve performance. Data retrieved from an index scan is likely to be in some sort of order. If the order of the data retrieved from an index scan is the same as the order to which it will be sorted at a later stage of the query, quicksort will probably be worse than something like shell sort.
mlw <markw@mohawksoft.com> writes:
> quicksort is a pretty poor algorithm if your data is in some kind of order
> already.
Only if you fail to take standard precautions against making a bad
choice of pivot element; every discussion I've ever seen of quicksort
explains ways to avoid that pitfall. Solaris' problem seems to be a
more subtle issue having to do with large numbers of equal keys. The
form of quicksort that Knuth presents is tuned to behave well in that
situation, at the cost of exchanging equal records (cf. his exercise
5.2.2.18) ... perhaps Sun overlooked that particular hack, or got it
wrong.
regards, tom lane
On Wed, Apr 03, 2002 at 11:35:30AM -0500, Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> writes:
>
> > ... so we can be fairly certain the problem is in the
> > 32 bit library. Maybe the 64 bit one is better?
>
> Good point. Please check it out and let us know.
Sorry this has taken me so long, but what with various stuff going on
here [1] and some vacation, I didn't have a chance to get to it
sooner.
But now I'm wondering, is there anyone who knows of troubles with the
profiling of programs compiled with -pg under gcc 3.0.3 64 bit on
Solaris 7? Here's my configure line:
CFLAGS="-pg -mcmodel=medlow" ./configure
--prefix=/opt/OXRS/pgsql721-profile --with-pgport=12000
--disable-shared
But I can't do anything with it:
postgres721-profdata$ /opt/OXRS/pgsql721-profile/bin/postgres
No space for profiling buffer(s)
FATAL 2: invalid checksum in control file
Segmentation Fault (core dumped)
Anyone with advice?
[1] We were purchased by our largest customer, which is probably a
good thing, but meant more meetings and less, um, other work.
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Sorry to reply to myself, but this might be useful for the archive.
On Tue, Apr 30, 2002 at 11:55:53AM -0400, Andrew Sullivan wrote:
> But now I'm wondering, is there anyone who knows of troubles with the
> profiling of programs compiled with -pg under gcc 3.0.3 64 bit on
> Solaris 7? Here's my configure line:
You can't use the -pg cflag without -mcmodel=medlow, because gcc (at
least in its 64 bit incarnation on Solaris) apparently can't produce
binaries that way: only the medlow memory model is supported for
profiling. Unhappily, this appears to cause conflicting libraries to
be invoked (I _think_ that's what's going on, anyway). I think this
means I can't build a 64-bit system with gcc for profiling. I might
be wrong (I'm sort of puzzling this out from two or three rather
cryptic entries in some man pages; and as anyone who's ever seen my
posts will attest, I'm not that bright anyway, so I may have
misunderstood something). In any case, I can't offer a definite
answer about the 64-bit qsort for now. If I have a chance to
come back to it, and discover anything, I'll post it here.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
On Tue, Apr 30, 2002 at 03:28:13PM -0400, Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> writes:
> > ... In any case, I can't offer a definite
> > answer about the 64-bit qsort for now.
>
> Do you need to profile it? It seemed that the 32-bit behavior for
> many-equal-keys was so bad that it'd be easy to tell whether it's
> fixed, just by rough overall timing of a test case...
Yes, that's what I thought, too, so I figured I'd do that instead
(although I didn't think of it until after I sent the mail). On the
other hand, now I'm like a dog with a bone, because I want to know
why in the world it doesn't work. No wonder I never get anything
done.
Thanks to Travis Hoyt, who pointed out that I could at least test for
library problems with truss. I did, and the interesting thing is
that it appears to be the profile writing that's causing the segfault
(it's during the write to gmon.out that the segfault occurs). So my
earlier view was wrong. But in any case, it looks like there really
is something broken about profiling with this configuration.
Since the original case was so bad, can anyone tell me roughly how
many equal keys were in the set, and how big the total set was? That
way I'll be able to get something reasonably close, and I can use
wall-clock time or something to expose whether there's a problem for
64 bit libraries too.
Thanks,
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Andrew Sullivan <andrew@libertyrms.info> writes:
> ... In any case, I can't offer a definite
> answer about the 64-bit qsort for now.
Do you need to profile it? It seemed that the 32-bit behavior for
many-equal-keys was so bad that it'd be easy to tell whether it's
fixed, just by rough overall timing of a test case...
regards, tom lane
On Tue, Apr 30, 2002 at 03:28:13PM -0400, Tom Lane wrote:
> Do you need to profile it? It seemed that the 32-bit behavior for
> many-equal-keys was so bad that it'd be easy to tell whether it's
> fixed, just by rough overall timing of a test case...
Sorry for taking yet again so long. Fitting in little tests of this
sort of thing can be a bit of a bear -- there's always about 50 other
things to do. Anyway, I've performed some simple timed tests that, I
think, confirm that the 64 bit library on Solaris is not so bad.
version
-----------------------------------------------------------------
PostgreSQL 7.2.1 on sparc-sun-solaris2.7, compiled by GCC 3.0.3
bin$ file postmaster
postmaster: ELF 64-bit MSB executable SPARCV9 Version 1,
dynamically linked, not stripped
The config file is the default
I _think_ I've captured the case that was problematic. As I
understood it, qsort was having trouble when hit with many equal
keys. I created this table:
CREATE TABLE table1 (_date_stamp timestamp default current_timestamp,
foo text);
The table has no index. It has 5120000 records; field "foo" has only
four distinct values.
No matter whether I compiled with the system qsort or the qsort from
FreeBSD, I got roughly equivalent results running psql under time. I
know that's hardly an ideal test, but as Tom suggested, the 32-bit
case seemed to be so astonishingly bad that it should have been
enough. I ran the test repeatedly, and the results seem pretty
consistent. Here are some typical results:
system lib:
src$ time psql -p 12000 -o /dev/null -c "select * from table1 order
^by foo" test1
real 29m23.822s
user 2m10.241s
sys 0m7.432s
FreeBSD lib:
postgresql-7.2.1$ time psql -p 12000 -o /dev/null -c "select * from
table1 order by foo" test1
real 29m38.880s
user 2m10.571s
sys 0m8.032s
This example suggests the FreeBSD library is slightly worse in the
64-bit case. That's consistently the case, but the difference is not
so great that I'd put any stock in it.
I do not know whether there might be any trouble using the FreeBSD
library in a 64-bit configuration. I'd say, if you're going to use a
64-bit postmaster, use the Solaris libraries.
Hope this is helpful,
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
TODO updated:
Add BSD-licensed qsort() for 32-bit Solaris
---------------------------------------------------------------------------
Andrew Sullivan wrote:
> On Tue, Apr 30, 2002 at 03:28:13PM -0400, Tom Lane wrote:
>
> > Do you need to profile it? It seemed that the 32-bit behavior for
> > many-equal-keys was so bad that it'd be easy to tell whether it's
> > fixed, just by rough overall timing of a test case...
>
> Sorry for taking yet again so long. Fitting in little tests of this
> sort of thing can be a bit of a bear -- there's always about 50 other
> things to do. Anyway, I've performed some simple timed tests that, I
> think, confirm that the 64 bit library on Solaris is not so bad.
>
> version
> -----------------------------------------------------------------
> PostgreSQL 7.2.1 on sparc-sun-solaris2.7, compiled by GCC 3.0.3
>
> bin$ file postmaster
> postmaster: ELF 64-bit MSB executable SPARCV9 Version 1,
> dynamically linked, not stripped
>
> The config file is the default
>
> I _think_ I've captured the case that was problematic. As I
> understood it, qsort was having trouble when hit with many equal
> keys. I created this table:
>
> CREATE TABLE table1 (_date_stamp timestamp default current_timestamp,
> foo text);
>
> The table has no index. It has 5120000 records; field "foo" has only
> four distinct values.
>
> No matter whether I compiled with the system qsort or the qsort from
> FreeBSD, I got roughly equivalent results running psql under time. I
> know that's hardly an ideal test, but as Tom suggested, the 32-bit
> case seemed to be so astonishingly bad that it should have been
> enough. I ran the test repeatedly, and the results seem pretty
> consistent. Here are some typical results:
>
> system lib:
>
> src$ time psql -p 12000 -o /dev/null -c "select * from table1 order
> ^by foo" test1
>
> real 29m23.822s
> user 2m10.241s
> sys 0m7.432s
>
> FreeBSD lib:
>
> postgresql-7.2.1$ time psql -p 12000 -o /dev/null -c "select * from
> table1 order by foo" test1
>
>
> real 29m38.880s
> user 2m10.571s
> sys 0m8.032s
>
>
> This example suggests the FreeBSD library is slightly worse in the
> 64-bit case. That's consistently the case, but the difference is not
> so great that I'd put any stock in it.
>
> I do not know whether there might be any trouble using the FreeBSD
> library in a 64-bit configuration. I'd say, if you're going to use a
> 64-bit postmaster, use the Solaris libraries.
>
> Hope this is helpful,
>
> A
>
> --
> ----
> Andrew Sullivan 87 Mowat Avenue
> Liberty RMS Toronto, Ontario Canada
> <andrew@libertyrms.info> M6K 3E3
> +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Mon, 27 May 2002 21:00:43 -0400 (EDT) "Bruce Momjian" <pgman@candle.pha.pa.us> wrote: > TODO updated: > > Add BSD-licensed qsort() for 32-bit Solaris Is this necessary? Didn't someone say that Sun had acknowledged the performance problem and were going to be releasing a patch for it? If that patch exists (or will exist), it would probably be better to suggest in the docs that users of 32-bit Solaris apply the patch. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
On Mon, May 27, 2002 at 09:00:43PM -0400, Bruce Momjian wrote:
>
> TODO updated:
>
> Add BSD-licensed qsort() for 32-bit Solaris
I've received an email noting that someone else ran a test program
with the 64 bit library, and had just as bad performance as the 32
bit one. I haven't had a chance to look at it yet, but it suggests
that the result is still inconclusive. Maybe, if just one more fire
goes out here, I can look at it this week.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Neil Conway wrote: > On Mon, 27 May 2002 21:00:43 -0400 (EDT) > "Bruce Momjian" <pgman@candle.pha.pa.us> wrote: > > TODO updated: > > > > Add BSD-licensed qsort() for 32-bit Solaris > > Is this necessary? Didn't someone say that Sun had acknowledged the > performance problem and were going to be releasing a patch for it? > If that patch exists (or will exist), it would probably be better to > suggest in the docs that users of 32-bit Solaris apply the patch. Sun said they would look at it (actually McNeeley (sp?)), but I haven't seen any mention of a patch. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Andrew Sullivan wrote:
> On Mon, May 27, 2002 at 09:00:43PM -0400, Bruce Momjian wrote:
> >
> > TODO updated:
> >
> > Add BSD-licensed qsort() for 32-bit Solaris
>
> I've received an email noting that someone else ran a test program
> with the 64 bit library, and had just as bad performance as the 32
> bit one. I haven't had a chance to look at it yet, but it suggests
> that the result is still inconclusive. Maybe, if just one more fire
> goes out here, I can look at it this week.
TODO reverted to be:
Add BSD-licensed qsort() for Solaris
My guess is that your test case didn't tickle the bug.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026