Discussion:
question about memory usage
klo uo
2014-07-22 22:16:47 UTC
Permalink
Hi,

I run Windows and I started using 64 bit PostgreSQL 9.3 a month ago.
I have several PostGIS databases on localhost, with these statistics:

===============================================================
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
===============================================================

Looking in process explorer, I see unusual size for postgres server
process, i.e. working set reported around 1GB:
Loading Image...Image (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?


Thanks
Bill Moran
2014-07-23 03:53:58 UTC
Permalink
On Wed, 23 Jul 2014 00:16:47 +0200
Post by klo uo
Looking in process explorer, I see unusual size for postgres server
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 <***@potentialtech.com>
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
klo uo
2014-07-23 13:56:22 UTC
Permalink
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.
Post by Bill Moran
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?
Tomas Vondra
2014-07-23 14:10:31 UTC
Permalink
Post by klo uo
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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
klo uo
2014-07-23 19:33:21 UTC
Permalink
Tomas, thanks for the heads up!

I certainly didn't know what this setting means, except the obvious name.
Your links helped.
I just can't find where was this setting suggested, but IIRC it was in a
guide for migrating OSM to PostGIS, as other tables were just created by
GDAL OGR.

I had this line in my `postgresql.conf`:

max_locks_per_transaction = 50000 # 10000

that's why I thought that 10000 is the default, but it may be that
commented value was entered by me, and not the real default value.

I've set it now to 64.

Thanks again
Post by Tomas Vondra
Post by klo uo
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
http://www.postgresql.org/docs/9.0/static/kernel-resources.html#SHARED-MEMORY-PARAMETERS
http://www.databasesoup.com/2012/06/postgresqlconf-maxlockspertransaction.html
regards
Tomas
Loading...