Re: Server Freezing - Mailing list pgsql-performance

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
>