pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: david@lang.hm
To: wyx6fox@sina.com
Cc: pgsql-performance@postgresql.org
Subject: Re: the jokes for pg concurrency write performance
Date: Mon, 1 Feb 2010 21:08:44 -0800 (PST)
Message-ID: <alpine.DEB.2.00.1002012101530.7748@asgard.lang.hm> (raw)
In-Reply-To: <20100202045701.93A404B1C73@mail175-236.sinamail.sina.com.cn>
References: <20100202045701.93A404B1C73@mail175-236.sinamail.sina.com.cn>

On Tue, 2 Feb 2010, wyx6fox@sina.com wrote:

> hi, first, thanks u for make so good opensource db .

first you thank the developers, then you insult them. are you asking for 
help or just trying to cause problems.

> recently maybe half an years ago ,i begin to use pg in a big project for insurance project, belong as the project go on ,and
> i found some performance problem on concurrency write situation , then i do a research on concurrency write strategy on postgresql ,
>
> i found a joke ,maybe this joke concurrency strategy is the designer's pround idea, but i think it is a joke , next let me describe the problems:
>
> * joke 1: insert operation would use a excluse lock on reference row by the foreign key . a big big big performance killer , i think this is a stupid design .

this I don't know enough to answer

> * joke 2: concurrency update on same row would lead to that other transaction must wait the earlier transaction complete , this would kill the concurrency performance in some long time transaction situation . a stupid design to ,
>
> this joke design's reason is avoid confliction on read committed isolation , such as this situation:
> UPDATE webpages SET hits = hits + 1 WHERE url ='some url ';
> when concurrency write transaction on read committed isolation , the hits may result wrong .
>
> this joke design would do seriable write , but i think any stupid developer would not write this code like this stupid sample code , a good code is
> use a exclusive lock to do a seriable write on this same row , but the joker think he should help us to do this , i say ,u should no kill concurrency performance and help i do this fucking stupid sample code , i would use a select .. for update to do this :
>
> select 1 from lock_table where lockId='lock1' for update ;
> UPDATE webpages SET hits = hits + 1 WHERE url ='some url ';
>

If you have one transaction start modifying a row, and then have another 
one start, how do you not have one wait for the other? Remember that any 
transaction can end up running for a long time and may revert at any time.

Why would you want to lock the entire table for an update as simple as you 
describe?

> * joke 3: update 100000 rows on a table no any index , it cost 5-8 seconds , this is not acceptable in some bulk update situation .

This one is easy, you did 100000 inserts as seperate transactions, if you 
do them all as one transaction (or better still as a copy) they would 
complete much faster.

You seem to be assuming incopatence on the part of the developers whenever 
you run into a problem. If you want them to help you, I would suggest that 
you assume that they know what they are doing (after all, if they didn't 
you wouldn't want to use their code for anything important anyway), and 
instead ask what the right way is to do what you are trying to do.

David Lang



view thread (8+ messages)  latest in thread

Message-ID: <alpine.DEB.2.00.1002012101530.7748@asgard.lang.hm>
Permalink:  ../alpine.DEB.2.00.1002012101530.7748@asgard.lang.hm/
Also on:    postgresql.org/message-id/alpine.DEB.2.00.1002012101530.7748@asgard.lang.hm

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: david@lang.hm, wyx6fox@sina.com
  Subject: Re: the jokes for pg concurrency write performance
  In-Reply-To: <alpine.DEB.2.00.1002012101530.7748@asgard.lang.hm>
  References: <20100202045701.93A404B1C73@mail175-236.sinamail.sina.com.cn> <alpine.DEB.2.00.1002012101530.7748@asgard.lang.hm>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox