Skip site navigation (1) Skip section navigation (2)

transaction delays to apply

From: Nickolay <nitro(at)zhukcity(dot)ru>
To: pgsql-performance(at)postgresql(dot)org
Subject: transaction delays to apply
Date: 2009-08-12 16:24:52
Message-ID: 4A82ECD4.2070009@zhukcity.ru (view raw or flat)
Thread:
Lists: pgsql-performance
Hello All,

I'm developing specialized message switching system and I've chosen to 
use PostgreSQL as general tool to handle transactions, store and manage 
all the data.
This system has pretty strong timing requirements. For example, it must 
process not less than 10 messages per second. FYI: messages are short 
(aprx. 400-500 bytes length). Incoming message should be routed via 
special routing system to its destinations (i.e. one incoming message 
may be routed in dozens of channels at once).
Normally this system works excellent with PostgreSQL database, the 
perfomance is quite impressive.
BUT sometimes bad things happen (delays). For example:
I have "log" table which contains all log entries for the system 
(warnings, errors, detailed routing info, rejections, etc).
The table includes "timestamp" field and this field defaults to "now()":
CREATE TABLE log
(
  id bigserial NOT NULL,
  "timestamp" timestamp without time zone NOT NULL DEFAULT now(),
.. etc.
So when incoming message is being processed, I do start new transaction 
and generate outgoing and log messages in this single transaction.
Normally, viewing the log sorted by ID it comes in right timing order:
ID   timestamp
1     2009-08-08 00:00:00.111
2     2009-08-08 00:00:00.211
3     2009-08-08 00:01:00.311
etc.
BUT it seems that rarely this transaction is being delayed to apply and 
log entry is being inserted in wrong order:
ID   timestamp
1     2009-08-08 00:00:00.111
2     2009-08-08 00:00:30.311
3     2009-08-08 00:00:00.211
Yep, that's right - sometimes for 30 seconds or even more.
I do understand that there should be some delays with the database, but 
30 seconds is unacceptable!
Does anybody know any way to solve this? I did monitor the system 
running at full load (~20 messages per second) - postmaster's processes 
didn't eat more than 10-20% of CPU and memory. Neither did any of my 
application's processes.

Best regards, Nick.

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2009-08-12 18:17:05
Subject: Re: Why is vacuum_freeze_min_age 100m?
Previous:From: Torsten ZühlsdorffDate: 2009-08-12 06:48:45
Subject: Re: Why is vacuum_freeze_min_age 100m?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group