pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
postgres on apple OS X server
2+ messages / 2 participants
[nested] [flat]

* postgres on apple OS X server
@ 2004-03-09 21:04  Mira Kaloper <mkaloper@ucdavis.edu>
  0 siblings, 1 reply; 2+ messages in thread

From: Mira Kaloper @ 2004-03-09 21:04 UTC (permalink / raw)
  To: pgsql-admin; mkaloper@ucdavis.edu



Hi,

I have a postgres database that is VERY slow - in one table we have 50 
mil records in it.
I have tried to play with postgres.conf file and shared memory but was 
not able to speed it up to something that we can work with. We have 
apple RAID and I am planning to take database to the raid. I was 
wondering if someone has any hints about improving the speed on postgres.

Currently we have:
shmall = 134217728
shmmax = 134217728

and in postgresql.conf  I was setting up values to:
shared_buffers = 15200
sort_mem = 32168



Can someone please help me to tune up this database?

Thanks a lot for your help,

Mira

P.S. Please reply also to mkaloper@ucdavis.edu




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

* Re: postgres on apple OS X server
@ 2004-03-09 22:12  scott.marlowe <scott.marlowe@ihs.com>
  parent: Mira Kaloper <mkaloper@ucdavis.edu>
  0 siblings, 0 replies; 2+ messages in thread

From: scott.marlowe @ 2004-03-09 22:12 UTC (permalink / raw)
  To: Mira Kaloper <mkaloper@ucdavis.edu>; +Cc: pgsql-admin

On Tue, 9 Mar 2004, Mira Kaloper wrote:

> Hi,
> 
> I have a postgres database that is VERY slow - in one table we have 50 
> mil records in it.
> I have tried to play with postgres.conf file and shared memory but was 
> not able to speed it up to something that we can work with. We have 
> apple RAID and I am planning to take database to the raid. I was 
> wondering if someone has any hints about improving the speed on postgres.
> 
> Currently we have:
> shmall = 134217728
> shmmax = 134217728
> 
> and in postgresql.conf  I was setting up values to:
> shared_buffers = 15200
> sort_mem = 32168

There are a few areas here.

One, is database tuning:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

The next is query tuning.  Use explain analyze select <rest of query>... 
to see what is taking the longest, and if any of the estimates for rows 
are way off from actual.  Further, check for things like mismatched FK 
relationships (i.e. a text field fking to an int field).   Lastly, make 
sure you're analyzing your data, and that you've vacuumed the database 
lately.  You may need a vacuum full if you've waited a long time since the 
last vacuum.

The next issue is your hardware.  There has been another report here on 
OSX performing pretty poorly, especially disappointing were the results 
with apple's XRAID product, which was running about 2/3 as fast as was 
advertised.

You might want to post the output of explain analyze of one of the slow 
queries.  The best list for this stuff is pgsql-perform..




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


end of thread, other threads:[~2004-03-09 22:12 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-09 21:04 postgres on apple OS X server Mira Kaloper <mkaloper@ucdavis.edu>
2004-03-09 22:12 ` scott.marlowe <scott.marlowe@ihs.com>

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