Re: MySQL and PostgreSQL speed compare
Search were almost the same ... sorting and
reading sorted entries from dba was the same.
But insert/modify/delete [ were up to 15 times
slower in PostgreSQL than MySQL ].
One of MySQL's big speed problems involves
multi-user situations where some users are writing
while others are reading; MySQL's table-level
locking is a real drawback here, which is
something your testing apparently did not
exercise. (The new row-level locking code
should alleviate this, but it's _very_ new
and not something I'd want to risk in a production
system today.)
I've had several problems in the past with
MySQL when one client would request a
time-consuming DELETE (e.g., dropping several
million rows containing last month's web log
data). The DELETE could lock the table (and block
most of the clients) for long periods of
time (anywhere from 10 minutes to 1/2 hour
for one system I implemented).
In practice, of course, web applications are
usually >99% reads, so this issue is often
not a big problem. Nevertheless, if you're
going to go with MySQL (which is a fine product),
you should give some consideration to avoiding
any time-consuming table modifications. (In the
system I mentioned above, we moved the log data
out of the database and into rotated log files
on disk, which addressed the problem quite handily.)
I also have some minor concerns about data
integrity with MySQL's Berkeley DB tables, since I
understand that the BDB tables store the row
data within the B-Tree. This is fundamentally
a less reliable design than other MySQL table
types, which separate the data file from the
index file. Index files are inherently more
brittle, but easy to reconstruct if they get
damaged. An almost purely theoretical quibble.
More seriously, I'm also not certain whether
MySQL's table-recovery utilities support BDB
tables at all right now. This gives me pause.
One big advantage of MySQL over PostgreSQL
right now is ease of upgrading. Upgrading
MySQL just requires shutting down the old
one and starting the new; PostgreSQL requires
a more involved process to migrate all of
your data. (Of course, upgrading your DB
is never a simple matter, but at least
MySQL tries to make it less painful.)
- Tim
One big advantage of MySQL over PostgreSQL
right now is ease of upgrading. Upgrading
MySQL just requires shutting down the old
one and starting the new; PostgreSQL requires
a more involved process to migrate all of
your data. (Of course, upgrading your DB
is never a simple matter, but at least
MySQL tries to make it less painful.)
Yes, that is a good point. We are clearly fixing/improving the storage
system, while MySQL has been able to keep the same one for many years.
--
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
...another advantage I've seen is the documentation! MySQL has its functions
clearly documented on the mysql.com site; I not able to find detailed docs
for the pgsql functions anywhere on the postgresql site.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruce Momjian
Sent: Tuesday, January 02, 2001 1:08 PM
To: kientzle@acm.org
Cc: PostgreSQL general mailing list
Subject: Re: Re: MySQL and PostgreSQL speed compare
One big advantage of MySQL over PostgreSQL
right now is ease of upgrading. Upgrading
MySQL just requires shutting down the old
one and starting the new; PostgreSQL requires
a more involved process to migrate all of
your data. (Of course, upgrading your DB
is never a simple matter, but at least
MySQL tries to make it less painful.)
Yes, that is a good point. We are clearly fixing/improving the storage
system, while MySQL has been able to keep the same one for many years.
--
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
On Tue, Jan 02, 2001 at 02:47:05PM -0800, some SMTP stream spewed forth:
...another advantage I've seen is the documentation! MySQL has its functions
clearly documented on the mysql.com site; I not able to find detailed docs
for the pgsql functions anywhere on the postgresql site.
I ask you the same question I ask myself in such situations:
How much documentation have I written?
How much have I aided the Cause?
gh
Show quoted text
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruce Momjian
Sent: Tuesday, January 02, 2001 1:08 PM
To: kientzle@acm.org
Cc: PostgreSQL general mailing list
Subject: Re: Re: MySQL and PostgreSQL speed compareOne big advantage of MySQL over PostgreSQL
right now is ease of upgrading. Upgrading
MySQL just requires shutting down the old
one and starting the new; PostgreSQL requires
a more involved process to migrate all of
your data. (Of course, upgrading your DB
is never a simple matter, but at least
MySQL tries to make it less painful.)Yes, that is a good point. We are clearly fixing/improving the storage
system, while MySQL has been able to keep the same one for many years.-- 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'm happy to contribute once I feel like I've got something to offer, which
I won't know until I have the lay of the land, and that's the problem. As a
long-time coder picking up postgresql for the first time, I'm baffled by
what seems to be a lack of detailed documentation for something so
fundamental as the built-in functions. Do the docs for the functions even
exist? Are they in the dist but not published on the web site? Zen-like
riddles and causes-in-need-of-aid aside, any hints to the answer for such a
basic question would help me get started with what seems like a very
powerful tool.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of GH
Sent: Tuesday, January 02, 2001 3:53 PM
To: Eric Mueller
Cc: 'PostgreSQL general mailing list'
Subject: Re: RE: Re: MySQL and PostgreSQL speed compare
On Tue, Jan 02, 2001 at 02:47:05PM -0800, some SMTP stream spewed forth:
...another advantage I've seen is the documentation! MySQL has its
functions
clearly documented on the mysql.com site; I not able to find detailed docs
for the pgsql functions anywhere on the postgresql site.
I ask you the same question I ask myself in such situations:
How much documentation have I written?
How much have I aided the Cause?
gh
Show quoted text
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruce Momjian
Sent: Tuesday, January 02, 2001 1:08 PM
To: kientzle@acm.org
Cc: PostgreSQL general mailing list
Subject: Re: Re: MySQL and PostgreSQL speed compareOne big advantage of MySQL over PostgreSQL
right now is ease of upgrading. Upgrading
MySQL just requires shutting down the old
one and starting the new; PostgreSQL requires
a more involved process to migrate all of
your data. (Of course, upgrading your DB
is never a simple matter, but at least
MySQL tries to make it less painful.)Yes, that is a good point. We are clearly fixing/improving the storage
system, while MySQL has been able to keep the same one for many years.-- 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
On Tue, 2 Jan 2001, Eric Mueller wrote:
...another advantage I've seen is the documentation! MySQL has its functions
clearly documented on the mysql.com site; I not able to find detailed docs
for the pgsql functions anywhere on the postgresql site.
From the main page, click "User's Lounge", then Click Documentation->
"English", then click "Current Release Docs (ver 7.0)".
http://www.postgresql.org:80/users-lounge/docs/7.0/postgres/functions.htm
http://www.postgresql.org/users-lounge/docs/7.0/postgres/operators.htm
http://www.postgresql.org:80/users-lounge/docs/#7.0
*yawn*
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
Bruce Momjian wrote:
One big advantage of MySQL over PostgreSQL
right now is ease of upgrading.Yes, that is a good point. We are clearly
fixing/improving the storage system, while
MySQL has been able to keep the same one
for many years.
Also, MySQL seems to have done a fairly good job
of abstracting the storage management, so that
they can support multiple storage managers.
They're exploiting this right now to develop
new storage systems while maintaining support
for the old ones during a transition period.
It also permits them to support features such
as in-memory temp tables and should simplify
implementing a distributed database at some
future date.
- Tim
Yeah... saw those. So that's as detailed as it gets, eh? Thanks for
confirming.
-----Original Message-----
From: Dominic J. Eidson [mailto:sauron@the-infinite.org]
Sent: Tuesday, January 02, 2001 4:39 PM
To: Eric Mueller
Cc: 'PostgreSQL general mailing list'
Subject: Re: [GENERAL] RE: Re: MySQL and PostgreSQL speed compare
On Tue, 2 Jan 2001, Eric Mueller wrote:
...another advantage I've seen is the documentation! MySQL has its
functions
clearly documented on the mysql.com site; I not able to find detailed docs
for the pgsql functions anywhere on the postgresql site.
From the main page, click "User's Lounge", then Click Documentation->
"English", then click "Current Release Docs (ver 7.0)".
http://www.postgresql.org:80/users-lounge/docs/7.0/postgres/functions.htm
http://www.postgresql.org/users-lounge/docs/7.0/postgres/operators.htm
http://www.postgresql.org:80/users-lounge/docs/#7.0
*yawn*
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" -
Gimli
----------------------------------------------------------------------------
---
http://www.the-infinite.org/
http://www.the-infinite.org/~dominic/
Yeah... saw those. So that's as detailed as it gets, eh? Thanks for
confirming.
-----Original Message-----
From: Dominic J. Eidson [mailto:sauron@the-infinite.org]
Sent: Tuesday, January 02, 2001 4:39 PM
To: Eric Mueller
Cc: 'PostgreSQL general mailing list'
Subject: Re: [GENERAL] RE: Re: MySQL and PostgreSQL speed compare
On Tue, 2 Jan 2001, Eric Mueller wrote:
...another advantage I've seen is the documentation! MySQL has its
functions
clearly documented on the mysql.com site; I not able to find detailed docs
for the pgsql functions anywhere on the postgresql site.
From the main page, click "User's Lounge", then Click Documentation->
"English", then click "Current Release Docs (ver 7.0)".
http://www.postgresql.org:80/users-lounge/docs/7.0/postgres/functions.htm
http://www.postgresql.org/users-lounge/docs/7.0/postgres/operators.htm
http://www.postgresql.org:80/users-lounge/docs/#7.0
*yawn*
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" -
Gimli
----------------------------------------------------------------------------
---
http://www.the-infinite.org/
http://www.the-infinite.org/~dominic/
On Tue, 2 Jan 2001, Eric Mueller wrote:
...another advantage I've seen is the documentation! MySQL has its functions
clearly documented on the mysql.com site; I not able to find detailed docs
for the pgsql functions anywhere on the postgresql site.
When I read comments like this I have to question the motives since it's
overly obvious you haven't even looked. It could be in big bold red
flashing text and you still wouldn't see it.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
Actually I agree with Eric.
The documentation is not as detailed or organised for usage. But it is
decent. Only sometimes you need to resort to real-life testing to see how
things really work. I do like the real world practical info which the MySQL
docs provide.
There still isn't as much mention of limits and limitations of various
things as I'd like.
Overall it's better than a "textbook style" doc.
In contrast: take a look at Oracle's installation manual for an example of
how bad things can get ;). Compare it with a Oracle HOWTO.
Cheerio,
Link.
At 08:12 PM 02-01-2001 -0500, Vince Vielhaber wrote:
On Tue, 2 Jan 2001, Eric Mueller wrote:
...another advantage I've seen is the documentation! MySQL has its
functions
Show quoted text
clearly documented on the mysql.com site; I not able to find detailed docs
for the pgsql functions anywhere on the postgresql site.When I read comments like this I have to question the motives since it's
overly obvious you haven't even looked. It could be in big bold red
flashing text and you still wouldn't see it.Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
On Tue, Jan 02, 2001 at 04:20:53PM -0800, Eric Mueller wrote:
I'm happy to contribute once I feel like I've got something to offer, which
I won't know until I have the lay of the land, and that's the problem. As a
long-time coder picking up postgresql for the first time, I'm baffled by
what seems to be a lack of detailed documentation for something so
fundamental as the built-in functions. Do the docs for the functions even
exist? Are they in the dist but not published on the web site? Zen-like
riddles and causes-in-need-of-aid aside, any hints to the answer for such a
basic question would help me get started with what seems like a very
powerful tool.
From the aforementioned "Big Integrated Document" at
http://www.postgresql.org/docs/postgres/index.html
5. Functions
SQL Functions
Mathematical Functions
String Functions
.
.
.
As has been said, it seems that you need big flashing red letters before
you will acknowledge documentation. If you are going to use postgres, good,
more power to you. If you want to use MySQL, go right ahead. If all you are
going to do is hang around here complaining, I'd rather you take it somewhere
else.
--
Adam Haberlach |A cat spends her life conflicted between a
adam@newsnipple.com |deep, passionate, and profound desire for
http://www.newsnipple.com |fish and an equally deep, passionate, and
'88 EX500 |profound desire to avoid getting wet.
"Eric Mueller" <eric@themepark.com> writes:
... I'm baffled by
what seems to be a lack of detailed documentation for something so
fundamental as the built-in functions.
The docs we have can be found at
http://www.postgresql.org/devel-corner/docs/postgres/
While they're usable, I don't think there's anyone around here who
wouldn't agree that they could stand improvement. If you can read code
and write English, then Uncle Sam wants *YOU* to help improve the docs.
Feel free to step up to the plate and swing away ...
regards, tom lane
The docs we have can be found at
http://www.postgresql.org/devel-corner/docs/postgres/
While they're usable, I don't think there's anyone around here who
wouldn't agree that they could stand improvement. If you can read code
and write English, then Uncle Sam wants *YOU* to help improve the docs.
Feel free to step up to the plate and swing away ...
The docs for user-defined functions are very brief, but they do seem
to cover most of it, and they are mostly correct. It's not a fun
programming language, but I have written thousands of lines of it, and
it works. Maybe I'll try to document it a little bit.
On Wed, 3 Jan 2001, Lincoln Yeoh wrote:
Actually I agree with Eric.
The documentation is not as detailed or organised for usage. But it is
decent. Only sometimes you need to resort to real-life testing to see how
things really work. I do like the real world practical info which the MySQL
docs provide.There still isn't as much mention of limits and limitations of various
things as I'd like.Overall it's better than a "textbook style" doc.
In contrast: take a look at Oracle's installation manual for an example of
how bad things can get ;). Compare it with a Oracle HOWTO.
Then it goes right back to what someone else eluded to... If you don't
like what you see in the documentation, contribute. Send in diffs, or
even new items. If you don't know what's needed go to the DOCS list and
ask. Getting people to help with documentation for ANY project is like
pulling teeth. Finding people to criticize existing documentation is
quite easy.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
Then it goes right back to what someone else eluded to... If you don't
like what you see in the documentation, contribute. Send in diffs, or
even new items. If you don't know what's needed go to the DOCS list and
ask. Getting people to help with documentation for ANY project is like
pulling teeth. Finding people to criticize existing documentation is
quite easy.
What a horrible chicken and egg scenario... you can't help with providing
documentation until you have some deal of experience with something, but if
you ask for better documentation to get that experience, you are told to
write some. I understand the frustration, but at the same time, please
understand the frustration of those trying to learn. Personally I find the
documentation is more than adequate, but could always be improved. Hopefully
I will learn enough at some point to be able to say that I'm ready to
contribute... until then I'll be here feeding off the scraps of knowledge
that are dribbled here and there...
Greg
On Wed, 3 Jan 2001, Gregory Wood wrote:
Then it goes right back to what someone else eluded to... If you don't
like what you see in the documentation, contribute. Send in diffs, or
even new items. If you don't know what's needed go to the DOCS list and
ask. Getting people to help with documentation for ANY project is like
pulling teeth. Finding people to criticize existing documentation is
quite easy.What a horrible chicken and egg scenario... you can't help with providing
documentation until you have some deal of experience with something, but if
you ask for better documentation to get that experience, you are told to
write some. I understand the frustration, but at the same time, please
understand the frustration of those trying to learn. Personally I find the
documentation is more than adequate, but could always be improved. Hopefully
I will learn enough at some point to be able to say that I'm ready to
contribute... until then I'll be here feeding off the scraps of knowledge
that are dribbled here and there...
You need to read things again 'cuze you completely missed the point.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
I received an off list comment regarding this documentation issue that's
been discussed here the last few days. Rather than respond to the
comments privately I'm posting them to the whole list so that maybe folks
will understand that asking for documentation contributions doesn't
necessarily imply 'put up or shut up'.
1) Whining about anything is counter-productive.
2) Whining on the GENERAL list about documentation (which has its
own list) is also counter-productive.
3) Rather than complain that the docs are inadequate, incomplete,
too hard to read or just plain suck; give some examples on how
they can be improved, ask for clarifications to things that may
be confusing or yes, contribute something.
4) Did you figure out how to do something that the docs were vague about?
Explain it and send it to the DOCS list, you don't have to be a
technical writer or know SGML, but a couple of sentences or an example
or something to that nature will eventually help someone who is and
is in process of updating things. It'll also be in the archives
and available via search.
Bottom line: Something that's confusing or inadequate to you may not be
to the person that wrote it, but they won't know that something's wrong if
you don't tell them *specifically* what it is. I guess in that respect
'put up or shut up' is correct, but not completely.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
Import Notes
Reply to msg id not found: 001601c0758b$1fd6f920$7889ffcc@comstock.com | Resolved by subject fallback
What a horrible chicken and egg scenario... you can't help with providing
documentation until you have some deal of experience with something, but if
you ask for better documentation to get that experience, you are told to
write some. I understand the frustration, but at the same time, please
understand the frustration of those trying to learn. Personally I find the
documentation is more than adequate, but could always be improved. Hopefully
I will learn enough at some point to be able to say that I'm ready to
contribute... until then I'll be here feeding off the scraps of knowledge
that are dribbled here and there...
You are right of course but what happens once you have learned it? For me I
never seem seem to be able to do the right thing that being "now that I
have solved the problem I should write it down and submit it to the
maintainers of the document".
:wq
Tim Uckun
Due Diligence Inc. http://www.diligence.com/ Americas Background
Investigation Expert.
If your company isn't doing background checks, maybe you haven't considered
the risks of a bad hire.
Eric Mueller writes:
I'm baffled by what seems to be a lack of detailed documentation for
something so fundamental as the built-in functions.
Just to add a particular point, in addition to Vince's excellent general
points, as the one who was last to update the function documentation:
The reason that there's no "detailed" documentation is that there are no
details. How much can you write about round(), avg(), or current_user?
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
