Re: "Priority Mechanisms for OLTP and Transactional Web Applications" - Mailing list pgsql-hackers

> -----Original Message-----
> From: cache+@cs.cmu.edu [mailto:cache+@cs.cmu.edu]
> Sent: Monday, May 09, 2005 5:36 PM
> To: Dann Corbit
> Subject: RE: "Priority Mechanisms for OLTP and Transactional Web
> Applications"
>
>
> So how should we move forward on this?  Do you have an application in
> mind for this?  Do you have any ideas on how the administrative
> mechanism should be designed (prioritiziation based on client names,
> user-based prioritization), etc?

I suspect that you have more experience in your modeling of the system
than I have.  Probably, your ideas would be as good as or better than
those I might think of.  I expect the PostgreSQL core team probably can
help you out more than I can as far as good ideas are concerned.

For the core team, see this paper for background:
http://www.db.cs.cmu.edu/Pubs/Lib/icde04oltp/oltp_icde04.pdf

My first inclination is that the postmaster should have a setting for
thread priority which would be nice to tune a particular machine for
maximum performance as a database server.

It would also be good to enable specific threads to get elevated
priority (perhaps a pqlib call or a modification to the query something
like {threadprio=<value>}

> -david
>
> Dann Corbit writes:
>  > Yes.  Something simple that can provide clear, tangible benefits is
the
>  > best kind of improvement.
>  >
>  > I am sure that adding parameters to the command line of PostgreSQL
> which
>  > enables superior tuning for differing computer systems would be
wildly
>  > appreciated.
>  >
>  > > -----Original Message-----
>  > > From: cache+@cs.cmu.edu [mailto:cache+@cs.cmu.edu]
>  > > Sent: Wednesday, May 04, 2005 11:40 AM
>  > > To: Dann Corbit
>  > > Cc: harchol@cs.cmu.edu; natassa@cmu.edu; bianca@cs.cmu.edu;
>  > > cache@cs.cmu.edu
>  > > Subject: "Priority Mechanisms for OLTP and Transactional Web
>  > Applications"
>  > >
>  > >
>  > > In our experimentation, we simply used a user-defined function to
>  > > handle changing the priority of transactions' threads.  It
shouldn't
>  > > be hard to port the implementation back into postgres --- and
>  > > provide an administrative mechanism to assign priorities.  Do you
>  > > think that PostgreSQL core would be interested in integrating
>  > > priorities of service in this manner?  I'd be very interested in
>  > > helping out with this.
>  > >
>  > > -David
>  > >
>  > >
>  > > Dann Corbit writes:
>  > >  > I have a question about your conclusion and the experiments as
> they
>  > >  > relate to the PostgreSQL database.  In the paper, we find
this:
>  > >  >
>  > >  >
>  > >  >
>  > >  > "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.  For PostgreSQL running under the TPC-W
workload, we
>  > find
>  > >  > that the best scheduling algorithm is the simplest CPU
scheduling
>  > > policy
>  > >  > CPU-Prio, which improves performance for high-priority
> transactions
>  > by
>  > > a
>  > >  > factor of up to 5. The reason why inheritance is more
effective
> for
>  > the
>  > >  > TPC-C example above is that TPC-C has much more data
contention
>  > than
>  > >  > TPC-W, leading to more priority inversions."
>  > >  >
>  > >  >
>  > >  >
>  > >  > To change the scheduling of the threads, did you modify the
source
>  > code
>  > >  > of the PostgreSQL database?  If so, are the modifications
>  > available?
>  > >  >
>  > >  >
>  > >  >
>  > >  > It seems that you have achieved a very significant performance
>  > boost by
>  > >  > a priority change, and I would be interested to know if the
>  > >  > modifications are available and also if they can be plowed
back
>  > into
>  > > the
>  > >  > PostgreSQL core.
>  > >  >
>  > >  >
>  > >  >
>  > >  > <html xmlns:o="urn:schemas-microsoft-com:office:office"
>  > > xmlns:w="urn:schemas-microsoft-com:office:word"
>  > > xmlns="http://www.w3.org/TR/REC-html40">
>  > >  >
>  > >  > <head>
>  > >  > <META HTTP-EQUIV="Content-Type" CONTENT="text/html;
>  > charset=us-ascii">
>  > >  > <meta name=Generator content="Microsoft Word 11 (filtered
> medium)">
>  > >  > <style>
>  > >  > <!--
>  > >  >  /* Style Definitions */
>  > >  >  p.MsoNormal, li.MsoNormal, div.MsoNormal
>  > >  >     {margin:0in;
>  > >  >     margin-bottom:.0001pt;
>  > >  >     font-size:12.0pt;
>  > >  >     font-family:"Times New Roman";}
>  > >  > a:link, span.MsoHyperlink
>  > >  >     {color:blue;
>  > >  >     text-decoration:underline;}
>  > >  > a:visited, span.MsoHyperlinkFollowed
>  > >  >     {color:purple;
>  > >  >     text-decoration:underline;}
>  > >  > span.EmailStyle17
>  > >  >     {mso-style-type:personal-compose;
>  > >  >     font-family:Arial;
>  > >  >     color:windowtext;}
>  > >  > @page Section1
>  > >  >     {size:8.5in 11.0in;
>  > >  >     margin:1.0in 1.25in 1.0in 1.25in;}
>  > >  > div.Section1
>  > >  >     {page:Section1;}
>  > >  > -->
>  > >  > </style>
>  > >  >
>  > >  > </head>
>  > >  >
>  > >  > <body lang=EN-US link=blue vlink=purple>
>  > >  >
>  > >  > <div class=Section1>
>  > >  >
>  > >  > <p class=MsoNormal><font size=2 face=Arial><span style='font-
>  > > size:10.0pt;
>  > >  > font-family:Arial'>I have a question about your conclusion and
the
>  > > experiments
>  > >  > as they relate to the PostgreSQL database.  In the paper,
we
>  > find
>  > > this:<o:p></o:p></span></font></p>
>  > >  >
>  > >  > <p class=MsoNormal><font size=2 face=Arial><span style='font-
>  > > size:10.0pt;
>  > >  > font-family:Arial'><o:p> </o:p></span></font></p>
>  > >  >
>  > >  > <p class=MsoNormal><font size=2 face=Arial><span style='font-
>  > > size:10.0pt;
>  > >  > font-family:Arial'>"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. 
> For
>  > > PostgreSQL
>  > >  > running under the TPC-W workload, we find that the best
scheduling
>  > > algorithm is
>  > >  > the simplest CPU scheduling policy CPU-Prio, which improves
>  > performance
>  > > for
>  > >  > high-priority transactions by a factor of up to 5. The reason
why
>  > > inheritance
>  > >  > is more effective for the TPC-C example above is that TPC-C
has
>  > much
>  > > more data
>  > >  > contention than TPC-W, leading to more priority
>  > > inversions."<o:p></o:p></span></font></p>
>  > >  >
>  > >  > <p class=MsoNormal><font size=2 face=Arial><span style='font-
>  > > size:10.0pt;
>  > >  > font-family:Arial'><o:p> </o:p></span></font></p>
>  > >  >
>  > >  > <p class=MsoNormal><font size=2 face=Arial><span style='font-
>  > > size:10.0pt;
>  > >  > font-family:Arial'>To change the scheduling of the threads,
did
> you
>  > > modify the
>  > >  > source code of the PostgreSQL database?  If so, are the
>  > > modifications
>  > >  > available?<o:p></o:p></span></font></p>
>  > >  >
>  > >  > <p class=MsoNormal><font size=2 face=Arial><span style='font-
>  > > size:10.0pt;
>  > >  > font-family:Arial'><o:p> </o:p></span></font></p>
>  > >  >
>  > >  > <p class=MsoNormal><font size=2 face=Arial><span style='font-
>  > > size:10.0pt;
>  > >  > font-family:Arial'>It seems that you have achieved a very
>  > significant
>  > >  > performance boost by a priority change, and I would be
interested
>  > to
>  > > know if
>  > >  > the modifications are available and also if they can be plowed
> back
>  > > into the
>  > >  > PostgreSQL core.<o:p></o:p></span></font></p>
>  > >  >
>  > >  > <p class=MsoNormal><font size=2 face=Arial><span style='font-
>  > > size:10.0pt;
>  > >  > font-family:Arial'><o:p> </o:p></span></font></p>
>  > >  >
>  > >  > </div>
>  > >  >
>  > >  > </body>
>  > >  >
>  > >  > </html>
>  > >
>  > > --
>  > > Hollywood is where if you don't have happiness you send out for
it.
>  > >         -- Rex Reed
>
> --
> A fool and his money are soon popular.