Re: Add the ability to limit the amount of memory that can be allocated to backends.

On Mon, 2023-05-22 at 08:42 -0400, reid(dot)thompson(at)crunchydata(dot)com wrote:

More followup to the above.
>
> I experimented on my system regarding
> "The simple query select * from generate_series(0, 10000000) shows roughly 18.9 % degradation on my test server."
>
> My laptop:
> 32GB ram
> 11th Gen Intel(R) Core(TM) i7-11850H 8 cores/16 threads @ 2.50GHz (Max Turbo Frequency. 4.80 GHz ; Cache. 24 MB)
> SSD -> Model: KXG60ZNV1T02 NVMe KIOXIA 1024GB (nvme)

Hi

Ran through a few more tests on my system varying the
initial_allocation_allowance and allocation_allowance_refill_qty from the
current 1MB to 2, 4, 6, 8, 10 mb. Also realized that in my last tests/email I
had posted percent difference rather than percent change. Turns out for the
numbers that were being compared they're essentially the same, but I'm
providing both for this set of tests. Ten runs for each comparison. Compared
dev-max-memory set, dev-max-memory unset, master, and pg-stat-activity-backend-memory-allocated
against master at each allocation value;

Again, the test invokes
psql -At -d postgres $connstr -P pager=off -c 'select * from generate_series(0, 10000000)'
100 times on each of the 2 instances and calculates the AVG time and SD
for the 100 runs. It then uses the AVG from each instance to calculate
the percentage difference/change.

These tests contain one code change not yet pushed to pgsql-hackers. In
AllocSetReset() do not enter pgstat_report_allocated_bytes_decrease if no
memory has been freed.

Will format and post some pgbench test result in a separate email.

Percent difference:

───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ Results: difference-dev-max-memory-set VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 1MB allocation 2MB allocation 4MB allocation 6MB allocation 8MB allocation 10MB allocation
2 │ 4.2263% difference 3.03961% difference 0.0585808% difference 2.92451% difference 3.34694% difference 2.67771% difference
3 │ 3.55709% difference 3.92339% difference 2.29144% difference 3.2156% difference 2.06153% difference 2.86217% difference
4 │ 2.04389% difference 2.91866% difference 3.73463% difference 2.86161% difference 3.60992% difference 3.07293% difference
5 │ 3.1306% difference 3.64773% difference 2.38063% difference 1.84845% difference 4.87375% difference 4.16953% difference
6 │ 3.12556% difference 3.34537% difference 2.99052% difference 2.60538% difference 2.14825% difference 1.95454% difference
7 │ 2.20615% difference 2.12861% difference 2.85282% difference 2.43336% difference 2.31389% difference 3.21563% difference
8 │ 1.9954% difference 3.61371% difference 3.35543% difference 3.49821% difference 3.41526% difference 8.25753% difference
9 │ 2.46845% difference 2.57784% difference 3.13067% difference 3.67681% difference 2.89139% difference 3.6067% difference
10 │ 3.60092% difference 2.16164% difference 3.9976% difference 2.6144% difference 4.27892% difference 2.68998% difference
11 │ 2.55454% difference 2.39073% difference 3.09631% difference 3.24292% difference 1.9107% difference 1.76182% difference
12 │
13 │ 28.9089/10 29.74729/10 27.888631/10 28.92125/10 30.85055/10 34.26854/10
14 │ 2.89089 2.974729 2.7888631 2.892125 3.085055 3.426854
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ Results: difference-dev-max-memory-unset VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 1MB allocation 2MB allocation 4MB allocation 6MB allocation 8MB allocation 10MB allocation
2 │ 3.96616% difference 3.05528% difference 0.563267% difference 1.12075% difference 3.52398% difference 3.25641% difference
3 │ 3.11387% difference 3.12499% difference 1.1133% difference 4.86997% difference 2.11481% difference 1.11668% difference
4 │ 3.14506% difference 2.06193% difference 3.36034% difference 2.80644% difference 2.37822% difference 3.07669% difference
5 │ 2.81052% difference 3.18499% difference 2.70705% difference 2.27847% difference 2.78506% difference 3.02919% difference
6 │ 2.9765% difference 3.44165% difference 2.62039% difference 4.61596% difference 2.27937% difference 3.89676% difference
7 │ 3.201% difference 1.35838% difference 2.40578% difference 3.95695% difference 2.25983% difference 4.17585% difference
8 │ 5.35191% difference 3.96434% difference 4.32891% difference 3.62715% difference 2.17503% difference 0.620856% difference
9 │ 3.44241% difference 2.9754% difference 3.03765% difference 1.48104% difference 1.53958% difference 3.14598% difference
10 │ 10.1155% difference 4.21062% difference 1.64416% difference 1.51458% difference 2.92131% difference 2.95603% difference
11 │ 3.11011% difference 4.31318% difference 2.01991% difference 4.71192% difference 2.37039% difference 4.25241% difference
12 │
13 │ 41.23304/10 31.69076/10 23.800757/10 30.98323/10 24.34758/10 29.526856/10
14 │ 4.123304 3.169076 2.3800757 3.098323 2.434758 2.9526856
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ Results: difference-master VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 1MB allocation 2MB allocation 4MB allocation 6MB allocation 8MB allocation 10MB allocation
2 │ 0.0734782% difference 0.0955457% difference 0.0521627% difference 2.32643% difference 0.286493% difference 1.26977% difference
3 │ 0.547862% difference 1.19087% difference 0.276915% difference 0.334332% difference 0.260545% difference 0.108956% difference
4 │ 0.0714666% difference 0.931605% difference 0.753996% difference 0.457174% difference 0.215904% difference 1.43979% difference
5 │ 0.269737% difference 0.848613% difference 0.222909% difference 0.315927% difference 0.290408% difference 0.248591% difference
6 │ 1.04231% difference 0.367444% difference 0.699571% difference 0.29266% difference 0.844548% difference 0.273776% difference
7 │ 0.0584984% difference 0.15094% difference 0.0721539% difference 0.594991% difference 1.80223% difference 0.500557% difference
8 │ 0.355129% difference 1.19517% difference 0.201835% difference 1.2351% difference 0.266004% difference 0.80893% difference
9 │ 0.0811794% difference 1.16184% difference 1.01913% difference 0.149087% difference 0.402931% difference 0.125788% difference
10 │ 0.950973% difference 0.154471% difference 0.42623% difference 0.874816% difference 0.157934% difference 0.225433% difference
11 │ 0.501783% difference 0.308357% difference 0.279147% difference 0.122458% difference 0.538141% difference 0.865846% difference
12 │
13 │ 3.952417/10 6.404856/10 4.00405/10 6.702975/10 5.065138/10 5.867437/10
14 │ 0.3952417 0.6404856 0.400405 0.6702975 0.5065138 0.5867437
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ Results: difference-pg-stat-activity-backend-memory-allocated VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 1MB allocation 2MB allocation 4MB allocation 6MB allocation 8MB allocation 10MB allocation
2 │ 2.04788% difference 0.50705% difference 0.504772% difference 0.136316% difference 0.590087% difference 1.33931% difference
3 │ 1.21173% difference 0.3309% difference 0.482685% difference 1.67956% difference 0.175478% difference 0.969286% difference
4 │ 0.0680972% difference 0.295211% difference 0.867547% difference 1.12959% difference 0.193756% difference 0.714178% difference
5 │ 0.91525% difference 1.42408% difference 1.49059% difference 0.641652% difference 1.34265% difference 0.378394% difference
6 │ 2.46448% difference 2.67081% difference 0.63824% difference 0.650301% difference 0.481858% difference 1.65711% difference
7 │ 1.31021% difference 0.0548831% difference 1.23217% difference 2.11691% difference 0.31629% difference 3.85858% difference
8 │ 1.61458% difference 0.46042% difference 0.724742% difference 0.172952% difference 1.33157% difference 0.556898% difference
9 │ 1.65063% difference 0.59815% difference 1.42473% difference 0.725576% difference 0.229639% difference 0.875489% difference
10 │ 1.78567% difference 1.45652% difference 0.6317% difference 1.99146% difference 0.999521% difference 1.85291% difference
11 │ 0.391318% difference 1.13216% difference 0.138291% difference 0.531084% difference 0.680197% difference 1.63162% difference
12 │
13 │ 13.459845/10 8.930184/10 8.135467/10 9.775401/10 6.341046/10 13.83377/10
14 │ 1.3459845 0.8930184 0.8135467 0.9775401 0.6341046 1.3833775
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

Percent change:

───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ Results: change-dev-max-memory-set VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 1MB allocation 2MB allocation 4MB allocation 6MB allocation 8MB allocation 10MB allocation
2 │ 4.13884% change 2.99411% change 0.0585636% change 2.88237% change 3.29185% change 2.64233% change
3 │ 3.49493% change 3.84791% change 2.26549% change 3.16472% change 2.0405% change 2.82179% change
4 │ 2.02322% change 2.87668% change 3.66617% change 2.82124% change 3.54592% change 3.02643% change
5 │ 3.08235% change 3.5824% change 2.35263% change 1.83153% change 4.75781% change 4.08438% change
6 │ 3.07746% change 3.29033% change 2.94646% change 2.57188% change 2.12542% change 1.93562% change
7 │ 2.18208% change 2.10619% change 2.8127% change 2.40411% change 2.28743% change 3.16474% change
8 │ 1.97569% change 3.54957% change 3.30007% change 3.43808% change 3.35792% change 7.93011% change
9 │ 2.43836% change 2.54504% change 3.08242% change 3.61044% change 2.85019% change 3.54281% change
10 │ 3.53724% change 2.13852% change 3.91926% change 2.58067% change 4.18929% change 2.65428% change
11 │ 2.52233% change 2.36249% change 3.0491% change 3.19118% change 1.89262% change 1.74644% change
12 │
13 │ 28.4725/10 29.29324/10 27.452864/10 28.49622/10 30.33895/10 33.54893/10
14 │ 2.84725 2.929324 2.7452864 2.849622 3.033895 3.354893
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ Results: change-dev-max-memory-unset VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 1MB allocation 2MB allocation 4MB allocation 6MB allocation 8MB allocation 10MB allocation
2 │ 3.88903% change 3.00931% change 0.564858% change 1.11451% change 3.46296% change 3.20424% change
3 │ 3.06613% change 3.07691% change 1.10714% change 4.75421% change 2.09268% change 1.11048% change
4 │ 3.09637% change 2.04089% change 3.30482% change 2.7676% change 2.35028% change 3.03008% change
5 │ 2.77157% change 3.13506% change 2.6709% change 2.2528% change 2.74681% change 2.984% change
6 │ 2.93285% change 3.38343% change 2.5865% change 4.51183% change 2.25368% change 3.82229% change
7 │ 3.15057% change 1.34921% change 2.37719% change 3.88018% change 2.23458% change 4.09044% change
8 │ 5.21243% change 3.88728% change 4.23719% change 3.56254% change 2.15163% change 0.62279% change
9 │ 3.38416% change 2.93178% change 2.99221% change 1.47015% change 1.52782% change 3.09726% change
10 │ 10.6543% change 4.1238% change 1.63075% change 1.5032% change 2.87926% change 2.91298% change
11 │ 3.06248% change 4.22213% change 1.99972% change 4.60347% change 2.34263% change 4.16388% change
12 │
13 │ 41.21989/10 31.1598/10 23.471278/10 30.42049/10 24.04233/10 29.03844/10
14 │ 4.121989 3.11598 2.3471278 3.042049 2.404233 2.903844
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ Results: change-master VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 1MB allocation 2MB allocation 4MB allocation 6MB allocation 8MB allocation 10MB allocation
2 │ 0.0734512% change 0.0955% change 0.0521763% change 2.35381% change 0.286904% change 1.27789% change
3 │ 0.549367% change 1.18382% change 0.276532% change 0.333774% change 0.260206% change 0.108897% change
4 │ 0.0714411% change 0.927286% change 0.751164% change 0.456132% change 0.216137% change 1.4295% change
5 │ 0.269374% change 0.845028% change 0.222661% change 0.315429% change 0.29083% change 0.2489% change
6 │ 1.0369% change 0.368121% change 0.702026% change 0.292232% change 0.840997% change 0.273402% change
7 │ 0.0584813% change 0.151054% change 0.07218% change 0.596766% change 1.78613% change 0.499307% change
8 │ 0.355761% change 1.18807% change 0.201631% change 1.22752% change 0.265651% change 0.805671% change
9 │ 0.0812124% change 1.16863% change 1.02435% change 0.149198% change 0.402121% change 0.125709% change
10 │ 0.955516% change 0.154351% change 0.425324% change 0.871006% change 0.158059% change 0.225179% change
11 │ 0.500527% change 0.307882% change 0.278758% change 0.122533% change 0.539593% change 0.862113% change
12 │
13 │ 3.952031/10 6.389742/10 4.006802/10 6.7184/10 5.046628/10 5.856568/10
14 │ 0.3952031 0.6389742 0.4006802 0.67184 0.5046628 0.5856568
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ Results: change-pg-stat-activity-backend-memory-allocated VS master
───────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 1MB allocation 2MB allocation 4MB allocation 6MB allocation 8MB allocation 10MB allocation
2 │ 2.02713% change 0.505768% change 0.506049% change 0.136223% change 0.591833% change 1.3304% change
3 │ 1.20444% change 0.331448% change 0.481523% change 1.66557% change 0.175325% change 0.974006% change
4 │ 0.068074% change 0.294776% change 0.8638% change 1.12325% change 0.193568% change 0.711637% change
5 │ 0.91108% change 1.41401% change 1.47956% change 0.6396% change 1.33369% change 0.377679% change
6 │ 2.43448% change 2.63562% change 0.636209% change 0.648194% change 0.4807% change 1.64349% change
7 │ 1.30168% change 0.054868% change 1.22463% change 2.09474% change 0.316791% change 3.93449% change
8 │ 1.60165% change 0.461483% change 0.722126% change 0.173102% change 1.32277% change 0.555352% change
9 │ 1.63712% change 0.599944% change 1.41466% change 0.722953% change 0.229375% change 0.871673% change
10 │ 1.76986% change 1.44599% change 0.629711% change 1.97183% change 0.99455% change 1.8359% change
11 │ 0.392085% change 1.12579% change 0.138195% change 0.532498% change 0.677892% change 1.61841% change
12 │
13 │ 13.347599/10 8.869697/10 8.096463/10 9.70796/10 6.316494/10 13.853037/10
14 │ 1.3347599 0.8869697 0.8096463 0.970796 0.6316494 1.385303
───────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────