Thread: PostgreSQL vs. MySQL
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
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?"
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.
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
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
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.
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.
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