DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=p/MxDtGfQFFWtadywztqVy/EHPoKwv/gqmgvX+Ke3fttIja+8yTYbwDTxbFvp/N4Bu
	pC95X8jhlpoHdkoRaBdVeOmBVxHmQbymQogdXldiYr+H9ajBVQ+XNZ5BJd5oH5PbdZyf
	DQVyUu/MXAQnqWms3IF08aofTr2tr+IZ91P0s=
MIME-Version: 1.0
In-Reply-To: <82545852-CE9E-4B0D-ABAB-B628C888D086@metaweb.com>
References: <22AC581D-50D1-4863-9FDE-35EDDB171269@metaweb.com>
	<f67928030911040847j434be6b0w59cf4a6218829ec3@mail.gmail.com>
	<82545852-CE9E-4B0D-ABAB-B628C888D086@metaweb.com>
Date: Wed, 4 Nov 2009 20:27:27 -0800
Message-ID: <f67928030911042027h4b613fe5nd2c12809f19d615c@mail.gmail.com>
Subject: Re: maintaining a reference to a fetched row
From: Jeff Janes <jeff.janes@gmail.com>
To: Brian Karlak <zenkat@metaweb.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Wed, Nov 4, 2009 at 9:41 AM, Brian Karlak <zenkat@metaweb.com> wrote:
>
> I do a select for update in a stored proc:
>
> FOR queue_item IN
>
> =A0=A0SELECT * =A0FROM queue
> =A0=A0 WHERE status IS NULL=A0AND id >=3D low_bound_id
> =A0=A0 ORDER BY id LIMIT batch_size
> =A0=A0 =A0 FOR UPDATE
>
> LOOP
>
> =A0=A0UPDATE queue_proc set status =3D 'proc' where id =3D queue_item.id =
;
>
> The daemons keep track of their last position in the queue with
> low_bound_id. =A0Also, as you probably notice, I also fetch a batch of (1=
00)
> items at a time. =A0In practice, it's pretty fast. =A0The job I'm running=
 now is
> showing an average fetch time of 30ms per 100 actions, which ain't bad.
>
> However, the writing of results back to the row takes ~5ms, which is slow=
er
> than I'd like.

5 ms per each of the 100 actions?  With one commit per action?

> > It seems you have an select, and update, and another update. =A0Where i=
n
> > this process do you commit? =A0Are you using fsync=3Doff or
> > synchronous_commit=3Doff?
>
> First commit occurs after the stored proc to select/update a batch of ite=
ms
> is complete.

So one commit per 100 items?

>=A0Second commit occurs on the writing of results back for each
> particular action.

So one commit per 1 item?
If so, this completely explains the difference in speed, I think.

> Two commits are required because the time it takes to
> complete the intervening action can vary wildly: anywhere between 20ms an=
d
> 45min.

Is there any way of knowing/approximating ahead of time how long it will ta=
ke?

The 45 min monsters must be exceedingly rare, or else the average
could not be ~50ms.

>> Why would the index scan take 1 ms two of the times it is done but 5ms
>> the third time? =A0Isn't it the same index scan each time? =A0Or does th=
e
>> change in queue.status change the plan?
>
> The final update is a different query -- just a plain old update by ID:
>
> UPDATE queue_proc set status =3D 'proc' where id =3D %s ;

That looks very much like the other UPDATE you showed.  The difference
it seems is that you commit after every one, rather than after every
100.  Right?

> This update by ID takes ~2.5ms, which means it's where the framework is
> spending most of its overhead.

You said the computation task can take anywhere from 20ms to 45min, so
it seems that this update overhead is at most 1/8 of the irreducible
time.  That doesn't seem like it is enough to worry about, to me.

Jeff