Server Freezing - Mailing list pgsql-performance
From | Waldomiro |
---|---|
Subject | Server Freezing |
Date | |
Msg-id | 4B146B29.80700@shx.com.br Whole thread Raw |
Responses |
Re: Server Freezing
("Fernando Hevia" <fhevia@ip-tel.com.ar>)
Re: Server Freezing (Robert Haas <robertmhaas@gmail.com>) Re: Server Freezing (Greg Smith <greg@2ndquadrant.com>) Re: Server Freezing (Denis Lussier <denis.lussier@enterprisedb.com>) |
List | pgsql-performance |
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
pgsql-performance by date: