postgre vs MySQL

[paul rivers-2]

Reece Hart wrote:

> On Tue, 2008-03-11 at 06:47 -0700, rrahul wrote:
>> Any major clients of the two.
>> You can add you own points too.
>
> Perhaps someone can comment on current MySQL backups procedures.  I
> believe that MySQL used to (still does?) require shutdown to be backed
> up. I don't know whether this was true for all engines or whether it
> might have been fixed. Having to shutdown a database to make a backup
> is a non-starter for anything that other than a toy (or read-only)
> databases.
>
> -Reece

For a database of InnoDB tables, people tend to replicate the database,
and then backup the slave (unless the db is trivially small, in which
case, mysqldump).  For MyISAM, you can back it up hot, or do the same
replication thing as with InnoDB tables.  

For larger and active MySQL installations, it's not uncommon to see a
MySQL database replicate to 2 or more slaves, and:

- use a slave to initialize any future additional slaves
- use a slave for backups
- promote a slave to master in case of master failure

There's the hot backup tool you can buy for InnoDB, but I've yet to meet
anyone who's actually used it.

Paul



--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Ivan Sergio Borgonovo]

On Wed, 12 Mar 2008 09:13:14 -0700
paul rivers <rivers.paul@...> wrote:

> For a database of InnoDB tables, people tend to replicate the
> database, and then backup the slave (unless the db is trivially

That recalled me the *unsupported* feeling I have that it is easier
to setup a HA replication solution on MySQL.

Pardon my ignorance of serious DBA jargon...

I'm thinking to something suited for load balancing the read as
highest priority in terms of performance *and* duplicate the write
across different boxes without the application layer has to know
about it as second priority in terms of performance...

I just would like to be contradicted and pointed to some viable
(easy?) setup for pgsql, so that I and other people will get rid of
this preconception if any.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[jose javier parra sanchez]

Take a look at pgpool . http://pgpool.projects.postgresql.org/
2008/3/12, Ivan Sergio Borgonovo <mail@...>:

> On Wed, 12 Mar 2008 09:13:14 -0700
>  paul rivers <rivers.paul@...> wrote:
>
>  > For a database of InnoDB tables, people tend to replicate the
>  > database, and then backup the slave (unless the db is trivially
>
>
> That recalled me the *unsupported* feeling I have that it is easier
>  to setup a HA replication solution on MySQL.
>
>  Pardon my ignorance of serious DBA jargon...
>
>  I'm thinking to something suited for load balancing the read as
>  highest priority in terms of performance *and* duplicate the write
>  across different boxes without the application layer has to know
>  about it as second priority in terms of performance...
>
>  I just would like to be contradicted and pointed to some viable
>  (easy?) setup for pgsql, so that I and other people will get rid of
>  this preconception if any.
>
>
>  --
>  Ivan Sergio Borgonovo
>  http://www.webthatworks.it
>
>
>
>  --
>  Sent via pgsql-general mailing list (pgsql-general@...)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-general
>

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Alvaro Herrera-7]

Ivan Sergio Borgonovo wrote:
> On Wed, 12 Mar 2008 09:13:14 -0700
> paul rivers <rivers.paul@...> wrote:
>
> > For a database of InnoDB tables, people tend to replicate the
> > database, and then backup the slave (unless the db is trivially
>
> That recalled me the *unsupported* feeling I have that it is easier
> to setup a HA replication solution on MySQL.

Well, if you have a crappy system that cannot sustain concurrent load or
even be backed up concurrently with regular operation, one solution is
to write a kick-ass replication system.

The other solution is to enhance the ability of the system to deal with
concurrent operation.

We keep hearing how great all those Web 2.0 sites are; Slashdot, Flickr,
etc; and they all run on farms and farms of MySQL servers, "because
MySQL replication is so good".  I wonder if replication is an actual
_need_ or it's there just because the other aspects of the system are so
crappy.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Scott Marlowe-2]

On Wed, Mar 12, 2008 at 10:15 AM, Alvaro Herrera
<alvherre@...> wrote:

> Ivan Sergio Borgonovo wrote:
>  > On Wed, 12 Mar 2008 09:13:14 -0700
>  > paul rivers <rivers.paul@...> wrote:
>  >
>  > > For a database of InnoDB tables, people tend to replicate the
>  > > database, and then backup the slave (unless the db is trivially
>  >
>  > That recalled me the *unsupported* feeling I have that it is easier
>  > to setup a HA replication solution on MySQL.
>
>  Well, if you have a crappy system that cannot sustain concurrent load or
>  even be backed up concurrently with regular operation, one solution is
>  to write a kick-ass replication system.
>
>  The other solution is to enhance the ability of the system to deal with
>  concurrent operation.
>
>  We keep hearing how great all those Web 2.0 sites are; Slashdot, Flickr,
>  etc; and they all run on farms and farms of MySQL servers, "because
>  MySQL replication is so good".  I wonder if replication is an actual
>  _need_ or it's there just because the other aspects of the system are so
>  crappy.

Reminds me of the saying that for each problem, there is a simple,
elegant solution that is completely wrong.  It amazes me that slony,
being basically a "bolt on" replication solution has given me much
fewer problems than MySQL replication which is known for silent
failures.  Slony is by no means perfect, but it is quite impressive as
both a replication tool and an upgrade tool.

An awful lot of the sites running on MySQL are running on it primarily
because it's what they started with, and now it's hard to switch
because their code is chock full of mysqlisms like "select field1,
field2 from table group by field1" and so on that no other database is
going to swallow without throwing an error.

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Ivan Sergio Borgonovo]

On Wed, 12 Mar 2008 17:47:35 +0100
"jose javier parra sanchez" <jojapasa@...> wrote:

> Take a look at pgpool . http://pgpool.projects.postgresql.org/

I knew about it.

Giving a look at
http://pgpool.projects.postgresql.org/#restriction
it doesn't seem something that can be completely hidden to the
application layer.

I know that most of the problems (not all) arise from the fact that
pg has features that MySQL can just dream of... but it doesn't look
as something that is really transparent to the application layer.

Not that this should imply I consider easy to achieve such result...
I know it is far from being easy, just that it doesn't look as what I
was trying to describe.

So let me rephrase, in order of importance:
- something completely transparent at the application layer
- something that won't die if one of your boxes die
- something that will improve performances of reads
- something that won't suffer too much for replicating writes

At a first sight it looks as if pgpool can't boost stuff in pl*
functions.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Ivan Sergio Borgonovo]

