pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedlowering priority automatically at connection
7+ messages / 6 participants
[nested] [flat]
* lowering priority automatically at connection
@ 2006-05-25 16:16 Chris Mair <list@1006.org>
0 siblings, 3 replies; 7+ messages in thread
From: Chris Mair @ 2006-05-25 16:16 UTC (permalink / raw)
To: pgsql-performance
Hi,
I find this very helpful:
Lowering the priority of a PostgreSQL query
http://weblog.bignerdranch.com/?p=11
Now I was wondering whether one could have a
SELECT pg_setpriority(10);
executed automatically each time a certain user
connects (not necessarily using psql)?
Any ideas if and how this might be possible?
Regards :)
Chris.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lowering priority automatically at connection
@ 2006-05-25 16:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Mair <list@1006.org>
2 siblings, 3 replies; 7+ messages in thread
From: Tom Lane @ 2006-05-25 16:26 UTC (permalink / raw)
To: Chris Mair <list@1006.org>; +Cc: pgsql-performance
Chris Mair <list@1006.org> writes:
> I find this very helpful:
> Lowering the priority of a PostgreSQL query
> http://weblog.bignerdranch.com/?p=11
That guy doesn't actually have the foggiest idea what he's doing.
The reason there is no built-in capability to do that is that it *does
not work well*. Search the list archives for "priority inversion" to
find out why not.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lowering priority automatically at connection
@ 2006-05-25 16:35 Chris Mair <list@1006.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 7+ messages in thread
From: Chris Mair @ 2006-05-25 16:35 UTC (permalink / raw)
To: pgsql-performance
> > I find this very helpful:
> > Lowering the priority of a PostgreSQL query
> > http://weblog.bignerdranch.com/?p=11
>
> That guy doesn't actually have the foggiest idea what he's doing.
> The reason there is no built-in capability to do that is that it *does
> not work well*. Search the list archives for "priority inversion" to
> find out why not.
>
> regards, tom lane
Ok,
I've learned something new (*).
I'll drop that idea :)
Bye,
Chris.
(*)
http://en.wikipedia.org/wiki/Priority_inversion
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lowering priority automatically at connection
@ 2006-05-25 16:54 Michael Fuhr <mike@fuhr.org>
parent: Chris Mair <list@1006.org>
2 siblings, 0 replies; 7+ messages in thread
From: Michael Fuhr @ 2006-05-25 16:54 UTC (permalink / raw)
To: Chris Mair <list@1006.org>; +Cc: pgsql-performance
On Thu, May 25, 2006 at 06:16:24PM +0200, Chris Mair wrote:
> I find this very helpful:
>
> Lowering the priority of a PostgreSQL query
> http://weblog.bignerdranch.com/?p=11
>
> Now I was wondering whether one could have a
> SELECT pg_setpriority(10);
> executed automatically each time a certain user
> connects (not necessarily using psql)?
Beware that setting priorities can have unintended, adverse effects.
Use a search engine to find information about "priority inversion"
before deciding that query priorities are a good idea.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lowering priority automatically at connection
@ 2006-05-26 01:32 Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 7+ messages in thread
From: Christopher Kings-Lynne @ 2006-05-26 01:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chris Mair <list@1006.org>; pgsql-performance
> That guy doesn't actually have the foggiest idea what he's doing.
> The reason there is no built-in capability to do that is that it *does
> not work well*. Search the list archives for "priority inversion" to
> find out why not.
http://en.wikipedia.org/wiki/Priority_inversion
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lowering priority automatically at connection
@ 2006-06-06 23:27 Ron Mayer <rm_pg@cheapcomplexdevices.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 7+ messages in thread
From: Ron Mayer @ 2006-06-06 23:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
Tom Lane wrote:
> That guy doesn't actually have the foggiest idea what he's doing.
> The reason there is no built-in capability to do that is that it *does
> not work well*. Search the list archives for "priority inversion" to
> find out why not.
I agree that that particular author seems clueless, but better
researched papers do show benefits as well:
The CMU paper
"Priority Mechanisms for OLTP and Transactional Web Applications" [1]
studied both TPC-C and TPC-W workloads on postgresql (as well as DB2).
For PostgreSQL they found that without priority inheritance they
had factor-of-2 benefits for high-priority transactions;
and with priority inheritance they had factor-of-6 benefits
for high priority transactions -- both with negligible harm
to the low priority transactions.
Unless there's something wrong with that paper (and at first glance
it looks like their methodologies apply at least to many workloads)
it seems that "it *does not work well*" is a bit of a generalization;
and that databases with TPC-C and TPC-W like workloads may indeed
be cases where this feature would be useful.
[1] http://www.cs.cmu.edu/~harchol/Papers/actual-icde-submission.pdf
"
...This paper analyzes and proposes prioritization for
transactional workloads in conventional DBMS...This paper
provides a detailed resource utilization breakdown for
OLTP workloads executing on a range of database platforms
including IBM DB2[14], Shore[16], and PostgreSQL[17]....
...
For DBMS using MVCC (with TPC-C or TPC-W workloads) and
for TPC-W workloads (with any concurrency control mechanism),
we find that lock scheduling is largely ineffective (even
preemptive lock scheduling) and CPU scheduling is highly
effective. For example, we find that for PostgreSQL
running under TPC-C, the simplest CPU scheduling
algorithm CPU-Prio provides a factor of 2 improvement
for the high-priority transactions, and adding priority
inheritance (CPU-Prio-Inherit) brings this up to a factor
of near 6 improvement under high loads, while hardly
penalizing low-priority transactions.
"
Or am I missing something?
Ron
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: lowering priority automatically at connection
@ 2006-06-20 15:12 Markus Schaber <schabi@logix-tt.com>
parent: Chris Mair <list@1006.org>
2 siblings, 0 replies; 7+ messages in thread
From: Markus Schaber @ 2006-06-20 15:12 UTC (permalink / raw)
To: Chris Mair <list@1006.org>; +Cc: pgsql-performance
Hi, Chris,
Chris Mair wrote:
> Now I was wondering whether one could have a
> SELECT pg_setpriority(10);
> executed automatically each time a certain user
> connects (not necessarily using psql)?
>
> Any ideas if and how this might be possible?
When using Java, most Datasource implementations (e. G. the JBoss one)
allow to specify SQL statements that are executed on connection init.
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-06-20 15:12 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-25 16:16 lowering priority automatically at connection Chris Mair <list@1006.org>
2006-05-25 16:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-25 16:35 ` Chris Mair <list@1006.org>
2006-05-26 01:32 ` Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
2006-06-06 23:27 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-05-25 16:54 ` Michael Fuhr <mike@fuhr.org>
2006-06-20 15:12 ` Markus Schaber <schabi@logix-tt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox