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

Hard to argue with that.

---------------------------------------------------------------------------

Ron Mayer wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Hard to argue with that.

Is it a strong enough argument to add a TODO?

I'm thinking some sort of TODO might be called for.

Perhaps two TODOs?
* Use the OS's priority features to prioritize
backends (and document that it might work
better with OS's that support priority inheritance).
* Investigate if postgresql could develop an
additional priority mechanism instead of using
the OS's.

Ron Mayer wrote:

Magnus Hagander wrote: ...

quite likely to suffer from priority inversion

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

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

Setting priorities seems a rather common request,
supposedly coming up every couple months [5]Tom suggests that priorities are a often requested feature. http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php.

The paper referenced [1]Linux with Priority inheritance showing benefits for PostgreSQL http://www.cs.cmu.edu/~bianca/icde04.pdf suggests that even with
naive schedulers, use of CPU priorities is very
effective for CPU and I/O intensive PostgreSQL
workloads.

If someone eventually finds a workload that does suffer
worse performance due to priority inversion,
(a) they could switch to an OS and scheduler
that supports priority inheritance;
(b) it'd be an interesting case for a paper
rebutting the CMU one; and
(c) they don't have to use priorities.

If a user does find he wants priority inheritance it
seems Linux[1]Linux with Priority inheritance showing benefits for PostgreSQL http://www.cs.cmu.edu/~bianca/icde04.pdf, BSD[2]BSD priority inheritance work mentioned: http://www.freebsd.org/news/status/report-july-2004-dec-2004.html, some flavors of Windows[3]Windows priority inheritance stuff: http://msdn2.microsoft.com/en-us/library/aa915356.aspx,
and Solaris[4]Solaris priority inheritance stuff http://safari5.bvdep.com/0131482092/ch17lev1sec7 http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/ all seem to be options; even though
I've only seen PostgreSQL specifically tested for
priority inversion problems with Linux (which did
not find problems but found additional benefit of
using priority inheritance).

[1]: Linux with Priority inheritance showing benefits for PostgreSQL http://www.cs.cmu.edu/~bianca/icde04.pdf
PostgreSQL
http://www.cs.cmu.edu/~bianca/icde04.pdf
[2]: BSD priority inheritance work mentioned: http://www.freebsd.org/news/status/report-july-2004-dec-2004.html
http://www.freebsd.org/news/status/report-july-2004-dec-2004.html
[3]: Windows priority inheritance stuff: http://msdn2.microsoft.com/en-us/library/aa915356.aspx
http://msdn2.microsoft.com/en-us/library/aa915356.aspx
[4]: Solaris priority inheritance stuff http://safari5.bvdep.com/0131482092/ch17lev1sec7 http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/
http://safari5.bvdep.com/0131482092/ch17lev1sec7
http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/
[5]: Tom suggests that priorities are a often requested feature. http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php
http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php

Benjamin Arai wrote:

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!

You might want to look at the following, as they mention a PostgreSQL
prototype:

http://www.cs.cmu.edu/~natassa/aapubs/conference/priority mechanisms.pdf

Regards,

--
Jose Orlando Pereira

The problem with using simple OS priority settings is you leave
yourself wide open to priority inversion.

There is already work being done on a queuing system; take a look at
the bizgres archives.

On Feb 20, 2007, at 5:19 PM, Ron Mayer wrote:

Bruce Momjian wrote:

Hard to argue with that.

Is it a strong enough argument to add a TODO?

I'm thinking some sort of TODO might be called for.

Perhaps two TODOs?
* Use the OS's priority features to prioritize
backends (and document that it might work
better with OS's that support priority inheritance).
* Investigate if postgresql could develop an
additional priority mechanism instead of using
the OS's.

Ron Mayer wrote:

Magnus Hagander wrote: ...

quite likely to suffer from priority inversion

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

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

Setting priorities seems a rather common request,
supposedly coming up every couple months [5].

The paper referenced [1] suggests that even with
naive schedulers, use of CPU priorities is very
effective for CPU and I/O intensive PostgreSQL
workloads.

If someone eventually finds a workload that does suffer
worse performance due to priority inversion,
(a) they could switch to an OS and scheduler
that supports priority inheritance;
(b) it'd be an interesting case for a paper
rebutting the CMU one; and
(c) they don't have to use priorities.

If a user does find he wants priority inheritance it
seems Linux[1], BSD[2], some flavors of Windows[3],
and Solaris[4] all seem to be options; even though
I've only seen PostgreSQL specifically tested for
priority inversion problems with Linux (which did
not find problems but found additional benefit of
using priority inheritance).

[1] Linux with Priority inheritance showing benefits for
PostgreSQL
http://www.cs.cmu.edu/~bianca/icde04.pdf
[2] BSD priority inheritance work mentioned:
http://www.freebsd.org/news/status/report-july-2004-dec-2004.html
[3] Windows priority inheritance stuff:
http://msdn2.microsoft.com/en-us/library/aa915356.aspx
[4] Solaris priority inheritance stuff
http://safari5.bvdep.com/0131482092/ch17lev1sec7
http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/
[5] Tom suggests that priorities are a often requested feature.
http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php

---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Wed, Feb 21, 2007 at 04:14:35PM +0000, Jos?? Orlando Pereira wrote:

Benjamin Arai wrote:

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!

You might want to look at the following, as they mention a PostgreSQL
prototype:

http://www.cs.cmu.edu/~natassa/aapubs/conference/priority mechanisms.pdf

That URL should be...
http://www.cs.cmu.edu/~natassa/aapubs/conference/priority%20mechanisms.pdf

There has been extensive discussion on the bizgres list about ways to
implement priorities, and I believe that paper was mentioned. You should
look at the archives.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Jim Nasby wrote:

The problem with using simple OS priority settings is you leave yourself
wide open to priority inversion.

Which is why you either
(a) note that papers studying priority inversion on RDBMS's
find that it's a non issue on many RDBMS workloads; and
(except for real-time databases) you tend to still get
at least partial benefits even in the face of priority
inversions.
or
(b) use a scheduler in your OS that supports priority
inheritance or other mechanisms to avoid priority
inversion problems.
If you want to use priority inheritance to avoid
the priority inversion settings it appears versions
of Linux, BSD, Windows, and Solaris at least give
you the ability to do so.

There is already work being done on a queuing system; take a look at the
bizgres archives.

Which is cool; but not quite the same as priorities.

It seems to me that Bizgres and/or PostgreSQL would not
want to re-implement OS features like schedulers.

My problem with [1] is that even for 10 users the percentage of time
spent in locks is very high. Can priorities scale?

Benjamin

Ron Mayer wrote:

My problem with [1] is that even for 10 users the percentage of time
spent in locks is very high. Can priorities scale?

Benjamin

Ron Mayer wrote:

Benjamin Arai wrote:

My problem with [1] is that even for 10 users the percentage of time
spent in locks is very high.

Really? In the paper referenced in the thread you quoted,
figure 1H shows TCP-C with PostgreSQL and shows that time
spent in locks with 10 users is extremely small (about 10%
of time in locks with 5 warehouses and near 0% at 30
warehouses).

This is in contrast with DB2 which shows about 80% time
in locks with 5 warehouses and ten clients. Perhaps you
were thinking DB2?

With TCP-W, neither PostgreSQL nor DB2 shows any significant
time spent in locks with 12 clients.

Can priorities scale?

The PostgreSQL-priority-mechanisms paper referenced in this thread
used TPC-C using 500MB - 3GB databases with 10 warehouses and
from 1 to 300 Clients and TPC-W with 150MB and between 12
and 150 clients.

So I'd say yes, it scales to meet most needs.

On Fri, Feb 23, 2007 at 12:07:56PM -0800, Ron Mayer wrote:

Jim Nasby wrote:

The problem with using simple OS priority settings is you leave yourself
wide open to priority inversion.

Which is why you either
(a) note that papers studying priority inversion on RDBMS's
find that it's a non issue on many RDBMS workloads; and
(except for real-time databases) you tend to still get
at least partial benefits even in the face of priority
inversions.
or
(b) use a scheduler in your OS that supports priority
inheritance or other mechanisms to avoid priority
inversion problems.
If you want to use priority inheritance to avoid
the priority inversion settings it appears versions
of Linux, BSD, Windows, and Solaris at least give
you the ability to do so.

There is already work being done on a queuing system; take a look at the
bizgres archives.

Which is cool; but not quite the same as priorities.

It seems to me that Bizgres and/or PostgreSQL would not
want to re-implement OS features like schedulers.

Actually, I believe part of the discussion also involved how to handle
long-running workloads that you don't want to monopolize the machine.
--
Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Added to TODO:

* Allow configuration of backend priorities via the operating system

Though backend priorities make priority inversion during lock
waits possible, research shows that this is not a huge problem.
http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php

---------------------------------------------------------------------------

Ron Mayer wrote:

Bruce Momjian wrote:

Hard to argue with that.

Is it a strong enough argument to add a TODO?

I'm thinking some sort of TODO might be called for.

Perhaps two TODOs?
* Use the OS's priority features to prioritize
backends (and document that it might work
better with OS's that support priority inheritance).
* Investigate if postgresql could develop an
additional priority mechanism instead of using
the OS's.

Ron Mayer wrote:

Magnus Hagander wrote: ...

quite likely to suffer from priority inversion

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

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

Setting priorities seems a rather common request,
supposedly coming up every couple months [5].

The paper referenced [1] suggests that even with
naive schedulers, use of CPU priorities is very
effective for CPU and I/O intensive PostgreSQL
workloads.

If someone eventually finds a workload that does suffer
worse performance due to priority inversion,
(a) they could switch to an OS and scheduler
that supports priority inheritance;
(b) it'd be an interesting case for a paper
rebutting the CMU one; and
(c) they don't have to use priorities.

If a user does find he wants priority inheritance it
seems Linux[1], BSD[2], some flavors of Windows[3],
and Solaris[4] all seem to be options; even though
I've only seen PostgreSQL specifically tested for
priority inversion problems with Linux (which did
not find problems but found additional benefit of
using priority inheritance).

[1] Linux with Priority inheritance showing benefits for
PostgreSQL
http://www.cs.cmu.edu/~bianca/icde04.pdf
[2] BSD priority inheritance work mentioned:
http://www.freebsd.org/news/status/report-july-2004-dec-2004.html
[3] Windows priority inheritance stuff:
http://msdn2.microsoft.com/en-us/library/aa915356.aspx
[4] Solaris priority inheritance stuff
http://safari5.bvdep.com/0131482092/ch17lev1sec7
http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/
[5] Tom suggests that priorities are a often requested feature.
http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Fri, 2007-02-23 at 12:07 -0800, Ron Mayer wrote:

Jim Nasby wrote:

The problem with using simple OS priority settings is you leave yourself
wide open to priority inversion.

Which is why you either
(a) note that papers studying priority inversion on RDBMS's
find that it's a non issue on many RDBMS workloads; and
(except for real-time databases) you tend to still get
at least partial benefits even in the face of priority
inversions.
or
(b) use a scheduler in your OS that supports priority
inheritance or other mechanisms to avoid priority
inversion problems.
If you want to use priority inheritance to avoid
the priority inversion settings it appears versions
of Linux, BSD, Windows, and Solaris at least give
you the ability to do so.

There is already work being done on a queuing system; take a look at the
bizgres archives.

Which is cool; but not quite the same as priorities.

It seems to me that Bizgres and/or PostgreSQL would not
want to re-implement OS features like schedulers.

Its now a TODO item, so I thought I'd add a few more notes for later
implementors.

Some feedback from earlier lives: Teradata's scheduling feature was
regularly used, as was the query queuing system. Both seem to be
effective and desirable as distinct features. There were some problems
in early days with priority inversions, but these were mainly caused by
heavily CPU bound queries interacting with heavily I/O bound queries.
Notably this meant that occasional rogue queries would bring the server
to its knees and this took a long time to identify, isolate and bring to
justice. I would hope to learn from lessons like that for PostgreSQL.

We do already have a home-grown priority mechanism in PostgreSQL:
vacuum_delay. Interestingly it handles both I/O and CPU quite well.

The Bizgres queueing feature is specifically designed to allow the
system to utilise large memories effectively without over-subscription.
If you set a query to a lower priority when its taking up lots of RAM,
you'll probably lose much of the benefit.

Simple scheduling seems to work best in practice. Both Teradata and
Microstrategy have provided implementation with just 3 levels of
priority: H, M, L, together with simple rules for when no queries exist
at higher levels.

None of this is patented or patentable, if kept very generic, IMHO.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com