On Wed, 12 Mar 2008 10:26:21 -0700
"Scott Marlowe" <scott.marlowe@...> wrote:

> On Wed, Mar 12, 2008 at 10:15 AM, Alvaro Herrera
> <alvherre@...> wrote:
> > Ivan Sergio Borgonovo wrote:
> >  > On Wed, 12 Mar 2008 09:13:14 -0700
> >  > paul rivers <rivers.paul@...> wrote:
> >  >
> >  > > For a database of InnoDB tables, people tend to replicate the
> >  > > database, and then backup the slave (unless the db is
> >  > > trivially
> >  >
> >  > That recalled me the *unsupported* feeling I have that it is
> >  > easier to setup a HA replication solution on MySQL.
> >
> >  Well, if you have a crappy system that cannot sustain concurrent
> > load or even be backed up concurrently with regular operation,
> > one solution is to write a kick-ass replication system.
> >
> >  The other solution is to enhance the ability of the system to
> > deal with concurrent operation.
> >
> >  We keep hearing how great all those Web 2.0 sites are; Slashdot,
> > Flickr, etc; and they all run on farms and farms of MySQL
> > servers, "because MySQL replication is so good".  I wonder if
> > replication is an actual _need_ or it's there just because the
> > other aspects of the system are so crappy.

> Reminds me of the saying that for each problem, there is a simple,
> elegant solution that is completely wrong.  It amazes me that slony,
> being basically a "bolt on" replication solution has given me much
> fewer problems than MySQL replication which is known for silent
> failures.  Slony is by no means perfect, but it is quite impressive
> as both a replication tool and an upgrade tool.

I heard about Slony as well (dho!)...

I'm not complaining about anything... I do like PostgreSQL from a
programmer point of view and it makes my life easier.

I never pushed it to the limit I need replication, pooling etc...
Just as there is a myth out there that mysql outperform pgsql... I'm
here to testify there is another myth (?) that says that pg doesn't
have an easy (erm kick-ass) replication system.

I've no deep knowledge of Slashdot, Flickr or Google to say they
don't "high level" of data integrity/coherence as the one pg
offers and it is famous for... so I doubt they would hit the
limitations of systems like Slony or pgpool etc... etc... and maybe
they have no interest in the more advanced features and data
integrity pg has to offer...

> An awful lot of the sites running on MySQL are running on it
> primarily because it's what they started with, and now it's hard to
> switch because their code is chock full of mysqlisms like "select
> field1, field2 from table group by field1" and so on that no other
> database is going to swallow without throwing an error.

For what is worth I mostly share your opinion... just Google makes me
wonder...

--
Ivan Sergio Borgonovo
http://www.webthatworks.it


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Glyn Astill]

--- Greg Smith <gsmith@...> wrote:

> If you look at the link I passed along before, you'll see the
> difference
> with MySQL is that they've been abusing their customers with minor
> point
> releases that try to add new features.  Instead some of these
> introduce
> functional regressions, which often hang around for a whole long
> longer
> than two days after being noticed (this isn't even considering the
> delays
> before those fixes make their way back into the open source
> product, some
> only even go to paying customers).

This is something I noticed too when looking at MySQL and postgres.
The frequency of bug fixes and features, some coming over pretty
quickly from the community release of MySQL scared me.


      ___________________________________________________________
Rise to the challenge for Sport Relief with Yahoo! For Good  

http://uk.promotions.yahoo.com/forgood/


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Bruce Momjian-5]

Glyn Astill wrote:

>
> --- Greg Smith <gsmith@...> wrote:
>
> > If you look at the link I passed along before, you'll see the
> > difference
> > with MySQL is that they've been abusing their customers with minor
> > point
> > releases that try to add new features.  Instead some of these
> > introduce
> > functional regressions, which often hang around for a whole long
> > longer
> > than two days after being noticed (this isn't even considering the
> > delays
> > before those fixes make their way back into the open source
> > product, some
> > only even go to paying customers).
>
> This is something I noticed too when looking at MySQL and postgres.
> The frequency of bug fixes and features, some coming over pretty
> quickly from the community release of MySQL scared me.

MySQL has incentives to _not_ make their community release
production-quality.

--
  Bruce Momjian  <bruce@...>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Lincoln Yeoh]

At 09:47 PM 3/11/2008, rrahul wrote:

>Hi,
>
>I am a database professional but have never used Postgre. My client was
>exploring the posiblity of using Postgre instead of Mysql and wnated to know
>the comments from the community.
>I waned you people you post your views on the following comparision points
>1] Performance
>2] Scalablity
>3] community support
>4] Speed
>5] ease of use
>6] robustness
>
>Any major clients of the two.
>You can add you own points too.

The "advantage" of MySQL is it looks good on paper with all the ticks
on the extensive feature list so it's easy to convince bosses to use it.

But the problem with MySQL is you often can't use all the "advertised
features" at the same time, some of them are mutually exclusive.

For example, with MySQL if you want fast single user selects or
insert speeds you use MyISAM tables, but if you start to need
transactions or high concurrency writes you have to use InnoDB which
is significantly slower.

Want to have a consistent backup of your MySQL database? For MyISAM
tables you have to lock all tables till the backup is done, and that
can affect performance a lot. OK so you use InnoDB. But when the time
comes to _restore_a multiGB innodb table, you might find innodb a bit
too slow. Worse, apparently "fun" things happen if someone halts the
restore process halfway ;).

Guess what happens if you have a mix of table types.

A "solution" of course is to have multiple database servers with a
master server replicating to a slave server that's used for backups,
and resort to backing up stuff in on-disk format - shutdown slave and
copy the files. This makes restoring faster. But after all this,
MySQL stops looking so easy right?

In fact such a multi database set up just to do proper backups and
restores resembles a bad implementation of Postgresql's MVCC :).

Basically with Postgresql, there's a lot less of this "If you want to
use Cool Feature A, you can't have Wonderful Feature B" thing.

I did a simple mass insert test (followed by backup with pg_dump or
mysqldump and restore ) and postgresql 8.1 is faster or as fast as
MySQL 5.0.26 with MyISAM tables, and faster than MySQL with innodb
tables- default packages from suse 10.2 with tuning done for MySQL
(increase of buffers etc) but postgresql is as per suse 10.2 defaults.

I use MySQL daily at my workplace, and it's not something I recommend
you use if you had a choice. In the old days (before version 6.x)
postgresql wasn't good, but postgresql is way ahead now.

Lastly, the other problem with MySQL is its Innodb and BDB stuff are
now owned by Oracle. While Oracle is not squeezing MySQL yet, who
knows what will happen a few years later.

Regards,
Link.


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Joshua D. Drake]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 12 Mar 2008 14:35:19 -0400 (EDT)
Bruce Momjian <bruce@...> wrote:


> > This is something I noticed too when looking at MySQL and postgres.
> > The frequency of bug fixes and features, some coming over pretty
> > quickly from the community release of MySQL scared me.
>
> MySQL has incentives to _not_ make their community release
> production-quality.
>

This thread is making my talk at MySQLCon very interesting.

Joshua D. Drake


- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
      PostgreSQL political pundit | Mocker of Dolphins

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH2CP+ATb/zqfZUUQRAg78AKCNPJsFvLY7I/zxIXVZ9ndFtf9aMACdF7J+
QJQWDqDMLoj4lTiUIyGx1Ps=
=8Xe7
-----END PGP SIGNATURE-----

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Glyn Astill]

--- Bruce Momjian <bruce@...> wrote:

> Glyn Astill wrote:
> >
> > --- Greg Smith <gsmith@...> wrote:
> >
> > > If you look at the link I passed along before, you'll see the
> > > difference
> > > with MySQL is that they've been abusing their customers with
> minor
> > > point
> > > releases that try to add new features.  Instead some of these
> > > introduce
> > > functional regressions, which often hang around for a whole
> long
> > > longer
> > > than two days after being noticed (this isn't even considering
> the
> > > delays
> > > before those fixes make their way back into the open source
> > > product, some
> > > only even go to paying customers).
> >
> > This is something I noticed too when looking at MySQL and
> postgres.
> > The frequency of bug fixes and features, some coming over pretty
> > quickly from the community release of MySQL scared me.
>
> MySQL has incentives to _not_ make their community release
> production-quality.
>

I mean features being pulled into the enterprise release that haven't
had much time to be tested even in the community release.


      ___________________________________________________________
Rise to the challenge for Sport Relief with Yahoo! For Good  

http://uk.promotions.yahoo.com/forgood/


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Tom Lane-2]

Glyn Astill <glynastill@...> writes:
> --- Bruce Momjian <bruce@...> wrote:
>> MySQL has incentives to _not_ make their community release
>> production-quality.

> I mean features being pulled into the enterprise release that haven't
> had much time to be tested even in the community release.

For the last year or so it's actually the other way around: they
put things into the for-pay version that have *not* hit the community
version yet.  So you pay to be a beta tester ;-)

                        regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[paul rivers-2]

Alvaro Herrera wrote:

> Ivan Sergio Borgonovo wrote:
>  
>> On Wed, 12 Mar 2008 09:13:14 -0700
>> paul rivers <rivers.paul@...> wrote:
>>
>>    
>>> For a database of InnoDB tables, people tend to replicate the
>>> database, and then backup the slave (unless the db is trivially
>>>      
>> That recalled me the *unsupported* feeling I have that it is easier
>> to setup a HA replication solution on MySQL.
>>    
>
> Well, if you have a crappy system that cannot sustain concurrent load or
> even be backed up concurrently with regular operation, one solution is
> to write a kick-ass replication system.
>
> The other solution is to enhance the ability of the system to deal with
> concurrent operation.
>
> We keep hearing how great all those Web 2.0 sites are; Slashdot, Flickr,
> etc; and they all run on farms and farms of MySQL servers, "because
> MySQL replication is so good".  I wonder if replication is an actual
> _need_ or it's there just because the other aspects of the system are so
> crappy

"Kick-ass" imho really means "really simple to setup" and included as
part of the standard db.

There are all kinds of corner cases that can bite you with MySQL
replication. Offhand, I wager most of these (at least in InnoDB) result
from the replication "commit" status of a transaction is in the binlogs,
which is not the same as the InnoDB database commit status in the .ibd
files. Writing out binlog entries happens at a higher level than the
storage engine, and so it's not hard to imagine what can go wrong there.
There are a few my.cnf settings that let you really roll the dice with
data integrity based on this dichotomy, if you so choose.

In those high volume shops, imho replication is a requirement, but in
part to overcome technical limitations of MySQL. Or to phrase it from a
MySQL point of view, to do it the MySQL way. If you have 50-ish minutes,
this video by the YouTube people talks about their evolution with MySQL
(among many other things) :

http://video.google.com/videoplay?docid=-6304964351441328559

The summary from the video is:

- Start with a MySQL instance using InnoDB
- Go to 1-M replication, and use the replicants as read-only version.
- Eventually the cost of replication outweighs the gains, so go to
database sharding
- Keep 1-M replication within a shard group to allow easy backups of a
slave, some read-only use of the slaves, and a new master in case of
master failure (i.e. high availability)


Almost everyone finds MyISAM unworkable in large scale environments
because of the repairs necessary post-crash.


Big complaints about MySQL high-volume shops often, imho, come back to :

- You can only have so many active threads in the InnoDB storage engine
module at a time. See e.g.:

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#option_mysqld_innodb_thread_concurrency

- Auto_increment columns as pkeys in InnoDB tables are practically
required, yet severely limited scalability due to how a transaction
would lock the structure to get the next auto-increment (significantly
improved in 5.1)

- Shutting down a MySQL engine can take forever, due partly dirty page
writes, partly due to insert buffer merging. See:

http://dev.mysql.com/doc/refman/5.1/en/innodb-insert-buffering.html


There are other complaints you'd expect people to have, but don't seem
to get talked about much, because people are so used to (from my point
of view) working around them. For example, statistics on an InnoDB table
are calculated when the table is first accessed, but not stored
anywhere, so there are extra costs on database startup. The backup issue
with InnoDB has already been covered. Tablespace management in InnoDB
seems exceptionally primitive, and is helped somewhat by the
tablespace-per-table option. There are many more, again imho.

Paul







--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[David Wall]

> Well, if you have a crappy system that cannot sustain concurrent load or
> even be backed up concurrently with regular operation, one solution is
> to write a kick-ass replication system.
>  
Still, it would be nice to have a kick-ass replication system for PG,
too.  We've been toying with WAL archiving and backup db recovery, which
works pretty well it seems as it appears to support all of our tables
(not just those with an explicit primary key) and does the DDL stuff for
creating/altering/dropping tables, columns, etc.

The downside is that the backup is not operational in order to run even
run a SELECT against, and because it's asynchronous in nature, there's
always a window of data loss for transactions written to the WAL that
haven't been archived yet.

David


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Scott Marlowe-2]

On Wed, Mar 12, 2008 at 1:02 PM, paul rivers <rivers.paul@...> wrote:

>  - Auto_increment columns as pkeys in InnoDB tables are practically
>  required, yet severely limited scalability due to how a transaction
>  would lock the structure to get the next auto-increment (significantly
>  improved in 5.1)

Pretty sure they implemented the fix for that in an early 5.0 release.
 I remember chatting with Heikki Turri about it.

>  There are other complaints you'd expect people to have, but don't seem
>  to get talked about much, because people are so used to (from my point
>  of view) working around them. For example, statistics on an InnoDB table
>  are calculated when the table is first accessed, but not stored
>  anywhere, so there are extra costs on database startup. The backup issue
>  with InnoDB has already been covered. Tablespace management in InnoDB
>  seems exceptionally primitive, and is helped somewhat by the
>  tablespace-per-table option. There are many more, again imho.

I remember seeing something about some problems that using the
tablespace per table option on some mysql site... goes to look...
paraphrased from the Mysql Performance Blod...  Using the
innodb_file_per_table=1 setting really tends to work against you, as
you tend to get lots of bloated tables over time.  If all your innodb
tables are in the same file, then when one frees space, another can
use it.  with files per table, you can't recover space this way.


My real complaint with InnoDB is it's a red headed step child.  If
mysql supported only innodb, it would be a very different database,
and probably a bit simpler as well.  no need to worry about how you
state fk-pk relationships (currently column level references are
silently dropped for innodb OR myisam).  If there was a run time
switch that said "use only innodb and use syntax that's sane" I'd
probably be willing to test that out.

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[paul rivers-2]

Scott Marlowe wrote:

> On Wed, Mar 12, 2008 at 1:02 PM, paul rivers <rivers.paul@...> wrote:
>
>  
>>  - Auto_increment columns as pkeys in InnoDB tables are practically
>>  required, yet severely limited scalability due to how a transaction
>>  would lock the structure to get the next auto-increment (significantly
>>  improved in 5.1)
>>    
>
> Pretty sure they implemented the fix for that in an early 5.0 release.
>  I remember chatting with Heikki Turri about it.
>  

Definitely not fixed until 5.1, in fact not until very recently (5.1.22) :

http://bugs.mysql.com/bug.php?id=16979

Anyway, enough of that for me.  It's a Postgres list, and my list of
MySQL complaints is far longer than my enthusiasm for documenting them.


Paul




--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Thomas Pundt]

On Donnerstag, 13. März 2008, Scott Marlowe wrote:
| My real complaint with InnoDB is it's a red headed step child.  If
| mysql supported only innodb, it would be a very different database,
| and probably a bit simpler as well.  no need to worry about how you
| state fk-pk relationships (currently column level references are
| silently dropped for innodb OR myisam).  If there was a run time
| switch that said "use only innodb and use syntax that's sane" I'd
| probably be willing to test that out.

To be fair, MySQL-5 has such a switch (kind of):

  http://dev.mysql.com/doc/refman/5.0/en/faqs-sql-modes.html

You can set the @@sql_mode variable to a value, that MySQL almost behaves
like standard SQL (I've not tested this by myself, though).

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@...> ---- http://rp-online.de/ ----

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Scott Marlowe-2]

On Thu, Mar 13, 2008 at 1:25 AM, Thomas Pundt <mlists@...> wrote:

> On Donnerstag, 13. März 2008, Scott Marlowe wrote:
>  | My real complaint with InnoDB is it's a red headed step child.  If
>  | mysql supported only innodb, it would be a very different database,
>  | and probably a bit simpler as well.  no need to worry about how you
>  | state fk-pk relationships (currently column level references are
>  | silently dropped for innodb OR myisam).  If there was a run time
>  | switch that said "use only innodb and use syntax that's sane" I'd
>  | probably be willing to test that out.
>
>  To be fair, MySQL-5 has such a switch (kind of):
>
>   http://dev.mysql.com/doc/refman/5.0/en/faqs-sql-modes.html
>
>  You can set the @@sql_mode variable to a value, that MySQL almost behaves
>  like standard SQL (I've not tested this by myself, though).

Sadly running in ansi mode only fixes about half the problems.  The
FK-PK syntax on a column level is still ignored in ansi sql mode, and
a lot of other issues are still there.  Admittedly, it does do things
like check ints for sane input values, and refuses to truncate
varchars silently if they're too large.  But that's the kind of thing
you really shouldn't have to be turning on in the first place.

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[rrahul]

Thanks to all you wonderful people out their. I don't know if its your love for Postgres or nepothism that makes it look far superior than mysql.
But why does the client list dosen't tell that?
I see Mysql bosting for Google,Yahoo, Alcatel......
What about Postgres the list is not that impressive.

Are their any major implementations that moved from Mysql to Postgres?
Howmany out their have done this or will advice to do that?

cheers,
Rahul.