pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Sun vs. Mac
20+ messages / 7 participants
[nested] [flat]

* Sun vs. Mac
@ 2003-01-14 15:00 CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:09 ` Re: Sun vs. Mac Justin Clift <justin@postgresql.org>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 16:18 ` Sun vs. Mac Lee Kindness <lkindness@csl.co.uk>
  2003-01-14 18:26 ` Re: Sun vs. Mac scott.marlowe <scott.marlowe@ihs.com>
  2003-01-14 19:26 ` Re: Sun vs. Mac Hannu Krosing <hannu@tm.ee>
  0 siblings, 5 replies; 20+ messages in thread

From: CaptainX0r @ 2003-01-14 15:00 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 15:09 ` Justin Clift <justin@postgresql.org>
  2003-01-14 15:18   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  4 siblings, 1 reply; 20+ messages in thread

From: Justin Clift @ 2003-01-14 15:09 UTC (permalink / raw)
  To: captainx0r@yahoo.com; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:09 ` Re: Sun vs. Mac Justin Clift <justin@postgresql.org>
@ 2003-01-14 15:18   ` CaptainX0r <captainx0r@yahoo.com>
  0 siblings, 0 replies; 20+ messages in thread

From: CaptainX0r @ 2003-01-14 15:18 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 15:10 ` Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 15:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  4 siblings, 1 reply; 20+ messages in thread

From: Andrew Sullivan @ 2003-01-14 15:10 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
@ 2003-01-14 15:41   ` CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 16:04     ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-14 16:08     ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  0 siblings, 2 replies; 20+ messages in thread

From: CaptainX0r @ 2003-01-14 15:41 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 15:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 16:04     ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-14 16:54       ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 17:50       ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  1 sibling, 2 replies; 20+ messages in thread

From: Tom Lane @ 2003-01-14 16:04 UTC (permalink / raw)
  To: captainx0r@yahoo.com; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 15:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 16:04     ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-14 16:54       ` CaptainX0r <captainx0r@yahoo.com>
  1 sibling, 0 replies; 20+ messages in thread

From: CaptainX0r @ 2003-01-14 16:54 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 15:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 16:04     ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-14 17:50       ` CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 17:58         ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 18:15         ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 2 replies; 20+ messages in thread

From: CaptainX0r @ 2003-01-14 17:50 UTC (permalink / raw)
  To: pgsql-performance

> 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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 15:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 16:04     ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-14 17:50       ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 17:58         ` Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 18:27           ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  1 sibling, 1 reply; 20+ messages in thread

From: Andrew Sullivan @ 2003-01-14 17:58 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 15:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 16:04     ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-14 17:50       ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 17:58         ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
@ 2003-01-14 18:27           ` CaptainX0r <captainx0r@yahoo.com>
  0 siblings, 0 replies; 20+ messages in thread

From: CaptainX0r @ 2003-01-14 18:27 UTC (permalink / raw)
  To: pgsql-performance


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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 15:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 16:04     ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-14 17:50       ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 18:15         ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-14 19:38           ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  1 sibling, 1 reply; 20+ messages in thread

From: Tom Lane @ 2003-01-14 18:15 UTC (permalink / raw)
  To: captainx0r@yahoo.com; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 15:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 16:04     ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-14 17:50       ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 18:15         ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-14 19:38           ` CaptainX0r <captainx0r@yahoo.com>
  2003-01-15 05:12             ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 20+ messages in thread

From: CaptainX0r @ 2003-01-14 19:38 UTC (permalink / raw)
  To: pgsql-performance

> 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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 15:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 16:04     ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-14 17:50       ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 18:15         ` Re: Sun vs. Mac Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-14 19:38           ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-15 05:12             ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 20+ messages in thread

From: Tom Lane @ 2003-01-15 05:12 UTC (permalink / raw)
  To: captainx0r@yahoo.com; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 15:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 16:08     ` Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 16:38       ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  1 sibling, 1 reply; 20+ messages in thread

