Thread: SSI tuning points
The attached patch addresses one of the open non-blockers for beta3. These are tuning points which emerged in testing. The first is more likely to be helpful. The second may be very important in a few types of transaction mixes, but I threw in a lot of weasel words and qualifiers because someone could easily try this to bring down the transaction retry rate, but suffer a net loss in throughput because less efficient plans could be chosen. I hope I made that point in a reasonable fashion, although I'm certainly open to suggestions for better wording. -Kevin
Attachment
On Fri, Jun 17, 2011 at 5:50 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > The attached patch addresses one of the open non-blockers for beta3. > > These are tuning points which emerged in testing. The first is more > likely to be helpful. The second may be very important in a few > types of transaction mixes, but I threw in a lot of weasel words and > qualifiers because someone could easily try this to bring down the > transaction retry rate, but suffer a net loss in throughput because > less efficient plans could be chosen. I hope I made that point in a > reasonable fashion, although I'm certainly open to suggestions for > better wording. This is good advice, but I think it could use a bit more wordsmithing.How about something like this: When the system is forced to combine multiple page-level predicate locks into a single relation-level predicate lock because the predicate lock table is short of memory, an increase in the rate of serialization failures may occur. You can avoid this by increasing max_pred_locks_per_transaction. A sequential scan will always necessitate a table-level predicate lock. This can result in an increased rate of serialization failures.It may be helpful to encourage the use of index scansby reducing random_page_cost or increasing cpu_tuple_cost. Be sure to <etc.> -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas wrote: > Kevin Grittner wrote: >> I'm certainly open to suggestions for better wording. > How about something like this: > > When the system is forced to combine multiple page-level predicate > locks into a single relation-level predicate lock because the > predicate lock table is short of memory, an increase in the rate of > serialization failures may occur. You can avoid this by increasing > max_pred_locks_per_transaction. > > A sequential scan will always necessitate a table-level predicate > lock. This can result in an increased rate of serialization failures. > It may be helpful to encourage the use of index scans by reducing > random_page_cost or increasing cpu_tuple_cost. Be sure to That does seem better. Thanks. -Kevin
On Sun, Jun 19, 2011 at 11:10 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > That does seem better. Thanks. OK, committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company