Re: Allow "snapshot too old" error, to prevent bloat

Поиск
Список
Период
Сортировка
От Rod Taylor
Тема Re: Allow "snapshot too old" error, to prevent bloat
Дата
Msg-id CAKddOFBM76D7dBgZoSz8FVReJKCEbJAKgy-RKN90dTvO=8cfPw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Allow "snapshot too old" error, to prevent bloat  (Kevin Grittner <kgrittn@ymail.com>)
Ответы Re: Allow "snapshot too old" error, to prevent bloat  (Kevin Grittner <kgrittn@ymail.com>)
Список pgsql-hackers


On Wed, Feb 18, 2015 at 4:57 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
>> But max_standby_streaming_delay, max_standby_archive_delay and
>> hot_standby_feedback are among the most frequent triggers for
>> questions and complaints that I/we see.
>>
> Agreed.
> And a really bad one used to be vacuum_defer_cleanup_age, because
> of confusing units amongst other things. Which in terms seems
> fairly close to Kevins suggestions, unfortunately.

Particularly my initial suggestion, which was to base snapshot
"age" it on the number of transaction IDs assigned.  Does this look
any better to you if it is something that can be set to '20min' or
'1h'?  Just to restate, that would not automatically cancel the
snapshots past that age; it would allow vacuum of any tuples which
became "dead" that long ago, and would cause a "snapshot too old"
message for any read of a page modified more than that long ago
using a snapshot which was older than that.


I like this thought. One of the first things I do in a new Pg environment is setup a cronjob that watches pg_stat_activity and terminates most backends over N minutes in age (about 5x the length of normal work) with an exception for a handful of accounts doing backups and other maintenance operations.  This prevents a stuck client from jamming up the database.

Would pg_dump be able to opt-out of such a restriction?

regards,

Rod

В списке pgsql-hackers по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: Add min and max execute statement time in pg_stat_statement
Следующее
От: Ants Aasma
Дата:
Сообщение: Re: Allow "snapshot too old" error, to prevent bloat