From: Reza Taheri <rtaheri@vmware.com>
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: High rate of transaction failure with the
 Serializable Isolation Level
Thread-Topic: [PERFORM] High rate of transaction failure with the
 Serializable Isolation Level
Thread-Index: 
 Ac+m3REUfhN8/sxlQTylzpfYlwqkMQAWWGKAAA8AZQAAHAtQgAAVfZpgAIaQQwAEqSW5IA==
Date: Thu, 21 Aug 2014 04:51:42 +0000
Message-ID: <003e3c7e20364e45b4723caad7e52f70@EX13-MBX-013.vmware.com>
References: <ded604822b28427a80b0751a973e4258@EX13-MBX-013.vmware.com>
 <53D0923B.7050501@2ndquadrant.com>
 <93e2642196e646adbcf5780d3cf54685@EX13-MBX-013.vmware.com>
 <53D1B31B.8070909@2ndquadrant.com>
 <80d78b6fec064f14aa025c1ca86c8099@EX13-MBX-013.vmware.com>
 <53D5CA5E.50002@2ndquadrant.com>
In-Reply-To: <53D5CA5E.50002@2ndquadrant.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

An update: following the recommendations on this list, I ran a number of ex=
periments:

- I ran with all foreign keys deleted. There was a 4% drop in the rate of d=
eadlocks/transaction, which went from 0.32 per transaction to 0.31. So we s=
till have pretty much the same failure rate. One interesting side effect wa=
s that throughput went up by 9%. So maintaining (a lot of) foreign constrai=
nts costs us a lot

- I ran with max_pred_locks_per_tran as high as 26,400. No difference

- I rebuilt the database with fillfactor=3D15 for all the tables and indexe=
s that are involved in the transactions that fail. This was to see if the p=
roblem is PGSQL upgrading row level locks to page level locks. With a low f=
illfactor, the chances of two transactions landing on the same page is low.=
 We have around 9 rows per data page instead of the original ~60. (The inde=
x pages are more tightly packed).   I ran a range of thread counts from 5 t=
o 60 for the threads that issue transactions.  The failure rate per transac=
tion dropped to around half for the thread count of 5, but that's misleadin=
g since with a fillfactor of15, our database size went up by around 6X, red=
ucing the effectiveness of PGSQL and OS disk caches, resulting in a through=
put of around half of what we used to see. So the reduced failure rate is j=
ust a result of fewer threads competing for resources. When I run with enou=
gh threads to max out the system with fillfactor=3D15 or 100, I get the sam=
e failure rates

- In case folks hadn't noticed, Ryan Johnson is getting very similar failur=
e rates with dbt-5. So this isn't a case of our having made a silly mistake=
 in our coding of the app or the stored procedures

Above experiments were the easy one. I am now working on rewriting the app =
code and the 6 stored procedures to see if I can execute the whole transact=
ion in a single stored procedure

Thanks,
Reza

> -----Original Message-----
> From: Craig Ringer [mailto:craig@2ndquadrant.com]
> Sent: Sunday, July 27, 2014 8:58 PM
> To: Reza Taheri; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] High rate of transaction failure with the Serializ=
able
> Isolation Level
>=20
> On 07/26/2014 02:58 AM, Reza Taheri wrote:
> > Hi Craig,
> >
> >> According to the attached SQL, each frame is a separate phase in the
> operation and performs many different operations.
> >> There's a *lot* going on here, so identifying possible
> >> interdependencies isn't something I can do in a ten minute skim read o=
ver
> my morning coffee.
> >
> > You didn't think I was going to bug you all with a trivial problem,
> > did you? :-) :-)
>=20
> One can hope, but usually in vain...
>=20
> > Yes, I am going to have to take an axe to the code and see what pops ou=
t.
> Just to put this in perspective, the transaction flow and its statements =
are
> borrowed verbatim from the TPC-E benchmark. There have been dozens of
> TPC-E disclosures with MS SQL Server, and there are Oracle and DB2 kits t=
hat,
> although not used in public disclosures for various non-technical reasons=
, are
> used internally in by the DB and server companies. These 3 products, and
> perhaps more, were used extensively in the prototyping phase of TPC-E.
> >
> > So, my hope is that if there is a "previously unidentified interdepende=
ncy
> between transactions" as you point out, it will be due to a mistake we ma=
de
> in coding this for PGSQL. Otherwise, we will have a hard time convincing =
all
> the council member companies that we need to change the schema or the
> business logic to make the kit work with PGSQL.
>=20
> Hopefully so.
>=20
> Personally I think it's moderately likely that PostgreSQL's much stricter
> enforcement of serializable isolation is detecting anomalies that other
> products do not, so it's potentially preventing errors.
>=20
> It would be nice to have the ability to tune this; sometimes there are
> anomalies you wish to ignore or permit. At present it is an all or nothin=
g affair
> - no predicate locking (REPEATABLE READ isolation) or strict predicate lo=
cking
> (SERIALIZABLE isolation).
>=20
> I recommend running some of the examples in the SERIALIZABLE
> documentation on other products. If they don't fail where they do in Pg,
> then the other products either have less strict (and arguably therefor le=
ss
> correct) serialisable isolation enforcement or they rely on blocking pred=
icate
> locks. In the latter case it should be easy to tell because statements wi=
ll block
> on locks where no ordinary row or table level lock could be taken.
>=20
> If you do run comparative tests I would be quite interested in seeing the
> results.
>=20
> --
>  Craig Ringer
> https://urldefense.proofpoint.com/v1/url?u=3Dhttp://www.2ndquadrant.com
> /&k=3DoIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=3Db9TKmA0CPjroD2HLPTH
> U27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=3DjyJSmq%2BgXoPIgY%2BNtgswlUg
> zHSm45s%2FmevjxBmPKrIs%3D%0A&s=3D401415fb62d6f76b22bc76469cbefb85
> 8342612f1fea2d359fe2bb3f18cab1ab
>  PostgreSQL Development, 24x7 Support, Training & Services


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance