Re: Linux server connection process consumes all memory - Mailing list pgsql-novice

On 05/12/2011 21:13, Merlin Moncure wrote:

On Mon, Dec 5, 2011 at 4:41 AM, Ioannis Anagnostopoulos
<ioannis@anatec.com> wrote:
> Below is the forum discussion I have opened with Devart's ado.net driver and
> the NPGSQL (opensource) driver.
>
> http://www.devart.com/forums/viewtopic.php?p=76192#76192
> http://pgfoundry.org/forum/forum.php?thread_id=10481&forum_id=519
>
> As I say there, I managed to reduce my queries to 55.000 from 22 million.
> The server in this scenario is not running out of memory
> but it is still apparent that memory consumption is high (8.9%!!). So if 20
> people try to run the same query we are going to be back
> in square 1. For one more time let me assure that there is ONLY one prepared
> statement that is created at the beginning and is executed
> over and over again. My question to postgres people is IF there is any bug
> that becomes apparent as a memory leak after a lot of executions
> of the same statement.  My next test will be to convert my .net code to
> PGSql and execute it on the server without involving any client.
>
> The top below show the "updated" query running. This time the load is at
> 8.9% right at the end of the run. Nowhere near the 96% but please
> keep in mind that this happened when I reduced to iterations to 55.000.
>
> top - 10:35:23 up 96 days,  1:30,  1 user,  load average: 0.00, 0.01, 0.05
> Tasks:  87 total,   1 running,  86 sleeping,   0 stopped,   0 zombie
> Cpu(s):  1.5%us,  0.0%sy,  0.0%ni, 98.5%id,  0.0%wa,  0.0%hi,  0.0%si,
>  0.0%st
> Mem:   8070900k total,  8017768k used,    53132k free,    56800k buffers
> Swap:  7811068k total,     4336k used,  7806732k free,  7671980k cached
>
>
>  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
> 22181 postgres  20   0 2187m 701m 697m S    6  8.9   1:29.33 postgres

You are misinterpreting what top is telling you (it's a pretty common
mistake).  Most of the memory postgres is using as you can see is
inside SHR, or is shared memory that is shared between all backend
processes.

Hello Merlin, thank you for the answer. Well the example that you quote is from
a sunshine scenario were I had reduced the iteration to something more down to earth
and the process concluded. However if you followed the other examples I listed in earlier
post you will see cases like this:

top - 11:46:05 up 85 days, 2:41, 1 user, load average: 0.67, 1.03, 1.19
Tasks: 89 total, 1 running, 88 sleeping, 0 stopped, 0 zombie
Cpu(s): 10.3%us, 0.7%sy, 0.0%ni, 69.8%id, 19.2%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8070900k total, 8017232k used, 53668k free, 1508k buffers
Swap: 7811068k total, 4283720k used, 3527348k free, 2088528k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15965 postgres 20 0 12.9g 6.4g 1.4g S 11 83.4 13:59.15 postgres
15961 postgres 20 0 4285m 1.8g 1.8g D 31 23.2 9:35.03 postgres
15827 postgres 20 0 4276m 52m 51m S 0 0.7 0:00.50 postgres
15830 postgres 20 0 4278m 25m 24m S 0 0.3 0:01.27 postgres
15959 postgres 20 0 4288m 4376 4288 S 0 0.1 0:00.25 postgres
15832 postgres 20 0 4279m 1388 888 S 0 0.0 0:00.19 postgres

This is an extract from top and below is an extract of iostat:

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 158.00 1802.00 1602.00 3604 3204
sdb 70.50 590.00 40.00 1180 80
sdc 16.00 528.00 0.00 1056 0

Please note that process 15965 is my connection process. In that example we have
VIRT = 12.9G
RES = 6.4G
SHR=1.4G

And swap of course is almost exhausted. Maybe you are right but in the light of this example
can you provide any further explanation?

What have you set shared_buffers to?  My money is on you having
oversubscribed your box.

merlin

Shared_buffers = 2GB and according to this http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
I am spot on since my machine has 8G of RAM.

Thank you
Yiannis