Priorities for users or queries?

Benjamin Arai wrote:

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.\

Nope :)

Joshua D. Drake

Thanks in advance!

Benjamin

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Just to clarify, there is no way to throttle specific queries or users
in PostgreSQL?

Benjamin

Joshua D. Drake 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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Benjamin Arai
Sent: Saturday, February 10, 2007 6:56 PM
To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
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/

Benjamin Arai wrote:

Just to clarify, there is no way to throttle specific queries or users
in PostgreSQL?

That is correct.

Sincerely,

Joshua D. Drake

Benjamin

Joshua D. Drake wrote:

Benjamin Arai wrote:

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.\

Nope :)

Joshua D. Drake

Thanks in advance!

Benjamin

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On 2/11/07, Benjamin Arai <benjamin@araisoft.com> wrote:

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!

with all due respect to Josh who has replied to this question already.

i have been thinking about this and would like to make sure i am on
the right track.

yes, there is no way to give priorities to queries or users in the db.
however, please correct me if i'm wrong, if i want something to run
with lower priority, i can use different connection parameters
which would use lower system resources. thus, this connection
would run "slower" that the other "normal" connections to the db.

would that be right?

Benjamin

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--
edwin eyan moragas
aspiring programmer
garnet:jasmin:beryllium:gluon::90-12264
http://www.eyan.org/

Hi Edwin,

Which connection parameters effect system resources?

Benjamin

Edwin Eyan Moragas wrote:

Edwin Eyan Moragas <haaktu@gmail.com> schrieb:

On 2/11/07, Benjamin Arai <benjamin@araisoft.com> wrote:

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!

with all due respect to Josh who has replied to this question already.

Just an idea:

You can retrieve the PID and the username of running querys in
pg_stat_activity. Perhaps, with this knowledge and an untrusted language
you can run system-commands such renice to change the priority of
running postmaster-processes.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

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:

Andreas Kretschmer <akretschmer@spamfence.net> writes:

You can retrieve the PID and the username of running querys in
pg_stat_activity. Perhaps, with this knowledge and an untrusted language
you can run system-commands such renice to change the priority of
running postmaster-processes.

See "priority inversion" in the archives.

regards, tom lane

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?

Benjamin

Magnus Hagander wrote:

am Sun, dem 11.02.2007, um 12:37:57 -0500 mailte Tom Lane folgendes:

Andreas Kretschmer <akretschmer@spamfence.net> writes:

You can retrieve the PID and the username of running querys in
pg_stat_activity. Perhaps, with this knowledge and an untrusted language
you can run system-commands such renice to change the priority of
running postmaster-processes.

See "priority inversion" in the archives.

Thanks for the hint, i have seen 'Magnus Hagander's mail ;-)

Btw.: thanks you and others for your excellent work to improve
PostgreSQL. I'm only a customer, but i want to help others.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On 2/11/07, Benjamin Arai <benjamin@araisoft.com> wrote:

Hi Edwin,

Which connection parameters effect system resources?

i remembered wrong. the connection parameters i was thinking of is here:
http://www.postgresql.org/docs/8.2/static/libpq-envars.html

however, looking more closely to the config file, it can be set here:
http://www.postgresql.org/docs/8.2/static/runtime-config-resource.html

but *i am not sure* if it can be set on a per user basis. internet too
slow just now.

Benjamin

Edwin Eyan Moragas wrote:

On 2/11/07, Benjamin Arai <benjamin@araisoft.com> wrote:

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!

with all due respect to Josh who has replied to this question already.

i have been thinking about this and would like to make sure i am on
the right track.

yes, there is no way to give priorities to queries or users in the db.
however, please correct me if i'm wrong, if i want something to run
with lower priority, i can use different connection parameters
which would use lower system resources. thus, this connection
would run "slower" that the other "normal" connections to the db.

would that be right?

Benjamin

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--
edwin eyan moragas
aspiring programmer
garnet:jasmin:beryllium:gluon::90-12264
http://www.eyan.org/

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Benjamin Arai
Sent: Saturday, February 10, 2007 6:56 PM
To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
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

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

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?

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Benjamin Arai
Sent: Saturday, February 10, 2007 6:56 PM
To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
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

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Fair enough, thanks for the clarification.

Benjamin

Jan Wieck wrote:

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Benjamin
Arai
Sent: Saturday, February 10, 2007 6:56 PM
To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
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/

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

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

Papers I've read suggest that the benefits of priorities
vastly outweigh the penalties of priority inversion for
virtually all workloads on most all RDBMs's including
PostgreSQL.

This CMU paper in particular tested PostgreSQL (and DB2)
on TPC-C and TPC-W workloads and found that indirectly
influencing I/O scheduling through CPU priorities
is a big win for postgresql.

http://www.cs.cmu.edu/~bianca/icde04.pdf

"For TPC-C running on PostgreSQL,
the simplest CPU scheduling policy (CPU-Prio) provides
a factor of 2 improvement for high-priority transactions,
while adding priority inheritance (CPU-Prio-Inherit)
provides a factor of 6 improvement while hardly
penalizing low-priority transactions."

Have you heard of any workload on any RDBMS where priority inversion
causes more harm than benefit?

Ron Mayer