In MySQL you can't update on a join.  It's a real pain in a well-factored
database.

	-Michael Robinson

P.S. When it comes to ROLAP, though, MySQL kicks PostgreSQL's butt.  For that
application only, I use MySQL.

Mon, 22 May 2000 14:39:46 +0800 (+0800)
Michael Robinson <robinson@netrinsics.com>
A test to add to the crashme test
Michael Robinson <robinson@netrinsics.com> writes:
> P.S. When it comes to ROLAP, though, MySQL kicks PostgreSQL's butt.  For that
> application only, I use MySQL.

Er ... "ROLAP"?  Expound, please.

			regards, tom lane

Mon, 22 May 2000 02:57:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: A test to add to the crashme test 
Tom Lane <tgl@sss.pgh.pa.us> writes:
>> P.S. When it comes to ROLAP, though, MySQL kicks PostgreSQL's butt.  For that
>> application only, I use MySQL.
>
>Er ... "ROLAP"?  Expound, please.

Relational On-Line Analytical Processing.  As opposed to Multidimensional
Online Analytical Processing (MOLAP), the other kind of OLAP.

The basic principle of operation is that you put all your data in a big
star (or snowflake) schema, and then pare down your "cube" by pre-aggregating
various dimensions of interest into various auxillary tables.

It works much better than MOLAP for big, sparse, high-dimensional data
(like, for example, six months of log data from an active e-commerce/content
website).

MySQL is extremely well suited for it: the data is essentially "read-only"
so transactions, locking, etc., are not an issue, the per-row overhead is
extremely small (important when you have hundreds of millions of short
records), and the speed, especially with prudent indexing and datatype
selection, is scorching fast.

Just don't ever put any data in it that you can't reconstruct from scratch.

	-Michael


Mon, 22 May 2000 15:46:39 +0800 (+0800)
Michael Robinson <robinson@netrinsics.com>
Re: A test to add to the crashme test
On Mon, May 22, 2000 at 03:46:39PM +0800, Michael Robinson wrote:
> 
> MySQL is extremely well suited for it: the data is essentially "read-only"
> so transactions, locking, etc., are not an issue, the per-row overhead is
> extremely small (important when you have hundreds of millions of short
> records), and the speed, especially with prudent indexing and datatype
> selection, is scorching fast.

People keep claiming that applications that are essentially "read-only"
don't need transactions. I'll agree in the limit, that truly read only
databases don't, but I think a lot of people might be surprised at how
little writing you need before you get into trouble. 

Case in point: Mozilla uses a MySQL db to back up their Bugzilla
bugtracking system.  Very popular site, _lots_ of people reading, not
a lot writing (from a developer's point of view, never enough...) The
problem they've seen is that if a reader someone fires off a "stupid"
query, like one that returns essentially every bug in the system, and
a developer then tries to update the status of a bug, every single
concurrent access to the system has to wait for the stupid query to
finish. Why? Because the writer attempts to aquire an exclusive lock,
and blocks, waiting for the stupid query. Everyone else blocks, waiting
for the writer's lock.

How many writer's does it take for this to happen? One. I'd call that
an "essentially read-only" system. A note, this is not a made up,
theoretical example. We're talking real world here.

Ross
P.S. here's the entry in bugzilla about this problem:

http://bugzilla.mozilla.org/show_bug.cgi?id=27146

-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005


Mon, 22 May 2000 10:58:41 -0500
"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
Re: A test to add to the crashme test
Hi,

Ross J. Reedstrom:
> People keep claiming that applications that are essentially "read-only"
> don't need transactions. I'll agree in the limit, that truly read only
> databases don't, but I think a lot of people might be surprised at how
> little writing you need before you get into trouble. 
>          [ Mozilla buchtracking example ]
> How many writer's does it take for this to happen? One. I'd call that
> an "essentially read-only" system. A note, this is not a made up,
> theoretical example. We're talking real world here.
> 
Right. But that's not about transactions; that's about concurrent read
and write access to a table.

People using MySQL in real-world situations usually solve this with one
read/write database for "normal" work, and another one for the
long-running multi-record "let's list every bug in the system" queries.

The update from one to the other is set to low-priority so that it won't
lock out any queries (with a timeout).


Mind you: I'm not saying this is ideal. A system with concurrent
read/write access would be better. But it has the benefit of giving
you a replicated database which you can fall back to, if the primary
system is down for whatever reason.

Besides, the MySQL people are currently busy integrating Berkeley DB
into their code. Voila, instant read/write concurrency, and instant
transactions.  Well, almost.  ;-)

-- 
Matthias Urlichs  |  noris network GmbH   |   smurf@noris.de  |  ICQ: 20193661
The quote was selected randomly. Really.       |        http://smurf.noris.de/
-- 
Acrophobes go down with little persuasion.

Tue, 23 May 2000 08:40:54 +0200
"Matthias Urlichs" <smurf@noris.net>
Re: A test to add to the crashme test
Matthias Urlichs wrote:
> 
> Besides, the MySQL people are currently busy integrating Berkeley DB
> into their code. 

Then MySQL may become a RDBMS after all ;)

> Voila, instant read/write concurrency, and instant transactions.

But won't it slow them down ?

-------------
Hannu

Tue, 23 May 2000 09:51:02 +0300
Hannu Krosing <hannu@tm.ee>
Re: A test to add to the crashme test
Hi,

Hannu Krosing:
> > Voila, instant read/write concurrency, and instant transactions.
> But won't it slow them down ?
> 
Of course it will. That's why they make the Berkeley tables optional.

Their idea is that you use the Berkeley stuff for the tables which really
require transactions, HEAP tables for in-memory cache/temp/whatever,
and the standard MyISAM tables otherwise.

Real-world example: Your customers' account balance really should be
transaction safe, and all that. But not their address, or their
clicktrail through your online shop system.

-- 
Matthias Urlichs  |  noris network GmbH   |   smurf@noris.de  |  ICQ: 20193661
The quote was selected randomly. Really.       |        http://smurf.noris.de/
-- 
If people think nature is their friend, then they sure don't need an enemy.
                                                -- Kurt Vonnegut

Tue, 23 May 2000 09:58:29 +0200
"Matthias Urlichs" <smurf@noris.net>
Re: A test to add to the crashme test