Horton

Re: BitmapHeapScan streaming read user and prelim refactoring

Thomas Munro <thomas.munro@gmail.com>

View thread View on postgr.es
From: Thomas Munro <thomas.munro@gmail.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Melanie Plageman <melanieplageman@gmail.com>, Heikki Linnakangas <hlinnaka@iki.fi>, Andres Freund <andres@anarazel.de>, Pg Hackers <pgsql-hackers@postgresql.org>, Nazir Bilal Yavuz <byavuz81@gmail.com>
Date: 2024-03-29T11:17:13Z
Lists: pgsql-hackers 
I spent a bit of time today testing Melanie's v11, except with
read_stream.c v13, on Linux, ext4, and 3000 IOPS cloud storage.  I
think I now know roughly what's going on.  Here are some numbers,
using your random table from above and a simple SELECT * FROM t WHERE
a < 100 OR a = 123456.  I'll keep parallelism out of this for now.
These are milliseconds:

eic unpatched patched
0        4172    9572
1       30846   10376
2       18435    5562
4       18980    3503
8       18980    2680
16      18976    3233

So with eic=0, unpatched wins.  The reason is that Linux readahead
wakes up and scans the table at 150MB/s, because there are enough
clusters to trigger it.  But patched doesn't look quite so sequential
because we removed the sequential accesses by I/O combining...

At eic=1, unpatched completely collapses.  I'm not sure why exactly.

Once you go above eic=1, Linux seems to get out of the way and just do
what we asked it to do: iostat shows exactly 3000 IOPS, exactly 8KB
avg read size, and (therefore) throughput of 24MB/sec, though you can
see the queue depth being exactly what we asked it to do,eg 7.9 or
whatever for eic=8, while patched eats it for breakfast because it
issues wide requests, averaging around 27KB.

It seems more informative to look at the absolute numbers rather than
the A/B ratios, because then you can see how the numbers themselves
are already completely nuts, sort of interference patterns from
interaction with kernel heuristics.

On the other hand this might be a pretty unusual data distribution.
People who store random numbers or hashes or whatever probably don't
really search for ranges of them (unless they're trying to mine
bitcoins in SQL).  I dunno.  Maybe we need more realistic tests, or
maybe we're just discovering all the things that are bad about the
pre-existing code.

Commits

  1. Fix bitmapheapscan incorrect recheck of NULL tuples

  2. Increase default maintenance_io_concurrency to 16

  3. Separate TBM[Shared|Private]Iterator and TBMIterateResult

  4. Improve read_stream.c advice for dense streams.

  5. Increase default effective_io_concurrency to 16

  6. Delay extraction of TIDBitmap per page offsets

  7. Add lossy indicator to TBMIterateResult

  8. Move BitmapTableScan per-scan setup into a helper

  9. Add and use BitmapHeapScanDescData struct

  10. Fix bitmap table scan crash on iterator release

  11. Bitmap Table Scans use unified TBMIterator

  12. Add common interface for TBMIterators

  13. Make table_scan_bitmap_next_block() async-friendly

  14. Move EXPLAIN counter increment to heapam_scan_bitmap_next_block

  15. Refactor tidstore.c iterator buffering.

  16. BitmapHeapScan: Remove incorrect assert and reset field

  17. Change BitmapAdjustPrefetchIterator to accept BlockNumber

  18. BitmapHeapScan: Use correct recheck flag for skip_fetch

  19. BitmapHeapScan: Push skip_fetch optimization into table AM

  20. BitmapHeapScan: postpone setting can_skip_fetch

  21. BitmapHeapScan: begin scan after bitmap creation

  22. Fix EXPLAIN Bitmap heap scan to count pages with no visible tuples

  23. Remove redundant snapshot copying from parallel leader to workers

  24. Remove some obsolete smgrcloseall() calls.

  25. Remove the "snapshot too old" feature.

  26. Compute XID horizon for page level index vacuum on primary.