Re: Server Freezing

[Waldomiro <waldomiro@xxxxxxxxxx>]

I´m using PostgreSQL 8.1, and my settings are:

checkpoint_segments=50
checkpoint_timeout=300
checkpoint_warning=30
commit_delay=0
commit_siblings=5
archive_command= cp -i %p/BACKUP/LOGS/%f
autovacuum=off
bgwriter_all_maxpages=5
bgwriter_all_percent=0.333
bgwriter_delay=200
bgwriter_lru_maxpages=5
bgwriter_lru_percent=1
fsync=on
full_page_writes=on
stats_block_level=on
stats_command_string=on
stats_reset_on_server_start=off
stats_row_level=on
stats_start_collector=on

Waldomiro

Fernando Hevia escreveu:

-----Mensaje original-----
De: pgsql-performance-owner@xxxxxxxxxxxxxx 
[mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] En nombre de Waldomiro
Enviado el: Lunes, 30 de Noviembre de 2009 22:03
Para: pgsql-performance@xxxxxxxxxxxxxx
Asunto:  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.


  

--
Waldomiro Caraiani Neto
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
GRUPO SHX

Desenvolvimento
+ 55 (16) 3331.3268
waldomiro@xxxxxxxxxx
www.shx.com.br