mysql-pgsql comparison

Le Dimanche 13 Janvier 2002 09:53, Michael Meskes a �crit :

The MySQL developers tried to make the benchmarks as fair as possible.
Unfortunately PostgreSQL never answered to the repeated questions about
optimal tuning.

Yeah. This article was probably written directly by the MySQL team. This is
what you learn in the computing business. If you want someting published ..
write it yourself and invite the journalist in a restaurant. I did it 2 or 3
times when working for a telecommunication company.

Don't worry about the results of such an article. It is simply not possible
to benchmark MySQL against PostgreSQL because MySQL lacks many PostgreSQL
features. A serious user immediatly understands this.

Lately I resigned from the MySQL list with some comment like "sorry I use
PostgreSQL". Monthy replied me with a series of ready-made-emails explaining
why MySQL was superior and all this crap. When you think MySQL is not a
transactional database, it makes you laught. I had to send hime emails like
"Thank you for this answer and Happy new year" and all this stuff to stop the
spam.

The point of view is : MySQL is just a trademark and a marketing sofware.
Monthy is prepared to spend hours selling his crap.

Why don't you start a mailing list for "Propaganda" and "Public relations".
It should be possible to meet the main journalists and contact them on
regular basis with ready-to-publish articles.

With a network of one person by country (US, Canada, France,Germany, Italy)
you would improve dramaticaly the presence of PostgreSQL in the world. It is
not difficult to have articles published ...

Bes regards,
Jean-Michel POURE

I am more concerned with the PHP/MySQL connection. It seems like it is a
defacto grouping. Engineers I know, who do not know SQL, will just use MySQL
because "everyone else does."

Invariably, MySQL (at first) seems like a good decision, then they have to do a
complex query. MySQL just can not execute the queries that a real SQL database
can. If an engineer does not know SQL, he will walk away thinking SQL is a
limiting environment.

An contractor came on to the company with a solid MySQL background. He was a
bright guy, but we are an Oracle/PostgreSQL shop. More than a few times, I had
to rewrite his code to use the SQL engine with a more complex, but overall more
efficient, query. His response is always, "Wow I didn't know you could do
that."

The word that needs to be heard is that a real SQL database is easier than a
simplistic SQL-like environment such as MySQL. I have tried several projects
with MySQL and have never been able to finish them with it, because MySQL is
just too limited.

Michael Meskes writes:

I just read one of those MySQL-PGSQL comparison on a German Linux magazine
that's not exactly known to be well informed. :-)

I think the article you're speaking of is here:

http://www.linuxenterprise.de/artikel/my_sql.pdf

The last part of this, which is the supposed comparison, was pretty much
copied straight out of their manual. I have a response to that here:

http://www.ca.postgresql.org/~petere/comparison.html

However, if you don't look hard enough, it's actually true that the only
benchmark that runs on both systems is the MySQL benchmark. I think the
OSDB benchmark should also work, though.

What we could do is:

1. Actually run the MySQL benchmark, to see what's wrong with it. (Note
that this is not the crashme test.)

2. Port pgbench to MySQL. Pgbench seems to be our daily benchmark of
choice, so it's not unimportant to verify how it does elsewhere.

3. Check out the OSDB benchmark more closely.

I think I should also submit my article to their interactive docs, so that
those that do comparisons in the future by just reading the manual will
get a clue.

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:

What we could do is:

1. Actually run the MySQL benchmark, to see what's wrong with it. (Note
that this is not the crashme test.)

This would be a worthwhile thing to try, just to see if it exposes any
PG weaknesses we didn't already know about. I have run crashme in the
past (not recently though); but I've never found time for their
benchmark.

2. Port pgbench to MySQL. Pgbench seems to be our daily benchmark of
choice, so it's not unimportant to verify how it does elsewhere.

We use pgbench mainly because it happens to be sitting in contrib ;-).
I'm not convinced that it's a really good benchmark. It certainly
emphasizes performance of only a very small part of the system. For
example, we could make a huge improvement in pgbench results just by
fixing the repeated-index-lookups-of-dead-tuples problem that's been
discussed so often. But I'm not sure that that problem is as bad in
the real world as it is in pgbench.

3. Check out the OSDB benchmark more closely.

I've been planning to take a hard look at that myself, but haven't found
the time. If it's reasonably easy to install and run, it probably ought
to become our standard benchmarking tool. (For anyone who wants to
take a look, OSDB lives at osdb.sourceforge.net.)

regards, tom lane

On Sun, 13 Jan 2002, Tom Lane wrote:

3. Check out the OSDB benchmark more closely.

I've been planning to take a hard look at that myself, but haven't found
the time. If it's reasonably easy to install and run, it probably ought
to become our standard benchmarking tool. (For anyone who wants to
take a look, OSDB lives at osdb.sourceforge.net.)

I tried it with current CVS and make test fails (didn't tried
to find what's the problem). Also, you have to edit configure script -
it tries to find postgres_fe.h in include directory, which now lives under
include/internal directory.

Error in test load() at (14252)osdb-pg.c:314:
... cmd
PQresultStatus: 7
postgres reports: ERROR: copy: line 740, value too long for type character varying(80)
OSDB_ERROR: ERR_DML, (null)
***** osdb-pg-emb *****

Error in test Counting tuples at (14285)../../osdb.c:280:
... empty database -- empty results
perror() reports: No child processes
OSDB_ERROR.error: 0***** osdb-pg-ui *****

Error in test Counting tuples at (14323)../../osdb.c:280:
... empty database -- empty results
perror() reports: Resource temporarily unavailable
make[1]: Leaving directory /db1/u/megera/app/pgsql/bench/osdb/cvs/osdb'

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Yeah. This article was probably written directly by the MySQL team. This is
what you learn in the computing business. If you want someting published ..
write it yourself and invite the journalist in a restaurant. I did it 2 or 3
times when working for a telecommunication company.

On a related note, I've been thinking about "Linux Journal" or
"Linux Magazine" level article on user-defined types and functions
and how they can make life much easier.

A related topic are views and rules, again how they can be used
to eliminate otherwise difficult problems.

My PKIX stuff is a good example of this. I defy anyone to implement
the following table definitions in MySQL:

create table certs (
cert x509 not null,

key text not null unique
constraint check (key = iands_hash(cert)),

subject text not null unique
constraint check (subject = subject(cert)),

issuer text not null
constraint check (issuer = issuer(cert)),

primary key(key),

foreign key(issuer) references certs(subject) deferrable
);

create view cert_insert as select cert from certs;

create rule certi as on insert into cert_insert do instead
insert into certs (cert, key, subject, issuer)
values (new.cert, iands_hash(new.cert), subject(new.cert),
issuer(new.cert));

There's a performance hit with all of these constraints,
of course, but that's more than offset by the confidence
that I (as database developer) can have in the database as
a whole. A solid database means that I can keep my application
code thin.

(As an aside, I hope to get release 0.4 out this evening; it
documents all PKIX types and functions and includes support
for public key encryption. But I digress....)

The problem with this example is that it's too obscure - it
gets people who understand this domain excited, but everyone
else gets sidetracked by it.

Can anyone think of a better example? I've come up with
three possibilities, each with its own problems:

- geographical positions - latitude, longitude and possibly
elevation, with related functions. But few constraints
make sense.

- credit cards. Constraints would be the number of digits,
the check digit and the leading digit (4xxx is Visa, 5xxx
is Mastercard). A "neat" feature would be a function that
masks the credit card information - you could use views/rules
so you could insert or update credit card info, but it would
be masked during access.

But this strikes me as evil. This information is still
in the datatabase, still accessible in any db dump.
If you have credit card info online, you *must* use
strong crypto. (Such as libpkix 0.4 et seq.)

- email and netnews. Create a new RFC822 type that understands
the RFC822 format and provides access to the headers via
accessor functions.

The benefits are that this type has all of the features
you would want. "Message-ID" should be unique. "References"
should give you referential integrity checks (although
it would not be possible to make it an actual constraint
in a live system.)

You could even illustrate advanced techniques by looking up
the sender's nominal email address with DNS. If it's not
a valid address, it goes into the bit bucket as spam. Even
if it is valid, a small configuration change and you're
checking RBL sites instead of the DNS servers and rejecting
mail from spammers.

The downside is that, like PKIX, this is too heavyweight
for an introductory article. But it may still be best -
the article would just need to gloss over the gory details.

Don't worry about the results of such an article. It is simply not possible
to benchmark MySQL against PostgreSQL because MySQL lacks many PostgreSQL
features. A serious user immediatly understands this.

The problem is that there are a lot of people out there who want
to use a relational database, but don't understand just how much
they give up with MySQL. Besides, if 4 of the 5 hosting companies
they considered suported MySQL instead of PostgreSQL it must be
better for their needs, right?

I've even caught friends making this mistake. For simple
schemas and light loads MySQL looks soooo good. The stuff
that I say is crucial for maintainability of even modestly
large databases (50 MB+) seems so abstract.

Why don't you start a mailing list for "Propaganda" and "Public relations".
It should be possible to meet the main journalists and contact them on
regular basis with ready-to-publish articles.

An even better approach is to find a vict... person willing and
able to write a regular "Database Guru" column for LJ, LM, or the
like. Most already have Kernel Korner, sysadmin, and similar columns.

Maybe 2 of 3 months will be basic. How do you access the database
with Perl or JDBC? Using ODBC to quietly replace SQL Server.
Why ESQL/C (ecpg, pro*c) is a tool every C/C++ developer should
have in their toolbox. Even basic things like actually setting
up the configuration files for the first time.

That odd month... that's when you pull out the advanced topics
that separate the real databases from the toys. Views and Rules.
User defined types. Clustering. Strong authentication of clients.
Crypto.

MySQL has mindspace for one reason alone - it's perceived as
faster.

PostgreSQL shouldn't try to compete in the same mindspace, it
should point out the many things that PostgreSQL supports but
MySQL doesn't... while quietly pointing out the folly in selecting
a system on the basis of single-user responsiveness. MySQL handles
a single query faster, but PostgreSQL handles far more concurrent
queries and does not catastrophically fail under heavy loads.

Oleg Bartunov <oleg@sai.msu.su> writes:

On Sun, 13 Jan 2002, Tom Lane wrote:
3. Check out the OSDB benchmark more closely.

I tried it with current CVS and make test fails (didn't tried
to find what's the problem). Also, you have to edit configure script -
it tries to find postgres_fe.h in include directory, which now lives under
include/internal directory.

I tried to build it on HPUX just now, and was rather sadly disillusioned
about its portability. After fixing several problems in the configure
script, removing hard-coded references to include files and libraries
that don't exist here, etc, I ended up with

make[2]: Entering directory `/home/tgl/tmp/osdb/src/callable-sql/postgres-call'
gcc -L/home/postgres/testversion/lib -lpq -g osdb-pg.o ../callable-sql.o ../../program-control.o ../../osdb.o -o osdb-pg
/usr/ccs/bin/ld: Unsatisfied symbols:
forkpty (code)
collect2: ld returned 1 exit status

Anybody know what forkpty() does? I see it exists in libutil.a on Linux,
but there's no man page for it.

Oh, OSDB also seems to assume that ecpg is in your $PATH. In general,
it's not prepared to deal with a Postgres that's not installed in the
"standard" place.

This thing needs some work :-(

regards, tom lane

On Sun, 13 Jan 2002, Tom Lane wrote:

Anybody know what forkpty() does? I see it exists in libutil.a on
Linux, but there's no man page for it.

It's a BSD function:

http://www.freebsd.org/cgi/man.cgi?query=forkpty

glibc does have info documentation about it. (Yes, info
is foul, but glibc's doco is actually pretty decent, and
"pinfo" makes it just about bearable.)

Matthew.

Hi Guys,

I'm not sure how long MySQL will be "non-transactional" and stuff for.

They have a version 4.0x out the door. Whilst it's still beta status,
they're recommending it for production use :

http://www.mysql.com/products/mysql-4.0/index.html

Consider me concerned.

Regards and best wishes,

Justin Clift

mlw wrote:

I am more concerned with the PHP/MySQL connection. It seems like it is a
defacto grouping. Engineers I know, who do not know SQL, will just use MySQL
because "everyone else does."

Invariably, MySQL (at first) seems like a good decision, then they have to do a
complex query. MySQL just can not execute the queries that a real SQL database
can. If an engineer does not know SQL, he will walk away thinking SQL is a
limiting environment.

An contractor came on to the company with a solid MySQL background. He was a
bright guy, but we are an Oracle/PostgreSQL shop. More than a few times, I had
to rewrite his code to use the SQL engine with a more complex, but overall more
efficient, query. His response is always, "Wow I didn't know you could do
that."

The word that needs to be heard is that a real SQL database is easier than a
simplistic SQL-like environment such as MySQL. I have tried several projects
with MySQL and have never been able to finish them with it, because MySQL is
just too limited.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Peter Eisentraut wrote:

Michael Meskes writes:

I just read one of those MySQL-PGSQL comparison on a German Linux magazine
that's not exactly known to be well informed. :-)

I think the article you're speaking of is here:

http://www.linuxenterprise.de/artikel/my_sql.pdf

The last part of this, which is the supposed comparison, was pretty much
copied straight out of their manual. I have a response to that here:

http://www.ca.postgresql.org/~petere/comparison.html

However, if you don't look hard enough, it's actually true that the only
benchmark that runs on both systems is the MySQL benchmark. I think the
OSDB benchmark should also work, though.

What we could do is:

1. Actually run the MySQL benchmark, to see what's wrong with it. (Note
that this is not the crashme test.)

2. Port pgbench to MySQL. Pgbench seems to be our daily benchmark of
choice, so it's not unimportant to verify how it does elsewhere.

3. Check out the OSDB benchmark more closely.

I think I should also submit my article to their interactive docs, so that
those that do comparisons in the future by just reading the manual will
get a clue.

I used to have a pointer to the OSDB benchmark in their interactive
docs. It would be very crappy of them to have removed it, as OSDB does
work for both MySQL and PostgreSQL. :(

+ Justin

--
Peter Eisentraut peter_e@gmx.net

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Hi Tom,

It definitely works under Linux for PostgreSQL 7.1.x. BUT, as you can
see the code is fairly simple and I don't believe it would be too hard
to make it far more cross-platform. In fact, with OSDB this is what is
wanted.

BTW - If anyone has patches for it they'd like to have committed, I'm an
official developer for it (username "Vapour") with CVS commit access.

Andy Riebs, the guy who is in charge of it is a nice guy and I'm sure
would allow you and experienced others to commit to CVS. He's just
short of time, the same as I.

:-)

Regards and best wishes,

Justin Clift

Tom Lane wrote:

Oleg Bartunov <oleg@sai.msu.su> writes:

On Sun, 13 Jan 2002, Tom Lane wrote:
3. Check out the OSDB benchmark more closely.

I tried it with current CVS and make test fails (didn't tried
to find what's the problem). Also, you have to edit configure script -
it tries to find postgres_fe.h in include directory, which now lives under
include/internal directory.

I tried to build it on HPUX just now, and was rather sadly disillusioned
about its portability. After fixing several problems in the configure
script, removing hard-coded references to include files and libraries
that don't exist here, etc, I ended up with

make[2]: Entering directory `/home/tgl/tmp/osdb/src/callable-sql/postgres-call'
gcc -L/home/postgres/testversion/lib -lpq -g osdb-pg.o ../callable-sql.o ../../program-control.o ../../osdb.o -o osdb-pg
/usr/ccs/bin/ld: Unsatisfied symbols:
forkpty (code)
collect2: ld returned 1 exit status

Anybody know what forkpty() does? I see it exists in libutil.a on Linux,
but there's no man page for it.

Oh, OSDB also seems to assume that ecpg is in your $PATH. In general,
it's not prepared to deal with a Postgres that's not installed in the
"standard" place.

This thing needs some work :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Tom Lane writes:

1. Actually run the MySQL benchmark, to see what's wrong with it. (Note
that this is not the crashme test.)

This would be a worthwhile thing to try, just to see if it exposes any
PG weaknesses we didn't already know about. I have run crashme in the
past (not recently though); but I've never found time for their
benchmark.

Well, I attempted to run the benchmark today, and it's not dissimilar to
crashme in the sense that a) it tends to crash, and b) it's quite useless.
It's the same single-user "run select a whole bunch of times" sort of
thing that's not really interesting anymore. The MySQL 4.0 server was
consistently killed by the kernel during this test. The stable
3.something version didn't quite work either because of some "unknown
errors", so I only got some tests to run. But when was the last time
someone actually cared about the speed of alter table or drop index?

--
Peter Eisentraut peter_e@gmx.net

On Sun, Jan 13, 2002 at 02:23:44PM +0100, Jean-Michel POURE wrote:

Yeah. This article was probably written directly by the MySQL team. This is
what you learn in the computing business. If you want someting published ..
write it yourself and invite the journalist in a restaurant. I did it 2 or 3
times when working for a telecommunication company.

:-)

Don't worry about the results of such an article. It is simply not possible
to benchmark MySQL against PostgreSQL because MySQL lacks many PostgreSQL
features. A serious user immediatly understands this.

But too many people decide without this knowledge. :-(

Why don't you start a mailing list for "Propaganda" and "Public relations".
It should be possible to meet the main journalists and contact them on
regular basis with ready-to-publish articles.

Good idea. Anyone else interested?

With a network of one person by country (US, Canada, France,Germany, Italy)
you would improve dramaticaly the presence of PostgreSQL in the world. It is
not difficult to have articles published ...

I would be willing to be the one person in Germany. After all we could
publish the same article in different countries.

But I wonder how easy it is to get the articles out. The last time I tried,
I was asked how much I would pay for writing the articles. Of course it was
formulated as "how much advertisments do you order?" :-)

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

On Sun, Jan 13, 2002 at 11:53:51AM -0500, Peter Eisentraut wrote:

I think the article you're speaking of is here:

http://www.linuxenterprise.de/artikel/my_sql.pdf

Yes, that's it. I didn't know it's online.

The last part of this, which is the supposed comparison, was pretty much
copied straight out of their manual. I have a response to that here:

http://www.ca.postgresql.org/~petere/comparison.html

Thanks. I doubt they are interested in any comment. They never printed
something like a comment. :-)

But their sales rep calls about once per week. :-)

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Le Lundi 14 Janvier 2002 13:37, Michael Meskes a �crit :

But I wonder how easy it is to get the articles out. The last time I tried,
I was asked how much I would pay for writing the articles. Of course it was
formulated as "how much advertisments do you order?" :-)

For what I experience, it is free and you don't have to buy
advertising space, unless you are a large company (and this can also be
discussed: I worked for a large company and never had to buy advertising
space).

Journalists are interested in product comparision (Pentium vs. Athlon). What
they need is ready-to-publish articles/figures/charts. They do not always
have the time to very the information. Journalists are not "liers", they just
need the right information ...

If you read Monty emails, he never mentions technical information when
comparing MySQL vs PostgreSQL. He rather write "read this article in which
you will find that..." or "a recent survey showed" or "MySQL has a market
share of". Some of these emails went to the basket immediately for obvious
reasons.

