Message-ID: <53D43EE7.4000500@cs.utoronto.ca>
Date: Sat, 26 Jul 2014 19:51:03 -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>
 <53D41831.1070306@cs.utoronto.ca>
 <a34f6676d65949f19c5efad75cbefe2f@EX13-MBX-013.vmware.com>
In-Reply-To: <a34f6676d65949f19c5efad75cbefe2f@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

That does sound pretty similar, modulo the raw performance difference. I 
have no idea how many MEE threads there were; it was just a quick run 
with exactly zero tuning, so I use whatever dbt5 does out of the box. 
Actually, though, if you have any general tuning tips for TPC-E I'd be 
interested to learn them (PM if that's off topic for this discussion).

Regards,
Ryan

On 26/07/2014 7:33 PM, Reza Taheri wrote:
> Hi Ryan,
> Thanks a lot for sharing this. When I run with 12 CE threads and 3-5 MEE threads (how many MEE threads do you have?) @  80-90 tps, I get something in the 20-30% of trade-result transactions rolled back depending on how I count. E.g., in a 5.5-minute run with 3 MEE threads, I saw 87.5 tps. There were 29200 successful trade-result transactions. Of these, 5800 were rolled back, some more than once for a total of 8450 rollbacks. So I'd say your results and ours tell similar stories!
>
> Thanks,
> Reza
>
>> -----Original Message-----
>> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-
>> performance-owner@postgresql.org] On Behalf Of Ryan Johnson
>> Sent: Saturday, July 26, 2014 2:06 PM
>> To: Reza Taheri
>> Cc: pgsql-performance@postgresql.org
>> Subject: Re: High rate of transaction failure with the Serializable Isolation
>> Level
>>
>> 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/peterge
>>>> og
>> 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:
>> 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=gzdXAra2QlJIiMTFSjH
>> cKAsSKNR5LST%2FrsLWdeb7Y9c%3D%0A&s=673454322b6239edd9d02472e95
>> e8a6c15cb1a095d2afb9c981642e44fb40672


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