Re: Server Freezing - Mailing list pgsql-performance

I´m using PostgreSQL 8.1, and my settings are:<br /><br /> checkpoint_segments=50<br /> checkpoint_timeout=300<br />
checkpoint_warning=30<br/> commit_delay=0<br /> commit_siblings=5<br /> archive_command= cp -i %p/BACKUP/LOGS/%f<br />
autovacuum=off<br/> bgwriter_all_maxpages=5<br /> bgwriter_all_percent=0.333<br /> bgwriter_delay=200<br />
bgwriter_lru_maxpages=5<br/> bgwriter_lru_percent=1<br /> fsync=on<br /> full_page_writes=on<br />
stats_block_level=on<br/> stats_command_string=on<br /> stats_reset_on_server_start=off<br /> stats_row_level=on<br />
stats_start_collector=on<br/><br /> Waldomiro<br /><br /> Fernando Hevia escreveu: <blockquote
cite="mid:115993AC76394FBE90BCDD919864DF05@iptel.com.ar"type="cite"><pre wrap=""> 
 
 </pre><blockquote type="cite"><pre wrap="">-----Mensaje original-----
De: <a class="moz-txt-link-abbreviated"
href="mailto:pgsql-performance-owner@postgresql.org">pgsql-performance-owner@postgresql.org</a>
 
[<a class="moz-txt-link-freetext"
href="mailto:pgsql-performance-owner@postgresql.org">mailto:pgsql-performance-owner@postgresql.org</a>]En nombre de
Waldomiro
Enviado el: Lunes, 30 de Noviembre de 2009 22:03
Para: <a class="moz-txt-link-abbreviated"
href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>
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'   </pre></blockquote><pre wrap="">
 
Hi.
You should probably consider creating a partial index on field2 = '10'.
 </pre><blockquote type="cite"><pre wrap="">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.   </pre></blockquote><pre wrap="">

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.
 </pre><blockquote type="cite"><pre wrap="">Why this 7 seconds delay? How could I figure out what is happening?
   </pre></blockquote><pre wrap="">
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.

 </pre></blockquote><br /><br /><div class="moz-signature">-- <br /><b> <font color="#003366" face="verdana"
size="2">WaldomiroCaraiani Neto</font><br /><font color="#becbd8"
size="1">|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||</font></b><br/><font color="#003366"
face="verdana"size="1"><b>GRUPO SHX</b></font><br /><br /><font color="#666666" face="Verdana" size="1">
<b>Desenvolvimento</b><br /> + 55 (16) 3331.3268<br /><a href="mailto:waldomiro@shx.com.br">waldomiro@shx.com.br</a><br
/><ahref="http://www.shx.com.br"> www.shx.com.br</a> </font></div>