On Tue, Apr 12, 2011 at 12:48 PM, Shianmiin <Shianmiin@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
