Обсуждение: MySQL vs. PostgreSQL
Which is better, PostgreSQL or MySQL? Both are excellent products with unique strengths, and the choice is often a matter of personal preference. Read on for a useful comparison of these two open source database systems. http://builder.com.com/article.jhtml?id=u00320020624gcn01.htm
On 10 Jul 2002, builder wrote: > Which is better, PostgreSQL or MySQL? Both are excellent products with > unique strengths, and the choice is often a matter of personal > preference. Read on for a useful comparison of these two open source > database systems. > http://builder.com.com/article.jhtml?id=u00320020624gcn01.htm For some definition of "useful." You say in your table that postgres is slower. How did you measure this? Not many Web developers use PostgreSQL because they feel that the additional features degrade performance. Why developers should feel that features such as MVCC will degrade performance, I have no idea. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
...
> Not many Web developers use PostgreSQL because they feel that
> the additional features degrade performance.
"Not many web developers" is a completely subjective statement which has
no basis in fact. How many is "not many"? Is that two? One hundred? Two
million? Twenty million?
How many is "many web developers"? Must be more than "not many", eh?
Probably can't narrow it down much beyond that :/
Hmm. Given the proven performance scaling benefits from Postgres, one
could also say "not many web developers feel that their applications
need to scale beyond 5 simultaneous users". Or "not many web developers
need transaction-enabled databases", or ???
- Thomas
This web developer for both internet and intranet applications, who does both connectionless and simulated connection oriented applications, uses PostgreSQL WHERE EVER POSSIBLE. If I ever have issues with load, there are much better ways of getting performance then choosing a less capable database for its trimness (eg horsepower and memory are cheap, dedicated servers are cheap, and better table design/layout/indexes usually will fix ANY databases slowness where mass volume is not the issue). I train all my programmers on ONE db architecture, I have to maintain only ONE db architecture on all my servers, and the db I have chosen does everything that even my most complex job needs to do. (Now that 7.2.1 supports OUTER JOINS). Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of > Thomas Lockhart > Sent: Friday, July 12, 2002 10:05 AM > To: Curt Sampson > Cc: builder; pgsql-general@postgresql.org > Subject: Re: [GENERAL] MySQL vs. PostgreSQL > > > ... > > Not many Web developers use PostgreSQL because they feel that > > the additional features degrade performance. > > "Not many web developers" is a completely subjective > statement which has > no basis in fact. How many is "not many"? Is that two? One > hundred? Two > million? Twenty million? > > How many is "many web developers"? Must be more than "not many", eh? > Probably can't narrow it down much beyond that :/ > > Hmm. Given the proven performance scaling benefits from Postgres, one > could also say "not many web developers feel that their applications > need to scale beyond 5 simultaneous users". Or "not many web > developers > need transaction-enabled databases", or ??? > > - Thomas > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org >
Yes, there are points to quibble on, but overall, I thought it was a decent article that exposed people to the idea of using PostgreSQL. After all, it could've been "MySQL vs MS SQL" or "MySQL vs MS Access". Sometimes Perfect is the enemy of Good.
On Fri, 12 Jul 2002, Elaine Lindelef wrote: > Yes, there are points to quibble on, but overall, I thought it was a > decent article that exposed people to the idea of using PostgreSQL. Well, yeah, but it's really time to kill, properly, this idea that postgres is always slower than mysql. I'm reasonably convinced that under fairly heavy OLTP loads with some large queries going, MySQL would grind to a halt at loads much less than postgres can handle. The only area I know of where MySQL is noticably faster is when dealing with large amounts of data in tables with very short rows; MySQL has much less row overhead, so of course it's faster. Though people are working on reducing postgres' row overhead as well, which will help.... cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
I try both and find that mysql can "order by" a Chinese language field when I use a "select" SQL, but postgresql can not do this. Therefore I choose mysql as my Chinese-Language -Database-System. I am a Chinese. Best regards, George.
On 7/13/02 5:11 AM, "Curt Sampson" <cjs@cynic.net> wrote: > On Fri, 12 Jul 2002, Elaine Lindelef wrote: > >> Yes, there are points to quibble on, but overall, I thought it was a >> decent article that exposed people to the idea of using PostgreSQL. > > Well, yeah, but it's really time to kill, properly, this idea that > postgres is always slower than mysql. I'm reasonably convinced that > under fairly heavy OLTP loads with some large queries going, MySQL would > grind to a halt at loads much less than postgres can handle. > > The only area I know of where MySQL is noticably faster is when > dealing with large amounts of data in tables with very short rows; > MySQL has much less row overhead, so of course it's faster. Though > people are working on reducing postgres' row overhead as well, > which will help.... > Are there any current benchmarks showing postgres' performance against other major dbs? I've only seen one, and I think it's a couple of years old. Eweek profiled major commercial dbs and had mySQL as the open source representative. Not even a whisper about PgSQL. I'd love to see some current numbers, just to fend off with a stick the people who keep asking me why I don't use mySQL. -- sgl ======================================================= Steve Lane Vice President Chris Moyer Consulting, Inc. 833 West Chicago Ave Suite 203 Voice: (312) 433-2421 Email: slane@fmpro.com Fax: (312) 850-3930 Web: http://www.fmpro.com =======================================================
Steve Lane wrote: > On 7/13/02 5:11 AM, "Curt Sampson" <cjs@cynic.net> wrote: > > > On Fri, 12 Jul 2002, Elaine Lindelef wrote: > > > >> Yes, there are points to quibble on, but overall, I thought it was a > >> decent article that exposed people to the idea of using PostgreSQL. > > > > Well, yeah, but it's really time to kill, properly, this idea that > > postgres is always slower than mysql. I'm reasonably convinced that > > under fairly heavy OLTP loads with some large queries going, MySQL would > > grind to a halt at loads much less than postgres can handle. > > > > The only area I know of where MySQL is noticably faster is when > > dealing with large amounts of data in tables with very short rows; > > MySQL has much less row overhead, so of course it's faster. Though > > people are working on reducing postgres' row overhead as well, > > which will help.... > > > > Are there any current benchmarks showing postgres' performance against other > major dbs? I've only seen one, and I think it's a couple of years old. Eweek > profiled major commercial dbs and had mySQL as the open source > representative. Not even a whisper about PgSQL. I'd love to see some current > numbers, just to fend off with a stick the people who keep asking me why I > don't use mySQL. I have heard we are faster for multi-user queries than MySQL and haven't heard anyone dispute that. -- 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
I did notice a considerable difference in time, with a
enquete-result-page (in PHP) using this query:
SELECT
answers.answertext,
answers.answerid,
count(answered_question.answerid) AS answercount
FROM
answers
LEFT JOIN answered_question ON answers.answerid =
answered_question.answerid
WHERE
answers.questionid = '{$this->questionid}'
GROUP BY answers.answerid
ORDER BY answers.answerorder
Which is, I presume, a quite common query...
It runs on exactly the same dataset with exactly the same indices and
table layouts (as far as possible) on exactly the same machine, with
both postgres and mysql optimised 'a bit'.
The query would take 29.45, 55.22 and 17.74 ms on postgresql (for
different questions) takes on mysql respectively: 36.74, 81.14 and 26.37 ms.
Resulting in some simple stats at the end of the page creation:
Database stats Time (s)
Total Execution time: 2.212 s
Total Query time: 2.130 s
Total Number of Queries: 230
Average Time per Query: 0.009 s
vs
Database stats Time (s)
Total Execution time: 3.061 s
Total Query time: 3.002 s
Total Number of Queries: 230
Average Time per Query: 0.013 s
About 150 of these 230 queries are the one showed above. The rest are
simple selects which run 'much' faster on mysql.
The only 'hack' for postgresql I had to make was to set the
ENABLE_MERGEJOIN to OFF, otherwise in some cases it would use the
mergejoin rather than the nested loops on the index scans of both
answers and answered_question, taking over 300ms in stead of 60ms... (a
optimiser problem perhaps?).
Anyway, this shows you that it is not even necessary to have huge
databases, heavy loads or very complicated queries to find postgresql
performing better than mysql.
Regards,
Arjen
Curt Sampson wrote:
> On Fri, 12 Jul 2002, Elaine Lindelef wrote:
> Well, yeah, but it's really time to kill, properly, this idea that
> postgres is always slower than mysql. I'm reasonably convinced that
> under fairly heavy OLTP loads with some large queries going, MySQL would
> grind to a halt at loads much less than postgres can handle.
Two years ago on phpbuilder.com, there was a performance comparison between MySQL and PostgreSQL. Tim Perdue simulated load on various sourceforge.net pages. See: http://phpbuilder.com/columns/tim20001112.php3?print_mode=1 George Essig > ------------------------------ > > Date: Sun, 14 Jul 2002 20:54:51 -0500 > From: Steve Lane <slane@fmpro.com> > To: <pgsql-general@postgresql.org> > Subject: Re: MySQL vs. PostgreSQL > Message-ID: <B957959B.10424%slane@fmpro.com> > > Are there any current benchmarks showing postgres' performance against other > major dbs? I've only seen one, and I think it's a couple of years old. Eweek > profiled major commercial dbs and had mySQL as the open source > representative. Not even a whisper about PgSQL. I'd love to see some current > numbers, just to fend off with a stick the people who keep asking me why I > don't use mySQL. > > -- sgl
An interesting talk back was posted to this article that parallels the experience of many folks with postgresql and mysql. http://phpbuilder.com/annotate/message.php3?id=1010522 On Mon, 15 Jul 2002 George.T.Essig@stls.frb.org wrote: > Two years ago on phpbuilder.com, there was a performance comparison between > MySQL and PostgreSQL. Tim Perdue simulated load on various > sourceforge.net pages. See: > > http://phpbuilder.com/columns/tim20001112.php3?print_mode=1 > > George Essig > > > ------------------------------ > > > > Date: Sun, 14 Jul 2002 20:54:51 -0500 > > From: Steve Lane <slane@fmpro.com> > > To: <pgsql-general@postgresql.org> > > Subject: Re: MySQL vs. PostgreSQL > > Message-ID: <B957959B.10424%slane@fmpro.com> > > > > Are there any current benchmarks showing postgres' performance against > other > > major dbs? I've only seen one, and I think it's a couple of years old. > Eweek > > profiled major commercial dbs and had mySQL as the open source > > representative. Not even a whisper about PgSQL. I'd love to see some > current > > numbers, just to fend off with a stick the people who keep asking me why > I > > don't use mySQL. > > > > -- sgl > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
Just curious, Is anyone keeping all of these links as an archive possibly for the redesigned website ? I know pgsql inc has a section with "news clippings" on it. Jeff. > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of scott.marlowe > Sent: Monday, July 15, 2002 4:30 PM > To: George.T.Essig@stls.frb.org > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] MySQL vs. PostgreSQL > > > An interesting talk back was posted to this article that parallels the > experience of many folks with postgresql and mysql. > > http://phpbuilder.com/annotate/message.php3?id=1010522 > > On Mon, 15 Jul 2002 George.T.Essig@stls.frb.org wrote: > > > Two years ago on phpbuilder.com, there was a performance > comparison between > > MySQL and PostgreSQL. Tim Perdue simulated load on various > > sourceforge.net pages. See: > > > > http://phpbuilder.com/columns/tim20001112.php3?print_mode=1 > > > > George Essig > > > > > ------------------------------ > > > > > > Date: Sun, 14 Jul 2002 20:54:51 -0500 > > > From: Steve Lane <slane@fmpro.com> > > > To: <pgsql-general@postgresql.org> > > > Subject: Re: MySQL vs. PostgreSQL > > > Message-ID: <B957959B.10424%slane@fmpro.com> > > > > > > Are there any current benchmarks showing postgres' performance against > > other > > > major dbs? I've only seen one, and I think it's a couple of years old. > > Eweek > > > profiled major commercial dbs and had mySQL as the open source > > > representative. Not even a whisper about PgSQL. I'd love to see some > > current > > > numbers, just to fend off with a stick the people who keep > asking me why > > I > > > don't use mySQL. > > > > > > -- sgl > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > ---------------------------(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 >
On Monday, July 15, 2002, at 01:30 PM, scott.marlowe wrote: > An interesting talk back was posted to this article that parallels the > experience of many folks with postgresql and mysql. > > http://phpbuilder.com/annotate/message.php3?id=1010522 OK, reading this article makes me want to throw in my .02c. - "most web developers" have no clue about transactions, referential integrity, triggers, stored procedures. - "most web developers" are dealing with small applications (think product catalogs with a couple of tables, with less than 1K rows). - Therefore, "most web developers" happen to be in the "sweet spot" for mysql's performance vs. it's set of features. That's why all these web development industry zines keep saying "mysql is best". I'm making these statements based on my experience since '96, working in the Internet Service Provider, and web development industries. For this to change, somehow, the average web developer needs to be educated about the world beyond MySQL. I really really hope this happens, somehow. Just thinking out loud... Alex Rice, Software Developer Architectural Research Consultants, Inc. alex_rice@arc.to alrice@swcp.com
> > An interesting talk back was posted to this article that parallels the > > experience of many folks with postgresql and mysql. > > > > http://phpbuilder.com/annotate/message.php3?id=1010522 > > OK, reading this article makes me want to throw in my .02c. > > - "most web developers" have no clue about transactions, referential > integrity, triggers, stored procedures. > - "most web developers" are dealing with small applications (think > product catalogs with a couple of tables, with less than 1K rows). > - Therefore, "most web developers" happen to be in the "sweet spot" for > mysql's performance vs. it's set of features. > > That's why all these web development industry zines keep saying "mysql > is best". I'm making these statements based on my experience since '96, > working in the Internet Service Provider, and web development industries. > > For this to change, somehow, the average web developer needs to be > educated about the world beyond MySQL. I really really hope this > happens, somehow. Just thinking out loud... Why does this need to change? I don't mean to start a war (I love postgres), but if mysql works for these people why should they learn something else? Take other technologies... ... if notepad works for HTML, why make them learn FrontPage/Dreamweaver? ... if a P400 running linux works for their webserver, why should they buy a Sun ES1000? In both cases they don't need it and won't use the added features. I'm not saying that we shouldn't education people, but I think for the "web developers" mentioned above postgres will lose that battle against mysql. Mysql does what they need it to do and it's everywhere. Just look at the number of ISP's that have mysql support vs postgres support. And yes, I know hub.org has it, but I think it's like $300/mo compared to say $10/mo and I know why that's the case, but for the people mentioned above they don't and they don't need to. I think what people need educating on is that *once* you've grown beyond mysql there is another open source (ie. free) database you can move to instead of shelling out $$$ for oracle and friends. That is postgresql's market.. at least in my mind. just my 2 cents... and again, I don't mean this to start a war, I like postgres just fine :) -philip
On Monday, July 15, 2002, at 03:29 PM, Philip Hallstrom wrote: > I think what people need educating on is that *once* you've grown beyond > mysql there is another open source (ie. free) database you can move to > instead of shelling out $$$ for oracle and friends. > > That is postgresql's market.. at least in my mind. I'm not suggesting they should not use MySQL at all. I've used MySQL a lot in the past, and don't regret it. But how will they know when they have grown beyond Mysql? ... I just noticed Robert Treat's followup on exactly this. I think we are all agreeing. Alex Rice, Software Developer Architectural Research Consultants, Inc. alex_rice@arc.to alrice@swcp.com
On Mon, 2002-07-15 at 17:29, Philip Hallstrom wrote: > > > An interesting talk back was posted to this article that parallels the > > > experience of many folks with postgresql and mysql. > > > > > > http://phpbuilder.com/annotate/message.php3?id=1010522 > > > > OK, reading this article makes me want to throw in my .02c. > > > > - "most web developers" have no clue about transactions, referential > > integrity, triggers, stored procedures. > > - "most web developers" are dealing with small applications (think > > product catalogs with a couple of tables, with less than 1K rows). > > - Therefore, "most web developers" happen to be in the "sweet spot" for > > mysql's performance vs. it's set of features. > > > > That's why all these web development industry zines keep saying "mysql > > is best". I'm making these statements based on my experience since '96, > > working in the Internet Service Provider, and web development industries. > > > > For this to change, somehow, the average web developer needs to be > > educated about the world beyond MySQL. I really really hope this > > happens, somehow. Just thinking out loud... > > Why does this need to change? I don't mean to start a war (I love > postgres), but if mysql works for these people why should they learn > something else? Take other technologies... > > ... if notepad works for HTML, why make them learn FrontPage/Dreamweaver? > ... if a P400 running linux works for their webserver, why should they buy > a Sun ES1000? > > In both cases they don't need it and won't use the added features. > > I'm not saying that we shouldn't education people, but I think for the > "web developers" mentioned above postgres will lose that battle against > mysql. Mysql does what they need it to do and it's everywhere. Just > look at the number of ISP's that have mysql support vs postgres support. > And yes, I know hub.org has it, but I think it's like $300/mo compared to > say $10/mo and I know why that's the case, but for the people mentioned > above they don't and they don't need to. > > I think what people need educating on is that *once* you've grown beyond > mysql there is another open source (ie. free) database you can move to > instead of shelling out $$$ for oracle and friends. > > That is postgresql's market.. at least in my mind. > > just my 2 cents... > > and again, I don't mean this to start a war, I like postgres just fine :) > > -philip > I think the education that needs to take place is the answer to "at what point do I outgrow mysql?" While I suppose ACIDity is not required for every application (though some would argue that point I'm sure) it probably should be used in many more applications than it is. A good example of this would be something like phpauction, which is an online auction (think ebay) software package based on php and mysql. While I have never heard of anyone having an issue, to me it is scary to think there are live auction sites running that need to reliably track things like bid times and monetary amounts that are doing it without any of the "data insurance" that postgres brings to the table. And I'm not picking on phpauction (I refer to it because I have submitted code for it), there are a lot of other projects that probably should be using postgres but the developers don't realize it. Robert Treat xzilla@users.sourceforge.net
On Fri, Jul 12, 2002 at 04:52:11PM +0900, Curt Sampson wrote: > > For some definition of "useful." You say in your table that postgres > is slower. How did you measure this? > In my personal experiance PostgreSQL runs circles around MySQL. Specifically MySQL makes lousy choices about which indexes to use and requires me to give it hints. PostgreSQL just works.
On Tue, 2002-07-16 at 00:33, Christopher Weimann wrote: > On Fri, Jul 12, 2002 at 04:52:11PM +0900, Curt Sampson wrote: > > > > For some definition of "useful." You say in your table that postgres > > is slower. How did you measure this? > > > > In my personal experiance PostgreSQL runs circles around MySQL. > Specifically MySQL makes lousy choices about which indexes to > use and requires me to give it hints. PostgreSQL just works. Weeeellll. While I'd never use mysql, I find postgres does not 'just work' - it needs quite a bit attention: - running vacuum by hand (there was a recent debate on auto-running vacuum when it's needed. I'll be happy to see this) - the default set up needs some tuning if your database grows to a certain size (I've not come to that point in my tiny projects, I'm just reading -general and -hackers cause I'm interested). But if you assume a proper installation and administration of the database, then I agree with you. cheers -- vbi -- secure email with gpg http://fortytwo.ch/gpg
Вложения
Christopher Weimann wrote: > On Fri, Jul 12, 2002 at 04:52:11PM +0900, Curt Sampson wrote: > >>For some definition of "useful." You say in your table that postgres >>is slower. How did you measure this? >> > > > In my personal experiance PostgreSQL runs circles around MySQL. > Specifically MySQL makes lousy choices about which indexes to > use and requires me to give it hints. PostgreSQL just works. > > > I use both databases depending on the project requirements. But I find that mysql is more willing to use your indexes than postgres is. I also like that fact that I can tell mysql to use specific indexes if I feel like it. Plus the ability to add ,delete and modify the name and type of any field , table or database is a fat fingered admin's dream. I love postgres, but sometimes the mysql bashing goes way out hand on the list. No war please. -- Vincent Stoessel Linux Systems Developer vincent xaymaca.com
Вложения
On Thu, Jul 25, 2002 at 04:01:26PM -0400, Vincent Stoessel wrote: > > > > In my personal experiance PostgreSQL runs circles around MySQL. > > Specifically MySQL makes lousy choices about which indexes to > > use and requires me to give it hints. PostgreSQL just works. > > > > I use both databases depending on the project requirements. But > I find that mysql is more willing to use your indexes than > postgres is. My experience differs greatly from yours here. > I also like that fact that I can tell mysql to use > specific indexes if I feel like it. I haven't needed to do this on PostgreSQL ( but it would be nice ) but I have needed to on MySQL because of its bad index choices. > Plus the ability to add ,delete > and modify the name and type of any field , table or database is a > fat fingered admin's dream. > Yes, that is something I miss on the sites where I use PostgreSQL. ( yes I use MySQL as well ) > I love postgres, but sometimes the mysql > bashing goes way out hand on the list. > No war please. > I would hardly call what I said bashing. I would call it the facts in my experience with both products.
>Plus the ability to add ,delete >and modify the name and type of any field , table or database is a >fat fingered admin's dream. I have to agree with that one. I do miss that one feature of MySQL
"Curt Sampson" <cjs@cynic.net> wrote in message news:Pine.NEB.4.44.0207131908540.454-100000@angelic.cynic.net... > > Well, yeah, but it's really time to kill, properly, this idea that > postgres is always slower than mysql. I'm reasonably convinced that > under fairly heavy OLTP loads with some large queries going, MySQL would > grind to a halt at loads much less than postgres can handle. We did a bunch of benchmarking at work, using real datasets from our application, as well as synthetic benchmarks. A very wide variety of db operations were measured, using postgres, and mysql with innodb and myisam tables. Although I am a huge postgres fan, and will not be switching myself, I have to admit that as far as a race goes, mysql is the clear winner. In our tests, it was drastically faster than postgres. It really bummed me out. Marshall
On Mon, 29 Jul 2002, Marshall Spight wrote: > "Curt Sampson" <cjs@cynic.net> wrote in message news:Pine.NEB.4.44.0207131908540.454-100000@angelic.cynic.net... > > > > Well, yeah, but it's really time to kill, properly, this idea that > > postgres is always slower than mysql. I'm reasonably convinced that > > under fairly heavy OLTP loads with some large queries going, MySQL would > > grind to a halt at loads much less than postgres can handle. > > We did a bunch of benchmarking at work, using real datasets from > our application, as well as synthetic benchmarks. A very wide > variety of db operations were measured, using postgres, and mysql > with innodb and myisam tables. > > Although I am a huge postgres fan, and will not be switching myself, > I have to admit that as far as a race goes, mysql is the clear winner. > In our tests, it was drastically faster than postgres. It really bummed > me out. Just wondering what version of postgresql you were running, and how much optimization was done to the postgresql.conf file before testing, as the default is really quite lowest common denominator.