Humor me: Postgresql vs. MySql (esp. licensing)

[John Wells]

Yes, I know you've seen the above subject before, so please be gentle with
the flamethrowers.

I'm preparing to enter a discussion with management at my company
regarding going forward as either a MySql shop or a Postgresql shop.

It's my opinion that we should be using PG, because of the full ACID
support, and the license involved. A consultant my company hired before
bringing me in is pushing hard for MySql, citing speed and community
support, as well as ACID support.

My biggest concern with MySQL is licensing. We need to keep costs low,
and last I remember the parent company was being pretty strict on "fair
use" under the GPL. If I recall, they even said a company would have to
license the commercial version if it were simply used operationally within
the company.

Also, I was under the impression that Postgresql had pretty much caught up
with MySql in the speed category...is this not the case?

Finally, ACID support in mysql always seemed kind of a hack....perhaps
this has changed?

Thanks for any input (armament ;) ) you can provide.

John

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

[scott.marlowe]

On Fri, 10 Oct 2003, Martin Marques wrote:

El Jue 09 Oct 2003 22:44, Marsh Ray escribió:

Just out of curiosity, what does Debian make MySQL's rather bizarre
interpretaion of the GPL:

http://www.mysql.com/documentation/m...roduction.html
#Copyright --- begin quote ----

You need a commercial license:
[...]
When you distribute a non-|GPL| application that *only* works with
the |MySQL| software and ship it with the |MySQL| software. This type of
solution is considered to be linking even if it's done over a network.

--- end quote ----
"Linking over a network"? What stops some GPL'ed web server (or
commercial one for that matter) from demanding non-free licensing for
web clients that connect to it?

I would like to know what Debian is going to do with PHP and MySQL. There's
alot of talk about this in the PHP related lists.
PHP folks do think that there may be some sort of incompatibility between the
two licenses.

Basically, if you include MySQL connect libs in PHP and distribute it PHP
now falls under the GPL. Since PHP's license expressly forbids
relicensing, they are incompatible to distribute together. This was
caused by MySQL changing their connect libs from LGPL to GPL. It's why
PHP now no longer includes MySQL connect libs in their source tree.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Oliver Elphick]

On Fri, 2003-10-10 at 12:54, Martin Marques wrote:

I would like to know what Debian is going to do with PHP and MySQL.

You can raise the issue with Debian's mysql package maintainer
(my***@packages.debian.org) and with the mailing list
de**********@lists.debian.org.

--
Oliver Elphick Ol************@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"He that covereth his sins shall not prosper; but whoso
confesseth and forsaketh them shall have mercy."
Proverbs 28:13
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Rick Gigger]

My experience with mysql and postgres was this. I had some apps that were
running on SQL Server and I wanted to get rid of it because it was
expensive. Didn't really do much for us that the others couldn't, and I
wanted to get rid of windows. Plus administratively SQL Server just seemed
to have a lot more weird problems than the other two. So I started porting
one of my apps simultaneously to both postgres and mysql. I got to a part
of my app that used unions. At that time mysql didn't support unions (I
realize that it does now). I was absolutely amazed. Needless to say I
dropped mysql right there and have used nothing but postgres ever since.
The learning curve was maybe a tiny, tiny bit steeper than mysql but that is
just because postgres tends to do things right and then make them simple
(for example having sequences and then adding the serial key word) whereas
mysql just makes them simple. Once I started figuring things out though I
lost any desire to ever look at mysql again because there were so many areas
where postgres excelled and mysql lacked.

crg

----- Original Message -----
From: "bob parker" <bo********@dodo.com.au>
To: "John Wells" <[e-mail]>; <pg***********@postgresql.org>
Sent: Thursday, October 09, 2003 5:12 PM
Subject: Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)

On Thu, 9 Oct 2003 01:28, John Wells wrote:

Yes, I know you've seen the above subject before, so please be gentle with the flamethrowers.

