From: | Reid Thompson <reid(dot)thompson(at)crunchydata(dot)com> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Cc: | reid(dot)thompson(at)crunchydata(dot)com |
Subject: | Re: Add the ability to limit the amount of memory that can be allocated to backends. |
Date: | 2022-09-04 03:40:03 |
Message-ID: | 3178e9a1b7acbcf023fafed68ca48d76afc07907.camel@crunchydata.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)thompson(at)crunchydata(dot)com
www.crunchydata.com
Attachment | Content-Type | Size |
---|---|---|
0002-Add-the-ability-to-limit-the-amount-of-memory-that-c.patch | text/x-patch | 13.7 KB |
0001-Add-tracking-of-backend-memory-allocated-to-pg_stat_.patch | text/x-patch | 28.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Dilip Kumar | 2022-09-04 03:57:44 | Re: making relfilenodes 56 bits |
Previous Message | Reid Thompson | 2022-09-04 03:34:20 | Re: Add tracking of backend memory allocated to pg_stat_activity |