Thread: question about memory usage
Hi,
I run Windows and I started using 64 bit PostgreSQL 9.3 a month ago.===============================================================
Xact
Xact Rolled Blocks Blocks Tuples Tuples
Size Committed Back Read Hit Returned Fetched
---------------------------------------------------------------
1839 MB 52290 105 7593 1962771 27426946 502140
312 MB 23 0 177 1671 2796 902
340 MB 31 0 242 2867 4138 1426
69 MB 40796 21 7996 2019122 27852048 463184
71 MB 23 0 176 1674 2101 914
663 MB 37 0 328 2825 14240 1481
6298 kB 51680 0 950 1431658 23355239 291505
===============================================================
I also use SqlExpress server with several databases (including spatial) but that doesn't go above 100MB in Private Bytes and around 1 MB in Working Set.
Attachment
On Wed, 23 Jul 2014 00:16:47 +0200 klo uo <klonuo@gmail.com> wrote: > > Looking in process explorer, I see unusual size for postgres server > process, i.e. working set reported around 1GB: > http://i.imgur.com/HmkvFLM.png (same in attachment) > > I also use SqlExpress server with several databases (including spatial) but > that doesn't go above 100MB in Private Bytes and around 1 MB in Working Set. > > I wanted to ask, if this is normal, or there is some problem with my server > setting? I'm not an expert on the Windows version, so I could be off-base, but the POSIX versions of Postgres allocate shared_buffers worth of memory at startup and lock it for exclusive use by Postgres. Do you have shared_buffers set to around 1G, perhaps? -- Bill Moran <wmoran@potentialtech.com>
Bill, thanks for your reply.
Now that you mention config file, the only thing I did change there, and was suggested to me while I made some on my databases was "max_locks_per_transaction = 50000" (which has default value 10000).
After resetting "max_locks_per_transaction" to default value and restarting the server, memory occupied in working set reduced linearly to around 200 MB.
I guess this is it.On Wed, Jul 23, 2014 at 5:53 AM, Bill Moran <wmoran@potentialtech.com> wrote:
I'm not an expert on the Windows version, so I could be off-base, but the
POSIX versions of Postgres allocate shared_buffers worth of memory at startup
and lock it for exclusive use by Postgres. Do you have shared_buffers set to
around 1G, perhaps?
On 23 Červenec 2014, 15:56, klo uo wrote: > Bill, thanks for your reply. > > "shared_buffers" is set to "128MB". > > Now that you mention config file, the only thing I did change there, and > was suggested to me while I made some on my databases was > "max_locks_per_transaction = 50000" (which has default value 10000). > > After resetting "max_locks_per_transaction" to default value and > restarting > the server, memory occupied in working set reduced linearly to around 200 > MB. > > I guess this is it. The default value for max_locks_per_transaction is 64, not 10000. Values this high are quite insane, and suggest that either you don't know what the value means (and increased it just in case, because "more is always better") or that the application does something wrong (eventually requiring so many locks). You really need to check this (notice how the amount of shared memory depends on max_locks_per_transaction): http://www.postgresql.org/docs/9.0/static/kernel-resources.html#SHARED-MEMORY-PARAMETERS and this (which explains what max_locks_per_transaction does): http://www.databasesoup.com/2012/06/postgresqlconf-maxlockspertransaction.html regards Tomas
Tomas, thanks for the heads up!
I certainly didn't know what this setting means, except the obvious name. Your links helped.max_locks_per_transaction = 50000 # 10000
Thanks again
On Wed, Jul 23, 2014 at 4:10 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
On 23 Červenec 2014, 15:56, klo uo wrote:The default value for max_locks_per_transaction is 64, not 10000. Values
> Bill, thanks for your reply.
>
> "shared_buffers" is set to "128MB".
>
> Now that you mention config file, the only thing I did change there, and
> was suggested to me while I made some on my databases was
> "max_locks_per_transaction = 50000" (which has default value 10000).
>
> After resetting "max_locks_per_transaction" to default value and
> restarting
> the server, memory occupied in working set reduced linearly to around 200
> MB.
>
> I guess this is it.
this high are quite insane, and suggest that either you don't know what
the value means (and increased it just in case, because "more is always
better") or that the application does something wrong (eventually
requiring so many locks).
You really need to check this (notice how the amount of shared memory
depends on max_locks_per_transaction):
http://www.postgresql.org/docs/9.0/static/kernel-resources.html#SHARED-MEMORY-PARAMETERS
and this (which explains what max_locks_per_transaction does):
http://www.databasesoup.com/2012/06/postgresqlconf-maxlockspertransaction.html
regards
Tomas