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.