Обсуждение: Shared memory usage in PostgreSQL 9.1

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.

-- Christoph

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

Am 03.12.2011 15:34, schrieb Tomas Vondra:
 > Do you need to know an exact value or are you just interested why the
 > values in docs are not exact?

Both. I'm writing an installation script that calculates the necessary
IPC memory and increases the limit on the OS level (kernel.shmmax) if
needed. I want to increase the limit only as much as really necessary,
so I need to calculate the IPC usage as exactly as possible.

(Btw, what negative consequences - if any - does it have if I set
kernel.shmmax higher as necessary, like all available memory? Does this
limit serve only as a protection against greedy applications?)

 > 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):

Thanks a lot, that was helpful. So it seems the values in the docs are
only correct for a 32 bit server.

But I still don't understand this:

In our example, we have set shared_buffers to:

400 MB = 419430400 Bytes

but according to your log the used memory is:

buffers = 424669472 Bytes

This is a discrepancy of 1.25%.

The difference could be explained by taking credit for the descriptors
which may not be comprised in the shared_buffers setting, even if the
shared_buffers value is set in memory units. But according to the docs,
the descriptors should use 208/8192 = 2.5%. And on a 64bit machine, the
descriptors should use even more memory, i.e. up to 5%.

So I'm still a bit confused.

-- Christoph

Christoph Zwerschke <cito@online.de> writes:
> ... This is a discrepancy of 1.25%.

> The difference could be explained by taking credit for the descriptors
> which may not be comprised in the shared_buffers setting, even if the
> shared_buffers value is set in memory units. But according to the docs,
> the descriptors should use 208/8192 = 2.5%. And on a 64bit machine, the
> descriptors should use even more memory, i.e. up to 5%.

> So I'm still a bit confused.

The long and the short of it is those numbers aren't meant to be exact.
If they were, we'd have to complicate the table to distinguish 32 vs 64
bit and possibly other factors, and we'd have to remember to re-measure
the values after any code change, neither of which seems worth the
trouble.  Please note that the table itself says that (a) the values are
approximate, and (b) nobody has bothered to update the numbers since
8.3.  Personally, I'm thrilled if you're seeing a discrepancy of only
1.25%.

            regards, tom lane

Am 03.12.2011 18:02, schrieb Christoph Zwerschke:
> The difference could be explained by taking credit for the descriptors
> which may not be comprised in the shared_buffers setting, even if the
> shared_buffers value is set in memory units.

Looked a bit more into this - the shared_buffers setting indeed only
determines the memory for he actual shared buffer blocks, even if given
in memory units. It does not include the descriptors and other freelist
related stuff that is also needed to build the shared buffer pool.

When I increased the shared_buffers by 10000, the shared memory usage
increased by 8372.4 Bytes, this is about 2.2% more than 10000 blocks
would use, close to the 2.5% which are documented.

-- Christoph

Am 03.12.2011 18:39, schrieb Tom Lane:
> The long and the short of it is those numbers aren't meant to be
> exact. If they were, we'd have to complicate the table to distinguish
> 32 vs 64 bit and possibly other factors, and we'd have to remember to
> re-measure the values after any code change, neither of which seems
> worth the trouble. Please note that the table itself says that (a)
> the values are approximate, and (b) nobody has bothered to update the
> numbers since 8.3. Personally, I'm thrilled if you're seeing a
> discrepancy of only 1.25%.

Understood. Btw, the 1.25% did not refer to the discrepancy between
calculated and measured value, but to the memory overhead Tomas Vondra
measured for the shared buffer pool, while I measured an overhead of
about 2.5%, which should be also expected according to the docs.

Another thing that's a bit confusing in Table 17.2 is that it is not
immediately clear what size the shared disk buffers and wal buffers have
when shared_buffers and wal_buffers are specified in memory units, not
as integers as the table implies.

The answer is, as I found out, in order to get the "real" values for
shared_buffers and wal_buffers, the memory values must be divided by
block_size resp. wal_block_size; the formula then stays the same.

-- Christoph

Am 03.12.2011 13:39, schrieb Christoph Zwerschke:
> 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.

Just so that this summation does not stay uncorrected: The major
discrepancy accrued because my values for shared disk buffers and WAL
buffers were wrong. They must be calculated as

Shared disk buffers = (1 + 208/8192) * 400MB = 430080000 Bytes
WAL buffers = (1 + 8/8192) * 16MB = 16793600 Bytes

Then, the corrected sum is 449627320 Bytes, which is only about 2MB less
than was requested. This remaining discrepancy can probably be explained
by additional overhead for a PostgreSQL 9.1 64bit server vs. a
PostgreSQL 8.3 32bit server for which the table was valid.

-- Christoph

Am 03.12.2011 20:31, schrieb Christoph Zwerschke:
> Then, the corrected sum is 449627320 Bytes, which is only about 2MB less
> than was requested. This remaining discrepancy can probably be explained
> by additional overhead for a PostgreSQL 9.1 64bit server vs. a
> PostgreSQL 8.3 32bit server for which the table was valid.

And this additional overhead obviously is created per max_connections,
not per shared_buffers. While the docs suggest there should be 19kB per
connection, we measured about 45kB per connection. This explains the
about 2MB difference when max_connections is 100.

-- Christoph

Am 03.12.2011 18:02, schrieb Christoph Zwerschke:
> 400 MB = 419430400 Bytes
>
> but according to your log the used memory is:
>
> buffers = 424669472 Bytes
>
> This is a discrepancy of 1.25%.
>
> The difference could be explained by taking credit for the descriptors
> which may not be comprised in the shared_buffers setting, even if the
> shared_buffers value is set in memory units. But according to the docs,
> the descriptors should use 208/8192 = 2.5%. And on a 64bit machine, the
> descriptors should use even more memory, i.e. up to 5%.

Just to clear up that last unexplained discrepancy, the problem is that
I wrongly assumed the descriptors were the only overhead to the shared
buffers. In reality it is more complex, e.g. additional memory for locks
is reserved for each shared buffer. The 208 Bytes in the docs refer to
the total overhead a shared buffer creates, while the value in Tomas'
log contained only the overhead caused by the descriptors.

Sorry for creating the noise and confusion.

-- Christoph

* Christoph Zwerschke (cito@online.de) wrote:
> (Btw, what negative consequences - if any - does it have if I set
> kernel.shmmax higher as necessary, like all available memory? Does
> this limit serve only as a protection against greedy applications?)

Didn't see this get answered...  The long-and-short of that there aren't
any negative consequences of having it higher, as I understand it
anyway, except the risk of greedy apps.  In some cases, shared memory
can't be swapped out, which makes it a bit more risky than 'regular'
memory getting sucked up by some app.

    Thanks,

        Stephen

On 4.12.2011 15:06, Stephen Frost wrote:
> * Christoph Zwerschke (cito@online.de) wrote:
>> (Btw, what negative consequences - if any - does it have if I set
>> kernel.shmmax higher as necessary, like all available memory? Does
>> this limit serve only as a protection against greedy applications?)
>
> Didn't see this get answered...  The long-and-short of that there aren't
> any negative consequences of having it higher, as I understand it
> anyway, except the risk of greedy apps.  In some cases, shared memory
> can't be swapped out, which makes it a bit more risky than 'regular'
> memory getting sucked up by some app.

AFAIK it's "just" a protection. It simply allows more memory to be
allocated as shared segments. If you care about swapping, you should
tune vm.swappiness kernel parameter (and vm.overcommit is your friend too).

Tomas

Am 04.12.2011 15:17, schrieb sfrost@snowman.net:
> Didn't see this get answered...  The long-and-short of that there aren't
> any negative consequences of having it higher, as I understand it
> anyway, except the risk of greedy apps.  In some cases, shared memory
> can't be swapped out, which makes it a bit more risky than 'regular'
> memory getting sucked up by some app.

That's how I understand it as well. So the solution is to calculate an
upper limit for the shared memory usage very generously, since it
doesn't matter if the limit is set a couple of MBs too high.

-- Christoph

Christoph Zwerschke <cito@online.de> writes:
> Am 03.12.2011 20:31, schrieb Christoph Zwerschke:
>> Then, the corrected sum is 449627320 Bytes, which is only about 2MB less
>> than was requested. This remaining discrepancy can probably be explained
>> by additional overhead for a PostgreSQL 9.1 64bit server vs. a
>> PostgreSQL 8.3 32bit server for which the table was valid.

> And this additional overhead obviously is created per max_connections,
> not per shared_buffers. While the docs suggest there should be 19kB per
> connection, we measured about 45kB per connection. This explains the
> about 2MB difference when max_connections is 100.

I suspect most of the difference from 8.3 to 9.1 has to do with the
additional shared memory eaten by the predicate lock manager (for SSI).
That table really ought to get updated to include a factor for
max_pred_locks_per_transaction.  (And I wonder why
max_locks_per_transaction and max_pred_locks_per_transaction aren't
documented as part of the "memory consumption" GUC group?)

            regards, tom lane