Thank you guys for your responses. I'm using top to look for the memory consumption by the postgres processes. Inside the top, I'm using the interactive sorting command F and choose the sort by Data segment size. The exact sequence is: 1. launch top 2. Press 'F', the list of available for sorting fields appears on the screen, including "s: DATA = Data+Stack size (kb)" 3. press "s" 4. Top now shows the list of processes sorted by the field DATA Here how the sorted top screen looks in the end: top - 15:14:37 up 1 day, 23:20, 3 users, load average: 5.64, 5.18, 5.10 Tasks: 819 total, 2 running, 817 sleeping, 0 stopped, 0 zombie Cpu(s): 1.4%us, 0.8%sy, 0.0%ni, 75.4%id, 22.1%wa, 0.0%hi, 0.3%si, 0.0%st Mem: 32876676k total, 18455704k used, 14420972k free, 129856k buffers Swap: 2104504k total, 2720k used, 2101784k free, 15038240k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ DATA COMMAND 4392 hyperic 16 0 410m 73m 9672 S 0 0.2 136:17.29 363m java 29487 postgres 16 0 108m 83m 2680 S 0 0.3 0:01.10 81m view 803 postgres 16 0 2300m 180m 163m S 0 0.6 0:01.04 23m postgres 1808 postgres 16 0 2300m 164m 147m S 0 0.5 0:01.03 23m postgres 577 postgres 16 0 2298m 166m 150m S 0 0.5 0:00.87 22m postgres 568 postgres 16 0 2298m 141m 126m S 0 0.4 0:00.63 22m postgres 1506 postgres 16 0 2298m 139m 124m S 0 0.4 0:00.81 22m postgres 362 postgres 16 0 2292m 128m 115m S 0 0.4 0:00.66 16m postgres 7674 postgres 15 0 2288m 29m 20m S 0 0.1 0:00.10 13m postgres 7238 postgres 16 0 2289m 61m 52m S 0 0.2 0:00.23 12m postgres 7440 postgres 16 0 2288m 51m 42m S 0 0.2 0:00.18 12m postgres 7248 postgres 16 0 2288m 52m 44m S 0 0.2 0:00.17 12m postgres 7336 postgres 16 0 2288m 59m 50m S 0 0.2 0:00.20 12m postgres 7246 postgres 16 0 2288m 52m 44m S 0 0.2 0:00.12 12m postgres 6913 postgres 16 0 2288m 59m 51m S 0 0.2 0:00.22 12m postgres 7013 postgres 16 0 2288m 51m 43m S 0 0.2 0:00.10 12m postgres 7288 postgres 16 0 2288m 48m 39m S 0 0.2 0:00.16 12m postgres 7327 postgres 16 0 2288m 53m 44m S 0 0.2 0:00.16 12m postgres 7070 postgres 16 0 2288m 50m 42m S 0 0.2 0:00.16 12m postgres 7543 postgres 15 0 2288m 47m 39m S 0 0.1 0:00.13 11m postgres ........ Also, in vmstat, I see the gradual reduction in size of the cache memory. Apparently, the Linux cache gets gradually dismissed by the postgres processes memory areas. Eventually, the database just hangs and the host becomes unresponsive for about 15 minutes till the sessions die out. So, I believe it is not just my misinterpretation of the metrics. Isn't it true, that work memory once allocated for a session does not get deallocated till the sessions is closed? It was my impression, anyway. So, eventually enough sessions get big work memory allocated to starve the Linux out of memory. My physical memory size is 32GB, Shared_buffers = 2GB On 6/15/09 2:13 PM, "Scott Marlowe" <scott.marlowe@xxxxxxxxx> wrote: > On Mon, Jun 15, 2009 at 2:15 PM, Igor Polishchuk<ipolishchuk@xxxxxxx> wrote: >> Hello everybody! >> >> I have many app servers using connection pooling. At any time, there are >> about 1000 total connection to the database from all the app servers; >> however, only few random connections are active. >> The application often executes relatively big sorts. The work_mem size is >> 32MB, and eventually many sessions have a chance to run a sort and allocate >> a big sort area. I see hundreds of postgres processes with DATA segment > >> 15MB. >> Eventually, it consumes all the available memory. Most of this memory is >> allocated to the sessions that are idle. I cannot change the connection >> pooling on the application side, and the big sorts cannot be eliminated. I >> need a solution on the DB side. > > Could you post the commands and output you used to determine this? I > agree with Tom that you might be mis-measuring memory usage. > > You do not mention your shared_memory size or total memory size. If > you've got say 16G of ram and 12 Gig of shared_memory, then it's quite > possible having touched all of shared_memory a pgsql backend will show > 10 or 12 Gigs memory used. It's not actually independently using that > much, but some people freak when they see it the first time. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
