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.
Oracle has just announced that they are open sourcing their famous database platform. Long live free software.
The above post is factually incorrect. Oracle acquired Innobase Oy, the firm responsible for InnoDB, a database engine commonly used with MySQL. Many people are obviously skeptical of this acquisition.
It's highly unlikely that they would ever donate their enterprise product to the open-source community. Suffice it to say that the shareholders would not be pleased with a bankruptcy filing.
Do you like German cars?
Sorry for the delay in replying, here's a description of the hardware.
I should really do an analysis of how many queries the DB processes per day/hour/whatever; that would be more useful.
The Army reading list
It works quite nicely and I hope they take the opportunity to improve from both mySQL and Sleepycat sides. It's always been my favorite table choice with mySQL.
Mostly I bring it up to get out of the Oracle bought InnoDB rut because one of the cool features of mySQL is the availability of tables to use. And I guess that is one of the great features of open source in general.
> 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.
I used to work on a site that got millions of hits per month. Most of the content was published static, but there was a fair amount of dynamic content. The same MySQL database was used for their content management system. It ran flawlessly. I'm not arguing that MySQL is better than PostgreSQL, but this company (should I name it) was the biggest in their industry. I would classify their use as "enterprise" level.
Don't NASA and Yahoo use MySQL for some things? They're pretty big, but they're not banks. How about we use the best tool for the job?
Our organization (although not big enough to be considered "enterprise" level) uses MSSQL for mission critical stuff, but we have a MySQL database that runs a lot of internal apps. I have no complaints.
Multi-Master replication will be available in MySQL 5
Marxism is the opiate of dumbasses
The first smell of trouble from Oracle and they fork the last GPL version and take it from there.
And can't use it in their commercial product.
If they can't get their license for InnoDB renewed, their commercial sales are in big trouble.
MySQL Still doesn't have nested queries
Yes it does. It has for 2 years now.
Totally offtopic I guess. But..
2 11450706
I hope the interview on Groklaw with Marten Mickos (MySQL AB CEO) will help you
out of your missery..
http://www.groklaw.net/article.php?story=20051011
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/
Why oh why are not many of these new modes not on by default? For example:
Don't get me wrong, it's nice to have these options. I just think that it's silly that some of the more important modes aren't set as the default. Thus, even though the option is there to change it, the default behavior is the same as what people have been complaining about for a long time now, and just having the option to change it is not a full fix.
That comparison is only valid if the criteria is, "Freely available open source database engines."
You're right, and it's fine for MySQL to be a Cessna.
But for many years, MySQL proponents have, in blatant defiance of the facts, claimed that their Cessna could seat 275 passengers and cross the Pacific in one hop. If MySQL lacked a feature, proponents claimed the feature was unnecessary fluff. As soon as the feature was added, they announced that "MySQL is ready to run with the big dogs." When the features in question were utterly fundamental to effectively exploiting the power of the relational database model, this tactic made MySQL proponents look like they were either ignorant or unabashedly dishonest.
That's why so many people today have a negative gut reaction to MySQL: the history of blatantly distortionary marketing, not the entirely justifiable choice of market segment.
Erlang.org: wow
We have the data available to us.
g e_site_status.html
http://rubyforge.org/docman/view.php/5/11/rubyfor
The PostgreSQL database contains about 3.2 million records and takes up 600 MB of disk space.
600 MB is obviously not a few TB. It's not even 1 GB!
RubyForge is currently running on a single machine with two 2.8 GHz Xeon CPUs, 2 GB of RAM, and a hardware RAID 5 SCSI array of 210 GB.
They have 2 GB of RAM for a 600 MB database. Even assuming the web server, mail server, Linux, etc., take half of the available real memory (probably unlikely), that still leaves 1 GB or so for the database. More than enough for the database itself to be resident in RAM.
Cyric Zndovzny at your service.
Well, Postgres does do its own caching in userspace (see the shared_buffers configuration parameter and related documentation). It just does that caching in addition to (or rather, on top of) the caching and I/O scheduling done by the kernel. Why do you consider this to be a problem?
(Yes, letting the kernel do most of the caching does result in a minor performance hit, but I think that the amount of work required to implement raw I/O doesn't justify the returns, at least at this point.)
This is not true: Postgres has supported 64-bit architectures for many years. The official list of supported platforms includes many 64-bit architectures (AMD64, IA64, Alpha, Sparc64, ppc64, MIPS, PA-RISC).
1) Inconsistancy in how create statements are handled. For example:
;-) )
create table table2 (
id int autoincriment,
fk int references table1 (id)
) type=innodb;
does not enforce the foreign key even in 5.0 while:
create table table2(
id int autoincriment,
fk int,
foreign key (fk) references table1 (id)
) type=innodb;
does enforce them.
2) Even with strict mode, any application can turn it off, allowing it to add bad data (try adding Feb 31, 20005 as a date in MySQL with strict mode off). This is a violation of Date's Central Rule.
3) "clustering" only works on tables that are cached entirely in RAM so if you use this you must have a beefy machine.
I know you like DB2, so you won't mind if I point out that an area Oracle has a problem is that it treats empty strings and NULL varchars as identical. Unfortunately no RDBMS is perfect.
Now for additional information on your complaints....
quite a few deviations from ansi sql - everything from comments to weird create statements
One should point out specifically that operators are non-standard leading to *very* unportable code.
mysql's performance on innodb was better for mixed environments, but innodb has a bloat problem that can get serious.
This is caused by the fact that innodb uses something like PostgreSQL's MVCC system but lacks an ability to vacuum the tables.... And MySQL people attack PostgreSQL for the need to vacuum the database....
no support for query parallelism, partitioning, etc - isn't 1/40th the speed of a commercial product for many queries.
BTW, PostgreSQL 8.1 will have much more useful table partitioning, and there is a project (Bizgress) aimed at adding parallelism across nodes in business intelligence environments (we had this discussion before once on Slashdot
LedgerSMB: Open source Accounting/ERP
Oh, a lesson in history from Mr. I'm my own grandpa.
> Are you kidding? Have you ever priced out a real Oracle license for a web-based application? (IE
> unlimited users or CPU basaed, which is what Oracle requires for anything that allows the general
> Internet to use it).
You're right - i was thinking of other applications. I shouldn't have since the parent had mentioned a million hits.
Not positive on what the current unlimited users oracle license is for two cpus. But the license cost goes up steeply for unlimited users. For DB2 I think it the cheapest unlimited user license is probably $7500 / CPU (list). Even assuming a substantial discount, mysql will be cheaper.
Sorry, but you're just plain wrong. MVCC is a fudamental design feature in InnoDB, and so it has been present in MySQL for over 4 years now. There's just no excuse. Facts need a source, you don't just make them up ;-)
Just for your information, InnoDB Hot Backup is not opensource and not part of MySQL.
Rest is offtopic.
SQlite is not meant to compete with mySQL. All locking, IIRC in the most recent version is file based. Since each database is a file, this means that only a single write to a database can happen at a time. If you really need concurrent access, that's not going to scale well for large databases with many concurrent reads/writes.
That said, SQLite is fantastic! I really mean that. I use it mostly in single-user/light-concurrency situations when a full scale DMBS isn't needed. It's great if you want to write a script/small app that deals with relational data or even a single table. It makes querying/massaging the data a breeze.
It's also pretty good for an offline demo. If you want to demo an application that normally relies on a database server, plugging in an SQLite driver in place of, say, a mySQL driver will yield very good results. SQLite is designed to support basic syntax for a variety of databases; as long as you aren't doing anything too fancy, you should be able to just swap out the drivers and go!