On Mon, 29 Aug 2005, Tom Lane wrote:

> "Sergey E. Koposov" <math@sai.msu.ru> writes:
>> Yes, the decreasing of max_prepared_transaction helped (after some
>> testing, I've found that the max_prepared_transactions=3
>> max_connections=10 shared_buffers=20 was well enough to fit 1mb of
>> shared memory)
>
> 20 buffers ... ugh. Obviously we are on the hairy edge of no longer
> functioning at all in 1MB shared memory. I'm not sure there is a whole
> lot we can do about this, but it's a tad irritating that clog, subtrans,
> and multixact are eating the equivalent of about 16 buffers
> (nonconfigurable) while the main buffer pool is so badly starved.
> It'd be better to reduce their allocations.

Would it be a good idea to make them share a single pool? (in 8.2, of
course).

And how about making the slru page size smaller? clog, subtrans and
multixact are quite randomly, I think.

- Heikki

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On Mon, Aug 29, 2005 at 11:30:46AM -0400, Tom Lane wrote:
>> 20 buffers ... ugh. Obviously we are on the hairy edge of no longer
>> functioning at all in 1MB shared memory. I'm not sure there is a whole
>> lot we can do about this, but it's a tad irritating that clog, subtrans,
>> and multixact are eating the equivalent of about 16 buffers
>> (nonconfigurable) while the main buffer pool is so badly starved.

> 8 buffers each, I think, no? That's 32 buffers total.

You're right; I was thinking that NUM_SLRU_BUFFERS was 4, but I see it's
now 8. Did we bump that up on the basis of any solid evidence? There's
256K of shared memory going into those four dedicated buffer areas,
which is kind of a lot when you're hoping to fit into 1MB.

I just finished going through the initialization sequence to trace the
calculation of shared memory size, and what I find in CVS tip is that
it works out like this:

shared_buffers * 8314
max_connections * (217.68 * max_locks_per_transaction + 356)
max_prepared_transactions * (217.68 * max_locks_per_transaction + 576)
wal_buffers * 8192
max_fsm_relations * 70
max_fsm_pages * 6
plus about 500K fixed space

(These numbers are on a 32-bit machine, some of the multipliers would be
a little higher on 64-bit.)

The formula given in the docs doesn't seem to have been updated since 7.2:
250 kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections

Most of the bloat since then seems to be accounted for by 2PC and the
addition of subtrans and multixact buffers.

> Maybe we could make them allocate them automatically based on
> shared_buffers, with a ceiling of 8?

Seems like it'd be reasonable to skinny down the number of dedicated
buffers when shared_buffers is tiny, but I'm not sure about the
particular equation to use.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

I wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>> 8 buffers each, I think, no? That's 32 buffers total.

> You're right; I was thinking that NUM_SLRU_BUFFERS was 4, but I see it's
> now 8. Did we bump that up on the basis of any solid evidence?

Never mind, looks like that goes all the way back: NUM_CLOG_BUFFERS was
8 in 7.2, and we just kept that value. I doubt there's been any testing
of different possible values at all ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Heikki Linnakangas <hlinnaka@iki.fi> writes:
> On Mon, 29 Aug 2005, Tom Lane wrote:
>> 20 buffers ... ugh. Obviously we are on the hairy edge of no longer
>> functioning at all in 1MB shared memory. I'm not sure there is a whole
>> lot we can do about this, but it's a tad irritating that clog, subtrans,
>> and multixact are eating the equivalent of about 16 buffers
>> (nonconfigurable) while the main buffer pool is so badly starved.

> Would it be a good idea to make them share a single pool? (in 8.2, of
> course).

Possibly, but I'd be worried about increasing contention. Right now
each of those modules has a separate lock and so accesses don't
interfere with each other. With a single pool I think there'd have to
be just one lock. Hard to guess if the effect would be noticeable,
though.

> And how about making the slru page size smaller? clog, subtrans and
> multixact are quite randomly, I think.

No, I disagree. The traffic to all these is concentrated heavily on
active and recent-past transactions. So mostly what you need is the
newest and next-to-newest pages, plus a few free slots for occasional
historical lookups. Making the page size smaller would mean that
we'd more frequently need to traverse the zero-out-a-new-page logic,
which is bad news (particularly for ExtendCLOG, which has to do that
while holding XidGenLock).

