DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=sbcglobal.net;
	h=Received:X-YMail-OSG:MIME-Version:From:Subject:Date:Importance:X-Priority:To:Content-Transfer-Encoding:Content-Type;
	b=DxGtbuqiT8G+4x5jRe8n5MnnmXVIgLdQ/Rqo7wNQ8MHE4KU3TsI29Y/ndEsFCyac42rh2JgPAoI02KJTvKbBJuEeVz93MExeDsKgaL1hCnE5/aSSZJlml3zTpNLF5yZl3werLku3LtMoBDnAwftnDTlIMdQ2mlw3yMmtKVgfuvE=
	;
MIME-Version: 1.0
From: Adam Rich <adam.r@sbcglobal.net>
Subject: Re: Configuration Advice
Date: Wed, 17 Jan 2007 15:24:02 -0600
Importance: normal
To: Steve <cheetah@tanabi.org>, <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="iso-8859-1"
Message-Id: <20070117212352.7B6EC9FB28E@postgresql.org>


Doesn't sound like you want postgres at all.... Try mysql.


-----Original Message-----
From: "Steve" <cheetah@tanabi.org>
To: pgsql-performance@postgresql.org
Sent: 1/17/2007 2:41 PM
Subject: [PERFORM] Configuration Advice

Hey there;

I've been lurking on this list awhile, and I've been working with postgres=
=20
for a number of years so I'm not exactly new to this.  But I'm still=20
having trouble getting a good balance of settings and I'd like to see what=
=20
other people think.  We may also be willing to hire a contractor to help=20
tackle this problem if anyone is interested.

I've got an application here that runs large (in terms of length -- the=20
queries have a lot of conditions in them) queries that can potentially=20
return millions of rows but on average probably return tens of thousands=20
of rows.  It's read only for most of the day, and pretty much all the=20
queries except one are really fast.

However, each night we load data from a legacy cobol system into the SQL=20
system and then we summarize that data to make the reports faster.  This=20
load process is intensely insert/update driven but also has a hefty=20
amount of selects as well.  This load process is taking ever longer to=20
complete.


SO ... our goal here is to make this load process take less time.  It=20
seems the big part is building the big summary table; this big summary=20
table is currently 9 million rows big.  Every night, we drop the table,=20
re-create it, build the 9 million rows of data (we use COPY to put hte=20
data in when it's prepared, not INSERT), and then build the indexes on it=20
-- of which there are many.  Unfortunately this table gets queried=20
in a lot of different ways and needs these indexes; also unfortunately, we=
=20
have operator class indexes to support both ASC and DESC sorting on=20
columns so these are for all intents and purposes duplicate but required=20
under Postgres 8.1 (we've recently upgraded to Postgres 8.2, is this still=
=20
a requirement?)

Building these indexes takes forever!  It's a long grind through inserts=20
and then building the indexes takes a hefty amount of time too.  (about 9=20
hours).  Now, the application is likely part at fault, and we're working=20
to make it more efficient, but it has nothing to do with the index=20
building time.  I'm wondering what we can do to make this better if=20
anything; would it be better to leave the indexes on?  It doesn't seem to=20
be.  Would it be better to use INSERTs instead of copies?  Doesn't seem to=
=20
be.


Anyway -- ANYTHING we can do to make this go faster is appreciated :)=20
Here's some vital statistics:

- Machine is a 16 GB, 4 actual CPU dual-core opteron system using SCSI=20
discs.  The disc configuration seems to be a good one, it's the best of=20
all the ones we've tested so far.

- The load process itself takes about 6 gigs of memory, the rest is free=20
for postgres because this is basically all the machine does.

- If this was your machine and situation, how would you lay out the emmory=
=20
settings?  What would you set the FSM to?  Would you leave teh bgwriter on=
=20
or off?  We've already got FSYNC off because "data integrity" doesn't=20
matter -- this stuff is religeously backed up and we've got no problem=20
reinstalling it.  Besides, in order for this machine to go down, data=20
integrity of the DB is the least of the worries :)

Do wal_buffers/full_page_writes matter of FSYNC is off?  If so, what=20
settings?  What about checkpoints?

Any finally, any ideas on planner constants?  Here's what I'm using:

seq_page_cost =3D 0.5                     # measured on an arbitrary scale
random_page_cost =3D 1.0                  # same scale as above
cpu_tuple_cost =3D 0.001                  # same scale as above
cpu_index_tuple_cost =3D 0.0001           # same scale as above
cpu_operator_cost =3D 0.00025             # same scale as above
effective_cache_size =3D 679006

I really don't remember how I came up with that effective_cache_size=20
number....


Anyway... any advice would be appreciated :)


Steve

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster