| From: | Waldomiro <waldomiro(at)shx(dot)com(dot)br> |
|---|---|
| To: | Fernando Hevia <fhevia(at)ip-tel(dot)com(dot)ar> |
| Cc: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Server Freezing |
| Date: | 2009-12-01 11:18:54 |
| Message-ID: | 4B14FB9E.3090302@shx.com.br |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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(at)iptel(dot)com(dot)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(at)postgresql(dot)org">pgsql-performance-owner(at)postgresql(dot)org</a>
[<a class="moz-txt-link-freetext" href="mailto:pgsql-performance-owner(at)postgresql(dot)org">mailto:pgsql-performance-owner(at)postgresql(dot)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(at)postgresql(dot)org">pgsql-performance(at)postgresql(dot)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">Waldomiro Caraiani
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(at)shx(dot)com(dot)br">waldomiro(at)shx(dot)com(dot)br</a> <br>
<a href="http://www.shx.com.br"> www.shx.com.br</a> </font>
</div>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 3.6 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Fernando Hevia | 2009-12-01 15:50:34 | Re: Server Freezing |
| Previous Message | Waldomiro | 2009-12-01 11:02:47 | Re: Server Freezing |