Sun performance - Major discovery!
Well, as you guys know I've been tinkering with sun-vs-linux postgres for
a while trying to come up with reasons for the HUGE performance
differences. We've all had our anecdotal thoughts (fork sucks, ipc sucks,
ufs sucks, etc) and I've had a breakthrough.
Knowing that GCC only produces good code on x86 (and powerpc with apple's
mods, but it is doubtful that is as good as ibm's power compiler) I
decided to try out Sunsoft CC. I'd heard from more than one person/place
that gcc makes abysmal sparc code. Given that the performance profiles
for both the linux and sun boxes showed the same functions taking up most
of the time I thought I'd see what a difference sunsoft could give me.
So - hardware -
Sun E450 4x400mhz ultrasparc IIi, 4GB ram, scsi soemthing disk. (not
raid) solaris 2.6
Linux - 2xP3 500mhz, 2GB, scsi disk of some flavor (not raid) linux 2.2.17
(old I know!)
So here's the results using my load tester (single connection per beater,
repeats the query 1000 times with different input each time (we'll get
~20k rows back), the query is a common query around here.
I discounted the first run of the test as caches populated.
Linux - 1x - 35 seconds, 20x - 180 seconds
Sun - gcc - 1x 60 seconds 20x 245 seconds
Sun - sunsoft defaults - 1x 52 seonds 20x [similar to gcc most likely]
Sun - sunsoft -fast - 1x 28 seconds 20x 164 seconds
As you math guru's can probably deduce - that is a rather large
improvement. And by rather large I mean hugely significant. With results
like this, I think it warrants mentioning in the FAQ_Solaris, and probably
the performance guide.
Connecting will always be a bit slower. But I think most people realize
that connecting to a db is not cheap.
I think update/etc will cause more locking, but I think IO will become the
bottle neck much sooner than lock/unlock will. (This is mostly anecdotal
given how fast solaris can lock/unlock a semaphore and how much IO I know
I have)
Oh yes, with was with 7.3.4 and sunsoft cc Sun WorkShop 6 update 1 C
5.2 2000/09/11 (which is old, perhaps newer ones make even better code?)
I'm not sure of PG's policy of non-gcc things in configure, but perhaps if
we detect sunsoft we toss in the -fast flag and maybe make it the
preferred one on sun? [btw, it compiled with no changes but it did spew
out tons of warnings]
comments?
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
On Wed, Oct 08, 2003 at 08:36:56AM -0400, Jeff wrote:
So here's the results using my load tester (single connection per beater,
repeats the query 1000 times with different input each time (we'll get
~20k rows back), the query is a common query around here.
My worry about this test is that it gives us precious little
knowledge about concurrent connection slowness, which is where I find
the most significant problems. When we tried a Sunsoft cc vs gcc 2.95
on Sol 7 about 1 1/2 years ago, we found more or less no difference
once we added more than 5 connections (and we always have more than 5
connections). It might be worth trying again, though, since we moved
to Sol 8.
Thanks for the result.
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Wed, 2003-10-08 at 08:36, Jeff wrote:
So here's the results using my load tester (single connection per beater,
repeats the query 1000 times with different input each time (we'll get
~20k rows back), the query is a common query around here.
What is the query?
Linux - 1x - 35 seconds, 20x - 180 seconds
"20x" means 20 concurrent testing processes, right?
Sun - gcc - 1x 60 seconds 20x 245 seconds
Sun - sunsoft defaults - 1x 52 seonds 20x [similar to gcc most likely]
Sun - sunsoft -fast - 1x 28 seconds 20x 164 seconds
Interesting (and surprising that the performance differential is that
large, to me at least). Can you tell if the performance gain comes from
an improvement in a particular subsystem? (i.e. could you get a profile
of Sun/gcc and compare it with Sun/sunsoft).
-Neil
On Wed, 8 Oct 2003, Andrew Sullivan wrote:
My worry about this test is that it gives us precious little
knowledge about concurrent connection slowness, which is where I find
the most significant problems. When we tried a Sunsoft cc vs gcc 2.95
on Sol 7 about 1 1/2 years ago, we found more or less no difference
once we added more than 5 connections (and we always have more than 5
connections). It might be worth trying again, though, since we moved
to Sol 8.
The 20x column are the results when I fired up 20 beater concurrently.
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
On Wed, 8 Oct 2003, Neil Conway wrote:
What is the query?
It retrieves an index listing for our boards. The boards are flat (not
threaded) and messages are numbered starting at 1 for each board.
If you pass in 0 for the start_from it assumes the latest 60.
And it should be noted - in some cases some boards have nearly 2M posts.
Index on board_name, number.
I cannot give out too too much stuff ;)
create or replace function get_index2(integer, varchar, varchar)
returns setof snippet
as '
DECLARE
p_start alias for $1;
p_board alias for $2;
v_start integer;
v_num integer;
v_body text;
v_sender varchar(35);
v_time timestamptz;
v_finish integer;
v_row record;
v_ret snippet;
BEGIN
v_start := p_start;
if v_start = 0 then
select * into v_start from get_high_msg(p_board);
v_start := v_start - 59;
end if;
v_finish := v_start + 60;
for v_row in
select number, substr(body, 0, 50) as snip, member_handle,
timestamp
from posts
where board_name = p_board and
number >= v_start and
number < v_finish
order by number desc
LOOP
return next v_row;
END LOOP;
return;
END;
' language 'plpgsql';
Interesting (and surprising that the performance differential is that
large, to me at least). Can you tell if the performance gain comes from
an improvement in a particular subsystem? (i.e. could you get a profile
of Sun/gcc and compare it with Sun/sunsoft).
I'll get these later today.
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
On Wed, 8 Oct 2003, Neil Conway wrote:
Interesting (and surprising that the performance differential is that
large, to me at least). Can you tell if the performance gain comes from
an improvement in a particular subsystem? (i.e. could you get a profile
of Sun/gcc and compare it with Sun/sunsoft).
Yeah - like I expected it was able to generate much better code for
_bt_checkkeys which was the #1 function in gcc on both sun & linux.
and as you can see, suncc was just able to generate much nicer code. I'd
look at the assembler output but that won't be useful since I am very
unfamiliar with the [ultra]sparc instruction set..
Here's the prof and gprof output for the latest run:
GCC:
% cumulative self self total
time seconds seconds calls ms/call ms/call name
31.52 19.44 19.44 internal_mcount
20.28 31.95 12.51 8199466 0.00 0.00 _bt_checkkeys
5.61 35.41 3.46 8197422 0.00 0.00 _bt_step
5.01 38.50 3.09 24738620 0.00 0.00 FunctionCall2
3.00 40.35 1.85 8194186 0.00 0.00 varchareq
2.61 41.96 1.61 24309 0.07 0.28 _bt_next
2.42 43.45 1.49 1003 1.49 1.51 AtEOXact_Buffers
2.37 44.91 1.46 12642 0.12 0.12 _read
2.33 46.35 1.44 16517771 0.00 0.00 pg_detoast_datum
2.08 47.63 1.28 8193186 0.00 0.00 int4lt
1.35 48.46 0.83 8237204 0.00 0.00 BufferGetBlockNumber
1.35 49.29 0.83 8193888 0.00 0.00 int4ge
1.35 50.12 0.83 _mcount
SunCC -pg -fast.
%Time Seconds Cumsecs #Calls msec/call Name
23.2 4.27 4.27108922056 0.0000 _mcount
20.7 3.82 8.09 8304052 0.0005 _bt_checkkeys
13.7 2.53 10.6225054788 0.0001 FunctionCall2
5.1 0.94 11.56 24002 0.0392 _bt_next
4.4 0.81 12.37 8301867 0.0001 _bt_step
3.4 0.63 13.00 8298219 0.0001 varchareq
2.7 0.50 13.5016726855 0.0000 pg_detoast_datum
2.4 0.45 13.95 8342464 0.0001 BufferGetBlockNumber
2.4 0.44 14.39 8297941 0.0001 int4ge
2.2 0.41 14.80 1003 0.409 AtEOXact_Buffers
2.0 0.37 15.17 4220349 0.0001 lc_collate_is_c
2.0 0.37 15.54 8297219 0.0000 int4lt
1.6 0.29 15.83 26537 0.0109 AllocSetContextCreate
0.9 0.16 15.99 1887 0.085 pglz_decompress
0.7 0.13 16.12 159966 0.0008 nocachegetattr
0.7 0.13 16.25 4220349 0.0000 varstr_cmp
0.6 0.11 16.36 937576 0.0001 MemoryContextAlloc
0.5 0.09 16.45 150453 0.0006 hash_search
-Neil
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
On Wed, 2003-10-08 at 10:48, Andrew Sullivan wrote:
My worry about this test is that it gives us precious little
knowledge about concurrent connection slowness, which is where I find
the most significant problems.
As Jeff points out, the second set of results is for 20 concurrent
connections. Note that the advantage sunsoft cc has over gcc decreases
as the number of connections increases (which makes sense, as the 20x
workload is likely to be more I/O bound).
-Neil
On Wed, 2003-10-08 at 11:46, Jeff wrote:
Yeah - like I expected it was able to generate much better code for
_bt_checkkeys which was the #1 function in gcc on both sun & linux.and as you can see, suncc was just able to generate much nicer code.
What CFLAGS does configure pick for gcc? From
src/backend/template/solaris, I'd guess it's not enabling any
optimization. Is that the case? If so, some gcc numbers with -O and -O2
would be useful.
-Neil
On Wed, 8 Oct 2003, Neil Conway wrote:
What CFLAGS does configure pick for gcc? From
src/backend/template/solaris, I'd guess it's not enabling any
optimization. Is that the case? If so, some gcc numbers with -O and -O2
would be useful.
I can't believe I didn't think of this before! heh.
Turns out gcc was getting nothing for flags.
I added -O2 to CFLAGS and my 60 seconds went down to 21. A rather mild
improvment huh?
I did a few more tests and suncc still beats it out - but not by too much
now (Not enought to justify buying a license just for compiling pg)
I'll go run the regression test suite with my gcc -O2 pg and the suncc pg.
See if they pass the test.
If they do we should consider adding -O2 and -fast to the CFLAGS.
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
Jeff wrote:
On Wed, 8 Oct 2003, Neil Conway wrote:
What CFLAGS does configure pick for gcc? From
src/backend/template/solaris, I'd guess it's not enabling any
optimization. Is that the case? If so, some gcc numbers with -O and -O2
would be useful.I can't believe I didn't think of this before! heh.
Turns out gcc was getting nothing for flags.I added -O2 to CFLAGS and my 60 seconds went down to 21. A rather mild
improvment huh?I did a few more tests and suncc still beats it out - but not by too much
now (Not enought to justify buying a license just for compiling pg)I'll go run the regression test suite with my gcc -O2 pg and the suncc pg.
See if they pass the test.If they do we should consider adding -O2 and -fast to the CFLAGS.
[ CC added for hackers.]
Well, this is really embarassing. I can't imagine why we would not set
at least -O on all platforms. Looking at the template files, I see
these have no optimization set:
darwin
dgux
freebsd (non-alpha)
irix5
nextstep
osf (gcc)
qnx4
solaris
sunos4
svr4
ultrix4
I thought we used to have code that did -O for any platforms that set no
cflags, but I don't see that around anywhere. I recommend adding -O2,
or at leaset -O to all these platforms --- we can then use platform
testing to make sure they are working.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Wed, 2003-10-08 at 14:31, Bruce Momjian wrote:
Well, this is really embarassing. I can't imagine why we would not set
at least -O on all platforms.
ISTM the most legitimate reason for not enabling compilater
optimizations on a given compiler/OS/architecture combination is might
cause compiler errors / bad code generation.
Can we get these optimizations enabled in time for the next 7.4 beta? It
might also be good to add an item in the release notes about it.
-Neil
On Wed, 8 Oct 2003, Neil Conway wrote:
ISTM the most legitimate reason for not enabling compilater
optimizations on a given compiler/OS/architecture combination is might
cause compiler errors / bad code generation.Can we get these optimizations enabled in time for the next 7.4 beta? It
might also be good to add an item in the release notes about it.-Neil
I just ran make check for sun with gcc -O2 and suncc -fast and both
passed.
We'll need other arguments to suncc to supress some warnings, etc. (-fast
generates a warning for every file compiled telling you it will only
run on ultrasparc machines)
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
Bruce Momjian wrote:
Jeff wrote:
On Wed, 8 Oct 2003, Neil Conway wrote:
What CFLAGS does configure pick for gcc? From
src/backend/template/solaris, I'd guess it's not enabling any
optimization. Is that the case? If so, some gcc numbers with -O and -O2
would be useful.I can't believe I didn't think of this before! heh.
Turns out gcc was getting nothing for flags.I added -O2 to CFLAGS and my 60 seconds went down to 21. A rather mild
improvment huh?I did a few more tests and suncc still beats it out - but not by too much
now (Not enought to justify buying a license just for compiling pg)I'll go run the regression test suite with my gcc -O2 pg and the suncc pg.
See if they pass the test.If they do we should consider adding -O2 and -fast to the CFLAGS.
[ CC added for hackers.]
Well, this is really embarassing. I can't imagine why we would not set
at least -O on all platforms. Looking at the template files, I see
these have no optimization set:darwin
dgux
freebsd (non-alpha)
irix5
nextstep
osf (gcc)
qnx4
solaris
sunos4
svr4
ultrix4I thought we used to have code that did -O for any platforms that set no
cflags, but I don't see that around anywhere. I recommend adding -O2,
or at leaset -O to all these platforms --- we can then use platform
testing to make sure they are working.
Actually, I would not be surprised to see gains on Solaris/SPARC from
-O3 with gcc, which enables inlining and register-renaming, although
this does make debugging pretty much impossible.
worth testing at least (but I no longer have access to a Solaris machine).
cheers
andrew
In message <Pine.BSF.4.44.0310081408370.64781-100000@torgo.978.org>, Jeff writes:
I'll go run the regression test suite with my gcc -O2 pg and the suncc pg.
See if they pass the test.
My default set of gcc optimization flags is:
-O3 -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -falign-functions -mcpu=i686 -march=i686
Obviously the last two flags product CPU specific code, so would have
to differ...autoconf is always possible, but so is just lopping them off.
I have found these flags to produce faster code that a simple -O2, but
I understand the exact combination which is best for you is
code-dependent. Of course, if you are getting really excited, you can
use -fbranch-probabilities, but as you will see if you investigate
that requires some profiling information, so is not very easy to
actually practically use.
-Seth Robertson
Neil Conway <neilc@samurai.com> writes:
On Wed, 2003-10-08 at 14:31, Bruce Momjian wrote:
Well, this is really embarassing. I can't imagine why we would not set
at least -O on all platforms.
I believe that autoconf will automatically select -O2 (when CFLAGS isn't
already set) *if* it's chosen gcc. It won't select anything for vendor
ccs.
Can we get these optimizations enabled in time for the next 7.4 beta?
I think it's too late in the beta cycle to add optimization flags except
for platforms we can get specific success results for. (Solaris is
probably okay for instance.) The risk of breaking things seems too
high.
regards, tom lane
Bruce Momjian writes:
Well, this is really embarassing. I can't imagine why we would not set
at least -O on all platforms. Looking at the template files, I see
these have no optimization set:
freebsd (non-alpha)
I'm wondering what that had in mind:
--
Peter Eisentraut peter_e@gmx.net
Tom Lane wrote:
Neil Conway <neilc@samurai.com> writes:
On Wed, 2003-10-08 at 14:31, Bruce Momjian wrote:
Well, this is really embarassing. I can't imagine why we would not set
at least -O on all platforms.I believe that autoconf will automatically select -O2 (when CFLAGS isn't
already set) *if* it's chosen gcc. It won't select anything for vendor
ccs.
I think the problem is that template/solaris overrides that with:
CFLAGS=
Can we get these optimizations enabled in time for the next 7.4 beta?
I think it's too late in the beta cycle to add optimization flags except
for platforms we can get specific success results for. (Solaris is
probably okay for instance.) The risk of breaking things seems too
high.
Agreed. Do we set them all to -O2, then remove it from the ones we
don't get successful reports on?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Peter Eisentraut wrote:
Bruce Momjian writes:
Well, this is really embarassing. I can't imagine why we would not set
at least -O on all platforms. Looking at the template files, I see
these have no optimization set:freebsd (non-alpha)
I'm wondering what that had in mind:
I was wondering that myself. I think the idea was that we already do
-O2 in configure if it is gcc, so why do it in the template files. What
is killing us is the CFLAGS= lines in the configuration files.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Well, this is really embarassing. I can't imagine why we would not set
at least -O on all platforms. Looking at the template files, I see
these have no optimization set:freebsd (non-alpha)
I'm wondering what that had in mind:
When I used to build pgsql on freebsd/alpha, I would get heaps of GCC
warnings saying 'optimisations for the alpha are broken'. I can't
remember if that meant anything more than just -O or not though.
Chris
Tom Lane wrote:
Neil Conway <neilc@samurai.com> writes:
On Wed, 2003-10-08 at 14:31, Bruce Momjian wrote:
Well, this is really embarassing. I can't imagine why we would not set
at least -O on all platforms.I believe that autoconf will automatically select -O2 (when CFLAGS isn't
already set) *if* it's chosen gcc. It won't select anything for vendor
ccs.Can we get these optimizations enabled in time for the next 7.4 beta?
I think it's too late in the beta cycle to add optimization flags except
for platforms we can get specific success results for. (Solaris is
probably okay for instance.) The risk of breaking things seems too
high.
OK, patch attached and applied. It centralizes the optimization
defaults into configure.in, rather than having CFLAGS= in the template
files.
It used -O2 for gcc (generated automatically by autoconf), and -O for
non-gcc, unless the template overrides it.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073