Re: Auto-tuning work_mem and maintenance_work_mem - Mailing list pgsql-hackers

<p dir="ltr"><br /> On Oct 12, 2013 2:13 AM, "MauMau" <<a
href="mailto:maumau307@gmail.com">maumau307@gmail.com</a>>wrote:<br /> ><br /> > From: "Bruce Momjian" <<a
href="mailto:bruce@momjian.us">bruce@momjian.us</a>><br/> >><br /> >> On Thu, Oct 10, 2013 at 11:01:52PM
+0900,MauMau wrote:<br /> >>><br /> >>> Although this is not directly related to memory, could you
set<br/> >>> max_prepared_transactions = max_connections at initdb time?  People<br /> >>> must feel
frustratedwhen they can't run applications on a Java or<br /> >>> .NET application server and notice that they
haveto set<br /> >>> max_prepared_transactions and restart PostgreSQL.  This is far from<br /> >>>
friendly.<br/> >><br /> >><br /> >> I think the problem is that many users don't need prepared
transactions<br/> >> and therefore don't want the overhead.  Is that still accurate?<br /> ><br /> ><br />
>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
possiblyhave 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:<br/> ><br /> > <a
href="https://community.jboss.org/wiki/InstallPostgreSQLOnFedora?_sscc=t">https://community.jboss.org/wiki/InstallPostgreSQLOnFedora?_sscc=t</a><p
dir="ltr">Anybodywho follows that page is screwed anyway. I notice they recommend running regular VACUUM FULL across
thewhole database, so it's obvious they know nothing about postgresql. There's nothing we can do about what people
writeon random pages around the Internet. <p dir="ltr">> According to the below page, the amount of memory consumed
forthis is "(770 + 270 * max_locks_per_transaction) * max_prepared_transactions".  With the default setting of
maxconnections=100and max_locks_per_transaction=64, this is only 180KB.  So the overhead is negligible.<p dir="ltr">You
areassuming memory is the only overhead. I don't think it is.<br /><p dir="ltr">> If the goal is to make PostgreSQL
morefriendly and run smoothly without frustration from the start and not perfect tuning, I think
max_prepared_transactions=max_connectionsis an easy and good item.  If the goal is limited to auto-tuning memory sizes,
thisimprovement can be treated separately.<br /><p dir="ltr">Frankly, I think we'd help 1000 times more users of we
enableda few wal writers by default and jumped the wal level. Mainly so they could run one off base backup. That's used
byorders of magnitude more users than XA. <p dir="ltr">/Magnus