From: scott.marlowe <scott.marlowe@ihs.com>
To: Mira Kaloper <mkaloper@ucdavis.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: postgres on apple OS X server
Date: Tue, 9 Mar 2004 15:12:01 -0700 (MST)
Message-ID: <Pine.LNX.4.33.0403091508190.7159-100000@css120.ihs.com> (raw)
In-Reply-To: <404E3145.2060209@ucdavis.edu>
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..
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: scott.marlowe@ihs.com, mkaloper@ucdavis.edu
Subject: Re: postgres on apple OS X server
In-Reply-To: <Pine.LNX.4.33.0403091508190.7159-100000@css120.ihs.com>
References: <404E3145.2060209@ucdavis.edu> <Pine.LNX.4.33.0403091508190.7159-100000@css120.ihs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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