Re: [GENERAL] Continual Postgres headaches...

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@postgresql.org so that your
       message can get through to the mailing list cleanly