transaction delays to apply

От: Nickolay
Тема: transaction delays to apply
Дата: ,
Msg-id: 4A82ECD4.2070009@zhukcity.ru
(см: обсуждение, исходный текст)
Ответы: Re: transaction delays to apply  (Pierre Frédéric Caillaud<>)
Re: transaction delays to apply  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

transaction delays to apply  (Nickolay, )
 Re: transaction delays to apply  (Pierre Frédéric Caillaud<>, )
 Re: transaction delays to apply  (Tom Lane, )
  Re: transaction delays to apply  (Nickolay, )
  Re: transaction delays to apply  (Nickolay, )

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.


В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: Why is vacuum_freeze_min_age 100m?
От: Robert Haas
Дата:
Сообщение: Re: Why is vacuum_freeze_min_age 100m?