Re: High rate of transaction failure with the Serializable Isolation Level
От | Reza Taheri |
---|---|
Тема | Re: High rate of transaction failure with the Serializable Isolation Level |
Дата | |
Msg-id | cada7898099045b7992cf1a3a08f1586@EX13-MBX-013.vmware.com обсуждение исходный текст |
Ответ на | Re: High rate of transaction failure with the Serializable Isolation Level (Kevin Grittner <kgrittn@ymail.com>) |
Список | pgsql-performance |
Hi Kevin, Thanks for the reply > As already pointed out by Craig, statements don't have serialization failures; transactions do. In some cases a transactionmay become > "doomed to fail" by the action of a concurrent transaction, but the actual failure cannot occur until the next statementis run on the > connection with the doomed transaction; it may have nothing to do with the statement itself. That's an interesting concept. I suppose I could test it by moving statements around to see what happens. > Note that I have seen reports of cases where max_pred_locks_per_transaction needed to be set to 20x the default to > reduce serialization failures to an acceptable level. I was running with the following two parameters set to 640; I then raised them to 6400, and saw no difference max_locks_per_transaction = 6400 max_pred_locks_per_transaction = 6400 Thanks, Reza > -----Original Message----- > From: Kevin Grittner [mailto:kgrittn@ymail.com] > Sent: Thursday, July 24, 2014 7:03 AM > To: Reza Taheri; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] High rate of transaction failure with the Serializable > Isolation Level > > Reza Taheri <rtaheri@vmware.com> wrote: > > > I am running into very high failure rates when I run with the > > Serializable Isolation Level. I have simplified our configuration to a > > single database with a constant workload, a TPC-E workload if you > > will, to focus on this this problem. We are running with PGSQL 9.2.4 > > I don't remember any bug fixes that would be directly related to what you > describe in the last 15 months, but it might be better to do any testing with > fixes for known bugs: > > https://urldefense.proofpoint.com/v1/url?u=http://www.postgresql.org/su > pport/versioning/&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKm > A0CPjroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2dsUC > 9oH2yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=6522bd258d0a034429522b > 61239134b07f1cabc086e8c2cb330aa9c9bc4a337d > > > When we raise the Trade-Result transaction to SQL_TXN_SERIALIZABLE, we > > face a storm of conflicts. Out of > > 37,342 Trade-Result transactions, 15,707 hit an error, and have to be > > rolled back and retired one or more times. The total failure count > > (due to many transactions failing more than once) is 31,388. > > > > What is unusual is that the majority of the failures occur in a > > statement that should not have any isolation conflicts. > > As already pointed out by Craig, statements don't have serialization failures; > transactions do. In some cases a transaction may become "doomed to fail" > by the action of a concurrent transaction, but the actual failure cannot occur > until the next statement is run on the connection with the doomed > transaction; it may have nothing to do with the statement itself. > > If you want to understand the theory of how SERIALIZABLE transactions are > implemented in PostgreSQL, these links may help: > > https://urldefense.proofpoint.com/v1/url?u=http://vldb.org/pvldb/vol5/p1 > 850_danrkports_vldb2012.pdf&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0 > A&r=b9TKmA0CPjroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C > 2sdUv2dsUC9oH2yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=d1b8cd62c431 > c267124c21d4e639c98eebb650caaf8fd05ba47aa825a9b54a52 > > https://urldefense.proofpoint.com/v1/url?u=http://git.postgresql.org/gitwe > b/?p%3Dpostgresql.git%3Ba%3Dblob_plain%3Bf%3Dsrc/backend/storage/lm > gr/README- > SSI%3Bhb%3Dmaster&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9T > KmA0CPjroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2d > sUC9oH2yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=1f60010253b8012dbe5 > e5a51af48fcb831dae81200708f620438e6afb48c0eef > > https://urldefense.proofpoint.com/v1/url?u=http://wiki.postgresql.org/wiki > /Serializable&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CPj > roD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2dsUC9oH2 > yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=040078780771088975f2abe3668 > 5b182ca626557ed2cd1c7241c78b9f417d325 > > For a more practical set of examples about the differences in using > REPEATABLE READ and SERIALIZABLE transaction isolation levels in > PostgreSQL, see: > > https://urldefense.proofpoint.com/v1/url?u=http://wiki.postgresql.org/wiki > /SSI&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CPjroD2HLP > THU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2dsUC9oH2yzDssdTb > CEBF5mbQZbZ871laGw%3D%0A&s=3c2629d0256b802ed7b701be6bff7443480 > 5f94beb1c400c772ace91c7204bc5 > > If you are just interested in reducing the number of serialization failures, see > the suggestions near the end of this section of the > documentation: > > https://urldefense.proofpoint.com/v1/url?u=http://www.postgresql.org/do > cs/9.2/interactive/transaction-iso.html%23XACT- > SERIALIZABLE&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CP > jroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2dsUC9oH2 > yzDssdTbCEBF5mbQZbZ871laGw%3D%0A&s=ae5349ae3cafcd86c6ba6be9404 > 990ae800d93d6ccfe892402c2d8d463bd8574 > > Any of these items (or perhaps a combination of them) may ameliorate the > problem. Note that I have seen reports of cases where > max_pred_locks_per_transaction needed to be set to 20x the default to > reduce serialization failures to an acceptable level. > The default is intentionally set very low because so many people do not use > this isolation level, and this setting reserves shared memory for purposes of > tracking serializable transactions; the space is wasted for those who don't > choose to use them. > > There is still a lot of work possible to reduce the rate of false positives, which > has largely gone undone so far due to a general lack of problem reports from > people which could not be solved through tuning. If you have such a case, it > would be interesting to have all relevant details, so that we can target which > of the many enhancements are relevant to your case. > > -- > Kevin Grittner > EDB: > https://urldefense.proofpoint.com/v1/url?u=http://www.enterprisedb.com > /&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CPjroD2HLPTH > U27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=H8C2sdUv2dsUC9oH2yzDssdTbCE > BF5mbQZbZ871laGw%3D%0A&s=ca419a3d34bca730a6a153fe027150e4975396 > 4be76b78a2b81dc84378d091e6 > The Enterprise PostgreSQL Company
В списке pgsql-performance по дате отправления:
Предыдущее
От: Reza TaheriДата:
Сообщение: Re: High rate of transaction failure with the Serializable Isolation Level
Следующее
От: Craig RingerДата:
Сообщение: Re: High rate of transaction failure with the Serializable Isolation Level