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

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.com