Greetings, * Jakub Wartak (jakub.war...@tomtom.com) wrote: > >Interesting that you weren't seeing any benefit to disabling readahead. > > I've got some free minutes and I have repeated the exercise in more realistic > and strict environment that previous one to conclude that the current > situation is preferable:
Thanks for spending time on this! > Analyzed table was having 171GB (as reported by \dt+) + indexes: 35GB, 147GB, > 35GB, 65GB (as reported by \di+) > Linux kernel 4.14.x, 2x NVME under dm-0 (it might matter as /dev/dm-0 might > is different layer and might have different storage settings), VG on top of > dm-0, LV with stripe-size 8kB, ext4. > s_b=128MB, RAM=128GB (- ~30GB which were reserved for HugePages), typical > output of PgSQL12: > INFO: "x": scanned 1500000 of 22395442 pages, containing 112410444 live rows > and 0 dead rows; 1500000 rows in sample, 1678321053 estimated total rows > > Hot VFS cache: > Run0: Defaults, default RA on dm-1=256 (*512=128kB), most of the time is > spent heapam_scan_analyze_next_block() -> .. -> pread() which causes > ~70..80MB/s as reported by pidstat, maximum 22-25% CPU, ~8k IOPS in iostat > with average request size per IO=25 sectors(*512/1024 = ~12kB), readahead on, > hot caches, total elapsed ~3m > Run1: Defaults, similar as above (hot VFS cache), total elapsed 2m:50s > Run2: Defaults, similar as above (hot VFS cache), total elapsed 2m:42s > Run3: Defaults, miliaria as above (hot VFS cache), total elapsed 2m:40s > > No VFS cache: > Run4: echo 3 > drop_caches, still with s_b=128MB: maximum 18-23% CPU, ~70MB/s > read, ondemand_readahead visible in perf, total elapsed 3m30s > Run5: echo 3 > drop_caches, still with s_b=128MB: same as above, total > elapsed 3m29s > Run6: echo 3 > drop_caches, still with s_b=128MB: same as above, total > elapsed 3m28s > > No VFS cache, readahead off: > Run7: echo 3 > drop_caches, still with s_b=128MB, blockdev --setra 0 > /dev/dm-0: reads at 33MB/s, ~13% CPU, 8.7k read IOPS @ avgrq-sz = 11 sectors > (*512=5.5kB), total elapsed 5m59s > Run8: echo 3 > drop_caches, still with s_b=128MB, blockdev --setra 0 > /dev/dm-0: as above, double-confirmed no readaheads [ > pread()->generic_file_read_iter()->ext4_mpage_readpages()-> bio.. ], total > elapsed 5m56s > Run9: echo 3 > drop_caches, still with s_b=128MB, blockdev --setra 0 > /dev/dm-0: as above, total elapsed 5m55s [ ... ] > >The VACUUM case is going to be complicated by what's in the visibility > >map. (..) > > After observing the ANALYZE readahead behavior benefit I've abandoned > the case of testing much more advanced VACUUM processing, clearly Linux > read-ahead is beneficial in even simple cases. This seems to be indicating that while the Linux kernel may end up reading pages we don't end up needing, it's much more often the case that it's ending up reading *some* pages that we do need, and that's happening often enough that it more than makes up for the extra reads being done. Instead of having these guessing games between the kernel and what PG's doing, however, we could potentially do better using posix_fadvise() to tell the kernel, up front, exactly what blocks we are going to ask for, and perhaps that would end up improving things. Attached is a very much rough-n-ready patch for doing that, using effective_io_concurrency to control how many blocks to pre-fetch for ANALYZE (0 meaning 'none'). If you've got a chance to test with different settings for effective_io_concurrency with the patch applied to see what impact posix_fadvise() has on these ANALYZE runs, that would be very cool to see. Going between effective_cache_size = 0 and effective_cache_size = 10 with this patch, in some very quick testing on a laptop NVMe, while making sure to drop caches and restart PG in between to clear out shared_buffers, definitely shows that prefetching done this way is an improvement over letting the kernel's normal read ahead handle it. > >> My only idea would be that a lot of those blocks could be read > >> asynchronously in batches (AIO) with POSIX_FADV_RANDOM issued on > >> block-range before, so maybe the the optimization is possible, but not > >> until we'll have AIO ;) > > > > (..)AIO is a whole other animal that's been discussed off and on > >around here but it's a much larger and more invasive change than just > >calling posix_fadvise(). > > Yes, I'm aware and I'm keeping my fingers crossed that maybe some day.... I don't think we should throw out the idea of using PrefetchBuffer() here. "Real" AIO would certainly be good to have one of these days, but until then, posix_fadvise() could net us some of those gains in the meantime. > The ANALYZE just seem fit to be natural candidate to use it. The only easy > chance > of acceleration of stats gathering - at least to me and enduser point of view > - > is to have more parallel autoanalyze workers running to drive more I/O > concurrency > (by e.g. partitioning the data), both in readahead and non-readahead > scenarios. > Which is a pity because 70-80% of such process sits idle. The readahead might > read > 10x more unnecessary data, but pread() doesn't have to wait. > <speculation>Once AIO > would be it could throw thousands of requests without readahead and achieve > much > better efficiency probably</speculation> Actual parallel ANALYZE could be interesting, of course, but that's also quite a bit more complicated to make happen, and even in that case it would seem like we'd probably want to use posix_fadvise() anyway since it'd hopefully help. > I hope the previous simple patch goes into master and helps other people > understand > the picture more easily. I agree that we should also be providing users with more information about what's going on, but maybe we can do that and improve things by leveraging posix_fadvise(). Thanks, Stephen
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 8af12b5c6b..6ce1159082 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -1070,6 +1070,33 @@ acquire_sample_rows(Relation onerel, int elevel,
if (!table_scan_analyze_next_block(scan, targblock, vac_strategy))
continue;
+ /*
+ * Now that we have a block, if prefetch is enabled then get a few more.
+ */
+#ifdef USE_PREFETCH
+ if (effective_io_concurrency)
+ {
+ /*
+ * Make a deep copy of the block sampler to use for prefetching
+ */
+ BlockSamplerData prefetch_bs = bs;
+
+ memcpy(prefetch_bs.randstate, bs.randstate, sizeof(SamplerRandomState));
+
+ for (int i = 0; i < effective_io_concurrency; i++)
+ {
+ BlockNumber prefetch_block;
+
+ if (!BlockSampler_HasMore(&prefetch_bs))
+ break;
+
+ prefetch_block = BlockSampler_Next(&prefetch_bs);
+ PrefetchBuffer(scan->rs_rd, MAIN_FORKNUM, prefetch_block);
+ }
+
+ }
+#endif
+
while (table_scan_analyze_next_tuple(scan, OldestXmin, &liverows, &deadrows, slot))
{
/*
signature.asc
Description: PGP signature
