pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Sun vs. Mac - best Postgres platform?
7+ messages / 4 participants
[nested] [flat]

* Re: Sun vs. Mac - best Postgres platform?
@ 2003-01-14 18:10 CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 18:30 ` Re: Sun vs. Mac - best Postgres platform? Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 18:39 ` Re: Sun vs. Mac - best Postgres platform? eric soroos <eric-psql@soroos.net>
  0 siblings, 2 replies; 7+ messages in thread

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

> 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



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

* Re: Sun vs. Mac - best Postgres platform?
  2003-01-14 18:10 Re: Sun vs. Mac - best Postgres platform? CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 18:30 ` Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 19:01   ` Re: Sun vs. Mac - best Postgres platform? CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 23:59   ` Re: Sun vs. Mac - best Postgres platform? Justin Clift <justin@postgresql.org>
  1 sibling, 2 replies; 7+ messages in thread

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

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




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

* Re: Sun vs. Mac - best Postgres platform?
  2003-01-14 18:10 Re: Sun vs. Mac - best Postgres platform? CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 18:30 ` Re: Sun vs. Mac - best Postgres platform? Andrew Sullivan <andrew@libertyrms.info>
@ 2003-01-14 19:01   ` CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 19:18     ` Re: Sun vs. Mac - best Postgres platform? Andrew Sullivan <andrew@libertyrms.info>
  1 sibling, 1 reply; 7+ messages in thread

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


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



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

* Re: Sun vs. Mac - best Postgres platform?
  2003-01-14 18:10 Re: Sun vs. Mac - best Postgres platform? CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 18:30 ` Re: Sun vs. Mac - best Postgres platform? Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 19:01   ` Re: Sun vs. Mac - best Postgres platform? CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 19:18     ` Andrew Sullivan <andrew@libertyrms.info>
  0 siblings, 0 replies; 7+ messages in thread

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

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




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

* Re: Sun vs. Mac - best Postgres platform?
  2003-01-14 18:10 Re: Sun vs. Mac - best Postgres platform? CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 18:30 ` Re: Sun vs. Mac - best Postgres platform? Andrew Sullivan <andrew@libertyrms.info>
@ 2003-01-14 23:59   ` Justin Clift <justin@postgresql.org>
  2003-01-15 00:02     ` Re: Sun vs. Mac - best Postgres platform? Andrew Sullivan <andrew@libertyrms.info>
  1 sibling, 1 reply; 7+ messages in thread

From: Justin Clift @ 2003-01-14 23:59 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance

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




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

* Re: Sun vs. Mac - best Postgres platform?
  2003-01-14 18:10 Re: Sun vs. Mac - best Postgres platform? CaptainX0r <captainx0r@yahoo.com>
  2003-01-14 18:30 ` Re: Sun vs. Mac - best Postgres platform? Andrew Sullivan <andrew@libertyrms.info>
  2003-01-14 23:59   ` Re: Sun vs. Mac - best Postgres platform? Justin Clift <justin@postgresql.org>
@ 2003-01-15 00:02     ` Andrew Sullivan <andrew@libertyrms.info>
  0 siblings, 0 replies; 7+ messages in thread

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

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




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

* Re: Sun vs. Mac - best Postgres platform?
  2003-01-14 18:10 Re: Sun vs. Mac - best Postgres platform? CaptainX0r <captainx0r@yahoo.com>
@ 2003-01-14 18:39 ` eric soroos <eric-psql@soroos.net>
  1 sibling, 0 replies; 7+ messages in thread

From: eric soroos @ 2003-01-14 18:39 UTC (permalink / raw)
  To: pgsql-performance


> > 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 for good 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 retain performance (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 a while 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 performance out of all of them desplite the 4x difference in processor power.  And that's because they basically have the same 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 mirroring setup.  (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






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


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

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-14 18:10 Re: Sun vs. Mac - best Postgres platform? CaptainX0r <captainx0r@yahoo.com>
2003-01-14 18:30 ` Andrew Sullivan <andrew@libertyrms.info>
2003-01-14 19:01   ` CaptainX0r <captainx0r@yahoo.com>
2003-01-14 19:18     ` Andrew Sullivan <andrew@libertyrms.info>
2003-01-14 23:59   ` Justin Clift <justin@postgresql.org>
2003-01-15 00:02     ` Andrew Sullivan <andrew@libertyrms.info>
2003-01-14 18:39 ` eric soroos <eric-psql@soroos.net>

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