From: Andrew Sullivan @ 2003-01-14 16:08 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 15:10 ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 15:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 16:08     ` Re: Sun vs. Mac Andrew Sullivan <andrew@libertyrms.info>
@ 2003-01-14 16:38       ` CaptainX0r <captainx0r@yahoo.com>
  0 siblings, 0 replies; 20+ messages in thread

From: CaptainX0r @ 2003-01-14 16:38 UTC (permalink / raw)
  To: pgsql-performance

> 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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 16:18 ` Lee Kindness <lkindness@csl.co.uk>
  2003-01-14 16:29   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  4 siblings, 1 reply; 20+ messages in thread

From: Lee Kindness @ 2003-01-14 16:18 UTC (permalink / raw)
  To: captainx0r@yahoo.com; +Cc: pgsql-performance@postgresql.org, Lee Kindness <lkindness@csl.co.uk>

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.



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 16:18 ` Sun vs. Mac Lee Kindness <lkindness@csl.co.uk>
@ 2003-01-14 16:29   ` CaptainX0r <captainx0r@yahoo.com>
  0 siblings, 0 replies; 20+ messages in thread

From: CaptainX0r @ 2003-01-14 16:29 UTC (permalink / raw)
  To: pgsql-performance


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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 18:26 ` scott.marlowe <scott.marlowe@ihs.com>
  2003-01-14 18:41   ` Re: Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  4 siblings, 1 reply; 20+ messages in thread

From: scott.marlowe @ 2003-01-14 18:26 UTC (permalink / raw)
  To: CaptainX0r <captainx0r@yahoo.com>; +Cc: pgsql-performance

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.




^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 18:26 ` Re: Sun vs. Mac scott.marlowe <scott.marlowe@ihs.com>
@ 2003-01-14 18:41   ` CaptainX0r <captainx0r@yahoo.com>
  0 siblings, 0 replies; 20+ messages in thread

From: CaptainX0r @ 2003-01-14 18:41 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 20+ messages in thread

* Re: Sun vs. Mac
  2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 19:26 ` Hannu Krosing <hannu@tm.ee>
  4 siblings, 0 replies; 20+ messages in thread

From: Hannu Krosing @ 2003-01-14 19:26 UTC (permalink / raw)
  To: captainx0r@yahoo.com; +Cc: pgsql-performance

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>



^ permalink  raw  reply  [nested|flat] 20+ messages in thread


end of thread, other threads:[~2003-01-15 05:12 UTC | newest]

Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-14 15:00 Sun vs. Mac CaptainX0r <captainx0r@yahoo.com>
2003-01-14 15:09 ` Justin Clift <justin@postgresql.org>
2003-01-14 15:18   ` CaptainX0r <captainx0r@yahoo.com>
2003-01-14 15:10 ` Andrew Sullivan <andrew@libertyrms.info>
2003-01-14 15:41   ` CaptainX0r <captainx0r@yahoo.com>
2003-01-14 16:04     ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-14 16:54       ` CaptainX0r <captainx0r@yahoo.com>
2003-01-14 17:50       ` CaptainX0r <captainx0r@yahoo.com>
2003-01-14 17:58         ` Andrew Sullivan <andrew@libertyrms.info>
2003-01-14 18:27           ` CaptainX0r <captainx0r@yahoo.com>
2003-01-14 18:15         ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-14 19:38           ` CaptainX0r <captainx0r@yahoo.com>
2003-01-15 05:12             ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-14 16:08     ` Andrew Sullivan <andrew@libertyrms.info>
2003-01-14 16:38       ` CaptainX0r <captainx0r@yahoo.com>
2003-01-14 16:18 ` Lee Kindness <lkindness@csl.co.uk>
2003-01-14 16:29   ` CaptainX0r <captainx0r@yahoo.com>
2003-01-14 18:26 ` scott.marlowe <scott.marlowe@ihs.com>
2003-01-14 18:41   ` CaptainX0r <captainx0r@yahoo.com>
2003-01-14 19:26 ` Hannu Krosing <hannu@tm.ee>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox