Thread: mysql-pgsql comparison
Hi, I just read one of those MySQL-PGSQL comparison on a German Linux magazine that's not exactly known to be well informed. :-) This article really cries for a reply. Here are some extracts: The only benchmarks that everyone can download for free to test MySQL versus PostgreSQL are the MySQL benchmarks. ... This benchmark suite is pretty old and was tuned over the years to be as fair as possible to PostgreSQL. ... The results show a huge difference between PostgreSQL and MySQL. ... The MySQL developers tried to make the benchmarks as fair as possible. Unfortunately PostgreSQL never answered to the repeated questions about optimal tuning. Since this is not the first kind stuff like that comes up, I wonder if we have some kind of standard answer. I for one don't like to read that kind of FUD about us. Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
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
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.
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
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
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
> 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 makesense. - 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 havecredit 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 - thearticle 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.
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