Re: Allow "snapshot too old" error, to prevent bloat
От | Kevin Grittner |
---|---|
Тема | Re: Allow "snapshot too old" error, to prevent bloat |
Дата | |
Msg-id | 1360007681.2022226.1424377877833.JavaMail.yahoo@mail.yahoo.com обсуждение исходный текст |
Ответ на | Re: Allow "snapshot too old" error, to prevent bloat (Ants Aasma <ants@cybertec.at>) |
Ответы |
Re: Allow "snapshot too old" error, to prevent bloat
(Andrew Dunstan <andrew@dunslane.net>)
Re: Allow "snapshot too old" error, to prevent bloat (Ants Aasma <ants@cybertec.at>) |
Список | pgsql-hackers |
Ants Aasma <ants@cybertec.at> wrote: > If I understood the issue correctly, you have long running snapshots > accessing one part of the data, while you have high churn on a > disjoint part of data. We would need to enable vacuum on the high > churn data while still being able to run those long queries. The > "snapshot too old" solution limits the maximum age of global xmin at > the cost of having to abort transactions that are older than this and > happen to touch a page that was modified after they were started. Pretty much. It's not quite as "black and white" regarding high churn and stable tables, though -- at least for the customer whose feedback drove my current work on containing bloat. They are willing to tolerate an occasional "snapshot too old" on a cursor, and they can automatically pick up again by restarting the cursor with a new "lower limit" and a new snapshot. > What about having the long running snapshots declare their working > set, and then only take them into account for global xmin for > relations that are in the working set? Like a SET TRANSACTION WORKING > SET command. This way the error is deterministic, vacuum on the high > churn tables doesn't have to wait for the old transaction delay to > expire and we avoid a hard to tune GUC (what do I need to set > old_snapshot_threshold to, to reduce bloat while not having "normal" > transactions abort). Let me make sure I understand your suggestion. You are suggesting that within a transaction you can declare a list of tables which should get the "snapshot too old" error (or something like it) if a page is read which was modified after the snapshot was taken? Snapshots within that transaction would not constrain the effective global xmin for purposes of vacuuming those particular tables? If I'm understanding your proposal, that would help some of the cases the "snapshot too old" case addresses, but it would not handle the accidental "idle in transaction" case (e.g., start a repeatable read transaction, run one query, then sit idle indefinitely). I don't think it would work quite as well for some of the other cases I've seen, but perhaps it could be made to fit if we could figure out the accidental "idle in transaction" case. I also think it might be hard to develop a correct global xmin for vacuuming a particular table with that solution. How do you see that working? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
В списке pgsql-hackers по дате отправления: