Anyone working on better transaction locking?
Ron Peacetree wrote:
...and if so, what are the current efforts focusing on?
What is it that you think of as being potentially "better" about some
would-be-alternative "transaction locking" scheme?
PostgreSQL already supports MVCC, which is commonly considered to be the
"better" scheme that eliminates a lot of need to lock data.
Furthermore, the phrase "transaction locking" doesn't seem to describe
what one would want to lock. I wouldn't want to lock a "transaction;"
I'd want to lock DATA.
--
(concatenate 'string "cbbrowne" "@cbbrowne.com")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #153. "My Legions of Terror will be an
equal-opportunity employer. Conversely, when it is prophesied that no
man can defeat me, I will keep in mind the increasing number of
non-traditional gender roles." <http://www.eviloverlord.com/>
<cbbrowne@cbbrowne.com> wrote in message
news:20030407194827.D0A3A56B1B@cbbrowne.com...
Ron Peacetree wrote:
...and if so, what are the current efforts focusing on?
What is it that you think of as being potentially "better" about
some
would-be-alternative "transaction locking" scheme?
PostgreSQL already supports MVCC, which is commonly considered to be
the
"better" scheme that eliminates a lot of need to lock data.
Agreed. FTR, the reason MVCC is "better" is that readers and writers
to the same data don't block each other. In "traditional" locking
schemes, readers don't block each other, but readers and writers to
the same data do. Clearly, writers to the same data must always block
each other.
Unfortunately, the performance of PostgreSQL MVCC in comparison to say
Oracle (the performance leader amongst MVCC DB's, and pretty much for
all DB's for that matter) is not competitive. Therefore there is a
need to improve the implementation of MVCC that PostgreSQL uses. If
someone can post a detailed blow-by-blow comparison of how the two
operate so that the entire list can see it that would be a Good Thing.
If I can, I'll put together the info and post it myself.
Furthermore, the phrase "transaction locking" doesn't seem to
describe
what one would want to lock. I wouldn't want to lock a
"transaction;"
I'd want to lock DATA.
*sigh*. The accepted terminology within this domain for what we are
talking about is "transaction locking". Therefore we should use it to
ease communications. Argue with Codd and Date if you think the term
is a misnomer. Secondly, you are thinking only in the space
dimension. Locks have to protect data within a minimum space vs time
"bubble". That bubble is defined by the beginning and end of a
transaction, hence we call the locking of resources we do during that
bubble as "transaction locking".
On Tue, Apr 08, 2003 at 01:45:25PM +0000, Ron Peacetree wrote:
Unfortunately, the performance of PostgreSQL MVCC in comparison to say
Oracle (the performance leader amongst MVCC DB's, and pretty much for
all DB's for that matter) is not competitive. Therefore there is a
What, is this a troll? The question apparently reduces to, "Why
isn't PostgreSQL as good as Oracle?" I have two things to say about
that:
1. For what? There are things that Oracle users will tell you
not to do, because there is a faster way in Oracle.
2. How do you know? I haven't seen any real benchmarks
comparing PostgreSQL and Oracle similarly tuned on similar hardware.
So I'm sceptical.
But if you have specifica areas which you think need improvement (and
aren't already listed in the TODO), I'll bet people would like to
hear about it.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
"Ron Peacetree" <rjpeace@earthlink.net> writes:
Unfortunately, the performance of PostgreSQL MVCC in comparison to say
Oracle (the performance leader amongst MVCC DB's, and pretty much for
all DB's for that matter) is not competitive.
Ron, the tests that I've seen offer no support for that thesis. If you
want us to accept such a blanket statement as fact, you'd better back
it up with evidence. Let's see some test cases.
Postgres certainly has plenty of performance issues, but I have no
reason to believe that the fundamental MVCC mechanism is one of them.
regards, tom lane
"Andrew Sullivan" <andrew@libertyrms.info> wrote in message
news:20030408230518.GB32207@libertyrms.info...
On Tue, Apr 08, 2003 at 01:45:25PM +0000, Ron Peacetree wrote:
Unfortunately, the performance of PostgreSQL MVCC in comparison to
say Oracle (the performance leader amongst MVCC DB's, and pretty
much
for all DB's for that matter) is not competitive. Therefore there
is
What, is this a troll?
Time will tell. Hopefully not.
The question apparently reduces to, "Why isn't PostgreSQL
as good as Oracle?"
Actually, you've just used reductio absurdium, not I. My question
compares PostgreSQL to the performance leaders within this domain
since I'll have to justify my decisions to my bosses based on such
comparisons. If you think that is unrealistic, then I wish I worked
where you do. If you think that is unreasonable, then I think you're
treating PostgreSQL as a religion and not a SW product that must
compete against every other DB solution in the real world in order to
be relevant or even survive.
1. For what? There are things that Oracle users will tell you
not to do, because there is a faster way in Oracle.2. How do you know? I haven't seen any real benchmarks
comparing PostgreSQL and Oracle similarly tuned on similar hardware.
So I'm sceptical.
Please see my response(s) to Tom below.
But if you have specifica areas which you think need improvement
(and aren't already listed in the TODO), I'll bet people would like
to
hear about it.
Please see my posts with regards to sorting and searching, two phase
execution, and two phase commit. I'll mention thread support in
passing, and I'll be bringing up other stuff as I investigate. Then
I'll hopefully start helping to solve some of the outstanding issues
in priority order...
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:4096.1049860699@sss.pgh.pa.us...
Ron, the tests that I've seen offer no support for that thesis.
What tests? I've seen no tests doing head-to-head,
feature-for-feature comparisons (particularly for low level features
like locking) of PostgreSQL vs the "biggies": DB2, Oracle, and SQL
Server. What data I have been able to find is application level, and
certainly not head-to-head. From those performance results, I've had
to try and extrapolate likely causes from behavioral characteristics,
docs, and what internal code I can look at (clearly not much from the
"biggies").
If you have specific head-to-head, feature-for-feature comparison test
results to share, PLEASE do so. I need the data.
If you want us to accept such a blanket statement as fact, you'd
better back it up with evidence. Let's see some test cases.
Soon as I have the HW and SW to do so, it'll happen. I have some "bet
the company" decisions to make in the DB realm.
Test cases are, of course, not the only possible evidence. I'll get
back to you and the list on this.
Postgres certainly has plenty of performance issues, but I have no
reason to believe that the fundamental MVCC mechanism is one of
them.
Where in your opinion are they then? How bad are they in comparison
to MySQL or any of the "Big Three"?
"Ron Peacetree" <rjpeace@earthlink.net> writes:
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:4096.1049860699@sss.pgh.pa.us...Ron, the tests that I've seen offer no support for that thesis.
What tests? I've seen no tests doing head-to-head,
feature-for-feature comparisons (particularly for low level features
like locking) of PostgreSQL vs the "biggies": DB2, Oracle, and SQL
Server. What data I have been able to find is application level, and
certainly not head-to-head.
Who said anything about feature-for-feature comparisons? You made an
(unsupported) assertion about performance, which has little to do with
feature checklists.
The reason I don't believe there's any fundamental MVCC problem is that
no such problem showed up in the head-to-head performance tests that
Great Bridge did about two years ago. GB is now defunct, and I have
not heard of anyone else willing to stick their neck out far enough to
publish comparative benchmarks against Oracle. But I still trust the
results they got.
I have helped various people privately with Oracle-to-PG migration
performance problems, and so far the issues have never been MVCC or
transaction issues at all. What I've seen is mostly planner
shortcomings, such as failure to optimize "foo IN (sub-SELECT)"
decently. Some of these things are already addressed in development
sources for 7.4.
Postgres certainly has plenty of performance issues, but I have no
reason to believe that the fundamental MVCC mechanism is one of
them.
Where in your opinion are they then? How bad are they in comparison
to MySQL or any of the "Big Three"?
See the TODO list for some of the known problems. As for "how bad are
they", that depends completely on the particular application and queries
you are looking at ...
regards, tom lane
On Wed, Apr 09, 2003 at 05:41:06AM +0000, Ron Peacetree wrote:
Actually, you've just used reductio absurdium, not I. My question
Nonsense. You explicitly made the MVCC comparison with Oracle, and
are asking for a "better" locking mechanism without providing any
evidence that PostgreSQL's is bad.
compares PostgreSQL to the performance leaders within this domain
since I'll have to justify my decisions to my bosses based on such
comparisons. If you think that is unrealistic, then I wish I worked
Where I work, we test our systems to performance targets having to do
with what we use the database for. Generic database benchmarks are
not something I have a great deal of faith in. I repeat my assertion
that, if you have specific areas of concern and the like, and they're
not on the TODO (or in the FAQ), then people would be likely to be
interested; although they'll likely be more interested if the
specifics are not a lot of hand-wavy talk about PostgreSQL not doing
something the right way.
treating PostgreSQL as a religion and not a SW product that must
compete against every other DB solution in the real world in order to
be relevant or even survive.
Actually, given that we are dependent on PostgreSQL's performance and
stability for the whole of the company's revenue, I am pretty certain
that I have as much "real world" experience of PostgreSQL use as
anyone else.
Please see my posts with regards to sorting and searching, two phase
execution, and two phase commit.
I think your other posts were similar to the one which started this
thread: full of mighty big pronouncements which turned out to depend
on a bunch of not-so-tenable assumptions.
I'm sorry to be so cranky about this, but I get tired of having to
defend one of my employer's core technologies from accusations based
on half-truths and "everybody knows" assumptions. For instance,
I'll mention thread support in passing,
there's actually a FAQ item about thread support, because in the
opinion of those who have looked at it, the cost is just not worth
the benefit. If you have evidence to the contrary (specific evidence,
please, for this application), and have already read all the previous
discussion of the topic, perhaps people would be interested in
opening that debate again (though I have my doubts).
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110Tom Lane wrote:
The reason I don't believe there's any fundamental MVCC problem is that
no such problem showed up in the head-to-head performance tests that
Great Bridge did about two years ago. GB is now defunct, and I have
not heard of anyone else willing to stick their neck out far enough to
publish comparative benchmarks against Oracle. But I still trust the
results they got.
<irony-mode-on>
You're missing where Mr Peacetree documented how MVCC performance
degraded by 42.37% between versions 7.1 and 7.3.1, as well as his
extensive statistical analysis of the relative behaviours of
PostgreSQL's semantics versus those of DB/2's MVCC implementation.
</irony-mode-off>
I have helped various people privately with Oracle-to-PG migration
performance problems, and so far the issues have never been MVCC or
transaction issues at all. What I've seen is mostly planner
shortcomings, such as failure to optimize "foo IN (sub-SELECT)"
decently. Some of these things are already addressed in development
sources for 7.4.
Ah, but that's just anecdotal evidence...
And if you used radix sorting, that would probably fix it all. (At
least until you discovered that you needed 65 bit addressing to set
sort_mem high enough... Oh, did I neglect to mention anything about
irony?)
--
output = reverse("gro.mca@" "enworbbc")
http://www.ntlug.org/~cbbrowne/oses.html
"Luckily for Microsoft, it's difficult to see a naked emperor in the
dark." --- Ted Lewis, (former) editor-in-chief, IEEE Computer
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:8236.1049906884@sss.pgh.pa.us...
"Ron Peacetree" <rjpeace@earthlink.net> writes:
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:4096.1049860699@sss.pgh.pa.us...Ron, the tests that I've seen offer no support for that thesis.
What tests? I've seen no tests doing head-to-head,
feature-for-feature comparisons (particularly for low level
features
like locking) of PostgreSQL vs the "biggies": DB2, Oracle, and SQL
Server. What data I have been able to find is application level,
and
certainly not head-to-head.
Who said anything about feature-for-feature comparisons? You made
an
(unsupported) assertion about performance, which has little to do
with
feature checklists.
That's not quite fair. My assertion was about the performance of an
exact feature in comparison to that same feature in another DB
product, not about overall application level performance... As I
said, I'll get back to you and the list on this.
The reason I don't believe there's any fundamental MVCC problem is
that
no such problem showed up in the head-to-head performance tests that
Great Bridge did about two years ago. GB is now defunct, and I have
not heard of anyone else willing to stick their neck out far enough
to
publish comparative benchmarks against Oracle. But I still trust
the
results they got.
Last year eWeek did a shoot out that PostgreSQL was notable in its
absence from:
http://www.eweek.com/print_article/0,3668,a=23115,00.asp
Taking those results and adding PostgreSQL to them should be eminently
feasible since the entire environment used for the test is documented
and the actual scripts and data used for the test are also available.
Of course, MySQL has been evolving at such a ferocious rate that even
one year old results, let alone two year old ones, run the risk of not
being accurate for it.
I have helped various people privately with Oracle-to-PG migration
performance problems, and so far the issues have never been MVCC or
transaction issues at all. What I've seen is mostly planner
shortcomings, such as failure to optimize "foo IN (sub-SELECT)"
decently. Some of these things are already addressed in development
sources for 7.4.
It's probably worth noting that since SQL support was added to
Postgres rather than being part of the product from Day One, certain
"hard" SQL constructs may still be having teething problems. NOT IN,
for instance, was a problem for both Oracle and SQL Server at some
point in their history (fuzzy memory: pre Oracle 6, not sure about SQL
Server version...)
Postgres certainly has plenty of performance issues, but I have
no
reason to believe that the fundamental MVCC mechanism is one of
them.Where in your opinion are they then? How bad are they in
comparison
to MySQL or any of the "Big Three"?
See the TODO list for some of the known problems. As for "how bad
are
they", that depends completely on the particular application and
queries
you are looking at ...
Fair enough.
"Andrew Sullivan" <andrew@libertyrms.info> wrote in message
news:20030409170926.GH2255@libertyrms.info...
On Wed, Apr 09, 2003 at 05:41:06AM +0000, Ron Peacetree wrote:
Nonsense. You explicitly made the MVCC comparison with Oracle, and
are asking for a "better" locking mechanism without providing any
evidence that PostgreSQL's is bad.
Just because someone else's is "better" does not mean PostgreSQL's is
"bad", and I've never said such. As I've said, I'll get back to Tom
and the list on this.
compares PostgreSQL to the performance leaders within this domain
since I'll have to justify my decisions to my bosses based on such
comparisons. If you think that is unrealistic, then I wish I
worked where you do.Where I work, we test our systems to performance targets having to
do with what we use the database for. Generic database benchmarks
are not something I have a great deal of faith in. I repeat my
assertion that, if you have specific areas of concern and the like,
and they're not on the TODO (or in the FAQ), then people would be
likely to be interested; although they'll likely be more interested
if the
specifics are not a lot of hand-wavy talk about PostgreSQL not doing
something the right way.
There's nothing "hand wavy"about this unless you think anything except
test cases is "hand wavy". In that case, you're right. I don't have
the time or resources to provide exhaustive tests between each DB for
each of the issues we are discussing. If I did, I'd be publishing a
=very= lucrative newsletter for IT decision makers. Also, there are
other
valid ways to analyze issues than just application level test cases.
In fact, there are some =better= ways, depending on the issue being
discussed.
treating PostgreSQL as a religion and not a SW product that must
compete against every other DB solution in the real world in order
to be relevant or even survive.Actually, given that we are dependent on PostgreSQL's performance
and stability for the whole of the company's revenue, I am pretty
certain that I have as much "real world" experience of PostgreSQL
use as anyone else.
Your experience was not questioned, and there were "if" clauses at the
beginning of my comments that you seem to be ignoring. I'm not here
to waste my or anyone else's time on flames. We've all got work to
do.
Please see my posts with regards to ...
I think your other posts were similar to the one which started this
thread: full of mighty big pronouncements which turned out to depend
on a bunch of not-so-tenable assumptions.
Hmmm. Well, I don't think of algorithm analysis by the likes of
Knuth, Sedgewick, Gonnet, and Baeza-Yates as being "not so tenable
assumptions", but YMMV. As for "mighty pronouncements", that also
seems a bit misleading since we are talking about quantifiable
programming and computer science issues, not unquantifiable things
like politics.
I'm sorry to be so cranky about this, but I get tired of having to
defend one of my employer's core technologies from accusations based
on half-truths and "everybody knows" assumptions. For instance,
Again, "accusations" is a bit strong. I thought the discussion was
about the technical merits and costs of various features and various
ways to implement them, particularly when this product must compete
for installed base with other solutions. Being coldly realistic about
what a product's strengths and weaknesses are is, again, just good
business. Sun Tzu's comment about knowing the enemy and yourself
seems appropriate here...
I'll mention thread support in passing,
there's actually a FAQ item about thread support, because in the
opinion of those who have looked at it, the cost is just not worth
the benefit. If you have evidence to the contrary (specific
evidence, please, for this application), and have already read all
the
previous discussion of the topic, perhaps people would be interested
in
opening that debate again (though I have my doubts).
Zeus had a performance ceiling roughly 3x that of Apache when Zeus
supported threading as well as pre-forking and Apache only supported
pre forking. The Apache folks now support both. DB2, Oracle, and SQL
Server all use threads. Etc, etc.
That's an awful lot of very bright programmers and some serious $$
voting that threads are worth it. Given all that, if PostgreSQL
specific
thread support is =not= showing itself to be a win that's an
unexpected
enough outcome that we should be asking hard questions as to why not.
At their core, threads are a context switching efficiency tweak.
Since DB's switch context a lot under many circumstances, threads
should be a win under such circumstances. At the least, it should be
helpful in situations where we have multiple CPUs to split query
execution between.
M$'s first implementation of threads was so "heavy" that it didn't
help them (until they actually implemented real threads and called
them "strings"), but that was not due to the inefficacy of the
concept, but rather M$'s implementation and the system environment
within which that implementation was being used. Perhaps something
similar is going on here?
Certainly it's =possible= that threads have nothing to offer
PostgreSQL, but IMHO it's not =probable=. Just another thing for me
to add to my TODO heap for looking at...
"Ron Peacetree" <rjpeace@earthlink.net> wrote in message
news:eS0la.16229$ey1.1398978@newsread1.prod.itd.earthlink.net...
M$'s first implementation of threads was so "heavy" that it didn't
help them (until they actually implemented real threads and called
them "strings"),
TYPO ALERT: M$'s better implementation of threads is called "fibers",
not "strings"
Ron Peacetree wrote:
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
[...]
If you want us to accept such a blanket statement as fact, you'd
better back it up with evidence. Let's see some test cases.Soon as I have the HW and SW to do so, it'll happen. I have some "bet
the company" decisions to make in the DB realm.
And you are comparing what? Just pure features and/or performace, or
total cost of ownership for your particular case?
It is a common misunderstanding open source would be free software. It
is not because since the software comes as is, without any warranty and
it's usually hard to get support provided or backed by large companies,
it is safe to build you own support team (depends on how much you "bet
the company"). Replacing license fees and support contracts with payroll
entries plus taking the feature and performance differences into account
makes this comparision a very individual, non-portable task.
Unfortunately most manager type people can produce an annoyingly high
volume of questions and suggestions as long as they need more input,
then all of the sudden disappear when they made their decision.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
"Jan Wieck" <JanWieck@Yahoo.com> wrote in message
news:3E956DD8.29432405@Yahoo.com...
Ron Peacetree wrote:
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
[...]
If you want us to accept such a blanket statement
as fact, you'd better back it up with evidence. Let's
see some test cases.Soon as I have the HW and SW to do so, it'll happen.
I have some "bet the company" decisions to make.And you are comparing what? Just pure features and/or
performance, or total cost of ownership for your
particular case?
Technical Analysis and Business Analysis are two separate, and equally
necessary, activities. However, before one can accurately measure
things like Total Cost of Ownership, one needs to have accurately and
sufficiently characterized what will be owned and one's choices as to
what could be owned...
It is a common misunderstanding open source would be
free software. It is not because since the software comes
as is, without any warranty and it's usually hard to get
support provided or backed by large companies, it is safe
to build you own support team (depends on how much
you "bet the company"). Replacing license fees and
support contracts with payroll entries plus taking the
feature and performance differences into account makes
this comparision a very individual, non-portable task.
Very valid points, and I was a supporter of the FSF and the LPF when
Usenet was "the net" and backbone nodes communicated by modem, so I've
been wrestling with people's sometimes misappropriate
use/understanding of the operator "free" for some time.
However, a correctly done Technical Analysis =should= be reasonably
portable since among other things you don't want to have to start all
over if your company's business or business model changes. Clearly
Business Analysis is very context dependant.
It should also be noted that given the prices of some of the solutions
out there, there are many companies who's choices are constrained, but
still need to stay in business...
Unfortunately most manager type people can produce an
annoyingly high volume of questions and suggestions as
long as they need more input, then all of the sudden
disappear when they made their decision.
Word. Although the phrase "manager type people" could be replaced
with "people" and the above would still be true IMHO. Thankfully,
most of my bosses are people who have worked their up from the
technical trenches, so the conversation at least rates to be focused
and reasonable while it's occurring...
On Wed, 9 Apr 2003, Ron Peacetree wrote:
"Andrew Sullivan" <andrew@libertyrms.info> wrote in message
news:20030409170926.GH2255@libertyrms.info...On Wed, Apr 09, 2003 at 05:41:06AM +0000, Ron Peacetree wrote:
Nonsense. You explicitly made the MVCC comparison with Oracle, and
are asking for a "better" locking mechanism without providing any
evidence that PostgreSQL's is bad.Just because someone else's is "better" does not mean PostgreSQL's is
"bad", and I've never said such. As I've said, I'll get back to Tom
and the list on this.
But you didn't identify HOW it was better. I think that's the point
being made.
Please see my posts with regards to ...
I think your other posts were similar to the one which started this
thread: full of mighty big pronouncements which turned out to depend
on a bunch of not-so-tenable assumptions.Hmmm. Well, I don't think of algorithm analysis by the likes of
Knuth, Sedgewick, Gonnet, and Baeza-Yates as being "not so tenable
assumptions", but YMMV. As for "mighty pronouncements", that also
seems a bit misleading since we are talking about quantifiable
programming and computer science issues, not unquantifiable things
like politics.
But the real truth is revealed when the rubber hits the pavement.
Remember that Linux Torvalds was roundly criticized for his choice of a
monolithic development model for his kernel, and was literally told that
his choice would restrict to "toy" status and that no commercial OS could
scale with a monolithic kernel.
There's no shortage of people with good ideas, just people with the skills
to implement those good ideas. If you've got a patch to apply that's been
tested to show something is faster EVERYONE here wants to see it.
If you've got a theory, no matter how well backed up by academic research,
it's still just a theory. Until someone writes to code to implement it,
the gains are theoretical, and many things that MIGHT help don't because
of the real world issues underlying your database, like I/O bandwidth or
CPU <-> memory bandwidth.
I'm sorry to be so cranky about this, but I get tired of having to
defend one of my employer's core technologies from accusations based
on half-truths and "everybody knows" assumptions. For instance,Again, "accusations" is a bit strong. I thought the discussion was
about the technical merits and costs of various features and various
ways to implement them, particularly when this product must compete
for installed base with other solutions. Being coldly realistic about
what a product's strengths and weaknesses are is, again, just good
business. Sun Tzu's comment about knowing the enemy and yourself
seems appropriate here...
No, you're wrong. Postgresql doesn't have to compete. It doesn't have to
win. it doesn't need a marketing department. All those things are nice,
and I'm glad if it does them, but doesn't HAVE TO. Postgresql has to
work. It does that well.
Postgresql CAN compete if someone wants to put the effort into competing,
but it isn't a priority for me. Working is the priority, and if other
people aren't smart enough to test Postgresql to see if it works for them,
all the better, I keep my edge by having a near zero cost database engine,
while the competition spends money on MSSQL or Oracle.
Tom and Andrew ARE coldly realistic about the shortcomings of postgresql.
It has issues, and things that need to be fixed. It needs more coders.
It doesn't need every feature that Oracle or DB2 have. Heck some of their
"features" would be considered a mis-feature in the Postgresql world.
I'll mention thread support in passing,
there's actually a FAQ item about thread support, because in the
opinion of those who have looked at it, the cost is just not worth
the benefit. If you have evidence to the contrary (specific
evidence, please, for this application), and have already read allthe
previous discussion of the topic, perhaps people would be interested
in
opening that debate again (though I have my doubts).
Zeus had a performance ceiling roughly 3x that of Apache when Zeus
supported threading as well as pre-forking and Apache only supported
pre forking. The Apache folks now support both. DB2, Oracle, and SQL
Server all use threads. Etc, etc.
Yes, and if you configured your apache server to have 20 or 30 spare
servers, in the real world, it was nearly neck and neck to Zeus, but since
Zeus cost like $3,000 a copy, it is still cheaper to just overwhelm it
with more servers running apache than to use zeus.
That's an awful lot of very bright programmers and some serious $$
voting that threads are worth it.
For THAT application. for what a web server does, threads can be very
useful, even useful enough to put up with the problems created by running
threads on multiple threading libs on different OSes.
Let me ask you, if Zeus scrams and crashes out, and it's installed
properly so it just comes right back up, how much data can you lose?
If Postgresql scrams and crashes out, how much data can you lost?
Given all that, if PostgreSQL
specific
thread support is =not= showing itself to be a win that's an
unexpected
enough outcome that we should be asking hard questions as to why not.
There HAS been testing on threads in Postgresql. It has been covered to
death. The fact that you're still arguing proves you likely haven't read
the archive (google has it back to way back when, use that to look it up)
about this subject.
Threads COULD help on multi-sorted results, and a few other areas, but the
increase in performance really wasn't that great for 95% of all the cases,
and for the 5% it was, simple query planner improvements have provided far
greater performance increases.
The problem with threading is that we can either use the one process ->
many thread design, which I personally don't trust for something like a
database, or a process per backend connection which can run
multi-threaded. This scenario makes Postgresql just as stable and
reliable as it was as a multi-process app, but allows threaded performance
in certain areas of the backend that are parallelizable to run in parallel
on multi-CPU systems.
the gain, again, is minimal, and on a system with many users accessing it,
there is NO real world gain.
At their core, threads are a context switching efficiency tweak.
Except that on the two OSes which Postgresql runs on the most, threads are
really no faster than processes. In the Linux kernel, the only real
difference is how the OS treats them, creation, destruction of threads
versus processes is virtually identical there.
Certainly it's =possible= that threads have nothing to offer
PostgreSQL, but IMHO it's not =probable=. Just another thing for me
to add to my TODO heap for looking at...
It's been tested, it didn't help a lot, and it made it MUCH harder to
maintain, as threads in Linux are handled by a different lib than in say
Solaris, or Windows or any other OS. I.e. you can't guarantee the thread
lib you need will be there, and that there are no bugs. MySQL still has
thread bug issues pop up, most of which are in the thread libs themselves.
Ron Peacetree wrote:
Zeus had a performance ceiling roughly 3x that of Apache when Zeus
supported threading as well as pre-forking and Apache only supported
pre forking. The Apache folks now support both. DB2, Oracle, and SQL
Server all use threads. Etc, etc.
You can't use Apache as an example of why you should thread a database
engine, except for the cases where the database is used much like the
web server is: for numerous short transactions.
That's an awful lot of very bright programmers and some serious $$
voting that threads are worth it. Given all that, if PostgreSQL
specific thread support is =not= showing itself to be a win that's
an unexpected enough outcome that we should be asking hard questions
as to why not.
It's not that there won't be any performance benefits to be had from
threading (there surely will, on some platforms), but gaining those
benefits comes at a very high development and maintenance cost. You
lose a *lot* of robustness when all of your threads share the same
memory space, and make yourself vulnerable to classes of failures that
simply don't happen when you don't have shared memory space.
PostgreSQL is a compromise in this regard: it *does* share memory, but
it only shares memory that has to be shared, and nothing else. To get
the benefits of full-fledged threads, though, requires that all memory
be shared (otherwise the OS has to tweak the page tables whenever it
switches contexts between your threads).
At their core, threads are a context switching efficiency tweak.
This is the heart of the matter. Context switching is an operating
system problem, and *that* is where the optimization belongs. Threads
exist in large part because operating system vendors didn't bother to
do a good job of optimizing process context switching and
creation/destruction.
Under Linux, from what I've read, process creation/destruction and
context switching happens almost as fast as thread context switching
on other operating systems (Windows in particular, if I'm not
mistaken).
Since DB's switch context a lot under many circumstances, threads
should be a win under such circumstances. At the least, it should be
helpful in situations where we have multiple CPUs to split query
execution between.
This is true, but I see little reason that we can't do the same thing
using fork()ed processes and shared memory instead.
There is context switching within databases, to be sure, but I think
you'll be hard pressed to demonstrate that it is anything more than an
insignificant fraction of the total overhead incurred by the database.
I strongly suspect that much larger gains are to be had by optimizing
other areas of the database, such as the planner, the storage manager
(using mmap for file handling may prove useful here), the shared
memory system (mmap may be faster than System V style shared memory),
etc.
The big overhead in the process model on most platforms is in creation
and destruction of processes. PostgreSQL has a relatively high
connection startup cost. But there are ways of dealing with this
problem other than threading, namely the use of a connection caching
middleware layer. Such layers exist for databases other than
PostgreSQL, so the high cost of fielding and setting up a database
connection is *not* unique to PostgreSQL ... which suggests that while
threading may help, it doesn't help *enough*.
I'd rather see some development work go into a connection caching
process that understands the PostgreSQL wire protocol well enough to
look like a PostgreSQL backend to connecting processes, rather than
see a much larger amount of effort be spent on converting PostgreSQL
to a threaded architecture (and then discover that connection caching
is still needed anyway).
Certainly it's =possible= that threads have nothing to offer
PostgreSQL, but IMHO it's not =probable=. Just another thing for me
to add to my TODO heap for looking at...
It's not that threads don't have anything to offer. It's that the
costs associated with them are high enough that it's not at all clear
that they're an overall win.
--
Kevin Brown kevin@sysexperts.com
On Saturday 12 April 2003 03:02, you wrote:
Ron Peacetree wrote:
Zeus had a performance ceiling roughly 3x that of Apache when Zeus
supported threading as well as pre-forking and Apache only supported
pre forking. The Apache folks now support both. DB2, Oracle, and SQL
Server all use threads. Etc, etc.You can't use Apache as an example of why you should thread a database
engine, except for the cases where the database is used much like the
web server is: for numerous short transactions.
OK. Let me put my experiences. These are benchmarks on a intranet(100MBps lan)
run off a 1GHZ P-III/IV webserver on mandrake9 for a single 8K file.
apache2044: 1300 rps
boa: 4500rps
Zeus: 6500 rps.
Apache does too many things to be a speed daemon and what it offers is pretty
impressive from performance POV.
But database is not webserver. It is not suppose to handle tons of concurrent
requests. That is a fundamental difference.
That's an awful lot of very bright programmers and some serious $$
voting that threads are worth it. Given all that, if PostgreSQL
specific thread support is =not= showing itself to be a win that's
an unexpected enough outcome that we should be asking hard questions
as to why not.It's not that there won't be any performance benefits to be had from
threading (there surely will, on some platforms), but gaining those
benefits comes at a very high development and maintenance cost. You
lose a *lot* of robustness when all of your threads share the same
memory space, and make yourself vulnerable to classes of failures that
simply don't happen when you don't have shared memory space.
Well. Threading does not necessarily imply one thread per connection model.
Threading can be used to make CPU work during I/O and taking advantage of SMP
for things like sort etc. This is especially true for 2.4.x linux kernels
where async I/O can not be used for threaded apps. as threads and signal do
not mix together well.
One connection per thread is not a good model for postgresql since it has
already built a robust product around process paradigm. If I have to start a
new database project today, a mix of process+thread is what I would choose bu
postgresql is not in same stage of life.
At their core, threads are a context switching efficiency tweak.
This is the heart of the matter. Context switching is an operating
system problem, and *that* is where the optimization belongs. Threads
exist in large part because operating system vendors didn't bother to
do a good job of optimizing process context switching and
creation/destruction.
But why would a database need a tons of context switches if it is not supposed
to service loads to request simaltenously? If there are 50 concurrent
connections, how much context switching overhead is involved regardless of
amount of work done in a single connection? Remeber that database state is
maintened in shared memory. It does not take a context switch to access it.
The assumption stems from database being very efficient in creating and
servicing a new connection. I am not very comfortable with that argument.
Under Linux, from what I've read, process creation/destruction and
context switching happens almost as fast as thread context switching
on other operating systems (Windows in particular, if I'm not
mistaken).
I hear solaris also has very heavy processes. But postgresql has other issues
with solaris as well.
Since DB's switch context a lot under many circumstances, threads
should be a win under such circumstances. At the least, it should be
helpful in situations where we have multiple CPUs to split query
execution between.
Can you give an example where database does a lot of context switching for
moderate number of connections?
Shridhar
Shridhar Daithankar wrote:
Apache does too many things to be a speed daemon and what it offers
is pretty impressive from performance POV.But database is not webserver. It is not suppose to handle tons of
concurrent requests. That is a fundamental difference.
I'm not sure I necessarily agree with this. A database is just a
tool, a means of reliably storing information in such a way that it
can be retrieved quickly. Whether or not it "should" handle lots of
concurrent requests is a question that the person trying to use it
must answer.
A better answer is that a database engine that can handle lots of
concurrent requests can also handle a smaller number, but not vice
versa. So it's clearly an advantage to have a database engine that
can handle lots of concurrent requests because such an engine can be
applied to a larger number of problems. That is, of course, assuming
that all other things are equal...
There are situations in which a database would have to handle a lot of
concurrent requests. Handling ATM transactions over a large area is
one such situation. A database with current weather information might
be another, if it is actively queried by clients all over the country.
Acting as a mail store for a large organization is another. And, of
course, acting as a filesystem is definitely another. :-)
Well. Threading does not necessarily imply one thread per connection
model. Threading can be used to make CPU work during I/O and taking
advantage of SMP for things like sort etc. This is especially true
for 2.4.x linux kernels where async I/O can not be used for threaded
apps. as threads and signal do not mix together well.
This is true, but whether you choose to limit the use of threads to a
few specific situations or use them throughout the database, the
dangers and difficulties faced by the developers when using threads
will be the same.
One connection per thread is not a good model for postgresql since
it has already built a robust product around process paradigm. If I
have to start a new database project today, a mix of process+thread
is what I would choose bu postgresql is not in same stage of life.
Certainly there are situations for which it would be advantageous to
have multiple concurrent actions happening on behalf of a single
connection, as you say. But that doesn't automatically mean that a
thread is the best overall solution. On systems such as Linux that
have fast process handling, processes are almost certainly the way to
go. On other systems such as Solaris or Windows, threads might be the
right answer (on Windows they might be the *only* answer). But my
argument here is simple: the responsibility of optimizing process
handling belongs to the maintainers of the OS. Application developers
shouldn't have to worry about this stuff.
Of course, back here in the real world they *do* have to worry about
this stuff, and that's why it's important to quantify the problem.
It's not sufficient to say that "processes are slow and threads are
fast". Processes on the target platform may well be slow relative to
other systems (and relative to threads). But the question is: for the
problem being solved, how much overhead does process handling
represent relative to the total amount of overhead the solution itself
incurs?
For instance, if we're talking about addressing the problem of
distributing sorts across multiple CPUs, the amount of overhead
involved in doing disk activity while sorting could easily swamp, in
the typical case, the overhead involved in creating parallel processes
to do the sorts themselves. And if that's the case, you may as well
gain the benefits of using full-fledged processes rather than deal
with the problems that come with the use of threads -- because the
gains to be found by using threads will be small in relative terms.
At their core, threads are a context switching efficiency tweak.
This is the heart of the matter. Context switching is an operating
system problem, and *that* is where the optimization belongs. Threads
exist in large part because operating system vendors didn't bother to
do a good job of optimizing process context switching and
creation/destruction.But why would a database need a tons of context switches if it is
not supposed to service loads to request simaltenously? If there are
50 concurrent connections, how much context switching overhead is
involved regardless of amount of work done in a single connection?
Remeber that database state is maintened in shared memory. It does
not take a context switch to access it.
If there are 50 concurrent connections with one process per
connection, then there are 50 database processes. The context switch
overhead is incurred whenever the current process blocks (or exhausts
its time slice) and the OS activates a different process. Since
database handling is generally rather I/O intensive as services go,
relatively few of those 50 processes are likely to be in a runnable
state, so I would expect the overall hit from context switching to be
rather low -- I'd expect the I/O subsystem to fall over well before
context switching became a real issue.
Of course, all of that is independent of whether or not the database
can handle a lot of simultaneous requests.
Under Linux, from what I've read, process creation/destruction and
context switching happens almost as fast as thread context switching
on other operating systems (Windows in particular, if I'm not
mistaken).I hear solaris also has very heavy processes. But postgresql has
other issues with solaris as well.
Yeah, I didn't want to mention Solaris because I haven't kept up with
it and thought that perhaps they had fixed this...
--
Kevin Brown kevin@sysexperts.com
On Saturday 12 April 2003 16:24, you wrote:
A better answer is that a database engine that can handle lots of
concurrent requests can also handle a smaller number, but not vice
versa. So it's clearly an advantage to have a database engine that
can handle lots of concurrent requests because such an engine can be
applied to a larger number of problems. That is, of course, assuming
that all other things are equal...There are situations in which a database would have to handle a lot of
concurrent requests. Handling ATM transactions over a large area is
one such situation. A database with current weather information might
be another, if it is actively queried by clients all over the country.
Acting as a mail store for a large organization is another. And, of
course, acting as a filesystem is definitely another. :-)
Well, there is another aspect one should consider. Tuning a database engine
for a specifiic workload is a hell of a job and shifting it to altogether
other end of paradigm must be justified.
OK. Postgresql is not optimised to handle lots of concurrent connections, at
least not much to allow one apache request handler to use a connection. Then
middleware connection pooling like done in php might be a simpler solution to
go rather than redoing the postgresql stuff. Because it works.
This is true, but whether you choose to limit the use of threads to a
few specific situations or use them throughout the database, the
dangers and difficulties faced by the developers when using threads
will be the same.
I do not agree. Let's say I put threading functions in posgresql that do not
touch shared memory interface at all. They would be hell lot simpler to code
and mainten than converting postgresql to one thread per connection model.
Of course, back here in the real world they *do* have to worry about
this stuff, and that's why it's important to quantify the problem.
It's not sufficient to say that "processes are slow and threads are
fast". Processes on the target platform may well be slow relative to
other systems (and relative to threads). But the question is: for the
problem being solved, how much overhead does process handling
represent relative to the total amount of overhead the solution itself
incurs?
That is correct. However it would be a fair assumption on part of postgresql
developers that a process once setup does not have much of processing
overhead involved as such, given the state of modern server class OS and
hardware. So postgresql as it is, fits in that model. I mean it is fine that
postgresql has heavy connections. Simpler solution is to pool them.
That gets me wondering. Has anybody ever benchmarked how much a database
connection weighs in terms of memory/CPU/IO BW. for different databases on
different platforms? Is postgresql really that slow?
Shridhar
Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
But database is not webserver. It is not suppose to handle tons of concurrent
requests. That is a fundamental difference.
And in one fell swoop you've dismissed the entire OLTP database industry.
Have you ever called a travel agent and had him or her look up a fare in the
airline database within seconds? Ever placed an order over the telephone?
Ever used a busy database-backed web site?
On database-backed web sites, probably the main application for databases
today, almost certainly the main application for free software databases,
every web page request translates into at least one, probably several database
queries.
All those database queries must complete within a limited time, measured in
milliseconds. When they complete another connection needs to be context
switched in and run again within milliseconds.
On a busy web site the database machine will have several processors and be
processing queries for several web pages simultaneously, but what really
matters is precisely the context switch time between one set of queries and
another.
The test I'm most interested in in the benchmarks effort is simply an index
lookup or update of a single record from a large table. How many thousands of
transactions per second is postgres going to be able to handle on the same
machine as mysql and oracle? How many hundreds of thousands of transactions
per second will they be able to handle on a 4 processor hyperthreaded machine
with a raid array striped across ten disks?
--
greg
