Re: Reliable and fast money transaction design

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Gregory Stark wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>
>> Tom Lane wrote:
>>> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>>>> Tom Lane wrote:
>>>>> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>>>>>> SERIALIZABLE is really slow :).
>>>>> Say what? If anything it's probably faster than READ COMMITTED, because
>>>>> it doesn't take as many snapshots. But the difference is likely down in
>>>>> the noise anyway.
>>>> Not in production it isn't.
>>> Well, I can believe that specific applications might be slower overall
>>> due to having to retry transactions that get serialization failures,
>>> or perhaps because they take more locks to prevent such failures.
>>> But it's not slower as far as the database engine is concerned.
>> Well I can only speak to live production loads. I have never profiled
>> the difference from that low of a level. I can definitely say that in a
>> standard web app, under velocity, serializable is a huge performance killer.
>
> Are you having to retry after serialization failures frequently?
>
> There's no reason for an individual transaction to take longer in SERIALIZABLE
> mode. In fact I believe SERIALIZABLE mode is actually measurably faster in
> benchmarks but haven't run one in READ COMMITTED mode recently (for that
> reason).

Oddly enough, I am the exact opposite boat :). We found that READ
COMMITTED was faster a while back and haven't looked back except where
the logic requires. The only recent testing I have done is with our
PostgreSQL Analytics software. We are using Pyscopg2 which defaults to
serializable. We were having serious performance problems under high
concurrency selects. We moved to READ COMMITTED and it went away.

I will see if I can do some digging and get some actual numbers for us.

Joshua D. Drake

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG1ju7ATb/zqfZUUQRAlXlAJ0TWwfTpUQX++TDN0QPtYvhGGRyuwCghzRi
8mIlB2013+T4QMdjK2F3a9M=
=HGhc
-----END PGP SIGNATURE-----