Re: [PoC] pgstattuple2: block sampling to reduce physical read - Mailing list pgsql-hackers

<div class="moz-cite-prefix">On 16/09/13 16:20, Satoshi Nagayasu wrote:<br /></div><blockquote
cite="mid:52368724.6050706@uptime.jp"type="cite">(2013/09/15 11:07), Peter Eisentraut wrote: <br /><blockquote
type="cite">OnSat, 2013-09-14 at 16:18 +0900, Satoshi Nagayasu wrote: <br /><blockquote type="cite">I'm looking forward
toseeing more feedback on this approach, <br /> in terms of design and performance improvement. <br /> So, I have
submittedthis for the next CF. <br /></blockquote><br /> Your patch fails to build: <br /><br /> pgstattuple.c: In
function‘pgstat_heap_sample’: <br /> pgstattuple.c:737:13: error: ‘SnapshotNow’ undeclared (first use in this function)
<br/> pgstattuple.c:737:13: note: each undeclared identifier is reported only once for each function it appears in <br
/></blockquote><br/> Thanks for checking. Fixed to eliminate SnapshotNow. <br /><br /></blockquote><br /> This seems
likea cool idea! I took a quick look, and initally replicated the sort of improvement you saw:<br /><br /><br
/><tt>bench=#explain analyze select * from pgstattuple('pgbench_accounts');<br />
                                                 QUERY PLAN                    <br />                               
<br/> --------------------------------------------------------------------------------<br />  Function Scan on
pgstattuple (cost=0.00..0.01 rows=1 width=72) (actual time=786.368..786.369 rows=1 loops=1)<br />  Total runtime:
786.384ms<br /> (2 rows)<br /><br /> bench=# explain analyze select * from pgstattuple2('pgbench_accounts');<br />
NOTICE: pgstattuple2: SE tuple_count 0.00, tuple_len 0.00, dead_tuple_count 0.00, dead_tuple_len 0.00, free_space
0.00<br/>                                                   QUERY PLAN                    <br />
                             <br /> --------------------------------------------------------------------------------<br
/> Function Scan on pgstattuple2  (cost=0.00..0.01 rows=1 width=72) (actual time=12.004..12.005 rows=1 loops=1)<br />
 Totalruntime: 12.019 ms<br /> (2 rows)<br /><br /><br /></tt><tt><br /><font face="sans-serif"><tt><font
face="sans-serif">Iwondered what sort of difference eliminating caching wo<tt><font face="sans-serif">uld make:<br
/><br/><tt><font face="sans-serif">$ sudo sysctl -w vm.drop_caches=3</font></tt><br /><br /><tt><font
face="sans-serif"><tt><fontface="sans-serif">Repeatin<tt><font face="sans-serif">g the above quer<tt><font
face="sans-serif">ies:<br/><br /><br
/></font></tt></font></tt></font></tt></font></tt></font></tt></font></tt></font><tt><tt><tt><tt><tt><tt><tt>bench=#
explainanalyze select * from pgstattuple('pgbench_accounts');</tt><tt><br
/></tt><tt>                                                  QUERY PLAN                   </tt><tt><br
/></tt><tt>                                </tt><tt><br
/></tt><tt>--------------------------------------------------------------------------------</tt><tt><br
/></tt><tt> FunctionScan on pgstattuple  (cost=0.00..0.01 rows=1 width=72) (actual time=95</tt><tt>03.774..9503.776
rows=1loops=1)</tt><tt><br /></tt><tt> Total runtime: 9504.523 ms</tt><tt><br /></tt><tt>(2 rows)</tt><tt><br
/></tt><tt><br/></tt><tt><tt>bench=# explain analyze select * from pgstattuple2('pgbench_accounts');</tt><tt><br
/></tt><tt>NOTICE: pgstattuple2: SE tuple_count 0.00, tuple_len 0.00, dead_tuple_count 0.00, dead_tuple_len 0.00,
free_space0.00</tt><tt><br /></tt><tt>                                                     QUERY PLAN                
</tt><tt><br/></tt><tt>                                    </tt><tt><br
/></tt><tt>--------------------------------------------------------------------------------</tt><tt><br
/></tt><tt> FunctionScan on pgstattuple2  (cost=0.00..0.01 rows=1 width=72) (actual time=1</tt><tt>2330.630..12330.631
rows=1loops=1)</tt><tt><br /></tt><tt> Total runtime: 12331.353 ms</tt><tt><br /></tt><tt>(2
rows)</tt></tt></tt></tt></tt></tt></tt></tt><fontface="sans-serif"><tt><font face="sans-serif"><tt><font
face="sans-serif"><tt><fontface="sans-serif"><tt><font face="sans-serif"><tt><font face="sans-serif"><tt><font
face="sans-serif"><tt><fontface="sans-serif"><br /></font></tt><br /><br
/></font></tt></font></tt></font></tt></font></tt></font></tt></font></tt></font></tt>Sothe sampling code seems
*slower*when the cache is completely cold - is that expected? (I have not looked at how the code works yet - I'll dive
inlater if I get a chance)!<br /><br /> Regards<br /><br /> Mark<br /><tt><br /></tt>