Re: Auto-tuning work_mem and maintenance_work_mem

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

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.

http://www.postgresql.org/docs/9.2/static/kernel-resources.html

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.

Regards
MauMau