Hi Jan,
That is true but it really only solves the case for readers/writers.
In the long run I feel there should be some mechanism to determine the
priority of a query either on a user or query basis. This would lend
PostgreSQL to a whole new industry that is currently only filled with
commercial solutions (i.e. Oracle, DB2).
Would this be difficult to implement? Maybe a summer of code person
could do it.
Benjamin Arai
Jan Wieck wrote:
On 2/16/2007 5:05 PM, Benjamin Arai wrote:
Fair enough, thanks for the clarification.
What you can do to throttle things in a reasonable manner would
require that your application knows which transaction requires
updating when it begins it. If that is the case, you can setup
multiple connection pools with pgpool, one for reading having many
physical connections, each shared for just a few clients, another
having few physical connections shared by all writers. That way you
will have a limited number of writers active at the same time.
Jan
Benjamin
Jan Wieck wrote:
On 2/16/2007 4:56 PM, Benjamin Arai wrote:
Hi Jan,
That makes sense. Does that mean that a low-priority "road-block"
can cause a deadlock or just an very long one lock?
It doesn't cause any deadlock by itself. Although the longer one
holds one lock, before attempting to acquire another, the higher the
risk someone else grabs that and tries visa versa. So if there is a
risk of deadlocks due to the access pattern of your application,
then slowing down the updating processes will increase the risk of
it to happen.
Jan
Benjamin
Jan Wieck wrote:
On 2/11/2007 1:02 PM, Benjamin Arai wrote:
Hi Magnus,
Think this can be avoided as long the the queries executed on the
lower priority process never lock anything important. In my
case, I would alway be doing inserts with the lower priority
process, so inversion should never occur. On the other hand if
some lock occur somewhere else specific to Postgres then there
may be an issue. Are there any other tables locked by the the
Postgres process other than those locks explicitly set by the query?
If you assume that the logical row level locks, placed by such low
priority "road-block", would be the important thing to watch out
for, you are quite wrong. Although Postgres appears to avoid
blocking readers by concurrent updates using MVCC, this isn't
entirely true. The moment one updating backend needs to scribble
around in any heap or index block, it needs an exclusive lock on
that block until it is done with that. It will not hold that block
level lock until the end of its transaction, but it needs to hold
it until the block is in a consistent state again. That means that
the lower the priority of those updating processes, the more
exclusively locked shared buffers you will have in the system,
with the locking processes currently not getting the CPU because
of their low priority.
Jan
Benjamin
Magnus Hagander wrote:
Most likely, you do not want to do this. You *can* do it, but
you are
quite likely to suffer from priority inversion
(http://en.wikipedia.org/wiki/Priority_inversion)
//Magnus
Adam Rich wrote:
There is a function pg_backend_pid() that will return the PID for
the current session. You could call this from your updating app
to get a pid to feed to the NICE command.
-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of
Benjamin Arai
Sent: Saturday, February 10, 2007 6:56 PM
To: pgsql-general@xxxxxxxxxxxxxx; pgsql-admin@xxxxxxxxxxxxxx
Subject: [GENERAL] Priorities for users or queries?
Hi,
Is there a way to give priorities to queries or users?
Something similar to NICE in Linux. My goal is to give the
updating (backend) application a very low priority and give the
web application a high priority to avoid disturbing the user
experience.
Thanks in advance!
Benjamin
---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
