Thread: A test to add to the crashme test
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.
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
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
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
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.
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
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