[HACKERS] snapshots in analyze

2015-10-31 Thread Andres Freund
Hi,

I previously complained about analyze keeping a snapshot while running in:
http://archives.postgresql.org/message-id/20141018174909.GA5790%40alap3.anarazel.de

since then I've been bitten by that, and I've seen other people being
bitten by it.

on a scale 400 database (so analyze actually takes a while), using
unlogged tables (for fewer unrelated effects) I see this:

latency average: 2.665 ms
latency stddev: 2.628 ms
tps = 18002.712356 (including connections establishing)
tps = 18004.177513 (excluding connections establishing)

and there's many slumps like:
progress: 104.0 s, 8161.9 tps, lat 5.878 ms stddev 3.431
progress: 105.0 s, 7936.3 tps, lat 5.990 ms stddev 2.978
progress: 106.0 s, 8003.9 tps, lat 6.047 ms stddev 5.269
progress: 107.0 s, 18609.6 tps, lat 2.582 ms stddev 2.858
progress: 108.0 s, 19227.0 tps, lat 2.496 ms stddev 2.715

these slumps coincide with moments where
SELECT max(age(backend_xmin)) FROM pg_stat_activity WHERE backend_xmin IS NOT 
NULL;
is high: 198551

hackishly pop'ing the snapshot during the bulk of the work in
acquire_sample_rows results in:
latency average: 2.325 ms
latency stddev: 2.522 ms
tps = 20634.532401 (including connections establishing)
tps = 20636.481548 (excluding connections establishing)

with the slowest 1 second interval being 19936.8 tps.

So that's a pretty clear improvement. It's trivial to make this much
more extreme by using bigger scales and/or different vacuum settings. I
just don't want to do so on my poor laptop.


It's not surprising that a long running analyze with a snapshot held
causes problems for an update heavy OLTP workload.  It's trivially
reproducible without involving analyze by simply holding up a
transaction with a snapshot.

acquire_sample_rows just uses HeapTupleSatisfiesVacuum() to determine
visibility, so it itself doesn't actually need (or use) a snapshot while
acquiring the sample.  But I think externally toasted tuples might
prevent optimizing this, we'll potentially detoast them when computing
the stats...

Does anybody have a good idea how to handle toasted tuples?


A simple approach would be to acquire a snapshot and re-check visibility
for after each row with external datums after acquiring the sample. Our
sample is already skewed over the runtime of acquire_sample_rows due to
our usage of HTSV so I'm not particularly concerned about that part -
but it'd also reduce the size of the sample which is a bit worrysome.


Another angle would be trying to reduce the effects of longrunning
transaction. Right now holding a snapshot open for 100 seconds results
in profiles like this:

After a 100 seconds of holding a snapshot a profile looks like:
+   22.13%  postgres postgres  [.] 
heap_hot_search_buffer
+   11.58%  postgres postgres  [.] 
hash_search_with_hash_value
+   11.58%  postgres postgres  [.] XidInMVCCSnapshot
+4.58%  postgres postgres  [.] 
heap_page_prune_opt
+4.43%  postgres postgres  [.] PinBuffer
+3.95%  postgres postgres  [.] LWLockAcquire
+2.49%  postgres postgres  [.] heap_hot_search
+1.72%  postgres postgres  [.] 
HeapTupleSatisfiesMVCC
+1.61%  postgres postgres  [.] tbm_iterate
+0.99%  postgres postgres  [.] pg_qsort
+0.90%  postgres postgres  [.] LWLockRelease

which is pretty extreme. It's not such a seldom thing to hold a snapshot
(e.g. pg_dump...) open for a while.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] snapshots in analyze

2015-10-31 Thread Tom Lane
Andres Freund  writes:
> Another angle would be trying to reduce the effects of longrunning
> transaction. Right now holding a snapshot open for 100 seconds results
> in profiles like this: ...
> which is pretty extreme. It's not such a seldom thing to hold a snapshot
> (e.g. pg_dump...) open for a while.

Yeah.  I'm afraid blaming ANALYZE for this is just shooting the messenger.
Even in a mostly-OLTP workload there are going to be some long-running
queries, for reporting or whatever.  I think we need to focus on how we
can improve that situation in general, rather than inserting kluges into
ANALYZE (or pg_dump).

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] snapshots in analyze

2015-10-31 Thread Andres Freund
On 2015-10-31 12:52:44 -0400, Tom Lane wrote:
> Andres Freund  writes:
> > Another angle would be trying to reduce the effects of longrunning
> > transaction. Right now holding a snapshot open for 100 seconds results
> > in profiles like this: ...
> > which is pretty extreme. It's not such a seldom thing to hold a snapshot
> > (e.g. pg_dump...) open for a while.
> 
> Yeah.  I'm afraid blaming ANALYZE for this is just shooting the messenger.
> Even in a mostly-OLTP workload there are going to be some long-running
> queries, for reporting or whatever.  I think we need to focus on how we
> can improve that situation in general, rather than inserting kluges into
> ANALYZE (or pg_dump).

I think we might want to do both. It'd obviously would be of great value
to handle longrunning transactions better. But even if we had an
implementation that knew exactly which rows are not visible to anyone, a
big if, such a longrunning transaction would still at least cause bloat
up to a factor of two (with just one longrunning xact).

Sure pg_dump and analytics queries do so as well, but the user actually
has control over these and can schedule them at opportune times, use
base backups and/or hot standby replicas.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers