Thread: 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
Attachment
This message has been digitally signed by the sender.
Attachment
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