Обсуждение: Some questions about old_snapshot_threshold
Hi, 1. The following presentation uses serializable transaction isolation level to force a snapshot too old error. (http://momjian.us/main/writings/pgsql/features.pdf) Is it possible to get a snapshot too old error with active old_snapshot_threshold when the only used transaction isolationlevel is read committed? 2. Does activating old_snapshot_threshold have a negative impact on peformance? Regards, Marc-Olaf
On Fri, Nov 25, 2016 at 1:39 PM, Marc-Olaf Jaschke <moj@dshare.de> wrote: > 1. The following presentation uses serializable transaction > isolation level to force a snapshot too old error. > (http://momjian.us/main/writings/pgsql/features.pdf) > Is it possible to get a snapshot too old error with active > old_snapshot_threshold when the only used transaction isolation > level is read committed? Yes. Transaction isolation level doesn't have much impact on this feature. > 2. Does activating old_snapshot_threshold have a negative impact > on peformance? The latest tests, near the end of development, were run by Tomas Vondra and are reported here: https://www.postgresql.org/message-id/067acb3d-80eb-279d-fce0-90e0a36c6aa2@2ndquadrant.com Note that the "immediate" lines are using a setting of zero, which is ridiculously aggressive, and only intended for testing purposes. The lines ending in "-10-rw" are using a 10 minute expiration, which is still far more aggressive than I would expect most environments to use, but should give some idea what the impact could be "worst case". Tomas says in the linked email, "I'm personally convinced the performance impact is within noise." FWIW, it makes no sense to enable this feature with a read-only workload, but that configuration was heavily tested, as it represents a worst case. Also note that Tomas used a multi-socket NUMA machine for these tests, because that is where we saw the largest differences during early tests, before some optimizations were applied. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Thanks for the explanation! Best Regards, Marc-Olaf > Am 28.11.2016 um 22:47 schrieb Kevin Grittner <kgrittn@gmail.com>: > > On Fri, Nov 25, 2016 at 1:39 PM, Marc-Olaf Jaschke <moj@dshare.de> wrote: > >> 1. The following presentation uses serializable transaction >> isolation level to force a snapshot too old error. >> (http://momjian.us/main/writings/pgsql/features.pdf) >> Is it possible to get a snapshot too old error with active >> old_snapshot_threshold when the only used transaction isolation >> level is read committed? > > Yes. Transaction isolation level doesn't have much impact on this > feature. > >> 2. Does activating old_snapshot_threshold have a negative impact >> on peformance? > > The latest tests, near the end of development, were run by Tomas > Vondra and are reported here: > > https://www.postgresql.org/message-id/067acb3d-80eb-279d-fce0-90e0a36c6aa2@2ndquadrant.com > > Note that the "immediate" lines are using a setting of zero, which > is ridiculously aggressive, and only intended for testing purposes. > The lines ending in "-10-rw" are using a 10 minute expiration, > which is still far more aggressive than I would expect most > environments to use, but should give some idea what the impact > could be "worst case". Tomas says in the linked email, "I'm > personally convinced the performance impact is within noise." > > FWIW, it makes no sense to enable this feature with a read-only > workload, but that configuration was heavily tested, as it > represents a worst case. Also note that Tomas used a multi-socket > NUMA machine for these tests, because that is where we saw the > largest differences during early tests, before some optimizations > were applied. > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company