I'm preparing to enter a discussion with management at my company
regarding going forward as either a MySql shop or a Postgresql shop.

It's my opinion that we should be using PG, because of the full ACID
support, and the license involved. A consultant my company hired before
bringing me in is pushing hard for MySql, citing speed and community
support, as well as ACID support.
Apologies for the empty reply - my mind is on brain death so I needed to
imitate it.

I'll address only the alleged community support for MySql because you will
get much better qualified replies to your other concerns from others.

About 18 months ago I had to choose a DB for my home grown small systems.
Knowing very little about them I lurked on the both this list and a MySql
list for a couple of months.

In contrast to this list, the MySql one not only had a high proportion of
brain dead questions, there were a fair few answers of the same grade too.

I quickly decided that Postgresql was the better product by far for that

and many other reasons.

HTH
Bob

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Vivek Khera]

>>>>> "sm" == scott marlowe <scott.marlowe> writes:

will ungracefully kill the DB process(es). Doesn't matter what DB (or
any other application) you're running, you *can* lose data this way.

sm> While it is possible to lose a non-committed transaction, WAL prevents the
sm> database from becoming corrupted. Assuming proper fsyncing of your hard
sm> drives (i.e. SCSI, or IDE with write cache disabled)

So you're saying it is not possible to corrupt the WAL if the process
is ungracefully killed by the OS?

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.com Rockville, MD [phone]
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruce Momjian]

Vivek Khera wrote:

>> "sm" == scott marlowe <scott.marlowe> writes: will ungracefully kill the DB process(es). Doesn't matter what DB (or
any other application) you're running, you *can* lose data this way.

sm> While it is possible to lose a non-committed transaction, WAL prevents the
sm> database from becoming corrupted. Assuming proper fsyncing of your hard
sm> drives (i.e. SCSI, or IDE with write cache disabled)

So you're saying it is not possible to corrupt the WAL if the process
is ungracefully killed by the OS?

Yes, it is impossible, even if the OS crashes too.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

http://archives.postgresql.org

[scott.marlowe]

On Fri, 10 Oct 2003, Vivek Khera wrote:

>> "sm" == scott marlowe <scott.marlowe> writes: will ungracefully kill the DB process(es). Doesn't matter what DB (or
any other application) you're running, you *can* lose data this way.

sm> While it is possible to lose a non-committed transaction, WAL prevents the
sm> database from becoming corrupted. Assuming proper fsyncing of your hard
sm> drives (i.e. SCSI, or IDE with write cache disabled)

So you're saying it is not possible to corrupt the WAL if the process
is ungracefully killed by the OS?

No, but it doesn't matter if it is corrupted, because the corrupted part
would be at the end, where a transaction was starting, and would just get
ignored. i.e. postgresql would replay only the parts of the WAL that
were complete and showed as committed.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Vivek Khera <kh***@kcilink.com> writes:

I think it is a timing issue. The PG has no way to notify the OS that
it has finished exiting, so if it takes a long time to exit, the OS
will ungracefully kill the DB process(es). Doesn't matter what DB (or
any other application) you're running, you *can* lose data this way.

You most certainly will *not* lose data that way. Committed
transactions will stay committed, no matter how the Postgres processes die.

regards, tom lane

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Miquel van Smoorenburg]

In article <39**********************************@mail.devsea. com>,
John Wells <[e-mail]> wrote:

Here's an interesting response from mysql.com sales. Frankly, I don't see
how using it on multiple internal servers violates the GPL?!?:

You're talking to a sales droid, a suit, someone whose brain
cells have died off because his tie was tied to tight.

So remember the old adage .. "never attribute to malice that
which can be adequately explained by stupidity."

Mike.
--
Never trust a statistic you didn't fake yourself.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Randolf Richardson, DevNet SysOp 29]

[sNip]

Do a shutdown -h on a live database machine with pg. It will gracefully
shut itself down.

