Re: PostgreSQL backend process high memory usage issue

Merlin Moncure-2 wrote:
>
>
> I am not seeing your results. I was able to run your test on a stock
> config (cut down to 50 schemas though) on a vm with 512mb of memory.
> What is your shared buffers set to?
>
>

The shared buffers was set to 32MB as in default postgresql.conf

To save you some time and make the conversion moves faster, I re-do a serial
of tests on a VM with 512MB of memory so that we have a common base. Here is
the test results and observations:

1. Setup: VM with 512MB of memory, CentOS 5.5 Final, PostgreSQL 9.0.3, a
fresh db cluster with everything default in all config files. For your
reference, I uploaded my postgresql.conf and top output here
http://postgresql.1045698.n5.nabble.com/file/n4298807/postgresql.conf
postgresql.conf
http://postgresql.1045698.n5.nabble.com/file/n4298807/01_Top_-_VM_with_512MB.gif
01_Top_-_VM_with_512MB.gif

2. using psql to connect to the instance, here is the top output
http://postgresql.1045698.n5.nabble.com/file/n4298807/02_Top_-_VM_with_512MB_-_fresh_connection_.gif
02_Top_-_VM_with_512MB_-_fresh_connection_.gif

3. The follow tests is on a db that initialized with 50 schemas, each with
50 tables/views

3a. single thread test with command "pgbench memoryusagetest -c 1 -j 1 -T
6000 -f test.sql"
http://postgresql.1045698.n5.nabble.com/file/n4298807/03_Top_-_VM_with_512MB_-_single_thread_test.gif
03_Top_-_VM_with_512MB_-_single_thread_test.gif

observations:
(backend process 1) VIRT 478 MB, RES 401 MB SHR 32 MB.
0% waiting
==> The single backend process pretty much use up all the physical memory
and maybe some swap spaces.
==> In the original test, 100 schemas with 100 tables/views per schema, the
process use 1.5 GB
In this test, 50 schemas with 50 tables/views per schema, the process
use 1.5 / 4 = 478 MB.
the memory used is somehow proportional to the number of objects in
the database.

3b. two threads test with command "pgbench memoryusagetest -c 2 -j 2 -T
6000 -f test.sql"
http://postgresql.1045698.n5.nabble.com/file/n4298807/04_Top_-_VM_with_512MB_-_two_threads_test.gif
04_Top_-_VM_with_512MB_-_two_threads_test.gif

observations:
(backend process 1) VIRT 476 MB, RES 320 MB SHR 9724 KB.
(backend process 2) VIRT 478 MB, RES 82 MB SHR 6308 KB.
37.4%waiting
==> the physically memory were all used up by the two backend processes,
plus 775 MB swap space used. The virtual (physical + swap) of each process
is the same as in the single thread test, i.e. ~ 470MB
==> please note that there is considerable %waiting here and kswapd0
starting to work a little hard

3c. three threads test with command "pgbench memoryusagetest -c 3 -j 3 -T
6000 -f test.sql"
http://postgresql.1045698.n5.nabble.com/file/n4298807/05_Top_-_VM_with_512MB_-_three_threads_test.gif
05_Top_-_VM_with_512MB_-_three_threads_test.gif

observations:
(backend process 1) VIRT 468 MB, RES 299 MB SHR 18 MB.
(backend process 2) VIRT 418 MB, RES 61 MB SHR 13 MB.
(backend process 3) VIRT 421 MB, RES 61 MB SHR 13 MB.
42.8%waiting
==> all physical memory is used and more swap spaces are used, I didn't let
it run long enough to see if the VIRT all go up to 470 MB since when
swapping is considerable, the tests slows down and the VIRT growth slows
down too. The VIRT still in the same range, i.e. 400-ish MB.
==> the % waiting gets higher and kswapd0 work harder and the tests run
slower

3d. four threads test with command "pgbench memoryusagetest -c 4 -j 4 -T
6000 -f test.sql"
http://postgresql.1045698.n5.nabble.com/file/n4298807/06_Top_-_VM_with_512MB_-_four_threads_test.gif
06_Top_-_VM_with_512MB_-_four_threads_test.gif
observations:

Observations:
(backend process 1) VIRT 424 MB, RES 196 MB SHR 21 MB.
(backend process 2) VIRT 416 MB, RES 83 MB SHR 15 MB.
(backend process 3) VIRT 418 MB, RES 86 MB SHR 16 MB.
(backend process 4) VIRT 466 MB, RES 66 MB SHR 16 MB.
47.8%waiting
==> all physical memory is used and more swap spaces are used, I didn't let
it run long enough to see if the VIRT all go up to 470 MB since when
swapping is considerable, the tests slows down and the VIRT growth slows
down too. The VIRT still in the same range, i.e. 400-ish MB.
==> the % waiting gets higher and kswapd0 work even harder and the tests run
even slower

3e. A crash test: 60 threads test with command "pgbench memoryusagetest
-c 60 -j 60 -T 6000 -f test.sql"
http://postgresql.1045698.n5.nabble.com/file/n4298807/07_Top_-_VM_with_512MB_-_60_threads_crashing_test.gif
07_Top_-_VM_with_512MB_-_60_threads_crashing_test.gif
Observations:
(backend process 1) VIRT 148 MB, RES 14 MB SHR 7852 KB.
(backend process 2) VIRT 149 MB, RES 13 MB SHR 7636 KB.
...
63.9%waiting
==> as expected all physical memory is used and here swap space is used up
too. Each backend process just get as much VIRT memory as they can. After
running it a while, the system reach a point that everything was almost
freeze, then the pgbench process got killed and system back to working
state. The PostgreSQL reception is running fine though.

Here is the questions and concerns:

1. while running the single-thread test, it shows that the PosgreSQL backend
process allocates 478 MB and majority of them are private memory. What are
those memory used for? It looks like it's some kind of cache and probably
related to query plan?

2. Is there a way we can configure the max memory that a PostgreSQL backend
process can allocate? The concern is, from the test results, when there are
a considerable number of objects in the database (it's not unusual in a
single-db-multi-schema multitenant data model) with a pool of long-lived
connections, the memory usage of the corresponding PostgreSQL backend
process will grow over time into all available memory (physical + swap) and
results in considerable swapping and make the system really slow. The only
way I can find to release those allocated (esp. private) memory from backend
process is to disconnect. The concern is that those private memory may
contain some rarely used data (assuming it's used for query plan related
purpose) that it may be more efficient to release those memory for other
backend process to use. That force us that if we go this way, to get around
this issue, we have to build some kind of connection recycling logic to make
sure the system not running into serious memory thrashing situation. It
would be nice to have some way to configure how backend process uses memory,
or is there other ways to see this issue?

Being not knowing PostgreSQL well, sorry I made some assumptions here to try
to make it clear about my concerns. Any insides or suggestions or
corrections is welcomed.

Samuel

--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-backend-process-high-memory-usage-issue-tp4289407p4298807.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.