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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
