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

Started by Dann Corbitover 20 years ago3 messages

DCorbit@connx.com

over 20 years ago

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

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>

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>

<o:p> </o:p>

"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>

<o:p> </o:p>

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>

<o:p> </o:p>

It seems that you have achieved a very

significant

performance boost by a priority change, and I would be interested

Show quoted text

know if

the modifications are available and also if they can be plowed back

into the

PostgreSQL core.<o:p></o:p>

<o:p> </o:p>

</div>

</body>

</html>

--
Hollywood is where if you don't have happiness you send out for it.
-- Rex Reed

Dann Corbit

DCorbit@connx.com

over 20 years ago

In reply to: Dann Corbit (#1)

-----Original Message-----
From: harchol+@mor.sync.cs.cmu.edu

[mailto:harchol+@mor.sync.cs.cmu.edu]

Sent: Wednesday, May 04, 2005 12:44 PM
To: Dann Corbit
Cc: cache+@cs.cmu.edu; harchol@cs.cmu.edu; natassa@cmu.edu;
bianca@cs.cmu.edu
Subject: Re: "Priority Mechanisms for OLTP and Transactional Web
Applications"

Dear Dann,

First of all, thank you for your interest in our work. We've
got quite a few different priority implementations, based on our
papers in: ICDE 2004, ICDE 2005, TOIT 2005, TOCS 2003, and some newly
submitted papers that we just sent to VLDB 2005.

Some of our most recent work, being led by my student Bianca
Schroeder, deals with implementing the priority mechanism *externally*
to the DBMS. The mechanism is a "black box" that sits between the web
server and the DBMS and schedules which transactions are allowed to be
sent into the DBMS and when. We find that this simple "external black
box" achieves comparable results to scheduling internally to the DBMS
and yet is far more portable, since it can work as a front-end to any
DBMS.

This is (of course) a fascinating idea, and will be generally useful for
any sort of database implementation. Since you are using a JDBC
interface and manipulation the Java calls, there is no need to consider
modifications to the PostgreSQL database [or any other] in such a
circumstance. It does make an interesting study, and I think it may be
even more interesting to see if both approaches can work together.

However, the internal modifications are of more immediate interest to
me, since it allows a simple way to achieve the benefits regardless of
the interface method chosen.

If you are interested in the most recent "external approach" described
above, please let me know and I will gladly forward to you our recent
proposal on this idea and some papers in submission.

I have seen and downloaded some of the papers from your web site.

All our earlier work deals with "internal scheduling approaches"
(scheduling *within* the DBMS or the OS). The relevant papers there
can be found on my web page or that of my students, and David
McWherter would be happy to help answer questions on implementation.

I have contacted Mr. McWherter and he seems to have some interest in
helping.

Thank you for your quick and helpful reply.

Show quoted text

With best regards,
Mor

P.S. I didn't cc to "pgsql-hackers@postgresql.org" since I wasn't
sure how far that list extended and didn't want to SPAM folks, but
feel free to pass on the above message if you think it's relevant
to these folks.

-------------------------------
Mor Harchol-Balter
Associate Professor
Computer Science Dept
Carnegie Mellon University
5000 Forbes Ave.
Pittsburgh, PA 15213-3891

WEB: www.cs.cmu.edu/~harchol
Email: harchol@cs.cmu.edu
Phone: (412) 268-7893
Fax: (412) 268-5576
Assistant: Charlotte Yano
yano@cs.cmu.edu
-------------------------------

Import Notes

Resolved by subject fallback

Anastassia Ailamaki

natassa@exchange.cs.cmu.edu

over 20 years ago

In reply to: Dann Corbit (#2)

Hi Dann,

I am also very happy you are interested in our work. I am working inside
the DB engine and have coauthored the work you are interested in; David
will happily share code with you. The latest and greatest on my side has
to do with componentization of database systems; we are using staging to
make databases work more reliably on modern hardware and expand easily
to networks of workstations. We deal with how to do queue management and
scheduling of different queries to different components in our
architecture. In addition to ICDE 04/05, please refer to our CIDR03,
VLDB04, and SIGMOD05 papers available off of my website.

I would be happy to talk in person about any and all of this.
Cheers,
Natassa

Anastassia Ailamaki
Computer Science Department
Carnegie Mellon University
http://www.cs.cmu.edu/~natassa

Show quoted text

-----Original Message-----
From: Dann Corbit [mailto:DCorbit@connx.com]
Sent: Wednesday, May 04, 2005 4:39 PM
To: harchol+@mor.sync.cs.cmu.edu; PostgreSQL-development
Cc: cache+@cs.cmu.edu; harchol@cs.cmu.edu; natassa@cmu.edu;
bianca@cs.cmu.edu
Subject: RE: "Priority Mechanisms for OLTP and Transactional
Web Applications"

-----Original Message-----
From: harchol+@mor.sync.cs.cmu.edu

[mailto:harchol+@mor.sync.cs.cmu.edu]

Sent: Wednesday, May 04, 2005 12:44 PM
To: Dann Corbit
Cc: cache+@cs.cmu.edu; harchol@cs.cmu.edu; natassa@cmu.edu;
bianca@cs.cmu.edu
Subject: Re: "Priority Mechanisms for OLTP and Transactional Web
Applications"

Dear Dann,

First of all, thank you for your interest in our

work. We've

got quite a few different priority implementations, based on our
papers in: ICDE 2004, ICDE 2005, TOIT 2005, TOCS 2003, and

some newly

submitted papers that we just sent to VLDB 2005.

Some of our most recent work, being led by my student Bianca
Schroeder, deals with implementing the priority mechanism

*externally*

to the DBMS. The mechanism is a "black box" that sits

between the web

server and the DBMS and schedules which transactions are

allowed to be

sent into the DBMS and when. We find that this simple

"external black

box" achieves comparable results to scheduling internally

to the DBMS

and yet is far more portable, since it can work as a

front-end to any

DBMS.

This is (of course) a fascinating idea, and will be generally
useful for any sort of database implementation. Since you
are using a JDBC interface and manipulation the Java calls,
there is no need to consider modifications to the PostgreSQL
database [or any other] in such a circumstance. It does make
an interesting study, and I think it may be even more
interesting to see if both approaches can work together.

However, the internal modifications are of more immediate
interest to me, since it allows a simple way to achieve the
benefits regardless of the interface method chosen.

If you are interested in the most recent "external

approach" described

above, please let me know and I will gladly forward to you

our recent

proposal on this idea and some papers in submission.

I have seen and downloaded some of the papers from your web site.

All our earlier work deals with "internal scheduling approaches"
(scheduling *within* the DBMS or the OS). The relevant

papers there

can be found on my web page or that of my students, and David
McWherter would be happy to help answer questions on implementation.

I have contacted Mr. McWherter and he seems to have some
interest in helping.

Thank you for your quick and helpful reply.

With best regards,
Mor

P.S. I didn't cc to "pgsql-hackers@postgresql.org" since I

wasn't sure

how far that list extended and didn't want to SPAM folks, but feel
free to pass on the above message if you think it's

relevant to these

folks.

-------------------------------
Mor Harchol-Balter
Associate Professor
Computer Science Dept
Carnegie Mellon University
5000 Forbes Ave.
Pittsburgh, PA 15213-3891

WEB: www.cs.cmu.edu/~harchol
Email: harchol@cs.cmu.edu
Phone: (412) 268-7893
Fax: (412) 268-5576
Assistant: Charlotte Yano
yano@cs.cmu.edu
-------------------------------

Import Notes

Resolved by subject fallback