Message-ID: <53D41831.1070306@cs.utoronto.ca>
Date: Sat, 26 Jul 2014 17:05:53 -0400
From: Ryan Johnson <ryan.johnson@cs.utoronto.ca>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:17.0) Gecko/20130801 Thunderbird/17.0.8
MIME-Version: 1.0
To: Reza Taheri <rtaheri@vmware.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: High rate of transaction failure with the Serializable Isolation
 Level
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>
 <53D2CDA3.8050707@cs.utoronto.ca>
 <20fd404f0e9c42f1b9847c35c1dc546c@EX13-MBX-013.vmware.com>
In-Reply-To: <20fd404f0e9c42f1b9847c35c1dc546c@EX13-MBX-013.vmware.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Dredging through some old run logs, 12 dbt-5 clients gave the following 
when everything was run under SSI (fully serializable, even the 
transactions that allow repeatable read isolation). Not sure how that 
translates to your results. Abort rates were admittedly rather high, 
though perhaps lower than what you report.

Transaction             % Average: 90th %   Total Rollbacks    % Warning Invalid
----------------- ------- --------------- ------- -------------- ------- -------
Trade Result        5.568   0.022:  0.056    2118    417  19.69%       0      91
Broker Volume       5.097   0.009:  0.014    1557      0   0.00%       0       0
Customer Position  13.530   0.016:  0.034    4134      1   0.02%       0       0
Market Feed         0.547   0.033:  0.065     212     45  21.23%       0      69
Market Watch       18.604   0.031:  0.061    5683      0   0.00%       0       0
Security Detail    14.462   0.015:  0.020    4418      0   0.00%       0       0
Trade Lookup        8.325   0.059:  0.146    2543      0   0.00%     432       0
Trade Order         9.110   0.006:  0.008    3227    444  13.76%       0       0
Trade Status       19.795   0.030:  0.046    6047      0   0.00%       0       0
Trade Update        1.990   0.064:  0.145     608      0   0.00%     432       0
Data Maintenance      N/A   0.012:  0.012       1      0   0.00%       0       0
----------------- ------- --------------- ------- -------------- ------- -------
28.35 trade-result transactions per second (trtps)

Regards,
Ryan

On 26/07/2014 3:55 PM, Reza Taheri wrote:
> Hi Ryan,
> That's a very good point. We are looking at dbt5. One question: what throughput rate, and how many threads of execution did you use for dbt5?  The failure rates I reported were at ~120 tps with 15 trade-result threads.
>
> Thanks,
> Reza
>
>> -----Original Message-----
>> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-
>> performance-owner@postgresql.org] On Behalf Of Ryan Johnson
>> Sent: Friday, July 25, 2014 2:36 PM
>> To: pgsql-performance@postgresql.org
>> Subject: Re: High rate of transaction failure with the Serializable Isolation
>> Level
>>
>> On 25/07/2014 2:58 PM, 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 over
>> my morning coffee.
>>> You didn't think I was going to bug you all with a trivial problem,
>>> did you? :-) :-)
>>>
>>> Yes, I am going to have to take an axe to the code and see what pops out.
>> 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 that,
>> 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 interdependency
>> between transactions" as you point out, it will be due to a mistake we made
>> 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.
>>> Just pointing out my uphill battle!!
>> You might compare against dbt-5 [1], just to see if the same problem occurs. I
>> didn't notice such high abort rates when I ran that workload a few weeks
>> ago. Just make sure to use the latest commit, because the "released" version
>> has fatal bugs.
>>
>> [1]
>> https://urldefense.proofpoint.com/v1/url?u=https://github.com/petergeog
>> hegan/dbt5&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CPjr
>> oD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=6E%2F9fWJPMGjpMyP
>> xtY0nsamLLW%2FNsTXu7FP9Wzauj10%3D%0A&s=b3f269216d419410f3f07bb
>> 774a27b7d377744c9d423df52a3e62324d9279958
>>
>> Ryan
>>
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> https://urldefense.proofpoint.com/v1/url?u=http://www.postgresql.org/m
>> ailpref/pgsql-
>> performance&k=oIvRg1%2BdGAgOoM1BIlLLqw%3D%3D%0A&r=b9TKmA0CP
>> jroD2HLPTHU27nI9PJr8wgKO2rU9QZyZZU%3D%0A&m=6E%2F9fWJPMGjpMy
>> PxtY0nsamLLW%2FNsTXu7FP9Wzauj10%3D%0A&s=45ab94ce068dbe28956af
>> 8bb3f999e9a91138dd1e3c3345c036e87e902da1ef1


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