pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alex Hayward <xelah-pgsql@xelah.com>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL performance issues
Date: Wed, 30 Aug 2006 12:22:37 +0100 (BST)
Message-ID: <Pine.LNX.4.58.0608301210250.28033@sphinx.mythic-beasts.com> (raw)
In-Reply-To: <44F56649.9050604@studentvillage.co.za>
References: <44F446B2.2090900@studentvillage.co.za>
	<b42b73150608291639o226b645cq41175b416590f029@mail.gmail.com>
	<44F56649.9050604@studentvillage.co.za>

On Wed, 30 Aug 2006, Willo van der Merwe wrote:

> Merlin Moncure wrote:
> > On 8/29/06, Willo van der Merwe <willo@studentvillage.co.za> wrote:
> >
> >>  and it has 743321 rows and a explain analyze select count(*) from
> >> property_values;
> >>
> >
> > you have a number of options:
> All good ideas and I'll be sure to implement them later.
>
> > I am curious why you need to query the count of records in the log
> > table to six digits of precision.
> I'm not with you you here.
> I'm drawing statistic for the my users on a per user basis in real-time,
> so there are a couple of where clauses attached.

Most of the advice so far has been aimed at improving the performance of
the query you gave. If this query isn't representative of your load then
you'll get better advice if you post the queries you are actually making
along with EXPLAIN ANALYZE output.

> Hi Merlin,
>
> This was just an example. All queries have slowed down. Could it be that
> I've reached some cut-off and now my disk is thrashing?
>
> Currently the load looks like this:
> Cpu0  : 96.8% us,  1.9% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0% hi,  1.0% si
> Cpu1  : 97.8% us,  1.6% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0% hi,  0.3% si
> Cpu2  : 96.8% us,  2.6% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0% hi,  0.3% si
> Cpu3  : 96.2% us,  3.2% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0% hi,  0.3% si

It seems to be a sort of standing assumption on this list that databases
are much larger than memory and that database servers are almost always IO
bound. This isn't always true, but as we don't know the size of your
database or working set we can't tell. You'd have to look at your OS's IO
statistics to be sure, but it doesn't look to me to be likely that you're
IO bound.

If there are significant writes going on then it may also be interesting
to know your context switch rate and whether dropping your foreign key
constraint makes any difference. IIRC your foreign key constraint will
result in the row in log_sites being locked FOR UPDATE and cause updates
and inserts into your log table for a particular site to be serialized (I
may be out of date on this, it's a while since I heavily used foreign
keys).



view thread (23+ messages)  latest in thread

Message-ID: <Pine.LNX.4.58.0608301210250.28033@sphinx.mythic-beasts.com>
Permalink:  ../Pine.LNX.4.58.0608301210250.28033@sphinx.mythic-beasts.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.58.0608301210250.28033@sphinx.mythic-beasts.com

 · 

reply

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-performance@postgresql.org
  Cc: xelah-pgsql@xelah.com
  Subject: Re: PostgreSQL performance issues
  In-Reply-To: <Pine.LNX.4.58.0608301210250.28033@sphinx.mythic-beasts.com>
  References: <44F446B2.2090900@studentvillage.co.za> <b42b73150608291639o226b645cq41175b416590f029@mail.gmail.com> <44F56649.9050604@studentvillage.co.za> <Pine.LNX.4.58.0608301210250.28033@sphinx.mythic-beasts.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