[SLL] Need MySQL expert

[Kyle Hayes]

On Thursday 03 August 2006 09:23, Justin Simpson wrote:
> Not that this helps you in any way, but this situation is
> a good example of why postgresql is a better choice than
> mysql.  Multi Version Concurrency Control.

This was true when MySQL 3.23.x was the standard shipping 
version.  Version 4.1 is now with the InnoDB engine.  That 
supports all kinds of concurrent actions depending on how 
you set it up.  By default it allows multiple versions of 
the data to exist simultaneously.

Postgres does have some features not yet in stable MySQL 
releases such as triggers, stored procedures etc.  If you 
need those, then Postgres may well be a better choice than 
MySQL.  However, allowing multiple modifier/reader access 
is not one of the differences anymore.

For applications I have done, MySQL seems to fit better.  
Those apps were generally:

A) relatively highly threaded or had many processes.  MySQL 
was the only DB tested that scaled to 500 simultaneous 
connections without pain.  Postgres did not do well (MySQL 
uses threads and Postgres uses processes and I suspect that 
is part of the reason for the difference).

B) cross platform use.  MySQL runs on Windows and has done 
so for years.  Postgres has only recently released Windows 
versions.  You can trust your data to it if you want...

C) different data treatment.  Often, I find that I need to 
treat different kinds of data differently.  With MySQL, I 
can chose the table type that is closest to what I need for 
that data.

D) non-invasive replication.  I hesitate to call MySQL's 
version of this "replication" because all it does is copy 
SQL statements back and forth.  But, for some things this 
is just what you wanted.  It is trivial to set up and start 
so you can add it later if you want.

Where MySQL has some serious shortcomings are in the areas 
of:

- ANSI SQL compliance.  While is has a lot of additions, 
MySQL leaves off a lot.  Like triggers.  If you are doing 
heavy lifting in the DB, MySQL is NOT your best choice.  
Postgres does very well here.

- Replication.  I listed it above as a feature, but it can 
become a nightmare unless you really understand what it is 
doing.  If you have non-deterministic mutator SQL, you are 
screwed.  Don't use the filtering.  Just don't.

- distro stability was a problem for a while.  I have not 
used distro versions of MySQL for a long time, so maybe 
this is not true anymore.  But, for quite a while, the 
binaries from mysql.com were a lot more stable than those 
in RPM from various vendors.

- licensing.  Postgres has a better license for many 
purposes.  MySQL even annoyed some distros to the point 
that they only shipped older versions with more permissive 
licenses.  I think this has mostly been cleared up, but it 
is something to think about depending on what your 
application is and how you want to license it.

> from the postgres docs
> http://www.postgresql.org/docs/8.1/interactive/mvcc.html
>
> Unlike traditional database systems which use locks for
> concurrency control, PostgreSQL maintains data
> consistency by using a multiversion model (Multiversion
> Concurrency Control, MVCC). This means that while
> querying a database each transaction sees a snapshot of
> data (a database version) as it was some time ago,
> regardless of the current state of the underlying data.
> This protects the transaction from viewing inconsistent
> data that could be caused by (other) concurrent
> transaction updates on the same data rows, providing
> transaction isolation for each database session.

I'm surprized not to see Krow chip in here...

> The main advantage to using the MVCC model of concurrency
> control rather than locking is that in MVCC locks
> acquired for querying (reading) data do not conflict with
> locks acquired for writing data, and so reading never
> blocks writing and writing never blocks reading.
>
> Table- and row-level locking facilities are also
> available in PostgreSQL for applications that cannot
> adapt easily to MVCC behavior. However, proper use of
> MVCC will generally provide better performance than
> locks.

In the case where your multiversion logs don't get out of 
hand, your transactions are small and fast, and you don't 
have too many connections at once.  If all those are true, 
then this is generally true.

> On Thu, 2006-08-03 at 09:02 -0700, James Moore wrote:
> > MySQL has a pretty straightforward one writer/many
> > readers table-level locking system; all reads have to
> > complete before a write can go ahead, and you can't
> > read while someone's writing; many reads can happen
> > simultaneously if there aren't any writes.

Yep, this is a good explanation of what will happen with 
MyISAM tables if your applications do what James talks 
about.  

Best,
Kyle