The real question in my mind is whether it's worth expending any effort
at all in this area. I think it's inevitable that we'll blow past the
magic 1MB mark in a release or two anyway, and it's certainly already
true that performance is going to suffer terribly if you have to run in
a shared memory segment of that size. So I'm disinclined to put more
than minimal work into supporting such configurations. I think we
should just document 2MB, or even 4MB, as the minimum usable SHMMAX ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Of course this might not make it into 8.1, but it seems somewhat
> backwards to be setting the default config just to satisfy make check.

Some of us prefer "make installcheck" ... so I'd still resist setting
the defaults to values that would make the regression tests fail.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Mon, Aug 29, 2005 at 01:28:22PM -0400, Tom Lane wrote:
> Heikki Linnakangas <hlinnaka@iki.fi> writes:
> > On Mon, 29 Aug 2005, Tom Lane wrote:
> >> Yes; mostly from 2PC support I think. Try reducing
> >> max_prepared_transactions. (We might want to debate whether the default
> >> setting should be smaller than 50 --- it looks to me like that's adding
> >> over 700K to the default shared memory block size.)
>
> > In fact, 0 might be reasonable default. Not many people need 2PC.
>
> Good point, but if we set it to 0 then the prepared-xacts regression
> test won't work. We could set it to just enough to run the test,
> say five or ten.

ISTM it would be better to allow the regression tests to use their own
config. It might even be useful to allow the regression tests to use
multiple postgresql.conf files as a means to test different things.

Of course this might not make it into 8.1, but it seems somewhat
backwards to be setting the default config just to satisfy make check.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Aug 29, 2005, at 12:41 PM, Tom Lane wrote:

> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>
>> On Mon, Aug 29, 2005 at 11:30:46AM -0400, Tom Lane wrote:
>>
>>> 20 buffers ... ugh. Obviously we are on the hairy edge of no longer
>>> functioning at all in 1MB shared memory. I'm not sure there is a
>>> whole
>>> lot we can do about this, but it's a tad irritating that clog,
>>> subtrans,
>>> and multixact are eating the equivalent of about 16 buffers
>>> (nonconfigurable) while the main buffer pool is so badly starved.
>>>
>
>
>> 8 buffers each, I think, no? That's 32 buffers total.
>>
>
> You're right; I was thinking that NUM_SLRU_BUFFERS was 4, but I see
> it's
> now 8. Did we bump that up on the basis of any solid evidence?
> There's
> 256K of shared memory going into those four dedicated buffer areas,
> which is kind of a lot when you're hoping to fit into 1MB.
>
> I just finished going through the initialization sequence to trace the
> calculation of shared memory size, and what I find in CVS tip is that
> it works out like this:
>
> shared_buffers * 8314
> max_connections * (217.68 * max_locks_per_transaction + 356)
> max_prepared_transactions * (217.68 * max_locks_per_transaction + 576)
> wal_buffers * 8192
> max_fsm_relations * 70
> max_fsm_pages * 6
> plus about 500K fixed space
>
> (These numbers are on a 32-bit machine, some of the multipliers
> would be
> a little higher on 64-bit.)
>
> The formula given in the docs doesn't seem to have been updated
> since 7.2:
> 250 kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections
>
> Most of the bloat since then seems to be accounted for by 2PC and the
> addition of subtrans and multixact buffers.
>
>
>> Maybe we could make them allocate them automatically based on
>> shared_buffers, with a ceiling of 8?
>>
>
> Seems like it'd be reasonable to skinny down the number of dedicated
> buffers when shared_buffers is tiny, but I'm not sure about the
> particular equation to use.
>
> regards, tom lane

Should the new formulation be sent to pgsql-docs? This looks like it
could be worked into a patch pretty easily. Seems like it would make
sense to update the docs for 8.1...

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Thomas F. O'Connell" <tfo@sitening.com> writes:
> On Aug 29, 2005, at 12:41 PM, Tom Lane wrote:
>> I just finished going through the initialization sequence to trace the
>> calculation of shared memory size, and what I find in CVS tip is that
>> it works out like this:

> Should the new formulation be sent to pgsql-docs? This looks like it
> could be worked into a patch pretty easily. Seems like it would make
> sense to update the docs for 8.1...

Already done.
http://developer.postgresql.org/docs...s.html#SYSVIPC

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match