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.'"
MySQL has finally caught up to the state of the enterprise relational database industry...as it was in 1999. Points for effort, but everyone else is still ahead with core features like integrity, leaving them free to build on new and better features. Bundling with PHP will only get you so far.
Sneaking in just in time before MS SQL 2005 can get out the door (or perhaps just after) is good for this.
I recently showed the latest rev to the SQL devs here, and they were most impressed. Most of the complaints about it were gone; the new GUI is miles beyond what they had before, and the new features (views, stored procedures, better VARCHAR support) have people thinking that for smaller projects, MySQL will work out just as well as MS-SQL, and at a fraction of the cost, if any cost at all.
You can never go home again... but I guess you can shop there.
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.
Not everyone needs the same set of features in their database product. Some people want something that is very lightweight and fast.
Other people want bulletproof "unbreakable" databases with thousands of features. Some people want something right in the middle.
Having a variety of solid choices is not a bad thing. You should't be affraid of a little competition, as it is good for the entire market.
As much as I love (and use) mySQL, it's still not, nor will it likely ever be (never say never) an enterprise solution. Prove to me that mySQL is robust enough to be the backend service for a major bank's mortgage application, for example. It's simply not. As a previous poster already mentioned, mySQL has finally caught up to the base set of features that all major DBMS's had years ago. Now, after that rant, I will say this. mySQL is great at what it's designed to do. I use mySQL as the backend for personal websites and applications. It's (relatively) lightweight, simple, easy to administer, and, best of all, free as in beer (not withstanding products purchased from mySQL AB). So before you get all huffy about what I said in the first paragraph, just remember that mySQL is great at what it's made for, it's just not made to be an enterprise solution.
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.
So they finally add features that postgresql has. And don't forget the more difficult to use licensing they have imposed compared to postgresql.
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?
I was using all of these in PostgreSQL in 1999.
You have attempted to launch a SQL injection attack on slashdot.
You have failed.
Please try again with the correct schema.
What sort of hardware is behind RubyForge?
240000 hits/day is just under 3 hits/second, after all.
When you consider the power of today's hardware, it should be able to cope with such a load, even when doing fairly heavy database activity.
Cyric Zndovzny at your service.
ERROR 1064: You have an error in your SQL syntax near '5
)' at line 6
It makes no sense why they didn't buy out Innobase a while ago. Now that Oracle owns Innobase, they are ultimately at Oracle's mercy for much of their development since MySQL uses Innobase's code for a lot of their work. They really should have bought them out a while ago and integrated them into MySQL as a company before Oracle could get their hands on them.
Click here or a puppy gets stomped!
they never are, though.
it's probably more like "8 hits per second for 8 hours; 1 hps for 16."
which is still a joke for static content, but for dynamic, it's respectable.
I mean, I never see a host offering MySQL. all they offer is PostgreSQL!
So maybe people will clean up Oracle's _nightmarish_ security track record, because unfixed critical bugs for years is not an uncommon sight in Oracle land. I'd take security over features anytime.
Don't get me wrong, i know you're just a troll, although MySQL was/is lacking in some ways, as i'm perfectly aware of that, but nothing compares to not giving a sh*t about security especially in case of an enterprise level software costing so much in cash and resources.
These Oracle issues are well known as a lot of security experts prove it day by day.
It takes a man to suffer ignorance and smile
Be yourself no matter what they say
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. Yes, software has become more demanding in the past few years, but the fundamentals haven't changed. If you could run 'enterprise' solutions on SQL Server 6.5 (and I saw companies doing it - and gosh, they didn't even have row level locking!) surely some "enterprise" industries can use MySQL5 today.
creation science book
Don't be silly. PostgreSQL has had this feature for ages (atleast since 7.x days) implemented via NOTIFY, which interested listeners can register to be notified of.
Thank you. But that is not what I meant. There should be a software mechanism that automatically identifies dependencies and resolves them. Leaving it to the programmer does not solve the problem because programmers come and go and may not be aware of old dependencies. The problem is especially annoying in legacy systems. Modifications made to a complex system can have unforeseen consequences if programmers are not aware of the dependencies.
One of the biggest advantages of enterprise level dbs is that they are not file based. That is there is one or more files that can store many table/indexes/etc.
Postgres recently added tablespaces, which allow you to specify a sub folder for these files. This is better then before, but not nearly where it needs to be.
There are advantages to having single files versus a larger file, but most are administrative in nature. It can also lessen the effect of corruption. A sinlge table might fail and would not effect others. If a big database file corrupts it can damage a lot of data.
Having a single files allows quite a few things.
First it greatly reduces File system level fragmentation. The file grows once and the sectors are right next to each other. When you have 10+ gigs of data this is a real concern.
Second it create a unified caching mechanism. The big file is broken into pages, generally 8k, which in turn store data rows. The data is not only user data, but indexes, system information. Other pages are used to store stats about other pages, and have header information about the file itself. Why is this important to caching, because you simply have a cache table, everytime a page is loaded it gets cached. Writing to page happens in memory and then written to disk. Enterprise dbs have huge caches. This is why 64 bit is so important for dbs, so we can have larger then 4 gig caches.
Third is backing up. Some might say the file backup is easier, I beg to differ. Especailly when it gets big. When you go to backup you backup each page in the file. You mark each one as being backedup. At the end of the backup you backup the write ahead log. This allows you to restore to exactly the time the backup finished. Lastly, a diff backup simple looks at each page that has changed since the backup and backs only those pages up. Diffs can be very fast and faster to restore then a write ahead log.
Also, single files on different drives arrays to increase performance. This is also capable with tablespaces. The good part is that the database knows only the file id that tables go on, and then file id corresponds to a file name on any path. With the right tool you can move the files around easily.
Replication is also easier because writes are to a file id and page id. The replica database can have files place on any drive at any map point as long as the data.
Both mysql and postgres got a way to go, but they are very nice products and one day can easily compete with the big boys. Although it will be a while before they are able to run high end clustered box with shared storage and super high speed interconnects, but if you need that kind of power, you've probably got the money...actually you absolutly do if you can afford the hardware.
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.
"MySQL doesn't have triggers or stored procedures and views" to "Even if it does have triggers, stored procedures and views, it's still not a real database like Postgres/Oracle/SQLServer".
We have two websites, Boats.com and Yachtworld.com - Boats has an Oracle backend databsae, and YW has MySQL using the InnoDB engine.
The uptime is about the same for the two. We've had some issues on the Yachtworld database box due to 3ware drivers in Linux - they were corrupting pages in the database. Guess what? Innodb recovered without any lost data. Twice. This was a driver/hardware/linux issue, not a MySQL issue. We now appear to have a stable set of drivers, and I expect the MySQL database to hit 100% uptime pretty much every month.
Yachtworld gets several million distict page views per day, whereas Boats.com gets half a million.
Our MySQL database runs on a dual-opteron server, with 8 gig of RAM, with 6 gig of it allocated to the innodb block buffer pool (it caches row and index data so you don't have to go to disk).
Try doing that in Oracle 10g on Linux. The SGA (Shared Global Area) can't get larger than 1.7 gig unless you,
1) Use memory as a temporary file system so that Oracle can cache a bit more, and you also get the benefit of dicking around for several days, trying to configure your machine to try to take advantage of it (if it even can - we were never successful).
2) Remap all the shared libraries so that they load in a lower memory address, to squeeze another few hundred meg of memory.
Postgres (last I checked) preferred to let the OS do the data-caching. Thanks, but no thanks. And no 64-bit version (though I've read a few people have managed to compile one, I wouldn't trust it unless Postgres gave it the thumbs up).
MySQL with InnoDB is straighforward (it's use of tablespaces, replication, tuning, and even compiling from source - someone with mediocre Linux skills like myself can do it without issue every time).
MySQL with InnoDB is very fast, very reliable, and has awesome support via the MySQL mailing lists.
MySQL is very well documented, with lots of great third party books that don't cost an arm and a leg (unlike an Oracle library).
MySQL does not have stored procedures, triggers, and views in the current production version.
Here's what I think of that:
1) Triggers are hidden application logic that are very hard to debug, and are easily overlooked or forgotten by developers. Business logic (other than defensive logic like unique indexes, primary keys, foreign keys, not-null columns) does not belong in the database. They belong in the middle tier. They also make it much more difficult to move to another database.
2) Stored procedures are like PERL - it's very easy to make a mess unless you are very careful. They are also hidden logic, and very difficult to debug. And again, keep that logic out of the database. They also make it much more difficult to move to another database.
3) Views are a nice feature, but most often used to support business and reporting. I don't like managers connecting to the database to run queries (SELECT * FROM very_large_table_1, very_large_table_2; and suddenly you have cartesian join that results in tens of millions of rows coming back, bogging everything down). To do reports, views aren't necessary.
If you think MySQL is not a "real" database, it is, and has been since 4.0. As an Oracle (and now MySQL DBA), I can honestly say that I can't wait to dump Oracle and get the Boats.com website over to MySQL.
And for the few people who made comments like, "Do you really want your bank running on MySQL?": many banks run on old, legacy hardware and systems. Transactions are written out in many places (with geographic diversity) to ensure that a hardware or software crash is recoverable. There is no reason why you couldn't put MySQL in a situation like that, so long as the same precautions are taken.
Multi-Master replication will be available in MySQL 5
Marxism is the opiate of dumbasses
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
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/
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.
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
What worries me is that new acquisition of InnoBase by Oracle a few days ago.
InnoBase is the maker of InnoDB, which is the full featured dual licensed storage engine with transactions, referential integrity, hot backups and more.
The GPL version of MySQL will not be affected should Oracle decide to misbehave.
What may get affected is the commerical version of MySQL. Oracle can demand a hefty price for relicensing InnoDB, when the contract is up for renewal hence choking MySQL AB financially, by depriving it from the revenue stream of commerical licensing MySQL with InnoDB.
This may in turn cause long term trouble for the community by depriving it from contributions by MySQL.
I hope Oracle does not do that, but still, they are a corporation with no open source culture, and may have the mentality of choking the competition, using the very rules of open source dual licensing.
Or, they may be softening MySQL to buy them cheap in the near future ....?
2bits.com, Inc: Drupal, WordPress, and LAMP performance tuning.
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 ;-)
Why would a non-expert on a topic write an whitepaper about it? Makes no sense... Sorry for being so picky, but I'm just a bit fed up with that kind of nonsense. Regardless of the intent, the result is malicious as such FUD on the net hangs around and is copied and quoted. Wouldn't you rather compete by focusing purely on the positive points that your own offering provides? Anyway, your image is yours to build or undermine. It's a choice. Space from deleted rows and old versions are automatically reused, no explicit maintenance is required for this. I don't know what that person was referring to; if he/she has a valid point, I'm sure they can be more specific about the matter.
You expect people to take your analyses seriously, but you don't feel like attempting to test...?. I suggest you leave serious comparison documentation to people who do feel like testing stuff. No offence intended.
-- Any sufficiently advanced technology is indistinguishable from a perl script.
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!