Обсуждение: Server Freezing
Hi everybody,
I have an java application like this:
while ( true ) {
Thread.sleep( 1000 ) // sleeps 1 second
SELECT field1
FROM TABLE1
WHERE field2 = '10'
if ( field1 != null ) {
BEGIN;
processSomething( field1 );
UPDATE TABLE1
SET field2 = '20'
WHERE field1 = '10';
COMMIT;
}
}
This is a simple program which is waiting for a record inserted by
another workstation, after I process that record I update to an
processed status.
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'
Last Friday, It happens about 4 times, one at 9:50 am, another on 13:14
pm, another on 17:27 pm and another on 17:57 pm.
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.
Why this 7 seconds delay? How could I figure out what is happening?
I know:
It is not disk, becouse statistics shows its reading memory.
It is not internet delay, becouse it is a local network
It is not workstations, becouse there are 2 workstations, and both
freeze at the same time
It is not processors, becouse my server has 8 processors
It is not memory, becouse my server has 32 GB, and about 200 MB free
It is not another big process or maybe not, becouse I think postgres
would not stops my simples process for 7 seconds to do a big process,
and I cant see any big process at that time.
Its not lock, becouse the simple select freezes, It doesnot have an "FOR
UPDATE"
Its not a vaccum needed, becouse I do a TRUNCATE every night.
Is It possible the checkpoint is doing that? Or the archiving? How can I
see?
Someone have any idea?
Thank you
Waldomiro Caraiani
> -----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.
On Mon, Nov 30, 2009 at 8:02 PM, Waldomiro <waldomiro@shx.com.br> wrote: > Its not a vaccum needed, becouse I do a TRUNCATE every night. But you're updating each row 20 times a day - you could very well need a vacuum. > Is It possible the checkpoint is doing that? Or the archiving? How can I > see? It seems likely to be caused by checkpoint I/O or vacuuming activity, but I'm not sure how to figure out which. ...Robert
Waldomiro wrote: > Is It possible the checkpoint is doing that? Or the archiving? How can > I see? If you're using PostgreSQL 8.3 or later, you can turn on log_checkpoints and you'll get a note when each checkpoint finishes. The parts that are more likely to slow the server down are right at the end, so if you see a bunch of slow queries around the same time as the checkpoint message appears in the logs, that's the likely cause. Bad checkpoint behavior can certainly cause several seconds of freezing on a system with 32GB of RAM, because with that much data you can have quite a bit in the OS write cache that all gets forced out at the end of the checkpoint. Finding when the checkpoints happen on 8.2 or earlier is much harder; I can tell you what to look for on Linux for example, but it's kind of painful to track them down. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com
I´m using PostgreSQL 8.1. There is a way to see that? Waldomiro Greg Smith escreveu: > Waldomiro wrote: >> Is It possible the checkpoint is doing that? Or the archiving? How >> can I see? > If you're using PostgreSQL 8.3 or later, you can turn on > log_checkpoints and you'll get a note when each checkpoint finishes. > The parts that are more likely to slow the server down are right at > the end, so if you see a bunch of slow queries around the same time as > the checkpoint message appears in the logs, that's the likely cause. > Bad checkpoint behavior can certainly cause several seconds of > freezing on a system with 32GB of RAM, because with that much data you > can have quite a bit in the OS write cache that all gets forced out at > the end of the checkpoint. > > Finding when the checkpoints happen on 8.2 or earlier is much harder; > I can tell you what to look for on Linux for example, but it's kind of > painful to track them down. >
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>
> -----Mensaje original----- > De: Waldomiro > > I´m using PostgreSQL 8.1, Sorry, log_checkpoints isn't supported till 8.3 > 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 > As tempting as it is to decrease checkpoint_segments, better confirm it is a checkpoint related problem before fiddling with these settings. I recommend reading Greg Smith's post on checkpoints & bg writer. It's about 8.3 improvements but it includes good advice on how to diagnose checkpoint issues on prior versions: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm In fact, one of his recomendations should be very helpful here: set checkpoint_warning=3600 and log_min_duration_statement=1000, that way you should see in the log if statements over 1 sec occur simultaneously with checkpoints being reached. Pay attention to the chapter on the bg_writer too. Regards, Fernando.
Perhaps making your select be explicitely part of a read-only
transaction rather than letting java make use of an implicit
transaction (which may be in auto commit mode)
On 11/30/09, Waldomiro <waldomiro@shx.com.br> wrote:
> Hi everybody,
>
> I have an java application like this:
>
> while ( true ) {
> Thread.sleep( 1000 ) // sleeps 1 second
>
> SELECT field1
> FROM TABLE1
> WHERE field2 = '10'
>
> if ( field1 != null ) {
> BEGIN;
>
> processSomething( field1 );
>
> UPDATE TABLE1
> SET field2 = '20'
> WHERE field1 = '10';
>
> COMMIT;
> }
> }
>
> This is a simple program which is waiting for a record inserted by
> another workstation, after I process that record I update to an
> processed status.
>
> 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'
>
> Last Friday, It happens about 4 times, one at 9:50 am, another on 13:14
> pm, another on 17:27 pm and another on 17:57 pm.
>
> 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.
>
> Why this 7 seconds delay? How could I figure out what is happening?
>
> I know:
>
> It is not disk, becouse statistics shows its reading memory.
> It is not internet delay, becouse it is a local network
> It is not workstations, becouse there are 2 workstations, and both
> freeze at the same time
> It is not processors, becouse my server has 8 processors
> It is not memory, becouse my server has 32 GB, and about 200 MB free
> It is not another big process or maybe not, becouse I think postgres
> would not stops my simples process for 7 seconds to do a big process,
> and I cant see any big process at that time.
> Its not lock, becouse the simple select freezes, It doesnot have an "FOR
> UPDATE"
> Its not a vaccum needed, becouse I do a TRUNCATE every night.
>
> Is It possible the checkpoint is doing that? Or the archiving? How can I
> see?
>
> Someone have any idea?
>
> Thank you
>
> Waldomiro Caraiani
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>