Dave Owens <dave@xxxxxxxxxxxxx> wrote: > I do see a handful of backends that like to stay IDLE in > transaction for minutes at a time. We are refactoring the > application responsible for these long IDLE times, which will > hopefully reduce the duration of their connections. That may help some. Other things to consider: - If you can use a connection pooler in transaction mode to reduce the number of active connections you may be able to improve performance all around, and dodge this problem in the process. Very few systems can make efficient use of hundreds of concurrent connections, but for various reasons fixing that with a connection pooler is sometimes difficult. - If you have transactions (or SELECT statements that you run outside of explicit transactions) which you know will not be modifying any data, flagging them as READ ONLY will help contain the number of predicate locks and will help overall performance. (If the SELECT statements are not in explicit transactions, you may have to put them in one to allow the READ ONLY property to be set, or set default_transaction_read_only in the session to accomplish this.) - Due to the heuristics used for thresholds for combining fine-grained locks into coarser ones, you might be able to work around this by boosting max_connections above the number you are going to use. Normally when you increase max_pred_locks_per_transaction it increases the number of page locks it will allow in a table or index before it combines them into a relation lock; increasing max_connections doesn't affect the granularity promotion threshold, but it increases the total number of predicate locks allowed, so if you boost that and reduce max_pred_locks_per_transaction in proportion, you may be able to dodge the problem. It's an ugly workaround, but it might get you into better shape. If that does work, it's good evidence that we should tweak those heuristics. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
