Changing pg_type records
Greetings!
I just discovered the existence of a couple of functions I don't
understand in one customer's PostgreSQL database:
-- Function: c_mode()
-- DROP FUNCTION c_mode();
CREATE OR REPLACE FUNCTION c_mode()
RETURNS text AS
$BODY$ UPDATE pg_type SET typoutput='c_textout' WHERE typname='SET';
UPDATE pg_type SET typoutput='c_varcharout' WHERE
typname='bpchar';
UPDATE pg_type SET typoutput='c_textout' WHERE typname='bytea';
UPDATE pg_type SET typoutput='c_charout' WHERE typname='char';
UPDATE pg_type SET typoutput='c_textout' WHERE typname='text';
UPDATE pg_type SET typoutput='c_textout' WHERE
typname='unknown';
UPDATE pg_type SET typoutput='c_varcharout' WHERE
typname='varchar';
select 'c_mode'::text;$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION c_mode() OWNER TO postgres;
-- Function: pg_mode()
-- DROP FUNCTION pg_mode();
CREATE OR REPLACE FUNCTION pg_mode()
RETURNS text AS
$BODY$ UPDATE pg_type SET typoutput='textout' WHERE typname='SET';
UPDATE pg_type SET typoutput='varcharout' WHERE typname='bpchar';
UPDATE pg_type SET typoutput='textout' WHERE typname='bytea';
UPDATE pg_type SET typoutput='charout' WHERE typname='char';
UPDATE pg_type SET typoutput='textout' WHERE typname='text';
UPDATE pg_type SET typoutput='textout' WHERE typname='unknown';
UPDATE pg_type SET typoutput='varcharout' WHERE typname='varchar';
select 'pg_mode'::text;$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION pg_mode() OWNER TO postgres;
This customer has demanded extensive modifications to their database and
our application. I think these functions are used in encryption, since
there are several other non-standard functions in this database that are
related to encryption.
Our lead developer is in another state, and is naturally very
uncommunicative. When I asked him about the c_mode() function and
expressed concern that changing pg_type records might not be a good
idea, his reply was:
"Ignore it, it is a database contrib routine for string I/O. It
doesn't change the database properties."
Are these functions really as innocuous as he claims? It seems to me
that between the time c_mode() is called and the time that pg_mode() is
called, any other database access is likely to be confused.
Thank you very much.
RobR
"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
I just discovered the existence of a couple of functions I don't
understand in one customer's PostgreSQL database:
What do the substitute output functions do differently?
UPDATE pg_type SET typoutput='c_textout' WHERE typname='SET';
This one has been a no-op since 7.4, because there is no longer any such
row in pg_type; and I'm pretty sure the row was vestigial long before that.
If the underlying code is old enough that it was designed when changing
that row did something interesting, it certainly all needs a fresh look
anyway...
Are these functions really as innocuous as he claims? It seems to me
that between the time c_mode() is called and the time that pg_mode() is
called, any other database access is likely to be confused.
Yes, you definitely are taking big risks there. Aside from the question
of how data appears to the client, this could bollix the behavior of
most PL languages too.
regards, tom lane
Hi. I'm writing up a database comparison paper in my department at
work, with Postgres being a major candidate. I have been attempting to
research various issues and provide a meaningful comparison.
One issue I would like to give some kind of information on is
comparative performance. When I look for such things in Google or
Wikipedia or the pgsql email archives, it's hard to find anything
reasonably definitive. I've found isolated claims and anecdotes here
and there, and a fellow on the list here who attempted to do a
comparison between Postgres, MySQL, and Oracle but gave it up for now.
Some of the claims I've seen said that in some cases MySQL with MyISAM
ran 2x faster than Postgres, but that may have been for a special case
with only read access to the database; whereas another one claimed that
MySQL with InnoDB was slower than Postgres. Other people commented that
it depends on how you tune the databases.
Maybe there's nothing definitive out there. However I'd like to get a
ballpark idea of how some databases compare, using some kind of average
case schema and application, in terms of transactions per second, on a
common hardware platform. I would like to be able to point to a
reasonable reference, rather than engaging in handwaving myself.
Does anyone know where I could look?
-Will
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Will Rutherdale (rutherw)
Sent: Thursday, March 19, 2009 1:32 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Is there a meaningful benchmark?Hi. I'm writing up a database comparison paper in my department at
work, with Postgres being a major candidate. I have been attempting
to
research various issues and provide a meaningful comparison.
One issue I would like to give some kind of information on is
comparative performance. When I look for such things in Google or
Wikipedia or the pgsql email archives, it's hard to find anything
reasonably definitive. I've found isolated claims and anecdotes here
and there, and a fellow on the list here who attempted to do a
comparison between Postgres, MySQL, and Oracle but gave it up for now.Some of the claims I've seen said that in some cases MySQL with MyISAM
ran 2x faster than Postgres, but that may have been for a special case
with only read access to the database; whereas another one claimed
that
MySQL with InnoDB was slower than Postgres. Other people commented
that
it depends on how you tune the databases.Maybe there's nothing definitive out there. However I'd like to get a
ballpark idea of how some databases compare, using some kind of
average
case schema and application, in terms of transactions per second, on a
common hardware platform. I would like to be able to point to a
reasonable reference, rather than engaging in handwaving myself.Does anyone know where I could look?
The only way to get an answer to a question like this is to actually
benchmark the application you have in mind.
And the answer won't be very good unless you have an expert on each
given system install and tune the application.
There is a regular benchmark that is run against the PostgreSQL
database. I don't remember where to find the graphs. Probably, someone
on the list can tell us the location.
Here are some benchmark figures:
http://tweakers.net/reviews/657/6
http://www.spec.org/jAppServer2004/results/res2007q3/jAppServer2004-2007
0606-00065.html
http://www.informationweek.com/news/software/linux/showArticle.jhtml?art
icleID=201001901
http://www.kaltenbrunner.cc/blog/index.php?/archives/21-guid.html
http://benchw.sourceforge.net/benchw_results_open3.html
P.S.
PostgreSQL seems to scale pretty well:
http://www.computerworld.com/action/article.do?command=viewArticleBasic&
taxonomyId=18&articleId=9087918&intsrc=hm_topic
My opinion:
Most benchmarks are run by someone with an axe to grind. I never
believe them. The TPC benchmarks are probably the most trustworthy,
because they have to be certified. But a fast TPC/whatever benchmark is
no guarantee that *your* application will run fast. So if you want to
evaluation several different technologies do your own benchmark. Do
your own calculations to find out the total cost of ownership over the
lifetime of the project. Examine all the features that are available,
and what kind of technical support is possible. Consider the impact of
product licensing. What happens if you need to scale up to titanic
volume? After you have thought all factors over very carefully, make
your choice.
If you rely on someone else to do the work for you, it's really begging
for trouble. MySQL guys will show you why MySQL is faster. PostgreSQL
guys will show you why PostgreSQL is faster. Oracle guys will show you
why Oracle is faster. SQL*Server guys will show you why SQL*Server is
faster. DB/2 guys will show you why DB/2 is faster. Now, none of them
are lying (at least hopefully) but they are experts in their own domain
and not in the domain of the other product and they are also going to
choose those tight little corners where their product has the biggest
advantage.
IMO-YMMV.
P.S.
I'm a PostgreSQL fan and so I am likely to (perhaps unconsciously) favor
PostgreSQL in my remarks.
Comparison between MySQL using the MyISAM engine with PostgreSQL is really
not sensible. For one, the MyISAM engine does not have transaction and
foreign key support, while PostgreSQL supports transaction and foreign key.
Would anyone really give up transaction and integrity for slightly more
performance?
So if it makes your research easier, you can rule MySQL w/MyISAM out as an
option.
On Thu, Mar 19, 2009 at 1:32 PM, Will Rutherdale (rutherw) <
rutherw@cisco.com> wrote:
Hi. I'm writing up a database comparison paper in my department at
work, with Postgres being a major candidate. I have been attempting to
research various issues and provide a meaningful comparison.One issue I would like to give some kind of information on is
comparative performance. When I look for such things in Google or
Wikipedia or the pgsql email archives, it's hard to find anything
reasonably definitive. I've found isolated claims and anecdotes here
and there, and a fellow on the list here who attempted to do a
comparison between Postgres, MySQL, and Oracle but gave it up for now.Some of the claims I've seen said that in some cases MySQL with MyISAM
ran 2x faster than Postgres, but that may have been for a special case
with only read access to the database; whereas another one claimed that
MySQL with InnoDB was slower than Postgres. Other people commented that
it depends on how you tune the databases.Maybe there's nothing definitive out there. However I'd like to get a
ballpark idea of how some databases compare, using some kind of average
case schema and application, in terms of transactions per second, on a
common hardware platform. I would like to be able to point to a
reasonable reference, rather than engaging in handwaving myself.Does anyone know where I could look?
-Will
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
- John L Cheng
On Thu, Mar 19, 2009 at 3:16 PM, Dann Corbit <DCorbit@connx.com> wrote:
Here are some benchmark figures:
http://tweakers.net/reviews/657/6
SNIP
My opinion:
Most benchmarks are run by someone with an axe to grind. I never
believe them.
Generally I agree with that sentiment. However, it's interesting that
the guys at tweakers.net run (or ran?) their chat board on mysql, and
expected it to win. They certainly didn't expect the kind of stomping
that postgresql gave to mysql, so I look at that benchmark as a
"statement against interest" kind of thing. They were genuinely
surprised that postgresql didn't just win, but behaved much much
better than mysql under heavier loads.
It isn't actually possible at this stage for me to benchmark "the
application" because it doesn't yet exist. There are a number of
potential projects floating around, with as yet unwritten
specifications, to run on different platforms ranging from embedded to
larger servers. People just want to hear what I think is a good RDBMS
to use. My opinion won't necessarily be followed.
Nobody at this point is expecting the RDBMS to become a bottleneck, if
they are planning to actually use one at all. However someone is sure
to ask the question, for an average application with an average
database, how is performance?
Even if such a question is answered, it isn't going to be the only
factor. For example I have collected reasonable numbers already on
footprints of different RDBMSs, because embedded guys might find that
important if they're restricted to 64MB of flash. On the other hand if
they went with some of the newer solid state drives with gigs of space,
then a few packages using 10s of MB wouldn't be such a problem any more.
In short, all bets are off and I'm just looking for baseline
information. This is just a general feasibility and technology
exploration phase.
I'm aware of the limitations of hard numbers, but the more simple
information I have in different dimensions, the easier it is to convince
people not to lock in too early.
Thanks for the info, I'll check some of those references.
-Will
-----Original Message-----
From: Dann Corbit [mailto:DCorbit@connx.com]
Sent: 19 March 2009 17:16
To: Will Rutherdale (rutherw); pgsql-general@postgresql.org
Subject: RE: [GENERAL] Is there a meaningful benchmark?
The only way to get an answer to a question like this is to actually
benchmark the application you have in mind.
And the answer won't be very good unless you have an expert on each
given system install and tune the application.
There is a regular benchmark that is run against the PostgreSQL
database. I don't remember where to find the graphs. Probably, someone
on the list can tell us the location.
Here are some benchmark figures:
http://tweakers.net/reviews/657/6
http://www.spec.org/jAppServer2004/results/res2007q3/jAppServer2004-2007
0606-00065.html
http://www.informationweek.com/news/software/linux/showArticle.jhtml?art
icleID=201001901
http://www.kaltenbrunner.cc/blog/index.php?/archives/21-guid.html
http://benchw.sourceforge.net/benchw_results_open3.html
P.S.
PostgreSQL seems to scale pretty well:
http://www.computerworld.com/action/article.do?command=viewArticleBasic&
taxonomyId=18&articleId=9087918&intsrc=hm_topic
My opinion:
Most benchmarks are run by someone with an axe to grind. I never
believe them. The TPC benchmarks are probably the most trustworthy,
because they have to be certified. But a fast TPC/whatever benchmark is
no guarantee that *your* application will run fast. So if you want to
evaluation several different technologies do your own benchmark. Do
your own calculations to find out the total cost of ownership over the
lifetime of the project. Examine all the features that are available,
and what kind of technical support is possible. Consider the impact of
product licensing. What happens if you need to scale up to titanic
volume? After you have thought all factors over very carefully, make
your choice.
If you rely on someone else to do the work for you, it's really begging
for trouble. MySQL guys will show you why MySQL is faster. PostgreSQL
guys will show you why PostgreSQL is faster. Oracle guys will show you
why Oracle is faster. SQL*Server guys will show you why SQL*Server is
faster. DB/2 guys will show you why DB/2 is faster. Now, none of them
are lying (at least hopefully) but they are experts in their own domain
and not in the domain of the other product and they are also going to
choose those tight little corners where their product has the biggest
advantage.
IMO-YMMV.
P.S.
I'm a PostgreSQL fan and so I am likely to (perhaps unconsciously) favor
PostgreSQL in my remarks.
On Thu, Mar 19, 2009 at 3:50 PM, Will Rutherdale (rutherw)
<rutherw@cisco.com> wrote:
Even if such a question is answered, it isn't going to be the only
factor. For example I have collected reasonable numbers already on
footprints of different RDBMSs, because embedded guys might find that
important if they're restricted to 64MB of flash. On the other hand if
they went with some of the newer solid state drives with gigs of space,
then a few packages using 10s of MB wouldn't be such a problem any more.
If you're looking at embedded usage, and footprint is an issue (it
usually is even if you think it won't be) look at sqllite. Pretty
good embedded db and lightweight. Pgsql is not intended to compete in
the embedded space.
I'm having trouble with the tweakers reference below.
I was hoping to see something where hardware platform is held constant while RDBMS is varied, but it seems to be just the opposite. Or maybe I didn't read the article the right way.
-Will
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: 19 March 2009 17:36
To: Dann Corbit
Cc: Will Rutherdale (rutherw); pgsql-general@postgresql.org
Subject: Re: [GENERAL] Is there a meaningful benchmark?
On Thu, Mar 19, 2009 at 3:16 PM, Dann Corbit <DCorbit@connx.com> wrote:
Here are some benchmark figures:
http://tweakers.net/reviews/657/6
SNIP
My opinion:
Most benchmarks are run by someone with an axe to grind. I never
believe them.
Generally I agree with that sentiment. However, it's interesting that
the guys at tweakers.net run (or ran?) their chat board on mysql, and
expected it to win. They certainly didn't expect the kind of stomping
that postgresql gave to mysql, so I look at that benchmark as a
"statement against interest" kind of thing. They were genuinely
surprised that postgresql didn't just win, but behaved much much
better than mysql under heavier loads.
I am *not* primarily interested in embedded, but I know people who are, and I have already compared with SQLite.
My main point of concern right now is for more middle sized platforms (such as an average workstation), to be able to answer the question of how Postgres shows in transactions per second against another RDBMS or two.
-Will
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: 19 March 2009 17:57
To: Will Rutherdale (rutherw)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Is there a meaningful benchmark?
On Thu, Mar 19, 2009 at 3:50 PM, Will Rutherdale (rutherw)
<rutherw@cisco.com> wrote:
Even if such a question is answered, it isn't going to be the only
factor. For example I have collected reasonable numbers already on
footprints of different RDBMSs, because embedded guys might find that
important if they're restricted to 64MB of flash. On the other hand if
they went with some of the newer solid state drives with gigs of space,
then a few packages using 10s of MB wouldn't be such a problem any more.
If you're looking at embedded usage, and footprint is an issue (it
usually is even if you think it won't be) look at sqllite. Pretty
good embedded db and lightweight. Pgsql is not intended to compete in
the embedded space.
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Scott Marlowe
Sent: Thursday, March 19, 2009 2:57 PM
To: Will Rutherdale (rutherw)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Is there a meaningful benchmark?On Thu, Mar 19, 2009 at 3:50 PM, Will Rutherdale (rutherw)
<rutherw@cisco.com> wrote:Even if such a question is answered, it isn't going to be the only
factor. For example I have collected reasonable numbers already on
footprints of different RDBMSs, because embedded guys might find that
important if they're restricted to 64MB of flash. On the other handif
they went with some of the newer solid state drives with gigs of
space,
then a few packages using 10s of MB wouldn't be such a problem any
more.
If you're looking at embedded usage, and footprint is an issue (it
usually is even if you think it won't be) look at sqllite. Pretty
good embedded db and lightweight. Pgsql is not intended to compete in
the embedded space.
FastDB is another good option there (it's a portable, embedded memory mapped database):
http://www.garret.ru/fastdb.html
An advantage for the SQLite option is that the grammar is a subset of PostgreSQL grammar, so if you need to scale up, you have a ready path.
On Thu, Mar 19, 2009 at 4:02 PM, Will Rutherdale (rutherw)
<rutherw@cisco.com> wrote:
I am *not* primarily interested in embedded, but I know people who are, and I have already compared with SQLite.
My main point of concern right now is for more middle sized platforms (such as an average workstation), to be able to answer the question of how Postgres shows in transactions per second against another RDBMS or two.
Way back right after the earth's crust had cooled I deployed a
corporate intranet on pgsql 6.5.2. I believe we had Wooly Mammoth for
dinner that night. It took a fair bit of work to keep that machine
happy, and every new version was an eye opener in terms of
performance, reliability, and capability improvements. By the time
8.0 came out I was more than prepared to use it for some pretty hefty
work. Now that 8.3 is out and 8.4 is out, for any kind of
intermediate size application (thousands of users daily, hundreds of
gigs of data) I'll put PostgreSQL against any other DBMS and expect it
to do well.
Where I work now, we handle 1.5 million or so users, a large chunk of
which log in each day, several times a day. We had to beef up our
servers to 8 core / 16 drive machines (two of them) to handle the load
reliably. We have enough spare capacity to handle about 3 to 4 times
the number of users we now have.
You are far more likely to be bitten by lack of familiarity with ANY
db you choose, and the real issue will be support and training.
Oracle, Pgsql, DB2, Interbase / Firebird, MySQL w/ innodb can all
handle the kind of load you've kind of hand waved about here. How
much money and time you'll spend setting them up, learning them,
supporting them, and using them will be far more important than
anything a benchmark is likely to tell you right now.
I am already aware of this issue, and am preparing to explain it to
people.
Having said that, if it were possible to set up a reasonably average
database, with a test application that hits it with a reasonable mix of
select, insert, and update operations, and run it one at a time against
different RDBMSs on the same machine, then it might yield some simple
numbers that could be quoted to people in case they asked.
The goal is not to absolutely determine which is fastest in the made-up
scenario, I don't think anyone cares. However it would be interesting
to see if the different RDBMSs came in within a reasonable percentage of
each other.
An analogy would be BogoMIPS. Nobody takes it that seriously because
they know there are numerous factors that affect how a machine runs
under different applications. But as a quick sanity check BogoMIPS can
be useful at times.
-Will
________________________________
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of John Cheng
Sent: 19 March 2009 17:27
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Is there a meaningful benchmark?
Comparison between MySQL using the MyISAM engine with PostgreSQL is
really not sensible. For one, the MyISAM engine does not have
transaction and foreign key support, while PostgreSQL supports
transaction and foreign key. Would anyone really give up transaction and
integrity for slightly more performance?
On Thu, Mar 19, 2009 at 3:56 PM, Will Rutherdale (rutherw)
<rutherw@cisco.com> wrote:
I'm having trouble with the tweakers reference below.
I was hoping to see something where hardware platform is held constant while RDBMS is varied, but it seems to be just the opposite. Or maybe I didn't read the article the right way.
The tweakers test is actually VERY telling. With a constant load, how
well do the database scale as you improve the hardware you've given
them to run on. Hardware is cheap, downtime is not. If you can toss
a 16 core server at a performance problem for $20,000 or so, that's
probably way cheaper than watching your main db chug under load and go
down twice a day.
Conversely, telling the bossman you need that 16 core server to
improve performance and seeing the new server collapse under load
faster than the old one due to poor concurrency is not gonna win you a
lot of brownie points.
On Thu, Mar 19, 2009 at 4:11 PM, Will Rutherdale (rutherw)
<rutherw@cisco.com> wrote:
I am already aware of this issue, and am preparing to explain it to people.
Well, keep in mind that MOST people are gonna wave you off, and figure
it doesn't matter that much. Lots of developers are pretty cavalier
with their user's data. Now, if the data is easily replaceable, then
fine. Put a price on the data. Pay attention to that price, and
don't underestimate it. It's usually the most expensive thing on a db
server.
Having said that, if it were possible to set up a reasonably average
database, with a test application that hits it with a reasonable mix of
select, insert, and update operations, and run it one at a time against
different RDBMSs on the same machine, then it might yield some simple
numbers that could be quoted to people in case they asked.
But will you be comparing apples to apples. MySQL plays fast and
loose with data integrity (with myisam tables). So at least make sure
you're comparing what you need to compare. If data integrity is
important, you need to use innodb. If it isn't, then just use flat
files.
The goal is not to absolutely determine which is fastest in the made-up
scenario, I don’t think anyone cares. However it would be interesting to
see if the different RDBMSs came in within a reasonable percentage of each
other.
But again, if you're comparing a Go kart to a pickup truck, you need
to know that's what you're doing. The abstract numbers mean little
outside of that fact.
An analogy would be BogoMIPS. Nobody takes it that seriously because they
know there are numerous factors that affect how a machine runs under
different applications. But as a quick sanity check BogoMIPS can be useful
at times.
Sorry, but I respectfully disagree. Bogomips has the word bogo in it
on purpose. It means, literally, almost nothing useful to the user.
Any modern db is fast enough for the kind of low level stuff you've
mentioned so far. Until you have a better idea what your
application(s) might look like, it's hard to offer any real advice
besides "avoid myisam"
On Thu, Mar 19, 2009 at 3:26 PM, John Cheng <chonger.cheng@gmail.com> wrote:
Comparison between MySQL using the MyISAM engine with PostgreSQL is really
not sensible. For one, the MyISAM engine does not have transaction and
foreign key support, while PostgreSQL supports transaction and foreign key.
Would anyone really give up transaction and integrity for slightly more
performance?
Sure, if the application fit. If I have to load 100Meg files into a
db, run some simple extraction on them, and output it back out, and
can recreate all my data at the drop of a hat, then mysql / myisam
might be a good match.
I am no a fan of MySQL, more because the company behind it seems to be
lost and drifting than the db itself. Bugs that are 5 years old
finally getting fixed after Monty chided them in december? Come on,
PostgreSQL comes out with a near bug free new version every 1 to 2
years. PostgreSQL stomps bugs in hours or days that MySQL AB takes
YEARS to fix, and then get reintroduced (see the order by on innodb
indexed field debacle for that story) and I just don't trust the
company or the db for anything complex. But as a tool it has some
uses that it's good enough at I could use it if I had to.
Okay, you've given me some useful information.
As the original subject line indicates, I'm open to the idea that no such benchmark exists.
If anyone asks about this stuff, I can just say that performance varies widely by database and application, that Postgres performs well enough against other RDBMSs, that Postgres is known to scale up well and make good use of concurrency, and that I couldn't find any clear benchmark results to back it up.
Of course, if I *did* find any benchmark values then I could have used that to dispel false rumours from the MySQL guys. However it looks like simple measured indicators aren't easy to come by.
-Will
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: 19 March 2009 18:14
To: Will Rutherdale (rutherw)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Is there a meaningful benchmark?
On Thu, Mar 19, 2009 at 3:56 PM, Will Rutherdale (rutherw)
<rutherw@cisco.com> wrote:
I'm having trouble with the tweakers reference below.
I was hoping to see something where hardware platform is held constant while RDBMS is varied, but it seems to be just the opposite. Or maybe I didn't read the article the right way.
The tweakers test is actually VERY telling. With a constant load, how
well do the database scale as you improve the hardware you've given
them to run on. Hardware is cheap, downtime is not. If you can toss
a 16 core server at a performance problem for $20,000 or so, that's
probably way cheaper than watching your main db chug under load and go
down twice a day.
Conversely, telling the bossman you need that 16 core server to
improve performance and seeing the new server collapse under load
faster than the old one due to poor concurrency is not gonna win you a
lot of brownie points.
Here is another interesting benchmark with a particular user's
application:
http://blog.page2rss.com/2007/01/postgresql-vs-mysql-performance.html
P.S.
Oracle won't let you publish any benchmark numbers.
So if you find an Oracle comparison, it's "unauthorized"
On Thu, Mar 19, 2009 at 4:24 PM, Will Rutherdale (rutherw)
<rutherw@cisco.com> wrote:
Okay, you've given me some useful information.
As the original subject line indicates, I'm open to the idea that no such benchmark exists.
If anyone asks about this stuff, I can just say that performance varies widely by database and application, that Postgres performs well enough against other RDBMSs, that Postgres is known to scale up well and make good use of concurrency, and that I couldn't find any clear benchmark results to back it up.
Of course, if I *did* find any benchmark values then I could have used that to dispel false rumours from the MySQL guys. However it looks like simple measured indicators aren't easy to come by.
Well, the tweakers benchmarks are a pretty good mysql pgsql
comparison, although getting older now. Apparently mysql has improved
a lot of their concurrency issues that were uncovered in that one.
Pgsql has just continued to get faster.
Google MySQL gotchas for a list of reasons to avoid it. There's a
pgsql gotcha list, it's shorter, and the gotchas are a lot less likely
to induce a case of the vapors.
This looks similar to things I've seen before. MyISAM can be made to
look twice as fast as Postgres if the application is cooked to throw
away transaction processing, updates, and referential integrity, none of
which MyISAM seems to support well.
I plan to make a point of this to people, as I personally have
experience working with RDBMSs in the past and understand the importance
of these capabilities. However not everyone I talk to will have any
experiences with databases and understand the issues.
That's why I was looking for a more balanced benchmark that exercises
said capabilities.
-Will
-----Original Message-----
From: Dann Corbit [mailto:DCorbit@connx.com]
Sent: 19 March 2009 18:26
To: Scott Marlowe; Will Rutherdale (rutherw)
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] Is there a meaningful benchmark?
Here is another interesting benchmark with a particular user's
application:
http://blog.page2rss.com/2007/01/postgresql-vs-mysql-performance.html