Re: [PERFORM] lowering priority automatically at connection
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] lowering priority automatically at connection
Chris Mair [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] lowering priority automatically at connection
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] lowering priority automatically at connection
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] lowering priority automatically at connection
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
