Thread: PostgreSQL vs. MySQL

PostgreSQL vs. MySQL

From
Vratislav_Morkus@lmc.cz
Date:
Hi all,
I am sorry for a stupid easy question, but I'am PostgreSQL novice.
Our development team has encountered problem with trying to install and
maintain cluster (pgcluster) on our production database. So they have
decided to switch the entire solution to MySql database.
a) have you got any good/bad experience with administering and running
cluster on PostrgeSQL DB?
b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why?

thx for any answer
Vrata


Re: PostgreSQL vs. MySQL

From
"Jim C. Nasby"
Date:
On Mon, Aug 01, 2005 at 06:44:50PM +0200, Vratislav_Morkus@lmc.cz wrote:
> Hi all,
> I am sorry for a stupid easy question, but I'am PostgreSQL novice.
> Our development team has encountered problem with trying to install and
> maintain cluster (pgcluster) on our production database. So they have
> decided to switch the entire solution to MySql database.
> a) have you got any good/bad experience with administering and running
> cluster on PostrgeSQL DB?
> b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why?

For pgcluster help, your best bet is to hit either their mailling list
or their forums. http://pgfoundry.org/projects/pgcluster/ has more info.

As for MySQL, http://sql-info.de/mysql/gotchas.html has about 100
different reasons why you don't want to use MySQL. Some favorites:

Feb. 31st is a valid date
Data will be silently truncated if it overflows
1/0 = NULL
count(*) is an approximation
easy to configure in such a way that it's not ACID
...
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: PostgreSQL vs. MySQL

From
Scott Marlowe
Date:
On Mon, 2005-08-01 at 11:44, Vratislav_Morkus@lmc.cz wrote:
> Hi all,
> I am sorry for a stupid easy question, but I'am PostgreSQL novice.
> Our development team has encountered problem with trying to install and
> maintain cluster (pgcluster) on our production database. So they have
> decided to switch the entire solution to MySql database.
> a) have you got any good/bad experience with administering and running
> cluster on PostrgeSQL DB?
> b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why?

I would never switch from pgsql to mysql.

Here's a short list of the reasons why:

http://sql-info.de/mysql/gotchas.html

If they're having problems and haven't asked for help in these lists,
then I'm willing to be they're just looking for an excuse to change to
what they're comfortable with, and not really serious about using
postgresql.

Also, why pgcluster?  why not slony or mammoth replicator?  Both of
those are fine pieces of software that handle most replication needs.
Combine slony with pgpool and a few scripts and you've got quite a nice
cluster setup.

Of course, setting mysql up is a bit easier.  But I don't trust their
replication, and searching google for mysql and clustering and problems
or what not will tell you why.

Computer Science ain't easy, and picking a product because it seems
easier to use up front is often the worst of all decisions.

Re: PostgreSQL vs. MySQL

From
Tino Wildenhain
Date:
Am Montag, den 01.08.2005, 18:44 +0200 schrieb Vratislav_Morkus@lmc.cz:
> Hi all,
> I am sorry for a stupid easy question, but I'am PostgreSQL novice.
> Our development team has encountered problem with trying to install and
> maintain cluster (pgcluster) on our production database. So they have
> decided to switch the entire solution to MySql database.
> a) have you got any good/bad experience with administering and running
> cluster on PostrgeSQL DB?
> b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why?

Usually only a few installations need a cluster solution anyway.
Did you try with optimized queries and a single machine
postgres? You have online backup too so you dont need a cluster
for this functionality.
If it turns out you have to distribute load somehow, there
is slony and pgpool.

Regards
Tino Wildenhain


Re: PostgreSQL vs. MySQL

From
Robert Treat
Date:
On Monday 01 August 2005 13:52, Scott Marlowe wrote:
> On Mon, 2005-08-01 at 11:44, Vratislav_Morkus@lmc.cz wrote:
> > Hi all,
> > I am sorry for a stupid easy question, but I'am PostgreSQL novice.
> > Our development team has encountered problem with trying to install and
> > maintain cluster (pgcluster) on our production database. So they have
> > decided to switch the entire solution to MySql database.
> > a) have you got any good/bad experience with administering and running
> > cluster on PostrgeSQL DB?
> > b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why?
>
> I would never switch from pgsql to mysql.
>
> Here's a short list of the reasons why:
>
> http://sql-info.de/mysql/gotchas.html
>
> If they're having problems and haven't asked for help in these lists,
> then I'm willing to be they're just looking for an excuse to change to
> what they're comfortable with, and not really serious about using
> postgresql.
>
> Also, why pgcluster?  why not slony or mammoth replicator?  Both of
> those are fine pieces of software that handle most replication needs.
> Combine slony with pgpool and a few scripts and you've got quite a nice
> cluster setup.
>

Can someone point me to the multi-master replication docs for my$ql 4.1?  I
agree with Scott, sounds like they are looking for an excuse.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Re: PostgreSQL vs. MySQL

From
Gregory Youngblood
Date:
On Aug 1, 2005, at 4:33 PM, Robert Treat wrote:

> On Monday 01 August 2005 13:52, Scott Marlowe wrote:
>
>> On Mon, 2005-08-01 at 11:44, Vratislav_Morkus@lmc.cz wrote:
>>
>>> Hi all,
>>> I am sorry for a stupid easy question, but I'am PostgreSQL novice.
>>> Our development team has encountered problem with trying to
>>> install and
>>> maintain cluster (pgcluster) on our production database. So they
>>> have
>>> decided to switch the entire solution to MySql database.
>>> a) have you got any good/bad experience with administering and
>>> running
>>> cluster on PostrgeSQL DB?
>>> b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why?
>>>
>>
>> I would never switch from pgsql to mysql.
>>
>> Here's a short list of the reasons why:
>>
>> http://sql-info.de/mysql/gotchas.html
>>
>> If they're having problems and haven't asked for help in these lists,
>> then I'm willing to be they're just looking for an excuse to
>> change to
>> what they're comfortable with, and not really serious about using
>> postgresql.
>>
--snip--
> Can someone point me to the multi-master replication docs for my$ql
> 4.1?  I
> agree with Scott, sounds like they are looking for an excuse.
>

I don't believe mysql 4.1 has multi-master replication. At least not
open sourced. If you find them, I'd be interested in reading/learning
about it.

I believe Mysql 5.0 will have a multi-master storage engine. But, it
will have a lot of gotchas too. For one, it's all in memory, so be
prepared to have several machines with lots and lots of RAM. The
Mysql docs for 5.0 cover it quite well. Watch out for its
wastefulness though. It does things like reserve space based on the
max size of a field, so if you have a varchar(250), then every row in
the database will have 250 bytes of space allocated, even if you only
use 1. At least, that's how it was documented the last time I looked
at it.

I had to rule out the mysql cluster for a project I'm working on due
to how some things are implemented.

Re: PostgreSQL vs. MySQL

From
"littlebutty"
Date:
Ou company just migrated in the opposite direction.  We moved from
MySQL 4.1 to PostgreSQL 8.  We had serious issues with MySQL and
foreign key support as well as data validation.  For example: MySQL
will simply insert an empty string value for a required field that was
not supplied.  We also increased productivity by leveraging triggers
and stored procdures with PostgreSQL.


Re: PostgreSQL vs. MySQL

From
Chris Travers
Date:
Vratislav_Morkus@lmc.cz wrote:

>Hi all,
>I am sorry for a stupid easy question, but I'am PostgreSQL novice.
>Our development team has encountered problem with trying to install and
>maintain cluster (pgcluster) on our production database. So they have
>decided to switch the entire solution to MySql database.
>a) have you got any good/bad experience with administering and running
>cluster on PostrgeSQL DB?
>
>
no experience whatsoever.  What exactly are you trying to accomplish?
There are many other replication solutions as well which may work better
for your solution at the moment.

>b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why?
>
>
>
Yes I would switch.

1)  PostgreSQL is far more standards compliant than MySQL
2)  PostgreSQL does not silently truncate your data unless you tell it
to do so.
3)  Performs better with complex queries and under load.

Quoting from my migration guide:


Information is the life-breath of any business in the way that money is
its life-blood. Many busi-
nesses today use Relational Dabase Management Systems (RDBMS's) to
manage this critical asset.
As such, it is important that the RDBMS chosen to manage this
information is as robust and powerful
as possible. PostgreSQL offers many powerful tools to provide
information from your database for a
variety of uses, including integration of diverse applications,
reporting, and a variety of other uses.
PostgreSQL is also quite scalable and extremely robust.
   PostgreSQL is also the most standards-compliant open source database
around, implimenting
more SQL-99 features than MySQL or FirebirdSQL. It has a very vibrant
community, and is free from
many of the licensing issues that have, as of the time of this writing,
surfaced with MySQL. Use or
even distribution of PostgreSQL will never require an additional license
from a commercial entity, for
example.
   PostgreSQL also performs better than MySQL in many real-world
scenarios. While MySQL does
perform better for simple read-only operations when transactional
control is disabled, PostgreSQL
handles more complex queries with ease, and provides better performance
under high load than
MySQL, especially when some users are writing to the database.
Therefore, while MySQL is quite
well suited for simple content management tools where there is no need
to integrate it with other line
of business applications, PostgreSQL is better for any application of
reasonable complexity.
    Finally, as of version 4.x, MySQL does not strictly check the
validity of information written to the
database. Numbers may be silently truncated, for example, or invalid
dates, such as Feb 31, 2005,
could be entered into the database. While this is possibly acceptible
where only a single application
is using the database, it becomes unacceptable quickly when several
independent applications must
access the same data because a bug in any one application could allow
invalid (or worse, erroneous)
data to be saved. Even when strict mode debuts in 5.x, it is unlikely
that this will be enabled by
default as MySQL will be largely required to be backwards compatible.


Hope this helps.

Chris Travers
Metatron Technology Consulting

Attachment