Hello David,
On Wed, 02 Feb 2011 01:36:15 +0100, David Johnston <polobo@xxxxxxxxx>
wrote:
If random sampling is desirable would the following construct limit
locking
only to the sampled rows?
SELECT id
FROM tasktable
WHERE id IN (SELECT random_id_sample())
FOR UPDATE
The "random_id_sample" would supply a configurable group of IDs off of
tasktable which the FOR UPDATE would then lock
I guess the issue remains that "random_id_sample()" would still end up
blocking if any of the rows it wants to return are already locked.
My immediate guess is that this would work, and I might explore it once I
get my new fullscale test-db up and running
I too am using this basic protocol of maintaining state info within the
database and sending every query against it. As I ponder this more it
really seems as if moving some of this logic into the application layer
would possibly make more sense in Yngve's situation (or at least
something
to consider). Continue to use the database as a persistence mechanism
but
code the "dispatching" of tasks in the application layer and then as each
task is dispatched you simply do an "UPDATE table SET state = 'dispatch'
WHERE id = 'ID'" and a similar UPDATE when the task is returned
completed.
This somewhat presumes you still only ever hand off one task at a time.
If
you are indeed handing off tasks in batches then it would make sense to
have
a "batch" table and operate at the batch level instead of individual
tasks -
assigning tasks to a given batch via some standard mechanism.
If I read you correctly that is what my system does (dispatch = started,
marked by the node that is to do the task).
The reason I am allocating tasks in batches is that there are so many
processes involved that if they pick one at a time they would block each
other. With the block allocation they only need to fetch the tasks once,
meaning that there are not as many requests to the queue at a time, on
average.
Either way if you truly want true parallel processing then you need to
create the parallel paths that can operate without clobbering each other
and
thus each path needs to have its own pool of tasks since as soon as you
have
That is what the offset part of the query was supposed to achieve.
At the moment I have worked around the problem by breaking the task list
into 2000 subgroups, and each process picks one at random. That limits the
number of processes that get in each others way, and the measured speed is
now 4-5 times what I saw on Monday, and back in the old range of
performance. However, it is a hack I had hoped to avoid (and I might get
rid of it with the above suggestion)
a shared resource the only true way to make sure it is only allocated
once
is to serialize access to it. An alternative method would be to allow
multiple dispatches but have a "write-once" method that is called and
sets
an immutable handler_id and then when the processing begins only the
handler
with the matching id would be able allow to perform the actual
processing.
This requires the handlers to have a unique ID, which my system has not
needed so far.
I say the above with certainty but at the moment I am using and fairly
happy
with my limited serialization - especially since I have specific
sub-properties that I can use to limit how many records are locked AND
also
because the locking time is very short (I cap around 20 or so active
tasks
to dispatch - and only infrequently at that) so my experience and
insight to
high-demand situations is limited.
Dave
-----Original Message-----
From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx]
Sent: Tuesday, February 01, 2011 12:18 PM
To: Yngve Nysaeter Pettersen
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Select for update with offset interferes with concurrent
transactions
"Yngve Nysaeter Pettersen" <yngve@xxxxxxxxx> writes:
To avoid having the processes trample each other's queries (the first
attempt was to select the first matching entries of the table, which
caused one to block all other transactions), one of the steps I took
was to select a set of idle rows at a random offset into the table
from the project, mark them for update, then update each record's state
as
started.
SELECT record_id FROM queue WHERE project_id = my_project AND state
= idle LIMIT n OFFSET i FOR UPDATE
At present "n" is 100-150, "i" is a random value in the range 0-10000.
There is, intentionally, no ordering specified, since that would just
slow down the query, and is not necessary.
This seems like a pretty bad design. There are recognized ways to solve
this problem with more predictability and much less chance of different
processes blocking each other. In particular, this query seems be based
on
some untenable assumptions about the physical row order being stable.
What I've discovered when using Postgres 9.0 is that the processes are
now blocking every other query into this table,
In 9.0, LIMIT/OFFSET processing is done after FOR UPDATE locking, which
means that rows skipped over by OFFSET still get locked, which means that
different sessions executing this query are now practically certain to
block
each other, rather than just likely to block each other.
This was an intentional change to improve the predictability of FOR
UPDATE's
interactions with LIMIT/OFFSET, and indeed it's improved the
predictability
of the behavior for you, just not in the direction you'd like :-(
regards, tom lane
--
Sincerely,
Yngve N. Pettersen
********************************************************************
Senior Developer Email: yngve@xxxxxxxxx
Opera Software ASA http://www.opera.com/
Phone: +47 23 69 32 60 Fax: +47 23 69 24 01
********************************************************************
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
