Thread: Sun vs. Mac

Sun vs. Mac

From
CaptainX0r
Date:
Hello,

I'm having some serious performance issues with PostgreSQL on
our newish SunFire 280R (1 900MHz ultrasparc III, 1 GB RAM).
It's painfully slow.  It took me almost a week of tuning to get
it in the range of an old Mac G3 laptop.  Now, a few days later,
after tweaking every nearly every parameter (only noting
decreased performance on some) in /etc/system and
$PGDATA/postgresql.conf it's about as fast as I can make it, but
still horribly slow.  A few simple queries that take 1.5-7
minutes on the G3 take 1-1.5 minutes on the Sun.  A bulk load of
roughly 2.4 GB database dump takes ~1 hour on each machine.  It
took almost 2 hours on the Sun before I turned off fsync.

We have plans to add another CPU, RAM and another disk, which
should all help, but in its current state, I (and many others)
would think that it should run circles around the G3.  I'm
thinking that I'm missing something big and obvious because this
can't be right.  Otherwise we might as well just get a bunch of
ibooks to run our databases - they're a lot smaller and much
more quiet.

Can someone please point me in the right direction?

Thanks,

-X

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac

From
Justin Clift
Date:
Hi CaptainXOr,

Which version of PostgreSQL, and which release of Solaris are you running?

Regards and best wishes,

Justin Clift


CaptainX0r wrote:
> Hello,
>
> I'm having some serious performance issues with PostgreSQL on
> our newish SunFire 280R (1 900MHz ultrasparc III, 1 GB RAM).
> It's painfully slow.  It took me almost a week of tuning to get
> it in the range of an old Mac G3 laptop.  Now, a few days later,
> after tweaking every nearly every parameter (only noting
> decreased performance on some) in /etc/system and
> $PGDATA/postgresql.conf it's about as fast as I can make it, but
> still horribly slow.  A few simple queries that take 1.5-7
> minutes on the G3 take 1-1.5 minutes on the Sun.  A bulk load of
> roughly 2.4 GB database dump takes ~1 hour on each machine.  It
> took almost 2 hours on the Sun before I turned off fsync.
>
> We have plans to add another CPU, RAM and another disk, which
> should all help, but in its current state, I (and many others)
> would think that it should run circles around the G3.  I'm
> thinking that I'm missing something big and obvious because this
> can't be right.  Otherwise we might as well just get a bunch of
> ibooks to run our databases - they're a lot smaller and much
> more quiet.
>
> Can someone please point me in the right direction?
>
> Thanks,
>
> -X
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@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


Re: Sun vs. Mac

From
Andrew Sullivan
Date:
On Tue, Jan 14, 2003 at 07:00:08AM -0800, CaptainX0r wrote:
> Hello,
>
> I'm having some serious performance issues with PostgreSQL on
> our newish SunFire 280R (1 900MHz ultrasparc III, 1 GB RAM).
> It's painfully slow.  It took me almost a week of tuning to get
> it in the range of an old Mac G3 laptop.  Now, a few days later,
> after tweaking every nearly every parameter (only noting
> decreased performance on some) in /etc/system and
> $PGDATA/postgresql.conf it's about as fast as I can make it, but

You should tell us about what version of Solaris you're running, what
version of Postgres, and what options you have used.  Did you split
the WAL onto its own filesystem?  You'll get a big win that way.
Also, what fsync setting are you using (open_datasync is the fastest
in my experience).  Finally, the bottleneck on Solaris is both disk
and process forking (fork() is notoriously slow on Solaris).

Also, certain sort routines are abysmal.  Replace the
Solaris-provided qsort().

I have to say, however, that my experience indicates that Solaris is
slower that the competition for Postgres.  It still shouldn't be that
bad.

A
--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: Sun vs. Mac

From
CaptainX0r
Date:
Sorry - I meant to include that.  I'm running PG 7.3.1 on
Solaris 8.

Thanks,

-X

--- Justin Clift <justin@postgresql.org> wrote:
>
> Which version of PostgreSQL, and which release of Solaris are
> you running?
>
> >
> > I'm having some serious performance issues with PostgreSQL
> on
> > our newish SunFire 280R (1 900MHz ultrasparc III, 1 GB RAM).
>
> > It's painfully slow.  It took me almost a week of tuning to
> get
> > it in the range of an old Mac G3 laptop.  Now, a few days
> later,
> > after tweaking every nearly every parameter (only noting
> > decreased performance on some) in /etc/system and
> > $PGDATA/postgresql.conf it's about as fast as I can make it,
> but
> > still horribly slow.  A few simple queries that take 1.5-7
> > minutes on the G3 take 1-1.5 minutes on the Sun.  A bulk
> load of
> > roughly 2.4 GB database dump takes ~1 hour on each machine.
> It
> > took almost 2 hours on the Sun before I turned off fsync.
> >
> > We have plans to add another CPU, RAM and another disk,
> which
> > should all help, but in its current state, I (and many
> others)
> > would think that it should run circles around the G3.  I'm
> > thinking that I'm missing something big and obvious because
> this
> > can't be right.  Otherwise we might as well just get a bunch
> of
> > ibooks to run our databases - they're a lot smaller and much
> > more quiet.
> >
> > Can someone please point me in the right direction?
> >
> > Thanks,
> >
> > -X
> >


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac

From
CaptainX0r
Date:
All,

> You should tell us about what version of Solaris you're
> running, what
> version of Postgres, and what options you have used.

You're right, sorry.  PG 7.3.1 on Solaris 8.  I've got the
default recommended /etc/system but with shmmax cranked way up
which seems to have helped.  I don't have the system in front of
me (and it's down, so I can't get to it), but from memory
max_connections was increased to 64, shared_buffers up to 65536,
sort_mem and vacuum_mem were doubled, and I think that's it.  I
changed every seemingly relevant one, and spent a lot of time on
the *cost section trying various factors of n*10 on each, with
no joy.

> Did you split
> the WAL onto its own filesystem?  You'll get a big win that
> way.

I have not.  What exactly do you by "own filesystem"?  Another
filesystem?  I was planning on putting pg_xlog on the OS disk
and moving $PGDATA off to a second disk.

> Also, what fsync setting are you using (open_datasync is the
> fastest in my experience).

I've read that somewhere (maybe in the archives?) and I got no
change with any of them.  But now I'm thinking back - do I need
fsync=true for that to have an affect?  I'm not worried about
the cons of having fsync=false at all - and I'm assuming that
should be better than true and open_datasync.  Or am I confusing
things?

> Also, certain sort routines are abysmal.  Replace the
> Solaris-provided qsort().

I've read about this as well - but haven't even gotten that far
on the testing/configuring yet.

> I have to say, however, that my experience indicates that
> Solaris is
> slower that the competition for Postgres.  It still shouldn't
> be that bad.

I agree completely.

Thanks for your input,

-X

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac

From
Tom Lane
Date:
CaptainX0r <captainx0r@yahoo.com> writes:
> I've read that somewhere (maybe in the archives?) and I got no
> change with any of them.  But now I'm thinking back - do I need
> fsync=true for that to have an affect?  I'm not worried about
> the cons of having fsync=false at all - and I'm assuming that
> should be better than true and open_datasync.

You are right that fsync_method is a no-op if you've got fsync turned
off.

Let me get this straight: the Sun is slower even with fsync off?  That
shoots down the first theory that I had, which was that the Sun's disk
drives were actually honoring fsync while the laptop's drive does not.
(See archives for more discussion of that, but briefly: IDE drives are
commonly set up to claim write complete as soon as they've absorbed
data into their onboard buffers.  SCSI drives usually tell the truth
about when they've completed a write.)

Andrew Sullivan's nearby recommendation to replace qsort() is a good
one, but PG 7.3 is already configured to do that by default.  (Look in
src/Makefile.global to confirm that qsort.o is mentioned in LIBOBJS.)

I'd suggest starting with some elementary measurements, for example
looking at I/O rates and CPU idle percentage while running the same
task on both Solaris and G3.  That would at least give us a clue whether
I/O or CPU is the bottleneck.

            regards, tom lane

Re: Sun vs. Mac

From
Andrew Sullivan
Date:
On Tue, Jan 14, 2003 at 07:41:21AM -0800, CaptainX0r wrote:
>
> You're right, sorry.  PG 7.3.1 on Solaris 8.  I've got the
> default recommended /etc/system but with shmmax cranked way up

Ok, I have no experience with 7.3.1 in a production setting - we're
using 7.2.  But here are some things.

> which seems to have helped.  I don't have the system in front of
> me (and it's down, so I can't get to it), but from memory
> max_connections was increased to 64, shared_buffers up to 65536,
> sort_mem and vacuum_mem were doubled, and I think that's it.  I
> changed every seemingly relevant one, and spent a lot of time on

You'll need to increase the number of available semaphores more than
likely, if you add any connections.  You do indeed need to fix
shmmax, but if the postmaster starts, you're fine.

I would worry slightly about sort_mem.  I have managed to make
Solaris boxes with _lots_ of memory start swapping by setting that
too high (while experimenting).  Look for problems in your I/O.

> the *cost section trying various factors of n*10 on each, with
> no joy.

These are fine-tuning knobs.  You have a different problem :)

> > Did you split
> > the WAL onto its own filesystem?  You'll get a big win that
> > way.
>
> I have not.  What exactly do you by "own filesystem"?  Another
> filesystem?  I was planning on putting pg_xlog on the OS disk
> and moving $PGDATA off to a second disk.

That's what you need.  Without any doubt at all.  The xlog on the
same UFS filesystem (and disk) as the rest of $PGDATA is a nightmare.

Interestingly, by the way, there is practically _no difference_ if
you do this with an A5200 managed by Veritas.  I have tried dozens of
things.  It never matters.  The array is too fast.

> > Also, what fsync setting are you using (open_datasync is the
> > fastest in my experience).
>
> I've read that somewhere (maybe in the archives?) and I got no
> change with any of them.  But now I'm thinking back - do I need
> fsync=true for that to have an affect?  I'm not worried about
> the cons of having fsync=false at all - and I'm assuming that
> should be better than true and open_datasync.  Or am I confusing
> things?

Yes, if you change the fsync method but have fsync turned off, it
will make no difference.

> > Also, certain sort routines are abysmal.  Replace the
> > Solaris-provided qsort().
>
> I've read about this as well - but haven't even gotten that far
> on the testing/configuring yet.

If you're doing any sorting that is not by an index, forget about it.
Change it now.  It's something like a multiple of 40 slower.

A
--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Sun vs. Mac

From
Lee Kindness
Date:
CaptainX0r writes:
 > I'm having some serious performance issues with PostgreSQL on
 > our newish SunFire 280R (1 900MHz ultrasparc III, 1 GB RAM).
 > It's painfully slow.

What has PostgreSQL been compiled by? Personal past experience has
shown the Sun Workshop C compiler to result in much better performance
compared to GCC...

L.

Re: Sun vs. Mac

From
CaptainX0r
Date:
--- Lee Kindness <lkindness@csl.co.uk> wrote:
> CaptainX0r writes:
>> I'm having some serious performance issues with PostgreSQL on
>> our newish SunFire 280R (1 900MHz ultrasparc III, 1 GB RAM).
>> It's painfully slow.
>
> What has PostgreSQL been compiled by? Personal past experience
> has
> shown the Sun Workshop C compiler to result in much better
> performance
> compared to GCC...

I used gcc - mostly because I have in the past, but also because
I've read that it is "the one to use".  Am I wrong on this one?
I'm certainly willing to try the one from Sun Workshop.

Thanks for the input,

-X

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac

From
CaptainX0r
Date:
> You'll need to increase the number of available semaphores
> more than
> likely, if you add any connections.  You do indeed need to fix
> shmmax, but if the postmaster starts, you're fine.

Thanks, I'll take a closer look at this.

> That's what you need.  Without any doubt at all.  The xlog on
> the same UFS filesystem (and disk) as the rest of $PGDATA is a
> nightmare.

The disks are on order - but that can't be the only thing hold
it up, can it?  I've got to check out the IO, as you suggest.

> > > Also, certain sort routines are abysmal.  Replace the
> > > Solaris-provided qsort().
> >
> > I've read about this as well - but haven't even gotten that
> > far on the testing/configuring yet.
>
> If you're doing any sorting that is not by an index, forget
> about it.
> Change it now.  It's something like a multiple of 40 slower.

I double checked, and this was one of the reasons I was glad to
try 7.3 on Solaris - it's already got it built in.

Thanks much for the input,

-X

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac

From
CaptainX0r
Date:
All,

> Let me get this straight: the Sun is slower even with fsync
> off?  That

Correct.  This really helped a lot, especially with the dump
load, but I've clearly got some more work ahead of me.

> Andrew Sullivan's nearby recommendation to replace qsort() is
> a good
> one, but PG 7.3 is already configured to do that by default.
> (Look in
> src/Makefile.global to confirm that qsort.o is mentioned in
> LIBOBJS.)

Thanks for confirming.  I've got LIBOBJS =  isinf.o qsort.o

> I'd suggest starting with some elementary measurements, for
> example looking at I/O rates and CPU idle percentage while
> running the same task on both Solaris and G3.  That would at
> least give us a clue whether I/O or CPU is the bottleneck.

Good thoughts - I'm working on it right now, though I'm not
really sure how to check I/O rates....

Thanks much for the input,

-X


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac

From
Hannu Krosing
Date:
On Tue, 2003-01-14 at 15:00, CaptainX0r wrote:
> Hello,
>
> I'm having some serious performance issues with PostgreSQL on
> our newish SunFire 280R (1 900MHz ultrasparc III, 1 GB RAM).
> It's painfully slow.  It took me almost a week of tuning to get
> it in the range of an old Mac G3 laptop.  Now, a few days later,
> after tweaking every nearly every parameter (only noting
> decreased performance on some) in /etc/system and
> $PGDATA/postgresql.conf

could you post your $PGDATA/postgresql.conf for our viewing pleasure ?

> it's about as fast as I can make it, but
> still horribly slow.  A few simple queries that take 1.5-7
> minutes on the G3 take 1-1.5 minutes on the Sun.  A bulk load of
> roughly 2.4 GB database dump takes ~1 hour on each machine.  It
> took almost 2 hours on the Sun before I turned off fsync.
>
> We have plans to add another CPU, RAM and another disk, which
> should all help,

Another CPU will probably not help with bulk loads or other single-user
stuff.

> but in its current state, I (and many others)
> would think that it should run circles around the G3. I'm
> thinking that I'm missing something big and obvious because this
> can't be right.  Otherwise we might as well just get a bunch of
> ibooks to run our databases - they're a lot smaller and much
> more quiet.

For single-user tasks you will probably be better off by getting a
gray box with Athlon 2600+ with 3 Gigs of memory and IDE disks
and running Linux or *BSD .

> Can someone please point me in the right direction?
>
> Thanks,
>
> -X
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Hannu Krosing <hannu@tm.ee>

Re: Sun vs. Mac

From
CaptainX0r
Date:
> I'd suggest starting with some elementary measurements, for
> example looking at I/O rates and CPU idle percentage while
> running the same task on both Solaris and G3.  That would at
> least give us a clue whether I/O or CPU is the bottleneck.

Well, I've got the Sun box now, but I don't really have acces to
the G3.  FWIW, top shows postgres slowly taking up all the CPU -
over the course of a minute or so it gradually ramps up to
around 90%.  Once the query is complete, however, top shows the
CPU ramping down slowly, ~1-2% per second over the next 2
minutes which I find very strange.  The CPU idle is 0% for the
duration of the query, while the user state is around 100% for
the same period.  This kind of makes me think top is wrong (100%
idle and 75% postgres?)

iostat gives: (sorry for line wrap).

# iostat -DcxnzP
     cpu
 us sy wt id
 10  1  4 85
                    extended device statistics
    r/s    w/s   kr/s   kw/s wait actv wsvc_t asvc_t  %w  %b
device
   11.2    1.0   65.5   13.1  0.1  0.1    9.5    9.6   0   3
c1t0d0s0
    0.0    0.0    0.0    0.1  0.0  0.0    0.0    6.0   0   0
c1t0d0s1
    7.3    0.1  502.3    0.5  0.0  0.0    0.0    2.3   0   1
c1t0d0s3
    0.0    0.0    0.0    0.0  0.0  0.0    0.0    0.1   0   0
host:vold(pid313)


This doesn't really tell me much, except I'm guessing that PG is
CPU bound?

-X

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac

From
Andrew Sullivan
Date:
On Tue, Jan 14, 2003 at 09:50:04AM -0800, CaptainX0r wrote:
> the G3.  FWIW, top shows postgres slowly taking up all the CPU -

Son't use top on Solaris 8.  It's inaccurate, and it affects the
results itself.  Use prstat instead.

> This doesn't really tell me much, except I'm guessing that PG is
> CPU bound?

It looks that way.  I've had iostat show CPU-bound, however, when the
problem actually turned out to be memory contention.  I think you may
want to have a poke with vmstat, and also have a look at the SE
toolkit.

A

----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: Sun vs. Mac - best Postgres platform?

From
CaptainX0r
Date:
> could you post your $PGDATA/postgresql.conf for our viewing
> pleasure ?

max_connections = 64
shared_buffers = 65536          # 1/2 total RAM /8K
sort_mem = 100000               # min 64, size in KB
checkpoint_timeout = 300        # range 30-3600, in seconds
fsync = false
effective_cache_size = 65536    # typically 8KB each
log_timestamp = true
notice, warning, error
stats_command_string = true
stats_row_level = true
stats_block_level = true
LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'

I've stripped out the default lines (grep -v ^#) comments and
blank lines.

> Another CPU will probably not help with bulk loads or other
> single-user stuff.
>
[snip]
>
> For single-user tasks you will probably be better off by
> getting a gray box with Athlon 2600+ with 3 Gigs of memory and

> IDE disks and running Linux or *BSD .

Hannu brings up a good point - one that was debated before my
attempts at making Solaris faster.  If you were going to make a
fast postgres server what would you use?  Assuming you could
afford a SunFire 280R (~$8k?), would that money be better spent
on a (say) Dell server running (say) linux?  We're doing light
multiuser (I guess effectively single user)  but at some point
(years) this may grow considereably.  I'm not particular to
Macs, but I've got to say, that stock out the box, postgres
loves it.  That old G3 was faster than the Sun, and still is
faster than my (years newer) linux laptop (on which I've done no
performance tweaking).  So maybe a dual G4 Xserver would scream?

Any suggestions?  It's still not too late for us to change our
minds on this one.

Thanks much,

-X


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac

From
Tom Lane
Date:
CaptainX0r <captainx0r@yahoo.com> writes:
> Well, I've got the Sun box now, but I don't really have acces to
> the G3.  FWIW, top shows postgres slowly taking up all the CPU -
> over the course of a minute or so it gradually ramps up to
> around 90%.  Once the query is complete, however, top shows the
> CPU ramping down slowly, ~1-2% per second over the next 2
> minutes which I find very strange.

I believe top's percent-of-CPU numbers for individual processes are time
averages over a minute or so, so the ramping effect is unsurprising.

> This doesn't really tell me much, except I'm guessing that PG is
> CPU bound?

Yup, that seems pretty clear.  Next step is to find out what the heck
it's doing.  My instinct would be to use gprof.  Recompile with
profiling enabled --- if you're using gcc, this should work
    cd postgres-distribution/src/backend
    make clean
    make PROFILE=-pg all
    make install-bin    -- may need to stop postmaster before install
Next run some sample queries (put them all into one session).  After
quitting the session, find gmon.out in the $PGDATA/base/nnn/
subdirectory corresponding to your database, and feed it to gprof.
The results should show where the code hotspot is.

            regards, tom lane

Re: Sun vs. Mac

From
"scott.marlowe"
Date:
On Tue, 14 Jan 2003, CaptainX0r wrote:

> Hello,
>
> I'm having some serious performance issues with PostgreSQL on
> our newish SunFire 280R (1 900MHz ultrasparc III, 1 GB RAM).
> It's painfully slow.  It took me almost a week of tuning to get
> it in the range of an old Mac G3 laptop.  Now, a few days later,
> after tweaking every nearly every parameter (only noting
> decreased performance on some) in /etc/system and
> $PGDATA/postgresql.conf it's about as fast as I can make it, but
> still horribly slow.  A few simple queries that take 1.5-7
> minutes on the G3 take 1-1.5 minutes on the Sun.  A bulk load of
> roughly 2.4 GB database dump takes ~1 hour on each machine.  It
> took almost 2 hours on the Sun before I turned off fsync.

Just for giggles, do you have a spare drive or something you can try
loading debian or some other Sparc compatible linux distro and get some
numbers?  My experience has been that on the same basic hardware, Linux
runs postgresql about twice as fast as Solaris, and no amount of tweaking
seemed to ever get postgresql up to the same performance on Solaris.  It's
so bad a Sparc 20 with 256 Meg ram and a 50 MHz 32 bit CPU running linux
was outrunning our Sun Ultra 1 with 512 Meg ram and a 150 MHz 64 bit CPU
by about 50%.  That was with the 2.0.x kernel for linux and Solaris 7 on
the Ultra I believe.  Could have been older on the Solaris version, as I
wasn't the SA on that box.


Re: Sun vs. Mac

From
CaptainX0r
Date:
--- Andrew Sullivan <andrew@libertyrms.info> wrote:
> On Tue, Jan 14, 2003 at 09:50:04AM -0800, CaptainX0r wrote:
> > the G3.  FWIW, top shows postgres slowly taking up all the
> CPU -
>
> Son't use top on Solaris 8.  It's inaccurate, and it affects
> the results itself.  Use prstat instead.

Thanks for the tip.  Unfortunately it shows the same exact
thing.

> > This doesn't really tell me much, except I'm guessing that
> PG is
> > CPU bound?
>
> It looks that way.  I've had iostat show CPU-bound, however,
> when the problem actually turned out to be memory contention.

> I think you may want to have a poke with vmstat, and also have

> a look at the SE toolkit.

I'll have a look at the SE toolkit - thanks.

vmstat shows me this:
# vmstat -s
        0 swap ins
        0 swap outs
        0 pages swapped in
        0 pages swapped out
   125452 total address trans. faults taken
    35245 page ins
       60 page outs
   194353 pages paged in
      229 pages paged out
   184621 total reclaims
   184563 reclaims from free list
        0 micro (hat) faults
   125452 minor (as) faults
    31764 major faults
    10769 copy-on-write faults
    80220 zero fill page faults
        0 pages examined by the clock daemon
        0 revolutions of the clock hand
      170 pages freed by the clock daemon
      601 forks
       19 vforks
      577 execs
   370612 cpu context switches
  1288431 device interrupts
   148288 traps
  1222653 system calls
   294090 total name lookups (cache hits 48%)
    43510 user   cpu
     4002 system cpu
   480912 idle   cpu
    13805 wait   cpu

 procs     memory            page            disk
faults      cpu
 r b w   swap  free  re  mf pi po fr de sr s6 sd -- --   in   sy
  cs us sy id
 0 0 0 815496 538976 31  21 261 0  0  0  0  0 12  0  0  136  209
  65  7  1 92

I've not much experience with this, it looks like there are
considerably more page ins than outs as compared to our other
solaris boxen but otherwise pretty normal.

-X

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac - best Postgres platform?

From
Andrew Sullivan
Date:
On Tue, Jan 14, 2003 at 10:10:54AM -0800, CaptainX0r wrote:
> > could you post your $PGDATA/postgresql.conf for our viewing
> > pleasure ?
>
> max_connections = 64
> shared_buffers = 65536          # 1/2 total RAM /8K
> sort_mem = 100000               # min 64, size in KB
             ^^^^^^
There's your problem.  Don't set that anywhere near that high.
If you run 2 queries that require sorting, _each sort_ can use up to
100000 K.  Which can chew up all your memory pretty fast.

> effective_cache_size = 65536    # typically 8KB each

What basis did you have to change this?  Have you done work figuring
out how big the kernel's disk cache is regularly on that system?

> Hannu brings up a good point - one that was debated before my
> attempts at making Solaris faster.  If you were going to make a
> fast postgres server what would you use?  Assuming you could
> afford a SunFire 280R (~$8k?), would that money be better spent
> on a (say) Dell server running (say) linux?  We're doing light

I've been finding FreeBSD way faster than Linux.  But yes.

A
--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: Sun vs. Mac - best Postgres platform?

From
eric soroos
Date:
> > For single-user tasks you will probably be better off by
> > getting a gray box with Athlon 2600+ with 3 Gigs of memory and
>
> > IDE disks and running Linux or *BSD .
>

> I'm not particular to
> Macs, but I've got to say, that stock out the box, postgres
> loves it.  That old G3 was faster than the Sun, and still is
> faster than my (years newer) linux laptop (on which I've done no
> performance tweaking).  So maybe a dual G4 Xserver would scream?
>
> Any suggestions?  It's still not too late for us to change our
> minds on this one.

I can't recommend macs for either brute force speed or price/performance.

My current flock of machines are mostly OSX g4 boxes (single 400s and dual 800), with a couple of linux boxen thrown in
forgood measure. 

