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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
