Re: Add the ability to limit the amount of memory that can be allocated to backends. - Mailing list pgsql-hackers

On Wed, 2022-08-31 at 12:34 -0500, Justin Pryzby wrote:
> You should name the patches with different prefixes, like
> 001,002,003  Otherwise, cfbot may try to apply them in the wrong
> order.
> git format-patch is the usual tool for that.

Thanks for the pointer. My experience with git in the past has been
minimal and basic.

> > +        Specifies a limit to the amount of memory (MB) that may be
> > allocated to
>
> MB are just the default unit, right ?
> The user should be allowed to write max_total_backend_memory='2GB'

Correct. Default units are MB. Other unit types are converted to MB.

> > +        causing that backends current query/transaction to fail.
>
> backend's
> > +        allocated exceeding the limit. Further requests will not
>
> allocated, and exceed the limit
>
> > +       if (MyAuxProcType != NotAnAuxProcess)
> The double negative is confusing, so could use a comment.

> > +               elog(WARNING,
> I think it should be ereport() rather than elog(), which is
> internal-only, and not-translated.

Corrected/added the the above items. Attached patches with the corrections.

> > +               0, 0, INT_MAX,
> > +               NULL, NULL, NULL
> I think this needs a maximum like INT_MAX/1024/1024

Is this noting that we'd set a ceiling of 2048MB?

> > +       for (i = 1; i <= NumBackendStatSlots; i++)
> > +       {
>
> It's looping over every backend for each allocation.
> Do you know if there's any performance impact of that ?

I'm not very familiar with how to test performance impact, I'm open to
suggestions.  I have performed the below pgbench tests and noted the basic
tps differences in the table.

Test 1:
branch master
CFLAGS="-I/usr/include/python3.8/ " /home/rthompso/src/git/postgres/configure --silent
--prefix=/home/rthompso/src/git/postgres/install/master--with-openssl --with-tcl --with-tclconfig=/usr/lib/tcl8.6
--with-perl--with-libxml --with-libxslt --with-python --with-gssapi --with-systemd --with-ldap --enable-nls 
make -s -j12 && make -s install
initdb
default postgresql.conf settings
init pgbench pgbench -U rthompso -p 5433 -h localhost -i -s 50 testpgbench
10 iterations
for ctr in {1..10}; do     { time pgbench  -p 5433 -h localhost -c 10 -j 10 -t 50000 testpgbench; } 2>&1 | tee -a
pgstatsResultsNoLimitSet;done 

Test 2:
branch pg-stat-activity-backend-memory-allocated
CFLAGS="-I/usr/include/python3.8/ " /home/rthompso/src/git/postgres/configure --silent
--prefix=/home/rthompso/src/git/postgres/install/pg-stats-memory/--with-openssl --with-tcl
--with-tclconfig=/usr/lib/tcl8.6--with-perl --with-libxml --with-libxslt --with-python --with-gssapi --with-systemd
--with-ldap--enable-nls 
make -s -j12 && make -s install
initdb
default postgresql.conf settings
init pgbench pgbench -U rthompso -p 5433 -h localhost -i -s 50
testpgbench
10 iterations
for ctr in {1..10}; do     { time pgbench  -p 5433 -h localhost -c 10  -j 10 -t 50000 testpgbench; } 2>&1 | tee -a
pgstatsResultsPg-stats-memory;done 

Test 3:
branch dev-max-memory
CFLAGS="-I/usr/include/python3.8/ " /home/rthompso/src/git/postgres/configure --silent
--prefix=/home/rthompso/src/git/postgres/install/dev-max-memory/--with-openssl --with-tcl
--with-tclconfig=/usr/lib/tcl8.6--with-perl --with-libxml --with-libxslt --with-python --with-gssapi --with-systemd
--with-ldap--enable-nls 
make -s -j12 && make -s install
initdb
default postgresql.conf settings
init pgbench pgbench -U rthompso -p 5433 -h localhost -i -s 50 testpgbench
10 iterations
for ctr in {1..10}; do     { time pgbench  -p 5433 -h localhost -c 10  -j 10 -t 50000 testpgbench; } 2>&1 | tee -a
pgstatsResultsDev-max-memory;done 

Test 4:
branch dev-max-memory
CFLAGS="-I/usr/include/python3.8/ " /home/rthompso/src/git/postgres/configure --silent
--prefix=/home/rthompso/src/git/postgres/install/dev-max-memory/--with-openssl --with-tcl
--with-tclconfig=/usr/lib/tcl8.6--with-perl --with-libxml --with-libxslt --with-python --with-gssapi --with-systemd
--with-ldap--enable-nls 
make -s -j12 && make -s install
initdb
non-default postgresql.conf setting for max_total_backend_memory = 100MB
init pgbench pgbench -U rthompso -p 5433 -h localhost -i -s 50 testpgbench
10 iterations
for ctr in {1..10}; do     { time pgbench  -p 5433 -h localhost -c 10  -j 10 -t 50000 testpgbench; } 2>&1 | tee -a
pgstatsResultsDev-max-memory100MB;done 

Laptop
11th Gen Intel(R) Core(TM) i7-11850H @ 2.50GHz 8 Cores 16 threads
32GB RAM
SSD drive

Averages from the 10 runs and tps difference over the 10 runs

|------------------+------------------+------------------------+-------------------+------------------+-------------------+---------------+------------------|
| Test Run         | Master           | Track Memory Allocated | Diff from Master  | Max Mem off      | Diff from
Master | Max Mem 100MB | Diff from Master | 
| Set 1            | Test 1           | Test 2                 |                   | Test 3           |
 | Test 4        |                  | 
| latency average  | 2.43390909090909 | 2.44327272727273       |                   | 2.44381818181818 |
 | 2.6843        |                  | 
| tps inc conn est | 3398.99291372727 | 3385.40984336364       | -13.583070363637  | 3385.08184309091 |
-13.9110706363631| 3729.5363413  | 330.54342757273  | 
| tps exc conn est | 3399.12185727273 | 3385.52527490909       | -13.5965823636366 | 3385.22100872727 |
-13.9008485454547| 3729.7097607  | 330.58790342727  | 

|------------------+------------------+------------------------+-------------------+------------------+-------------------+---------------+------------------|
| Set 2            |                  |                        |                   |                  |
 |               |                  | 
| latency average  | 2.691            | 2.6895                 | 2                 | 2.69             | 3
 | 2.6827        | 4                | 
| tps inc conn est | 3719.56          | 3721.7587106           | 2.1987106         | 3720.3           | .74
 | 3730.86       | 11.30            | 
| tps exc conn est | 3719.71          | 3721.9268465           | 2.2168465         | 3720.47          | .76
 | 3731.02       | 11.31            | 

|------------------+------------------+------------------------+-------------------+------------------+-------------------+---------------+------------------|


> I think it may be necessary to track the current allocation size in
> shared memory (with atomic increments?).  Maybe decrements would need
> to
> be exactly accounted for, or otherwise Assert() that the value is not
> negative.  I don't know how expensive it'd be to have conditionals
> for
> each decrement, but maybe the value would only be decremented at
> strategic times, like at transaction commit or backend shutdown.
>

--
Reid Thompson
Senior Software Engineer
Crunchy Data, Inc.

reid.thompson@crunchydata.com
www.crunchydata.com