On Dec 6, 2007, at 2:22 PM, Weber, Geoffrey M. wrote:
I've been trying for quite a while to get Postgresql tuned for use
as an OLTP system. I have several PL/pgSQL functions that handle
inserts and updates to the main table and several near-real-time
daemons written that access the data and can take automated actions
on it (email/page concerned people, get complimentary information
from a different system, etc.). I started with Postgres 8.1 and am
now using 8.2.4 (and have been since its release). I'll try to
provide enough information for a decent response, but as I can't
obviously put my entire schema and database out there, I'm hoping
that I can get some decent guidelines beyond that what I've found
though Google, etc. to get this thing tuned better.
Most of the data centers in on a central table and has 23 columns,
1 constraint, and 9 indexes. 4 of the indexes are partial. The
table usually contains about 3-4 million rows, but I've cut it down
to 1.2 million (cut out 2/3 of the data) in an effort to migrate
the database to a 2nd sever for more testing. The two partial
indexes used the most: 242MB accessed nearly constantly, and 15MB
accessed every 5 seconds - but also updated constantly via inserts
using the 242MB index. Other than one other 25MB index, the others
seem to average around 300MB each, but these aren't used quite as
often (usually about every minute or so).
My problems really are with performance consistency. I have
tweaked the execution so that everything should run with sub-second
execution times, but even after everything is running well, I can
get at most a week or two of steady running before things start to
degrade.
Without some examples of reproducible problematic behavior, you are
likely to get only hazy responses. With your rate of database
changes, you may need to be vacuuming more often (or certain tables
more and other tables less).
From your description above, it sounds like you are persistently
polling the database for changes. Have you considered using
asynchronous notifications?
http://www.postgresql.org/docs/8.2/interactive/sql-listen.html
Cheers,
M
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
