Обсуждение: Re: [GENERAL] PostgreSQL backend process high memory usage issue

On Tue, Apr 12, 2011 at 12:48 PM, Shianmiin <Shianmiin@gmail.com> wrote:
>
> 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.

I think you may have uncovered a leak (I stand corrected).

The number of schemas in your test is irrelevant -- the leak is
happening in proportion to the number of views (set via \setrandom
tidx 1 10).  At 1 I don't think it exists at all -- at 100 memory use
grows very fast.

Postgresql memory architecture is such that process local permanent
memory is extremely cautiously allocated typically for caching
purposes (like a tiny tablespace cache).  Temporarily, you can see
some per process memory allocations for hashing and sorting which you
can control with work_mem, and for maintenance purposes (create index)
with maintenance_work_mem.  This memory is always given back on
transaction end however.  In normal state of affairs, it's almost
impossible to run postgres out of memory unless you oversubscribe
work_mem and/or shared_buffers.

There are some operations in postgres which are notorious for
exhausting *shared* memory, like creating a lot of schemas and tables
in a single transaction.   However long term memory growth in resident
memory is a serious issue and needs to be tracked down and fixed.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> I think you may have uncovered a leak (I stand corrected).

> The number of schemas in your test is irrelevant -- the leak is
> happening in proportion to the number of views (set via \setrandom
> tidx 1 10).  At 1 I don't think it exists at all -- at 100 memory use
> grows very fast.

I don't think it's a leak, exactly: it's just that the "relcache" entry
for each one of these views occupies about 100K.  A backend that touches
N of the views is going to need about N*100K in relcache space.  I can't
get terribly excited about that.  Trying to reduce the size of the
relcache would be a net loss for most usage patterns (ie, we'd end up
increasing the amount of re-fetching from the system catalogs that
backends would have to do).  And I don't think that this test case has
much of anything to do with sane application design, anyway.  Do you
really need that many complex views?  Do you really need to have most
sessions touching all of them?

            regards, tom lane

On Wed, Apr 13, 2011 at 12:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> I think you may have uncovered a leak (I stand corrected).
>
>> The number of schemas in your test is irrelevant -- the leak is
>> happening in proportion to the number of views (set via \setrandom
>> tidx 1 10).  At 1 I don't think it exists at all -- at 100 memory use
>> grows very fast.
>
> I don't think it's a leak, exactly: it's just that the "relcache" entry
> for each one of these views occupies about 100K.  A backend that touches
> N of the views is going to need about N*100K in relcache space.  I can't
> get terribly excited about that.  Trying to reduce the size of the
> relcache would be a net loss for most usage patterns (ie, we'd end up
> increasing the amount of re-fetching from the system catalogs that
> backends would have to do).  And I don't think that this test case has
> much of anything to do with sane application design, anyway.  Do you
> really need that many complex views?  Do you really need to have most
> sessions touching all of them?

Ya, my mistake -- it *felt* like a leak when of course it was not.
100k does seem like an awful lot though -- perhaps this could be
organized better? -- but that's not really the point.  I've coded a
lot of multi schema designs and they tend to either go the one
session/schema route or the connection pooling route.  Either way,
cache memory usage tends to work itself out pretty well (it's never
been a problem for me before at least).  I can't recall anyone ever
even complaining about it in a non synthetic test.

merlin