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
Attachments:
ssi-tuning-1.patchtext/plain; name=ssi-tuning-1.patchDownload
*** a/doc/src/sgml/mvcc.sgml
--- b/doc/src/sgml/mvcc.sgml
***************
*** 658,663 **** ERROR: could not serialize access due to read/write dependencies among transact
--- 658,680 ----
protections automatically provided by Serializable transactions.
</para>
</listitem>
+ <listitem>
+ <para>
+ If you are seeing a lot of serialization failures because multiple
+ page locks are being combined into relation locks, you might want to
+ increase <xref linkend="guc-max-pred-locks-per-transaction">.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ If you are experiencing a lot of serialization failures due to
+ table-scan plans being used, you might want to try reducing
+ <xref linkend="guc-random-page-cost"> and/or increasing
+ <xref linkend="guc-cpu-tuple-cost">. Be sure to weigh any decrease
+ in transaction rollbacks and restarts against any overall change in
+ query execution time.
+ </para>
+ </listitem>
</itemizedlist>
</para>
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 scans by 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
Import Notes
Resolved by subject fallback
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