Thread: 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
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:
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. DrakeThanks in advance! Benjamin ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
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: > 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 <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: > 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
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: > 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 >> > >
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/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 > >
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: > 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 >>> >>> > >
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 #
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/ > >
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].
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
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)
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.
> 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)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
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: > 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 > >
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. > > Benjamin > > 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 >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
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?"
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: > 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 > >
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