Is that true for all OS flavors and is it dependent upon the DBA having
set up proper shutdown scripts?

[sNip]

When I tested this on PostgreSQL on Novell NetWare 6 it shut down
gracefully. I don't see why this would be different on other Operating
Systems since the code base is essentially the same (isn't it?).

--
Randolf Richardson - [e-mail]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

[Randolf Richardson, DevNet SysOp 29]

[sNip]

"We have all the features they do! Nobody uses views or triggers!"

Which cave has that person been hiding in all these years? Views are a
very important part of SQL, and any SQL server that doesn't support Views is,
in my view (sorry, I couldn't resist), simply isn't suitable for large scale
applications (and even some small ones that will use a table in many
different ways).

--
Randolf Richardson - [e-mail]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

[Randolf Richardson, DevNet SysOp 29]

>>Here's an interesting response from mysql.com sales. Frankly, I don't see

how using it on multiple internal servers violates the GPL?!?:

You're talking to a sales droid, a suit, someone whose brain
cells have died off because his tie was tied to tight.

[sNip]

That's an official answer from the company, and it should be treated as
such. If you think an employee is spreading mis-information, then you should
contact the company directly and ask for further clarification with a short
explanation of your doubts about the information you were provided.

Making insults to discredit someone because you don't like their
official response due to their job title is a childish tactic that doesn't
help anyone.

--
Randolf Richardson - [e-mail]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

[Alex Satrapa]

Randolf Richardson, DevNet SysOp 29 wrote:

[sNip]

Do a shutdown -h on a live database machine with pg. It will gracefully
shut itself down.

Is that true for all OS flavors and is it dependent upon the DBA having
set up proper shutdown scripts?

[sNip]

When I tested this on PostgreSQL on Novell NetWare 6 it shut down
gracefully. I don't see why this would be different on other Operating
Systems since the code base is essentially the same (isn't it?).

No it's not. Don't confuse the PostgreSQL code base with the operating
system it's running on.

On Mac OS X (desktop version, at least) there are no shutdown scripts.
All running applications are simply sent the "TERM" signal, then later
sent the "KILL" signal. Luckily enough, PostgreSQL seems to respond to
TERM by shutting down gracefully.

Totally off topic, but this lack of shutdown scripts, along with a lack
of proper package management are the two most painful faults in Mac OS X.

Alex

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Randolf Richardson, DevNet SysOp 29]

[sNip]

Do a shutdown -h on a live database machine with pg. It will gracefully
shut itself down.

Is that true for all OS flavors and is it dependent upon the DBA having
set up proper shutdown scripts?

[sNip]

When I tested this on PostgreSQL on Novell NetWare 6 it shut down
gracefully. I don't see why this would be different on other Operating
Systems since the code base is essentially the same (isn't it?).

--
Randolf Richardson - [e-mail]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

[Randolf Richardson, DevNet SysOp 29]

>>>>Do a shutdown -h on a live database machine with pg. It will

gracefully shut itself down.

[sNip] When I tested this on PostgreSQL on Novell NetWare 6 it shut down
gracefully. I don't see why this would be different on other Operating
Systems since the code base is essentially the same (isn't it?).

No it's not. Don't confuse the PostgreSQL code base with the operating
system it's running on.

[sNip]

On NetWare if I type "Unload Postmaster.NLM" then PostgreSQL's
shutdown functions are called and PostgreSQL proceeds with a graceful
shutdown.

I just assumed that all Operating Systems that PosgreSQL has been
ported to have this type of call-back API functionality built-in for
shutdown signals.

Now isn't this a part of the PostgreSQL code base to provide the
graceful shutdown functionality when the OS attempts to shut it down?

--
Randolf Richardson - [e-mail]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

[Randolf Richardson, DevNet SysOp 29]

[sNip]

"We have all the features they do! Nobody uses views or triggers!"

Which cave has that person been hiding in all these years? Views are a
very important part of SQL, and any SQL server that doesn't support Views is,
in my view (sorry, I couldn't resist), simply isn't suitable for large scale
applications (and even some small ones that will use a table in many
different ways).

--
Randolf Richardson - [e-mail]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

[Randolf Richardson, DevNet SysOp 29]

>>Here's an interesting response from mysql.com sales. Frankly, I don't see

how using it on multiple internal servers violates the GPL?!?:

You're talking to a sales droid, a suit, someone whose brain
cells have died off because his tie was tied to tight.

[sNip]

That's an official answer from the company, and it should be treated as
such. If you think an employee is spreading mis-information, then you should
contact the company directly and ask for further clarification with a short
explanation of your doubts about the information you were provided.

Making insults to discredit someone because you don't like their
official response due to their job title is a childish tactic that doesn't
help anyone.

--
Randolf Richardson - [e-mail]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

[Alex Satrapa]

Randolf Richardson, DevNet SysOp 29 wrote:

[sNip]

Do a shutdown -h on a live database machine with pg. It will gracefully
shut itself down.

Is that true for all OS flavors and is it dependent upon the DBA having
set up proper shutdown scripts?

[sNip]

When I tested this on PostgreSQL on Novell NetWare 6 it shut down
gracefully. I don't see why this would be different on other Operating
Systems since the code base is essentially the same (isn't it?).

No it's not. Don't confuse the PostgreSQL code base with the operating
system it's running on.

On Mac OS X (desktop version, at least) there are no shutdown scripts.
All running applications are simply sent the "TERM" signal, then later
sent the "KILL" signal. Luckily enough, PostgreSQL seems to respond to
TERM by shutting down gracefully.

Totally off topic, but this lack of shutdown scripts, along with a lack
of proper package management are the two most painful faults in Mac OS X.

Alex

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Randolf Richardson, DevNet SysOp 29]

>>>>Do a shutdown -h on a live database machine with pg. It will

gracefully shut itself down.

[sNip] When I tested this on PostgreSQL on Novell NetWare 6 it shut down
gracefully. I don't see why this would be different on other Operating
Systems since the code base is essentially the same (isn't it?).

No it's not. Don't confuse the PostgreSQL code base with the operating
system it's running on.

[sNip]

On NetWare if I type "Unload Postmaster.NLM" then PostgreSQL's
shutdown functions are called and PostgreSQL proceeds with a graceful
shutdown.

I just assumed that all Operating Systems that PosgreSQL has been
ported to have this type of call-back API functionality built-in for
shutdown signals.

Now isn't this a part of the PostgreSQL code base to provide the
graceful shutdown functionality when the OS attempts to shut it down?

--
Randolf Richardson - [e-mail]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.

[Tom Lane]

Alex Satrapa <al**@lintelsys.com.au> writes:

On Mac OS X (desktop version, at least) there are no shutdown scripts.
All running applications are simply sent the "TERM" signal, then later
sent the "KILL" signal. Luckily enough, PostgreSQL seems to respond to
TERM by shutting down gracefully.
No "luckily" about it: that's been the standard shutdown procedure for
Unix systems since approximately forever, and the signal responses of
the Postgres backend were consciously chosen to behave well with it.
Totally off topic, but this lack of shutdown scripts, along with a lack
of proper package management are the two most painful faults in Mac OS X.

I dunno whether OS X is lacking in shutdown scripts or not --- but PG
is built to shut down cleanly on any moderately-standard Unix system,
whether you have a shutdown script for it or not. OS X is certainly
standard enough for this.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bruce Momjian]

Tom Lane wrote:

Alex Satrapa <al**@lintelsys.com.au> writes:

On Mac OS X (desktop version, at least) there are no shutdown scripts.
All running applications are simply sent the "TERM" signal, then later
sent the "KILL" signal. Luckily enough, PostgreSQL seems to respond to
TERM by shutting down gracefully.

No "luckily" about it: that's been the standard shutdown procedure for
Unix systems since approximately forever, and the signal responses of
the Postgres backend were consciously chosen to behave well with it.

Totally off topic, but this lack of shutdown scripts, along with a lack
of proper package management are the two most painful faults in Mac OS X.

I dunno whether OS X is lacking in shutdown scripts or not --- but PG
is built to shut down cleanly on any moderately-standard Unix system,
whether you have a shutdown script for it or not. OS X is certainly
standard enough for this.

The one problem with the signal approach is how long does the system
wait before giving up on the app shutdown? Seems that should be
something controllable by the admin, but without shutdown scripts, it
isn't.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Bruce Momjian <pg***@candle.pha.pa.us> writes:

The one problem with the signal approach is how long does the system
wait before giving up on the app shutdown? Seems that should be
something controllable by the admin, but without shutdown scripts, it
isn't.

I believe 20 seconds is the standard number --- that's plenty for
Postgres. (I know that it is about 20 seconds on OS X, because
that's how much time tended to get added to the shutdown procedure
back when OS X 10.0 had that shutdown bug that prevented the postmaster
from forking a shutdown subprocess.)

The fact that the number isn't readily configurable is indeed a PITA.
In a previous lifetime I ran a data-collection application that needed
more than 20 seconds to shut down, and so would not exit cleanly if
you didn't have a shutdown script step that would wait for it. But
I don't see it as a problem for Postgres.

regards, tom lane

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

[Joshua D. Drake]

I believe 20 seconds is the standard number --- that's plenty for
Postgres.
Not always. More than once I have had a postgresql connection hung up
that will stop the main postmaster from dying on TERM. However the
machine will eventually kill it hard and thus could produce during restart.

Sincerely,

Joshua Drake

(I know that it is about 20 seconds on OS X, because
that's how much time tended to get added to the shutdown procedure
back when OS X 10.0 had that shutdown bug that prevented the postmaster
from forking a shutdown subprocess.)

The fact that the number isn't readily configurable is indeed a PITA.
In a previous lifetime I ran a data-collection application that needed
more than 20 seconds to shut down, and so would not exit cleanly if
you didn't have a shutdown script step that would wait for it. But
I don't see it as a problem for Postgres.

regards, tom lane

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

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - [e-mail] - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org

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

[Tom Lane]

"Joshua D. Drake" <[e-mail]> writes:

I believe 20 seconds is the standard number --- that's plenty for
Postgres.

Not always. More than once I have had a postgresql connection hung up
that will stop the main postmaster from dying on TERM. However the
machine will eventually kill it hard and thus could produce during restart.

Seems fine to me. Otherwise such a hangup could prevent system shutdown
indefinitely, which is a Bad Thing when your UPS batteries have thirty
seconds left in 'em ...

You will get a WAL replay at restart in that scenario, but your data
should be perfectly safe.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Alex Satrapa]

Tom Lane wrote:

"Joshua D. Drake" <[e-mail]> writes:

Not always. More than once I have had a postgresql connection hung up
that will stop the main postmaster from dying on TERM. However the
machine will eventually kill it hard and thus could produce during restart.

Seems fine to me. Otherwise such a hangup could prevent system shutdown
indefinitely, which is a Bad Thing when your UPS batteries have thirty
seconds left in 'em ...

The thing that bothers me is that the OS will 'KILL' instead of 'TERM'
when applications haven't gone away fast enough. If it takes PostgreSQL
21 seconds to finish shutting down cleanly, it's taken too long.

Using a shutdown script, most OSes will wait for the script to terminate
before going into TERM/wait/KILL mode.

I've been told that apparently Mac OS X does support shutdown scripts,
so I'll experiment a little with them over the next day or so, and
respond to this message once I've got some conclusive evidence either way.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Scott Ribe]

> On Mac OS X (desktop version, at least) there are no shutdown scripts.

10.0 and 10.1, but this changed starting with 10.2

--
Scott Ribe
sc********@killerbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend