Eating server memory
Posted: Thu 24 Nov 2011 12:01
Hi all,
This is my first post here so I hope that the issue has not been discussed again. If so, please accept my apologises. Here is my problem:
I have a Postgres database with millions of rows and a prepared Command that I execute over and over again changing the parameters to retrieve specific rows. In fact this command is usually executed around 20 million times. Now I know that possibly I could reduce those iteration, but before I do this I need to make sure that what I observer is normal. Here is what I observe:
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
So, after 2 hours of run, the postgres connection process (15965) has consumed all the server's memory and has start swapping (sda is my swap hd). This has as a result the degradation of my retrieval time. When I started it was 0.001sec per execution and now it has gone up to 0.01.
My question is why my process has consumed so much memory since I have a prepared command that I simply execute and normally close the reader after all. Are these cached data that are accumulating in the memory accessed by the process? And if yes, how can I clear them as I will obviously not use them again.
I have to admit that I was using before the freeware npgsql ado.net "driver" and same behaviour was observed there as well. I jumped here hoping that this issue might go away. So I am not sure if this is a postgres "issue" or a ado.net flavour issue or an issue all togather! I would expect though somehow to be able to clear this "memory" by closing the connection and disposing it every so often. Would this be a correct course of action?
Thank you in advance
Yiannis
This is my first post here so I hope that the issue has not been discussed again. If so, please accept my apologises. Here is my problem:
I have a Postgres database with millions of rows and a prepared Command that I execute over and over again changing the parameters to retrieve specific rows. In fact this command is usually executed around 20 million times. Now I know that possibly I could reduce those iteration, but before I do this I need to make sure that what I observer is normal. Here is what I observe:
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
So, after 2 hours of run, the postgres connection process (15965) has consumed all the server's memory and has start swapping (sda is my swap hd). This has as a result the degradation of my retrieval time. When I started it was 0.001sec per execution and now it has gone up to 0.01.
My question is why my process has consumed so much memory since I have a prepared command that I simply execute and normally close the reader after all. Are these cached data that are accumulating in the memory accessed by the process? And if yes, how can I clear them as I will obviously not use them again.
I have to admit that I was using before the freeware npgsql ado.net "driver" and same behaviour was observed there as well. I jumped here hoping that this issue might go away. So I am not sure if this is a postgres "issue" or a ado.net flavour issue or an issue all togather! I would expect though somehow to be able to clear this "memory" by closing the connection and disposing it every so often. Would this be a correct course of action?
Thank you in advance
Yiannis