> -----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=
}
> -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.
> > > >
> > > >
> > > >
> > > > > > xmlns:w="urn:schemas-microsoft-com:office:word"
> > > xmlns="http://www.w3.org/TR/REC-html40">
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
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.
> > > >
> > > >
> > > >
> > > >
