Re: Performance advice - Mailing list pgsql-performance

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