pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jack Coates <jack@lyris.com>
To: josh@agliodbs.com
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: tuning questions
Date: Thu, 04 Dec 2003 11:50:55 -0800
Message-ID: <1070567455.13923.83.camel@cletus.lyris.com> (raw)
In-Reply-To: <200312041120.21124.josh@agliodbs.com>
References: <1070553983.6498.52.camel@cletus.lyris.com>
	<3FCF6AEB.908@dsvr.net>
	<1070565411.13923.70.camel@cletus.lyris.com>
	<200312041120.21124.josh@agliodbs.com>

On Thu, 2003-12-04 at 11:20, Josh Berkus wrote:
> Jack,
> 
> > Following this, I've done:
> > 2gb ram
> > =
> >  2,000,000,000
> > bytes
> 
> This calculation is fun, but I really don't know where you got it from.   It 
> seems quite baroque.  What are you trying to set, exactly?
Message-ID:  <3FCF6AEB.908@dsvr.net>
Date: Thu, 04 Dec 2003 17:12:11 +0000
From: Rob Fielding <rob@dsvr.net

I'm trying to set Postgres's shared memory usage in a fashion that
allows it to return requested results quickly. Unfortunately, none of
these changes allow PG to use more than a little under 300M RAM.
vacuumdb --analyze is now taking an inordinate amount of time as well
(40 minutes and counting), so that change needs to be rolled back.

> 
> > getting the SQL query better optimized for PG is on my todo list, but
> > not something I can do right now -- this application is designed to be
> > cross-platform with MS-SQL, PG, and Oracle so tweaking SQL is a touchy
> > subject.
> 
> Well, if you're queries are screwed up, no amount of .conf optimization is 
> going to help you much.     You could criticize that PG is less adept than 
> some other systems at re-writing "bad queries", and you would be correct.  
> However, there's not much to do about that on existing systems.
> 
> How about posting some sample code?

Tracking that down in CVS and translating from C++ is going to take a
while -- is there a way to get PG to log the queries it's receiving?

> 
> > The pgavd conversation is intriguing, but I don't really understand the
> > role of vacuuming. Would this be a correct statement: "PG needs to
> > regularly re-evaluate the database in order to adjust itself?" I'm
> > imagining that it continues to treat the table as a small one until
> > vacuum informs it that the table is now large?
> 
> Not Vacuum, Analyze.  Otherwise correct.  Mind you, in "regular use" where 
> only a small % of the table changes per hour, periodic ANALYZE is fine.  
> However, in "batch data transform" analyze statements need to be keyed to the 
> updates and/or imports.
> 
> BTW, I send a couple of e-mails to the Lyris documentation maintainer about 
> updating out-of-date information about setting up PostgreSQL.   I never got a 
> response, and I don't think my changes were made.

She sits on the other side of the cube wall from me, and if I find a
decent config it's going into the manual -- consider this a golden
opportunity :-)

-- 
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
				--Olivier Fourdan





view thread (32+ messages)  latest in thread

Message-ID: <1070567455.13923.83.camel@cletus.lyris.com>
Permalink:  ../1070567455.13923.83.camel@cletus.lyris.com/
Also on:    postgresql.org/message-id/1070567455.13923.83.camel@cletus.lyris.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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jack@lyris.com, josh@agliodbs.com
  Subject: Re: tuning questions
  In-Reply-To: <1070567455.13923.83.camel@cletus.lyris.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