Re: Auto-tuning work_mem and maintenance_work_mem

On Oct 12, 2013 2:13 AM, "MauMau" <maumau307(at)gmail(dot)com> wrote:
>
> From: "Bruce Momjian" <bruce(at)momjian(dot)us>
>>
>> On Thu, Oct 10, 2013 at 11:01:52PM +0900, MauMau wrote:
>>>
>>> Although this is not directly related to memory, could you set
>>> max_prepared_transactions = max_connections at initdb time? People
>>> must feel frustrated when they can't run applications on a Java or
>>> .NET application server and notice that they have to set
>>> max_prepared_transactions and restart PostgreSQL. This is far from
>>> friendly.
>>
>>
>> I think the problem is that many users don't need prepared transactions
>> and therefore don't want the overhead. Is that still accurate?
>
>
> I'm not sure if many use XA features, but I saw the questions and answer
a few times, IIRC. In the trouble situation, PostgreSQL outputs an
intuitive message like "increase max_prepared_transactions", so many users
might possibly have been able to change the setting and solve the problem
themselves without asking for help, feeling stress like "Why do I have to
set this?" For example, max_prepared_transactions is called "hideous
creature" in the following page:
>
> https://community.jboss.org/wiki/InstallPostgreSQLOnFedora?_sscc=t

Anybody who follows that page is screwed anyway. I notice they recommend
running regular VACUUM FULL across the whole database, so it's obvious they
know nothing about postgresql. There's nothing we can do about what people
write on random pages around the Internet.

> According to the below page, the amount of memory consumed for this is
"(770 + 270 * max_locks_per_transaction) * max_prepared_transactions".
With the default setting of maxconnections=100 and
max_locks_per_transaction=64, this is only 180KB. So the overhead is
negligible.

You are assuming memory is the only overhead. I don't think it is.

> If the goal is to make PostgreSQL more friendly and run smoothly without
frustration from the start and not perfect tuning, I think
max_prepared_transactions=max_connections is an easy and good item. If the
goal is limited to auto-tuning memory sizes, this improvement can be
treated separately.

Frankly, I think we'd help 1000 times more users of we enabled a few wal
writers by default and jumped the wal level. Mainly so they could run one
off base backup. That's used by orders of magnitude more users than XA.

/Magnus