Quick Links

Re: "Priority Mechanisms for OLTP and Transactional Web Applications"

From:	"Dann Corbit" <DCorbit(at)connx(dot)com>
To:	<cache+(at)cs(dot)cmu(dot)edu>
Cc:	<pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: "Priority Mechanisms for OLTP and Transactional Web Applications"
Date:	2005-05-12 17:09:01
Message-ID:	D425483C2C5C9F49B5B7A41F89441547055B86@postal.corporate.connx.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-hackers

> -----Original Message-----
> From: cache+(at)cs(dot)cmu(dot)edu [mailto:cache+(at)cs(dot)cmu(dot)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+(at)cs(dot)cmu(dot)edu [mailto:cache+(at)cs(dot)cmu(dot)edu]
> > > Sent: Wednesday, May 04, 2005 11:40 AM
> > > To: Dann Corbit
> > > Cc: harchol(at)cs(dot)cmu(dot)edu; natassa(at)cmu(dot)edu; bianca(at)cs(dot)cmu(dot)edu;
> > > cache(at)cs(dot)cmu(dot)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>
> > > >
> > > > </head>
> > > >
> > > > <body lang=EN-US link=blue vlink=purple>
> > > >
> > > > <div class=Section1>
> > > >
> > > > > > 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>
> > > >
> > > > > > size:10.0pt;
> > > > font-family:Arial'><o:p> </o:p>
> > > >
> > > > > > 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>
> > > >
> > > > > > size:10.0pt;
> > > > font-family:Arial'><o:p> </o:p>
> > > >
> > > > > > 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>
> > > >
> > > > > > size:10.0pt;
> > > > font-family:Arial'><o:p> </o:p>
> > > >
> > > > > > 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>
> > > >
> > > > > > size:10.0pt;
> > > > font-family:Arial'><o:p> </o: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.

Browse pgsql-hackers by date

	From	Date	Subject
Next Message	Sergey Ten	2005-05-12 18:41:54	Re: patches for items from TODO list
Previous Message	Josh Berkus	2005-05-12 17:04:20	Re: Server instrumentation for 8.1