I would be willing to be the one person in Germany. After all we could
publish the same article in different countries.

I could be the one for France (das lebte die franz�siche Freundschaft) and
the two of us could start with an experimental
http://postgresql-propaganda.org site, with secure access for
the team.

I can handle the creation of a small database with newspapers address and
journalist names.

Cheers,
Jean-Michel POURE
--
Tel : +33(0)1 39 64 87 61
Mobile : +33 (0)6 76 88 60 29
Fax : +33(0)1 39 64 96 72
Mailto:jm.poure@freesurf.fr
38 bld de la R�publique
95160 MONTMORENCY
France

On Sun, 13 Jan 2002, Bear Giles wrote:

Lots of great stuff - then

- geographical positions - latitude, longitude and possibly
elevation, with related functions. But few constraints
make sense.

PostGIS?

Cheers,
Rod
--
Let Accuracy Triumph Over Victory

Zetetic Institute
"David's Sling"
Marc Stiegler

MySQL has mindspace for one reason alone - it's perceived as
faster.

PostgreSQL shouldn't try to compete in the same mindspace, it
should point out the many things that PostgreSQL supports but
MySQL doesn't... while quietly pointing out the folly in selecting
a system on the basis of single-user responsiveness. MySQL handles
a single query faster, but PostgreSQL handles far more concurrent
queries and does not catastrophically fail under heavy loads.

This is the crux of the issue -- I most people who are objective would
choose PostgreSQL; it is the "just go with what most people are using"
crowd that we have trouble attracting, and that is the largest group of
the population.

Great Bridge helped with that mind share, and Red Hat is in that space
now. We are clearly on the upswing, it is just taking time to get where
we want to be. Certainly any articles/comments people can make would be
a help in that direction. [ Not sure why this disucssion is taking
place on hackers and not in general, where most of our users live, CC'ing.]

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026