Re: Shared memory usage in PostgreSQL 9.1

From: Tomas Vondra <tv(at)fuzzy(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Shared memory usage in PostgreSQL 9.1
Date: 2011-12-03 14:34:57
Message-ID: 4EDA3391.2070803@fuzzy.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 3.12.2011 13:39, Christoph Zwerschke wrote:
> For a PostgreSQL 9.1.1 instance,
> I have used the following postgresql.conf settings:
>
> max_connections = 100
> shared_buffers = 400MB
> wal_buffers = 16MB
>
> All the other parameters have been left as default values.
>
> When I startup the instance, I get an error message
> saying that the shared memory does not suffice
> and 451837952 Bytes would be used.
>
> However, this is not what I expect when calculating
> the needs according to the documentation, Table 17-2 at
> http://www.postgresql.org/docs/current/static/kernel-resources.html
>
> According to that table the usage would be:
> Connections: 1908000 Bytes
> Autovac workers: 57240 Bytes
> Prepared transactions: 0 Bytes
> Shared disk buffers: 400MB
> WAL buffers: 16MB
> Fixed space: 788480 Bytes
> Sum: 435145336
>
> This is about 16MB less than what is really requested.
>
> How can this substantial discrepancy be explained?
>
> For PostgreSQL 9.1, some important item must be missing
> in Table 17-2, or some values are wrong.

Hi,

the documentation is not exact, in this area. It's rather an overview
than exhaustive description, but I admit it's a bit confusing.

There are internal pieces that are not accounted for in the docs, and
part of the discrepancy probably comes from 32bit vs. 64bit differences.

Do you need to know an exact value or are you just interested why the
values in docs are not exact?

If you want to see what exactly needs how much memory, check the
src/backend/storage/ipc/ipci.c file in sources. I've added some log
messages, and this is the result on my 64bit machine (using the values
you've posted):

WARNING: initial = 100000
WARNING: hash estimate = 12368
WARNING: buffers = 424669472
WARNING: locks = 2509584
WARNING: predicate locks = 2278566
WARNING: proc global = 70237
WARNING: xlogs = 16803120
WARNING: clogs = 131360
WARNING: subtrans = 263040
WARNING: two-phase commits = 16
WARNING: multi-xacts = 198224
WARNING: lwlocks = 3282376
WARNING: proc array = 864
WARNING: backend status = 141440
WARNING: sinval = 67224
WARNING: pm signal = 872
WARNING: proc signal = 3960
WARNING: bgwriter = 1228840
WARNING: autovacuum = 216
WARNING: wal sender = 28
WARNING: wal receiver = 1072
WARNING: btree = 1260
WARNING: sync scan = 656
WARNING: async = 67112
WARNING: final size = 451985408

Tomas

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Torsten Zühlsdorff 2011-12-03 14:35:50 Re: How to convert HEX to ASCII?
Previous Message Tomas Vondra 2011-12-03 14:10:32 Re: Postgresql + corrupted disk = data loss. (Need help for database recover)