3 ms·

If you're truncating/dropping tables all the the time, it can actually be beneficial to set shared_buffers to a pretty small value. When dropping/truncating, po

If you're truncating/dropping tables all the the time, it can actually be beneficial to set shared_buffers to a pretty small value. When dropping/truncating, postgres needs to scan the buffer cache to get rid of all entries belonging to that relation (happens at the end of the transaction). The mapping is a hashtable, so there's no ordered scan to do so in a nice way. Also, do the truncations all in a single transaction. Since the actual dropping/truncations only happens after commit (for obvious reasons), we've at least optimized multiple truncations to happen in one pass. It's unfortunate that a larger shared buffers can hurt this way :( I've long hoped to find the cycles to finish a prototype that converted the buffer mapping to a hash table (for relations) over radix trees (or blocks in the relation).

MaxGabriel 7y ago
Nice, that's a good tip. It's a little hard to test small speedups like this, but I used https://hackage.haskell.org/package/bench https://hackage.haskell.org/package/bench and got these results: 128MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 15:19:04 benchmarking make test time 226.4 s (NaN s .. 240.6 s) 1.000 R² (0.999 R² .. 1.000 R²) mean 222.3 s (220.6 s .. 224.4 s) std dev 2.390 s (104.1 ms .. 2.986 s) variance introduced by outliers: 19% (moderately inflated) 128MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 16:18:57 benchmarking make test time 225.8 s (208.8 s .. 233.9 s) 0.999 R² (0.999 R² .. 1.000 R²) mean 221.6 s (215.8 s .. 224.8 s) std dev 5.576 s (1.671 s .. 7.491 s) variance introduced by outliers: 19% (moderately inflated) 2MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 18:30:27 benchmarking make test time 216.9 s (204.5 s .. 229.5 s) 1.000 R² (0.998 R² .. 1.000 R²) mean 219.7 s (216.7 s .. 221.3 s) std dev 2.863 s (1.283 s .. 3.658 s) variance introduced by outliers: 19% (moderately inflated) 2MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 19:29:26 benchmarking make test time 209.6 s (192.8 s .. 224.1 s) 0.999 R² (0.999 R² .. 1.000 R²) mean 216.0 s (211.0 s .. 218.5 s) std dev 3.732 s (1.442 s .. 5.168 s) variance introduced by outliers: 19% (moderately inflated) So it seems like 2MB shared buffers shaves a few seconds off our tests. Is 2MB in the range you were thinking of?
- anarazel 7y ago
  > So it seems like 2MB shared buffers shaves a few seconds off our tests. Is 2MB in the range you were thinking of? Well, hard to say without knowing anything about your tests. I think the cases where I've seen it make a really big difference were more ones where people upped and upped shared_buffers trying to improve test performance, without noticing that it made things worse. I'd probably not go below 16MB, otherwise it's plausible you could run into some errors (e.g. if you have a large number of cursors or such - they can end up pinning a few buffers, and if there's only 256 buffers, you don't need to be too creative for that).

MaxGabriel 7y ago

Nice, that's a good tip. It's a little hard to test small speedups like this, but I used https://hackage.haskell.org/package/bench https://hackage.haskell.org/package/bench and got these results: 128MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 15:19:04 benchmarking make test time 226.4 s (NaN s .. 240.6 s) 1.000 R² (0.999 R² .. 1.000 R²) mean 222.3 s (220.6 s .. 224.4 s) std dev 2.390 s (104.1 ms .. 2.986 s) variance introduced by outliers: 19% (moderately inflated) 128MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 16:18:57 benchmarking make test time 225.8 s (208.8 s .. 233.9 s) 0.999 R² (0.999 R² .. 1.000 R²) mean 221.6 s (215.8 s .. 224.8 s) std dev 5.576 s (1.671 s .. 7.491 s) variance introduced by outliers: 19% (moderately inflated) 2MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 18:30:27 benchmarking make test time 216.9 s (204.5 s .. 229.5 s) 1.000 R² (0.998 R² .. 1.000 R²) mean 219.7 s (216.7 s .. 221.3 s) std dev 2.863 s (1.283 s .. 3.658 s) variance introduced by outliers: 19% (moderately inflated) 2MB ️ ~/D/M/h/mercury-web-backend> caffeinate bench "make test" 19:29:26 benchmarking make test time 209.6 s (192.8 s .. 224.1 s) 0.999 R² (0.999 R² .. 1.000 R²) mean 216.0 s (211.0 s .. 218.5 s) std dev 3.732 s (1.442 s .. 5.168 s) variance introduced by outliers: 19% (moderately inflated) So it seems like 2MB shared buffers shaves a few seconds off our tests. Is 2MB in the range you were thinking of?

anarazel 7y ago
> So it seems like 2MB shared buffers shaves a few seconds off our tests. Is 2MB in the range you were thinking of? Well, hard to say without knowing anything about your tests. I think the cases where I've seen it make a really big difference were more ones where people upped and upped shared_buffers trying to improve test performance, without noticing that it made things worse. I'd probably not go below 16MB, otherwise it's plausible you could run into some errors (e.g. if you have a large number of cursors or such - they can end up pinning a few buffers, and if there's only 256 buffers, you don't need to be too creative for that).