Slashdot Mirror
MySQL 5 Production in November
thatoneguyfromphoeni writes "CIO.com is reporting that MySQL AB is eyeing Nov. for the production release of MySQL 5. 'The company is calling version 5 its most significant upgrade yet. It adds a handful of features considered important for enterprises that have long been available from market leaders Oracle Corp., IBM Corp. and Microsoft Corp. Chief among them are triggers, views and stored procedures.'"
Yup, and meanwhile PostgreSQL is prepping an 8.1 release with shared row locking, table partitioning, and better SMP support. Draft press release is here.
Anecdotally, RubyForge got 240K hits yesterday on a GForge site backed by a PostgreSQL 8 database with no problems; good times. PostgreSQL is good enough that our problem is bandwidth, not server load.
The Army reading list
See the MySQL documentation for triggers, views, and stored procedures, respectively. To answer your question, if you don't know what they are, you probably don't need them.
A trigger is something that is invoked on the database server when a predefined even occurs. For instance, an update to table 1 in database A could make the database server update table 2 in database B automatically.
A view is a way of making a pseudo-table. You can create something that looks like a single table, but can contain columns from multiple tables. If you have table 1 with columns A, B, C, D and table 2 with columns E, F, G, H , you can create a view 3 with columns A, C, F, H.
A stored procedure is something that is precompiled and put on the database server that performs a number of actions when called by a client. It can replace a complex series of SQL statements, say, in such a way that performance is much improved over having separate statements that would need to make multiple calls to the server.
> Yes, mostly mainframes, but I've no doubt that some industries were running
> "enterprise" apps 5 years ago on platforms that aren't as robust as MySQL5
> is now.
Ah, good question. Here's how to look at this:
1. mysql is just now in v5 putting in pieces that most commercial products had 10-20 years ago:
- views (been around since something like 1981 in db2 & oracle)
- triggers (been around since around 1995)
- subselects (been around in db2/oracle since 1981 or so, in mysql for what? 1 year?)
- transactions (been around in db2/oracle since around 1981, in mysql via innodb for 2 years)
- online backups (been around 10 years? mysql still requires a separate product)
- stored procs (been around 10 years? mysql just getting to it)
2. data quality - mysql has:
- silent errors
- silent data truncations & conversions
3. standardization - mysql has:
- quite a few deviations from ansi sql - everything from comments to weird create statements
- historically the lack of views, transactions, stored procs, triggers, and poor join performance meant that many queries had to be completely rewritten for mysql
4. performance
- mysql's performance reputation was built upon easily-cached data that could be easily looked up using simple indexes on mysql. Its performance of large queries (select many/most rows) stank, and its write performance was horrible - since required table locking.
- mysql's performance on innodb was better for mixed environments, but innodb has a bloat problem that can get serious.
- no support for query parallelism, partitioning, etc - isn't 1/40th the speed of a commercial product for many queries.
- mysql's optimizer is trivial - and can't be relied upon for complex queries (> 5 tables)
No, you can live without row locking - as long as you've got at least page-level locking. It's the accumulation of all the other stuff that makes you want to run from it.
Triggers are hidden *data* logic, and they should be hidden. They have the added benefit of being asyncronous if you choose, so if you need to write data fast, you can still lay it out in another format, or do something else arbitrary to it.
Stored procedures are like PERL
Agreed. But when you need them, you need them. They also go hand-in-hand with triggers frequently.
Views are a nice feature, but most often used to support business and reporting.
Views are an abstracted view of data. You can have a table called subscribers with lots of columns that tell you the status of the subscriber, and a view called current_subscribers that encapsulates all that logic.
(psuedo sql)
create table subscribers (id, start_date, end_date, cancelled, payment_is_late, is_overdue);
create view current_subscribers as select id from subscribers where start_date now() and cancelled = 'N' and is_overdue = 'N';
You could argue that this logic belongs in you DAO, but that only works if you have one DAO runtime, which is not true for a lot of application environments.
There aint no pancake so thin it doesn't have two sides.
Very few (recent) comparisons around. From my experience, however, if you're running a simple web site with many SELECTs over a single table then MySQL may well suffice. If you're doing serious stuff with multiple table/view joins then you should move up a gear and use PostgreSQL.
I've moved completely to PostgreSQL (works beautifully on core Drupal too) and have found complex queries complete in a fraction of the time. I had a complicated application which had multiple threads inserting, updating and reading all at the same time- complete run-time was reduced to a tenth by using PostgreSQL.
It works for me- just make sure you use ADODB in PHP or Perl/DBI to make switching easy when you hit the MySQL limits.
One more thing: I work with serious mainframe DB2 during the day. MySQL just doesn't compare. Postgres feels closer.
http://blog.grcm.net/