On Oct 12, 2013 2:13 AM, "MauMau" <maumau...@gmail.com> wrote: > > From: "Bruce Momjian" <br...@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
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
