Re: Auto-tuning work_mem and maintenance_work_mem

From: "Bruce Momjian" <bruce@momjian.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