pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Mattox <michael.mattox@verideon.com>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance advice
Date: Wed, 25 Jun 2003 11:47:48 +0200
Message-ID: <CJEBLDCHAADCLAGIGCOOCEKBCKAA.michael.mattox@verideon.com> (raw)
In-Reply-To: <k3hifvg6on4qckbo5qjktptckf5tvfghp6@4ax.com>

> [ This has been written offline yesterday.  Now I see that most of it
> has already been covered.  I send it anyway ... ]

Still great advice with slightly different explanations, very useful.

> |INFO:  --Relation public.jdo_sequencex--
> |INFO:  Pages 28: Changed 1, Empty 0; Tup 1: Vac 5124, Keep 0, UnUsed 0.
>                                           ^      ^^^^
> This table could stand more frequent VACUUMs, every 15 minutes or so.

Can you explain what the "Vac" is and how you knew that it should be
vacuumed more often?  I'd like to understand how to interpret my vacuum log.
I looked in the vacuum section of the docs and there's nothing about the
vacuum output <hint>.

> BTW, from the name of this table and from the fact that there is only
> one live tuple I guess that you are using it to keep track of a
> sequence number.  By using a real sequence you could get what you need
> with less contention; and you don't have to VACUUM a sequence.

I'm using Java Data Objects (JDO) which is an O/R mapper.  It generated the
schema from my object model by default it used a table for a sequence.  I
just got finished configuring it to use a real postgres sequence.  With the
way they have it designed, it opens and closes a connection each time it
retrieves a sequence.  Would I get a performance increase if I modify their
code to retrieve multiple sequence numbers in one connection?  For example I
could have it grab 50 at a time, which would replace 50 connections with 1.

> >  The
> >monitor table has columns "nextdate" and "status" which are updated with
> >every monitoring, [...]
> > updating the "nextdate" before the monitoring and inserting the
> >status and status item records after.
>
> Do you mean updating monitor.nextdate before the monitoring and
> monitor.status after the monitoring?  Can you combine these two
> UPDATEs into one?

I was doing this to prevent the monitor from being added to the queue while
it was executing.  But I fixed this, effectively reducing my transactions by
1/2.

> >shared_buffers = 3072           # min max_connections*2 or 16, 8KB each
> >sort_mem = 8192         # min 64, size in KB
> >vacuum_mem = 24576              # min 1024, size in KB
> >
> >The rest are left uncommented (using the defaults).
>
> As has already been said, don't forget effective_cache_size.  I'm not
> so sure about random_page_cost.  Try to find out which queries are too
> slow.  EXPLAIN ANALYSE is your friend.
>
> One more thing:  I see 2 or 3 UPDATEs and 5 INSERTs per monitoring.
> Are these changes wrapped into a single transaction?

These were in 2 transactions but now I have it into a single transaction.

Thanks,
Michael





view thread (23+ messages)  latest in thread

Message-ID: <CJEBLDCHAADCLAGIGCOOCEKBCKAA.michael.mattox@verideon.com>
Permalink:  ../CJEBLDCHAADCLAGIGCOOCEKBCKAA.michael.mattox@verideon.com/
Also on:    postgresql.org/message-id/CJEBLDCHAADCLAGIGCOOCEKBCKAA.michael.mattox@verideon.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: michael.mattox@verideon.com, mkoi-pg@aon.at
  Subject: Re: Performance advice
  In-Reply-To: <CJEBLDCHAADCLAGIGCOOCEKBCKAA.michael.mattox@verideon.com>
  References: <k3hifvg6on4qckbo5qjktptckf5tvfghp6@4ax.com> <CJEBLDCHAADCLAGIGCOOCEKBCKAA.michael.mattox@verideon.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