public inbox for [email protected]
help / color / mirror / Atom feedFrom: Anton A. Melnikov <[email protected]>
To: Tomas Vondra <[email protected]>
To: Andres Freund <[email protected]>
To: Andrei Lepikhov <[email protected]>
Cc: Stephen Frost <[email protected]>
Cc: [email protected]
Cc: Arne Roland <[email protected]>
Cc: [email protected] <[email protected]>
Cc: vignesh C <[email protected]>
Cc: Justin Pryzby <[email protected]>
Cc: Ibrar Ahmed <[email protected]>
Cc: stephen.frost <[email protected]>
Subject: Re: Add the ability to limit the amount of memory that can be allocated to backends.
Date: Tue, 23 Jan 2024 14:47:18 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
Hi!
Thanks for your interest and reply!
On 26.12.2023 20:28, Tomas Vondra wrote:
> Can you share some info about the hardware? For example the CPU model,
> number of cores, and so on. 12GB RAM is not quite huge, so presumably it
> was a small machine.
>
It is HP ProLanit 2x socket server. 2x6 cores Intel(R) Xeon(R) CPU X5675 @ 3.07GHz,
2x12GB RAM, RAID from SSD drives.
Linux 5.10.0-21-amd64 #1 SMP Debian 5.10.162-1 (2023-01-21) x86_64 GNU/Linux
One cpu was disabled and some tweaks was made as Andres advised to avoid
NUMA and other side effects.
Full set of the configuration commands for server was like that:
numactl --cpunodebind=0 --membind=0 --physcpubind=1,3,5,7,9,11 bash
sudo cpupower frequency-set -g performance
sudo cpupower idle-set -D0
echo 3059000 | sudo tee /sys/devices/system/cpu/cpu*/cpufreq/scaling_min_freq
(Turbo Boost and hyperthreading was disabled in BIOS.)
> I think what would be interesting are runs with
>
> pgbench -M prepared -S -c $N -j $N
>
> i.e. read-only tests (to not hit I/O), and $N being sufficiently large
> to maybe also show some concurrency/locking bottlenecks, etc.
>
> I may do some benchmarks if I happen to find a bit of time, but maybe
> you could collect such numbers too?
Firstly, i repeated the same -c and -j values in read-only mode as you advised.
As one can see in the read-only.png, the absolute TPS value has increased
significantly, by about 13 times.
Patched version with limit 200Mb was slightly slower than with limit 0 by ~2%.
The standard error in all series was ~0.5%.
Since the deviation has increased in comparison with rw test
the difference between unpatched version and patched ones with
limits 0, 8Gb and 16Gb is not sensible.
There is a raw data in the raw_data-read-only.txt.
> I think 6350 is a pretty terrible number, especially for scale 8, which
> is maybe 150MB of data. I think that's a pretty clear sign the system
> was hitting some other bottleneck, which can easily mask regressions in
> the memory allocation code. AFAICS the pgbench runs were regular r/w
> benchmarks, so I'd bet it was hitting I/O, and possibly even subject to
> some random effects at that level.
>
To avoid possible I/O bottleneck i followed these steps:
- gave all 24G mem to cpu 0 rather than 12G as in [1];
- created a ramdisk of 12G size;
- disable swap like that:
numactl --cpunodebind=0 --physcpubind=1,3,5,7,9,11 bash
sudo swapoff -a
sudo mkdir /mnt/ramdisk
sudo mount -t tmpfs -o rw,size=12G tmpfs /mnt/ramdisk
The inst dir, data dir and log file were all on ramdisk.
Pgbench in rw mode gave the following results:
- the difference between unpatched version and patched ones with
limits 0 and 16Gb almost the same: ~7470+-0.2% TPS.
(orange, green and blue distributions on the RW-ramdisk.png respectively)
- patched version with limit 8GB is slightly slower than three above;
(yellow distribution)
- patched version with limit 200MB slower than the first three
by a measurable value ~0,4% (~7440 TPS);
(black distribution)
The standard error in all series was ~0.2%. There is a raw data in the
raw_data-rw-ramdisk.txt
For the sake of completeness i'm going to repeat read-only measurements
with ramdisk. Аnd perform some tests with increased -c and -j values
as you advised to find the possible point where concurrency/blocking
bottlenecks start to play a role. And do this, of cause, for the last
version of the patch. Thanks for rebased it!
In general, i don't observe any considerable degradation in performance
from this patch of several or even 10%, which were mentioned in [2].
With the best regards,
--
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
[1] https://www.postgresql.org/message-id/1d3a7d8f-cb7c-4468-a578-d8a1194ea2de%40postgrespro.ru
[2] https://www.postgresql.org/message-id/3178e9a1b7acbcf023fafed68ca48d76afc07907.camel%40crunchydata.c...
REL_16_BETA1 (e0b82fc8e83) unpatched
85761.111563
85021.605432
85454.083033
85917.957666
85876.042244
85642.072514
86296.248572
85619.937094
85297.361215
85862.202724
84540.978854 (x)
85396.633119
85690.647063
84988.591565
85823.448629
84773.324608
86216.489115
85501.651461
86009.350080
85358.496042
85486.594373
85779.622224
85814.955234
84737.148907
85732.179382
85586.101863
85978.317659
85288.636666
85710.628041
85152.516444
85907.719844
84770.623411
85886.087293
85793.628493
85009.003868
85291.167576
86049.283058
85240.478343
86482.472191
85178.651590
85081.329863
85819.632868
85828.367169
86140.325742
85910.775960
85317.224079
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.c...
max_total_backend_memory undefined (default = 0)
86111.507420
86191.677434
85884.145207
86548.583375
85925.729848
86073.041345
85556.167244
86515.518378
85882.827060
86089.279375
84442.238391 (x)
86181.390728
86605.506000
86131.111260
85850.438380
86285.492544
85878.295476
85485.605781
86280.781506
85481.309046
86791.510072
85878.009945
86855.543915
85724.634195
84685.561101 (x)
85810.684502
84852.093784 (x)
86393.727723
86950.634063 (x)
85296.535016
85952.156912
85833.631996
85533.831539
85972.110542
85918.424161
85144.936232 (x)
86078.101155
86042.642900
86309.387185
85563.696537
85984.949710
86189.425869
85346.739647
86672.086794
86576.575964
85683.585455
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.c...
max_total_backend_memory = 16GB
85786.954867
86099.397176
86161.602185
86263.763959
85538.865680
85432.794926
85728.016002
85979.774947
85075.008854
86327.744016
85537.786933
86245.287068
86487.177382
85934.602769
85621.782529
85874.416847
86418.906684
85653.498016
86167.811566
85640.948196
86767.193839 (x)
86159.553447
86199.056598
85947.101168
85542.140478
86082.251029
85523.251587
85856.236524
85924.012803
85972.899323
85644.499894
85462.754650
85724.552728
85787.436042
85965.040630
85687.389455
84922.795072
85358.044611
86205.357569
85064.849443
84984.181808
85256.160481
85680.558543
85334.634567
85280.112157
84868.810444 (x)
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.c...
max_total_backend_memory = 8GB
85100.232214 (x)
86145.956362
86454.848486
85973.080571
86175.046121
86002.597626
85908.845992
85843.346909
86276.874013
86941.448180 (x)
85335.027277
86315.033717
85044.675027 (x)
86212.423345
85594.552032
85787.800684
85980.895557
85758.813670
85410.670102
85640.278088
86270.514436
86126.509444
86472.934287
86352.242914
86266.541944
85643.059914
86490.924339
84973.214587 (x)
86219.673171
86898.294513 (x)
85280.591982
86790.409937
85804.289889
86145.406187
86092.140989
84692.229690 (x)
85253.872633
85057.863152 (x)
85630.225186
84910.186408 (x)
86260.305705
86153.212996
85936.390876
86208.713158
84413.248101 (x)
86529.579668
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.c...
max_total_backend_memory = 200MB
71.710505 (x) out of memory errors in log
84243.587084
84576.485240
84546.191505
84769.692835
84811.989459
83941.333178
84390.922436
84080.510960
84406.063419
84815.054344
84926.435865
83382.114485 (x)
84233.526059
83737.372571
84710.087530
84266.060690
83979.118354
84966.343014
85145.211891
84821.723252
84575.757291
83986.231873
85087.270906
85140.140349
84125.963441
84090.664968
83898.033737
83646.132496
83868.054474
84194.324422
84295.701512
84154.903928
83877.842592
84018.297675
84840.741742
84893.298428
83717.601912
84367.085598
84675.434517
84871.418337
84412.975964
83599.945813
84713.575852
83989.731131
84818.386157
REL_16_BETA1 (e0b82fc8e83) unpatched
7467.419891
7472.014013
7493.242036
7408.774587
7496.802158
7457.542737
7487.910772
7475.640175
7485.466664
7499.874236
7474.825139
7479.573388
7456.172459
7454.242942
7500.062756
7440.119529
7429.548826
7464.584901
7466.640344
7472.130154
7457.652816
7498.109421
7505.506842
7475.923936
7465.309986
7470.681113
7411.998612
7457.822630
7509.467781
7449.197064
7504.898429
7464.936431
7447.685630
7497.218840
7465.513425
7481.328682
7463.571360
7429.434920
7462.178302
7505.404955
7490.309328
7488.477102
7462.639174
7471.688637
7461.336425
7499.165045
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.c...
max_total_backend_memory undefined (default = 0)
7464.611403
7478.403075
7450.917444
7483.562575
7500.521682
7479.266432
7459.974700
7507.553000
7495.172003
7452.101295
7513.070841
7463.382158
7456.085082
7472.833051
7503.577746
7433.234381
7461.966344
7483.855314
7495.659665
7493.387966
7397.391891
7471.962202
7476.375392
7460.974030
7514.881548
7505.659485
7455.310069
7453.988434
7448.730999
7445.222442
7501.325753
7420.613952
7409.553181
7482.888561
7493.232293
7507.268369
7482.801647
7429.492431
7470.676577
7482.153079
7457.478934
7469.759722
7416.620216
7471.906157
7454.781634
7466.844149
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.c...
max_total_backend_memory = 16GB
7481.056709
7468.899961
7489.134861
7498.626080
7504.534880
7503.127220
7471.474104
7488.119337
7458.440996
7464.739382
7488.627320
7455.193405
7459.978467
7482.468559
7491.174587
7432.616983
7492.806772
7469.908569
7449.892009
7508.213210
7458.613016
7476.039933
7492.311968
7496.616756
7435.722964
7476.914236
7478.436463
7474.484447
7431.591836
7475.391966
7495.125213
7481.887843
7436.923440
7421.318947
7461.459195
7491.085440
7491.841513
7467.331830
7506.848057
7509.429479
7483.237177
7488.256111
7459.021238
7387.323083 (x)
7428.320688
7471.441284
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.c...
max_total_backend_memory = 8GB
38.884155 (x) out of memory errors in log
7418.194045
7484.248051
7429.342711
7445.906427
7479.215456
7482.549510
7479.521513
7489.821055
7425.255640
7473.898287
7475.271906
7410.911195
7482.227422
7471.891959
7441.920472
7461.338663
7414.412202
7472.875753
7430.044709
7465.442867
7473.856803
7415.170436
7457.467000
7474.149360
7462.451469
7454.570057
7486.853451
7476.660190
7441.361623
7464.706562
7477.256048
7465.801516
7476.568591
7475.006562
7493.299365
7456.332872
7454.174261
7432.872466
7477.096962
7431.126181
7452.820622
7469.352342
7430.955516
7481.356856
7458.982820
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.c...
max_total_backend_memory = 200MB
36.223742 (x)
7399.245419
7451.173946
7396.641811
7454.101001
7479.472891
7427.912475
7441.225376
7450.589681
7412.533841
7437.228197
7446.286700
7441.405104
7477.718911
7398.548184
7392.161290
7458.758503
7467.946442
7424.785796
7459.894852
7440.628290
7454.374715
7445.956025
7425.295741
7448.542992
7437.003008
7440.505807
7462.882483
7453.967328
7449.913158
7431.653525
7465.553274
7420.272079
7458.774888
7476.748517
7469.100314
7433.124333
7424.384757
7462.361532
7475.693894
7431.876259
7414.515549
7451.841013
7407.344808
7455.825825
7384.738105
Attachments:
[image/png] read-only.png (213.7K, ../[email protected]/2-read-only.png)
download | view image
[text/plain] raw_data-read-only.txt (3.8K, ../[email protected]/3-raw_data-read-only.txt)
download | inline:
REL_16_BETA1 (e0b82fc8e83) unpatched
85761.111563
85021.605432
85454.083033
85917.957666
85876.042244
85642.072514
86296.248572
85619.937094
85297.361215
85862.202724
84540.978854 (x)
85396.633119
85690.647063
84988.591565
85823.448629
84773.324608
86216.489115
85501.651461
86009.350080
85358.496042
85486.594373
85779.622224
85814.955234
84737.148907
85732.179382
85586.101863
85978.317659
85288.636666
85710.628041
85152.516444
85907.719844
84770.623411
85886.087293
85793.628493
85009.003868
85291.167576
86049.283058
85240.478343
86482.472191
85178.651590
85081.329863
85819.632868
85828.367169
86140.325742
85910.775960
85317.224079
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.com
max_total_backend_memory undefined (default = 0)
86111.507420
86191.677434
85884.145207
86548.583375
85925.729848
86073.041345
85556.167244
86515.518378
85882.827060
86089.279375
84442.238391 (x)
86181.390728
86605.506000
86131.111260
85850.438380
86285.492544
85878.295476
85485.605781
86280.781506
85481.309046
86791.510072
85878.009945
86855.543915
85724.634195
84685.561101 (x)
85810.684502
84852.093784 (x)
86393.727723
86950.634063 (x)
85296.535016
85952.156912
85833.631996
85533.831539
85972.110542
85918.424161
85144.936232 (x)
86078.101155
86042.642900
86309.387185
85563.696537
85984.949710
86189.425869
85346.739647
86672.086794
86576.575964
85683.585455
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.com
max_total_backend_memory = 16GB
85786.954867
86099.397176
86161.602185
86263.763959
85538.865680
85432.794926
85728.016002
85979.774947
85075.008854
86327.744016
85537.786933
86245.287068
86487.177382
85934.602769
85621.782529
85874.416847
86418.906684
85653.498016
86167.811566
85640.948196
86767.193839 (x)
86159.553447
86199.056598
85947.101168
85542.140478
86082.251029
85523.251587
85856.236524
85924.012803
85972.899323
85644.499894
85462.754650
85724.552728
85787.436042
85965.040630
85687.389455
84922.795072
85358.044611
86205.357569
85064.849443
84984.181808
85256.160481
85680.558543
85334.634567
85280.112157
84868.810444 (x)
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.com
max_total_backend_memory = 8GB
85100.232214 (x)
86145.956362
86454.848486
85973.080571
86175.046121
86002.597626
85908.845992
85843.346909
86276.874013
86941.448180 (x)
85335.027277
86315.033717
85044.675027 (x)
86212.423345
85594.552032
85787.800684
85980.895557
85758.813670
85410.670102
85640.278088
86270.514436
86126.509444
86472.934287
86352.242914
86266.541944
85643.059914
86490.924339
84973.214587 (x)
86219.673171
86898.294513 (x)
85280.591982
86790.409937
85804.289889
86145.406187
86092.140989
84692.229690 (x)
85253.872633
85057.863152 (x)
85630.225186
84910.186408 (x)
86260.305705
86153.212996
85936.390876
86208.713158
84413.248101 (x)
86529.579668
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.com
max_total_backend_memory = 200MB
71.710505 (x) out of memory errors in log
84243.587084
84576.485240
84546.191505
84769.692835
84811.989459
83941.333178
84390.922436
84080.510960
84406.063419
84815.054344
84926.435865
83382.114485 (x)
84233.526059
83737.372571
84710.087530
84266.060690
83979.118354
84966.343014
85145.211891
84821.723252
84575.757291
83986.231873
85087.270906
85140.140349
84125.963441
84090.664968
83898.033737
83646.132496
83868.054474
84194.324422
84295.701512
84154.903928
83877.842592
84018.297675
84840.741742
84893.298428
83717.601912
84367.085598
84675.434517
84871.418337
84412.975964
83599.945813
84713.575852
83989.731131
84818.386157
[image/png] RW-ramdisk.png (214.6K, ../[email protected]/4-RW-ramdisk.png)
download | view image
[text/plain] raw_data-rw-ramdisk.txt (3.5K, ../[email protected]/5-raw_data-rw-ramdisk.txt)
download | inline:
REL_16_BETA1 (e0b82fc8e83) unpatched
7467.419891
7472.014013
7493.242036
7408.774587
7496.802158
7457.542737
7487.910772
7475.640175
7485.466664
7499.874236
7474.825139
7479.573388
7456.172459
7454.242942
7500.062756
7440.119529
7429.548826
7464.584901
7466.640344
7472.130154
7457.652816
7498.109421
7505.506842
7475.923936
7465.309986
7470.681113
7411.998612
7457.822630
7509.467781
7449.197064
7504.898429
7464.936431
7447.685630
7497.218840
7465.513425
7481.328682
7463.571360
7429.434920
7462.178302
7505.404955
7490.309328
7488.477102
7462.639174
7471.688637
7461.336425
7499.165045
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.com
max_total_backend_memory undefined (default = 0)
7464.611403
7478.403075
7450.917444
7483.562575
7500.521682
7479.266432
7459.974700
7507.553000
7495.172003
7452.101295
7513.070841
7463.382158
7456.085082
7472.833051
7503.577746
7433.234381
7461.966344
7483.855314
7495.659665
7493.387966
7397.391891
7471.962202
7476.375392
7460.974030
7514.881548
7505.659485
7455.310069
7453.988434
7448.730999
7445.222442
7501.325753
7420.613952
7409.553181
7482.888561
7493.232293
7507.268369
7482.801647
7429.492431
7470.676577
7482.153079
7457.478934
7469.759722
7416.620216
7471.906157
7454.781634
7466.844149
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.com
max_total_backend_memory = 16GB
7481.056709
7468.899961
7489.134861
7498.626080
7504.534880
7503.127220
7471.474104
7488.119337
7458.440996
7464.739382
7488.627320
7455.193405
7459.978467
7482.468559
7491.174587
7432.616983
7492.806772
7469.908569
7449.892009
7508.213210
7458.613016
7476.039933
7492.311968
7496.616756
7435.722964
7476.914236
7478.436463
7474.484447
7431.591836
7475.391966
7495.125213
7481.887843
7436.923440
7421.318947
7461.459195
7491.085440
7491.841513
7467.331830
7506.848057
7509.429479
7483.237177
7488.256111
7459.021238
7387.323083 (x)
7428.320688
7471.441284
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.com
max_total_backend_memory = 8GB
38.884155 (x) out of memory errors in log
7418.194045
7484.248051
7429.342711
7445.906427
7479.215456
7482.549510
7479.521513
7489.821055
7425.255640
7473.898287
7475.271906
7410.911195
7482.227422
7471.891959
7441.920472
7461.338663
7414.412202
7472.875753
7430.044709
7465.442867
7473.856803
7415.170436
7457.467000
7474.149360
7462.451469
7454.570057
7486.853451
7476.660190
7441.361623
7464.706562
7477.256048
7465.801516
7476.568591
7475.006562
7493.299365
7456.332872
7454.174261
7432.872466
7477.096962
7431.126181
7452.820622
7469.352342
7430.955516
7481.356856
7458.982820
REL_16_BETA1 (e0b82fc8e83) patched with https://www.postgresql.org/message-id/4edafedc0f8acb12a2979088ac1317bd7dd42145.camel%40crunchydata.com
max_total_backend_memory = 200MB
36.223742 (x)
7399.245419
7451.173946
7396.641811
7454.101001
7479.472891
7427.912475
7441.225376
7450.589681
7412.533841
7437.228197
7446.286700
7441.405104
7477.718911
7398.548184
7392.161290
7458.758503
7467.946442
7424.785796
7459.894852
7440.628290
7454.374715
7445.956025
7425.295741
7448.542992
7437.003008
7440.505807
7462.882483
7453.967328
7449.913158
7431.653525
7465.553274
7420.272079
7458.774888
7476.748517
7469.100314
7433.124333
7424.384757
7462.361532
7475.693894
7431.876259
7414.515549
7451.841013
7407.344808
7455.825825
7384.738105
view thread (64+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Add the ability to limit the amount of memory that can be allocated to backends.
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox