> -----Mensaje original-----
> De: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] En nombre de Waldomiro
> Enviado el: Lunes, 30 de Noviembre de 2009 22:03
> Para: pgsql-performance@postgresql.org
> Asunto: [PERFORM] Server Freezing
>
> Hi everybody,
>
> ...
>
> That table receives about 3000 inserts and 60000 updates each
> day, but at night I do a TRUNCATE TABLE1 (Every Night), so
> the table is very small. There is an index by field1 too.
>
> Some days It works very good all day, but somedays I have 7
> seconds freeze, I mean, my serves delays 7 seconds on this statement:
> SELECT field1
> FROM TABLE1
> WHERE field2 = '10'
Hi.
You should probably consider creating a partial index on field2 = '10'.
> I looked up to the statistics for that table, but the
> statistics says that postgres is reading memory, not disk,
> becouse the table is very small and I do a select every
> second, so the postgres keeps the table in shared buffers.
You say you dont vacuum this table, but considering 60000 updates on 3000
records, assuming you are updating each record 20 times, your table could
eat up the space of 60M records. ¿Have you considered this?
Though, I am not sure how this impacts when the whole table is held in
shared buffers.
>
> Why this 7 seconds delay? How could I figure out what is happening?
>
Turn log_checkpoints = on to see in the logs if these occur during the
freeze.
Also log_lock_waits = on will help diagnose the situation.
What version of postgres are you running and how are your checkpoints
configured?
Regards,
Fernando.