The mac's biggest issues are:

1) Tweakability - you've got one file system, and it doesn't really do useful mount options like noatime.
2) There are bugs in mount and traversing symlinks that make it hard to move pg_xlog onto another file system and
retainperformance (in 10.1.5, I don't have test hardware with enough drives to test 10.2) 
3) vm_stat gives vm status, iostat gives nothing. Looks like this is working on my 10.2 laptop, but it's annoyed me for
awhile on 10.1.5 
4) SW raid is not that much of a help for speed.
5) Bus bandwidth is a good factor behind x86 linux boxen. (DDR ram isn't really taken advantage of in current designs)

Having said that, I'm getting reasonable performance out of all the macs, in fact, I'm getting reasonably similar
performanceout of all of them desplite the 4x difference in processor power.  And that's because they basically have
thesame low end disk system.  

I'm piecing together something that I hope will be faster out of a x86 box  loaded with more drives in a sw raid
mirroringsetup.  (tentative is 1x system+logs, mirror for pg_xlog, mirror for pg data) 

I'm planning on running some comparative benchmarks prior to going live, so I should be able to tell how much faster it
is.

eric




Re: Sun vs. Mac

From
CaptainX0r
Date:
Hi,

> Just for giggles, do you have a spare drive or something you
> can try
> loading debian or some other Sparc compatible linux distro and
> get some
> numbers?  My experience has been that on the same basic

This was on the todo list, "just to see", but I'm not sure how
much time we want to spend trying a myriad of options when
concentrating on one should (maybe?) do the trick.

> hardware, Linux runs postgresql about twice as fast as
> Solaris, and no amount of tweaking  seemed to ever get
> postgresql up to the same performance on Solaris.  It's
> so bad a Sparc 20 with 256 Meg ram and a 50 MHz 32 bit CPU
> running linux  was outrunning our Sun Ultra 1 with 512 Meg ram

> and a 150 MHz 64 bit CPU  by about 50%.  That was with the
> 2.0.x kernel for linux and

This is not encouraging.....  We may be revisiting the linux
option.

Thanks much for the input,

-X

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac - best Postgres platform?

From
CaptainX0r
Date:
--- Andrew Sullivan <andrew@libertyrms.info> wrote:
> On Tue, Jan 14, 2003 at 10:10:54AM -0800, CaptainX0r wrote:
> > > could you post your $PGDATA/postgresql.conf for our
> viewing
> > > pleasure ?
> >
> > max_connections = 64
> > shared_buffers = 65536          # 1/2 total RAM /8K
> > sort_mem = 100000               # min 64, size in KB
>              ^^^^^^
> There's your problem.  Don't set that anywhere near that high.
> If you run 2 queries that require sorting, _each sort_ can use
> up to
> 100000 K.  Which can chew up all your memory pretty fast.

I changed back to the default 1024, and down to the minimum, 64
- no change.  I think that was changed simultaneously with some
other parameter (bad, I know) that actually had an affect.  I
guess I can remove it.

> > effective_cache_size = 65536    # typically 8KB each
>
> What basis did you have to change this?  Have you done work
> figuring out how big the kernel's disk cache is regularly on
> that system?

I read somewhere that this should be set to half the system RAM
size, 64k*8k=512m = 1/2 of the 1 Gig RAM.  I guess this is way
off since you're saying that it's disk cache.  This agrees with
the documentation.  I can't really rely on the (precious little
Solaris postgres) info I find on the net....  ;)

Unfortunately, setting back to 1000 doesn't appear to help.

> > Hannu brings up a good point - one that was debated before
> > my attempts at making Solaris faster.  If you were going to
> make a
> > fast postgres server what would you use?  Assuming you could
> > afford a SunFire 280R (~$8k?), would that money be better
> > spent on a (say) Dell server running (say) linux?  We're
> > doing light
>
> I've been finding FreeBSD way faster than Linux.  But yes.

I like to hear this since I'm a big FreeBSD fan.  So far I think
I've understood this as: FreeBSD > Linux > OSX > Solaris.

Thanks much for the input,

-X

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac - best Postgres platform?

From
Andrew Sullivan
Date:
On Tue, Jan 14, 2003 at 11:01:49AM -0800, CaptainX0r wrote:

> I changed back to the default 1024, and down to the minimum, 64
> - no change.  I think that was changed simultaneously with some
> other parameter (bad, I know) that actually had an affect.  I
> guess I can remove it.

Very bad to change two things at once.  You think it's saving you
time, but now . . . well, you already know what happens ;-)  Anyway,
you _still_ shouldn't have it that high.

> > > effective_cache_size = 65536    # typically 8KB each
>
> I read somewhere that this should be set to half the system RAM
> size, 64k*8k=512m = 1/2 of the 1 Gig RAM.  I guess this is way
> off since you're saying that it's disk cache.  This agrees with
> the documentation.  I can't really rely on the (precious little
> Solaris postgres) info I find on the net....  ;)

I think you should rely on the Postgres documentation, which has way
fewer errors than just about any other technical documentation I've
ever seen.  Yes, it's disk cache.

I wouldn't set _anything_ to half the system RAM.  It'd be real nice
if your disk cache was half your RAM, but I'd be amazed if anyone's
system were that efficient.

It sounds like you need to follow Tom Lane's advice, though, and do
some profiling.

A
--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: Sun vs. Mac

From
CaptainX0r
Date:
> I believe top's percent-of-CPU numbers for individual
> processes are time
> averages over a minute or so, so the ramping effect is
> unsurprising.

Thanks - this makes much more sense.

> > This doesn't really tell me much, except I'm guessing that
> > PG is CPU bound?
>
> Yup, that seems pretty clear.  Next step is to find out what
> the heck
> it's doing.  My instinct would be to use gprof.  Recompile
> with
> profiling enabled --- if you're using gcc, this should work
>     cd postgres-distribution/src/backend
>     make clean
>     make PROFILE=-pg all
>     make install-bin    -- may need to stop postmaster
> Next run some sample queries (put them all into one session).
> After quitting the session, find gmon.out in the
> $PGDATA/base/nnn/ subdirectory corresponding to your database,

> and feed it to gprof.
> The results should show where the code hotspot is.

Well if that isn't a fancy bit of info....  Thanks!

gprof says:

Fatal ELF error: can't read ehdr (Request error: class
file/memory mismatch)

I'm guessing that's not what we're expecting...  I'm using
/usr/ccs/bin/gprof - maybe there's a better one?

-X


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac - best Postgres platform?

From
Justin Clift
Date:
Andrew Sullivan wrote:
<snip>
 > I've been finding FreeBSD way faster than Linux.  But yes.

Out of curiosity, have you been trying FreeBSD 4.7x or the developer
releases of 5.0?  Apparently there are new kernel scheduling
improvements in FreeBSD 5.0 that will help certain types of tasks and
might boost our performance further.

Would be interested in seeing if the profiling/optimisation options of
GCC 3.2.x are useful as well.

:-)

Regards and best wishes,

Justin Clift


 > A


--
"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


Re: Sun vs. Mac - best Postgres platform?

From
Andrew Sullivan
Date:
On Wed, Jan 15, 2003 at 10:29:46AM +1030, Justin Clift wrote:
> Andrew Sullivan wrote:
> <snip>
>  > I've been finding FreeBSD way faster than Linux.  But yes.
>
> Out of curiosity, have you been trying FreeBSD 4.7x or the developer

Just 4.7x.  And mostly for little jobs for myself, so I can't speak
about testing it in a production case.

A


--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: Sun vs. Mac

From
Tom Lane
Date:
CaptainX0r <captainx0r@yahoo.com> writes:
> gprof says:
> Fatal ELF error: can't read ehdr (Request error: class
> file/memory mismatch)

Hm, that's a new one on me.  Just to eliminate the obvious: you did
read the gprof man page?  It typically needs both the pathname of the
postgres executable and that of the gmon.out file.

If that's not it, I fear you need a gprof expert, which I ain't.

            regards, tom lane

Re: Sun vs. Mac - gprof output

From
CaptainX0r
Date:
All,

> Hm, that's a new one on me.  Just to eliminate the obvious:
> you did read the gprof man page?  It typically needs both the
> pathname of the postgres executable and that of the gmon.out

I read it, but apparently not very well.  It appears that as long as gmon.out
is in the current dir, all that's needed is the name of the executeable (with
full path).  The way it's formated I read it as all that's needed is the
image-file.  Anyways...

There's a ton of output, so I'm picking what appear to be the highlights.

granularity: each sample hit covers 4 byte(s) for 0.00% of 705.17 seconds

                                  called/total       parents
index  %time    self descendents  called+self    name           index
                                  called/total       children

[1]     63.6  446.05        2.65 44386289+57463869 <cycle 1 as a whole> [1]
              442.08        2.50 59491100+2566048020   <external>  <cycle 1>
[2]
                2.91        0.00 23045572+366         _fini     <cycle 1> [23]
                0.57        0.00 17763681             _rl_input_available
<cycle 1> [42]
                0.11        0.15  478216+7           history_expand     <cycle
1> [54]
                0.21        0.00       7             history_tokenize_internal
<cycle 1> [56]
                0.10        0.00 1071137             tilde_expand       <cycle
1> [68]
                0.07        0.00     397             rl_gather_tyi      <cycle
1> [70]
                0.00        0.00      31             qsort      <cycle 1> [81]
                0.00        0.00      17             rl_insert_close    <cycle
1> [82]
-----------------------------------------------
                                                     <spontaneous>
[3]     32.3    0.65      227.47                 rl_get_termcap [3]
              226.13        1.34 22502064/44386289   <external>    <cycle 1>
[2]
-----------------------------------------------
                                                     <spontaneous>
[4]     26.3    0.78      184.35                 rl_stuff_char [4]
              178.51        1.06 17763681/44386289    <external>   <cycle 1>
[2]
                4.78        0.00 17763681/17763681     rl_clear_signals [18]
-----------------------------------------------
                                                     <spontaneous>
[5]     15.8  111.61        0.00                 rl_signal_handler [5]
                0.00        0.00       1/44386289    <external>    <cycle 1>
[2]
-----------------------------------------------
                                                     <spontaneous>
[6]      4.3   30.57        0.00                 rl_sigwinch_handler [6]
-----------------------------------------------

And:

granularity: each sample hit covers 4 byte(s) for 0.00% of 705.17 seconds

   %  cumulative    self              self    total
 time   seconds   seconds    calls  ms/call  ms/call name
 15.8     111.61   111.61                            rl_signal_handler [5]
  4.3     142.18    30.57                            rl_sigwinch_handler [6]
  1.9     155.42    13.24                            rl_set_sighandler [8]
  1.9     168.52    13.10                            rl_maybe_set_sighandler
[9]
  1.1     176.37     7.85                            _rl_next_macro_key [11]
  0.9     182.38     6.01                            rl_read_key [12]
  0.8     188.07     5.69                            rl_backward_kill_line [13]
  0.8     193.56     5.49                            rl_unix_word_rubout [14]
  0.8     198.91     5.35                           _rl_pop_executing_macro
[15]
  0.7     203.73     4.82                         _rl_fix_last_undo_of_type
[17]
  0.7     208.51     4.78 17763681     0.00     0.00  rl_clear_signals [18]
  0.6     212.87     4.36                            rl_modifying [19]
  0.6     216.95     4.08                            rl_begin_undo_group [20]
  0.6     221.00     4.05                            rl_tilde_expand [21]
  0.4     223.98     2.98                            region_kill_internal [22]
  0.4     226.89     2.91 23045572     0.00     0.00  _fini     <cycle 1> [23]


So.  Any thoughts?  This looks really useful in the hands of someone who knows
what it all means.  Looks like some signal handlers are using up most of the
time.  Good?  Bad?  Am I reading that first part correctly in that a good part
of the time spent is external to Postgres?  This report also seems to verify
that qsort isn't a problem since it was the 81st index, with 31 calls (not
much) and 0.00 self seconds.

Thanks much,

-X


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: Sun vs. Mac - gprof output

From
Tom Lane
Date:
CaptainX0r <captainx0r@yahoo.com> writes:
>    %  cumulative    self              self    total
>  time   seconds   seconds    calls  ms/call  ms/call name
>  15.8     111.61   111.61                            rl_signal_handler [5]
>   4.3     142.18    30.57                            rl_sigwinch_handler [6]
>   1.9     155.42    13.24                            rl_set_sighandler [8]
>   1.9     168.52    13.10                            rl_maybe_set_sighandler
> [9]
>   1.1     176.37     7.85                            _rl_next_macro_key [11]
>   0.9     182.38     6.01                            rl_read_key [12]
>   0.8     188.07     5.69                            rl_backward_kill_line [13]

All of these names correspond to internal routines in libreadline.

It'd not be surprising for libreadline to suck a good deal of the
runtime of psql ... but I don't believe the backend will call it at all.
So, either this trace is erroneous, or you profiled the wrong process
(client instead of backend), or there's something truly weird going on.

            regards, tom lane

Re: Sun vs. Mac - gprof output

From
CaptainX0r
Date:
> It'd not be surprising for libreadline to suck a good deal of the
> runtime of psql ... but I don't believe the backend will call it at all.
> So, either this trace is erroneous, or you profiled the wrong process
> (client instead of backend), or there's something truly weird going on.

You're right, I got the client, here's the backend:

   %  cumulative    self              self    total
 time   seconds   seconds    calls  ms/call  ms/call name
 23.4     125.31   125.31                            internal_mcount [13]
 21.2     239.07   113.76 79296415     0.00     0.00  ExecMakeFunctionResult
<cycle 4> [14]
  7.8     280.68    41.61 98971658     0.00     0.00  AllocSetReset [23]
  6.8     317.13    36.45 193735603     0.00     0.00  ExecEvalVar [18]
  5.2     345.21    28.08 280731963     0.00     0.00  ExecEvalExpr     <cycle
4> [15]
  2.7     359.93    14.72 38140599     0.00     0.00  nocachegetattr [35]
  2.7     374.28    14.35   320207     0.04     0.04  _read [38]
  2.2     385.97    11.69 78969393     0.00     0.00  ExecQual  <cycle 4> [34]
  2.1     397.46    11.49 79296415     0.00     0.00  ExecEvalFuncArgs  <cycle
4> [42]
  1.4     404.71     7.25                            _mcount (6219)
  1.3     411.73     7.02 11293115     0.00     0.00  heapgettup [31]
  1.2     418.34     6.61 98975017     0.00     0.00  ExecClearTuple [43]
  1.0     423.93     5.59 98971592     0.00     0.00  ExecStoreTuple [33]
  0.9     428.87     4.94 197952332     0.00     0.00  MemoryContextSwitchTo
[53]
  0.9     433.53     4.66  7612547     0.00     0.00  heap_formtuple [39]
  0.8     437.96     4.43  7609318     0.00     0.01  ExecScanHashBucket
<cycle 4> [17]
  0.8     442.34     4.38        8   547.50   547.50  .rem [55]
  0.8     446.64     4.30 79296261     0.00     0.00  ExecEvalOper      <cycle
4> [56]


I'm not sure what to make of this.

Thanks,

-X


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com