MySQL 4 Declared Production-Ready

Simprini writes "After absolute ages of testing MySQL 4.0.x in various versions of BETA through GAMMA it looks like MySQL AB finally released MySQL 4.0.12 as ready for prime-time production use. I know my company has been waiting for a long time for this because our customers absolutely refused to use beta releases of this product. Query caching here we come."

Comments

Uh oh

[grub]

MySQL 4.0.x in various versions of BETA through GAMMA

Uh oh.. I flat out refuse to use code that isn't ALPHA... well at least as an OS on my Windows machine.

Re:Uh oh

[DNS-and-BIND]

I thought it was strange that the story submitter said, "I know my company has been waiting for a long time for this because our customers absolutely refused to use beta releases of this product." It's as if he's surprised that customers don't have the same standards as his personal linux box. Sure, they released a new tiny rev today, let's compile it and put it into production!

Re:Uh oh

[wideBlueSkies]

When the hell are they going to start alpha or beta testing subselects? I mean come on already.

select a.cusip, a.description
from security_master a
where
a.cusip in (select b.cusip from new_issues b)
order by a.cusip;

When MySQL can do that simple contrived query I'll take it out of the toy box.

from the hardly-any-data-loss dept ?

[tweder]

Oh that's reassuring!

Re:from the hardly-any-data-loss dept ?

[Gortbusters.org]

Production for what? For banks.. maybe not. For mom 'n pop online shops.. sure! And oh yes, even for /.

Uh, postgres?

[Anonymous Coward]

Does MySQL still do table-level locks and no foreign keys? If so, I'll stick to using a real database.

Re:Uh, postgres?

[rushfan]

I think the InnoDB allows FK constraints (and InnoDB is included now)

http://www.mysql.com/doc/en/SEC458.html

Re:Uh, postgres?

[huhmz]

InnoDB uses row based locking.

Re:Uh, postgres?

[lamber45]

MySQL 3.something+ also supports row-level locks in MySQL tables.

I hope the MD5, SHA, etc. functions are default now... they seem to be absent from the 3.23.55 build that comes with the Debian distribution.

Re:Uh, postgres?

[AlecC]

The InnoDB table type, which is now a part of the standard production release, supports transactions, row locking, and foreign keys.

Re:Uh, postgres?

[althalus]

Actually, Innodb does FK's (as mentioned earlier). It also does row-locks/ Multi-versioning. It's had those features for quite a while now.

Re:Uh, postgres?

[oliverthered]

Use postgres for anything complicated and MYSQL for anything simple, light weight and fast.

Why use openoffice for editing /fstab when vi is fine.

Just now?

[Geekenstein]

According to the MySQL site (manual) Gamma status was granted in December with version 4.0.6.

Re:Just now?

[JabberWokky]

Yes, and the production release was just made. They have been saying stable release was the 3.x, and the unstable was the Gamma 4.x. The 4.x Gamma is now the stable production release.

--
Evan

Excellent.

[Anonymous Coward]

My MySQL 4 worm is also ready for your production environments. Get ready, sucka!

Its Stable

[krow]

We have been using 4.x for Slashdot for some time now. Its quite stable and the new query cache seems to be working for around 13% of our queries, which has been a great boon for us.

Re:Its Stable

[CableModemSniper]

Allright, now I *know* not to upgrade. (kidding)

Re:Its Stable

[krow]

Did you get all of the cache variable set correctly?
This is what I have for Slashdot:

query_cache_size = 100M

To be honest that number is overkill even for us. I find that tuning the Innodb pool size for more memory has better gain for memory used. Slashdot's is 1548 megs at the moment.

Simply powerful or powerfully simple?

[Gortbusters.org]

Things like embedded MySQL make MySQL a bag of tricks to use in many environments.

What other features might there be?

• Version 4.0 further increases the speed of MySQL Server in a number of areas, such as bulk INSERTs, searching on packed indexes, creation of FULLTEXT indexes, as well as COUNT(DISTINCT).
• The InnoDB storage engine is now offered as a feature of the standard MySQL server, including full support for transactions and row-level locking.
• Our German, Austrian, and Swiss users will note that we have a new character set, latin1_de, which corrects the German sorting order, placing German umlauts in the same order as German telephone books.
• Features to simplify migration from other database systems to MySQL Server include TRUNCATE TABLE (like in Oracle) and IDENTITY as a synonym for automatically incremented keys (like in Sybase). Many users will also be happy to learn that MySQL Server now supports the UNION statement, a long-awaited standard SQL feature.
• In the process of building features for new users, we have not forgotten requests by the community of loyal users. We have multi-table DELETE and UPDATE statements. By adding support for symbolic linking to MyISAM on the table level (and not just the database level as before), as well as by enabling symlink handling by default on Windows, we hope to show that we take enhancement requests seriously. Functions like SQL_CALC_FOUND_ROWS and FOUND_ROWS() make it possible to know how many rows a query would have returned without a LIMIT clause.

Re:Simply powerful or powerfully simple?

[micromoog]

In the process of building features for new users, we have not forgotten requests by the community of loyal users.

What about integrity constraints, foreign keys, interval datatypes, full outer joins, subqueries, set operations, VIEWS for god's sake, and triggers? Too hard?

For cryin' out loud, half of these missing features put the "relational" in "relational database"!

Re:Simply powerful or powerfully simple?

[Ragica]

Good God, mysql doesn't have intervals either? I never even realised how much i take intervals for granted in postgresql until i read this. It's hard to imagine life (for very long) without them.

But of course it's probably just another thing mysqlers will claim that "90% of people would never use anyhow". Well, 100% of mysqler's, anyhow.

They don't know what they're missing.

Re:Simply powerful or powerfully simple?

[JohnDenver]

Views? They are for people who don't know how to write queries.
Triggers? They are for people who like mysterious things to happen to their data.
Foreign Keys? They are for people who don't like to delete their data.
Interval Datatypes? They are for people who are iffy.
Full Outer Joins? They are for people who like lots of data.
Subqueries? They are for people who can't program simple loops.
Set operations? They are for people who can't relate.

shall I add???

Programming Languages They are for people who can't read machine code in hex.

(I realize you're kidding, but there are some people who might take you seriously)

Re:Simply powerful or powerfully simple?

[micromoog]

select *
from people
where person_id in (select person_id from slashdot_users)

This one can be rewritten to use a join instead (MySQL's excuse for a workaround). However, there's another concept of "correlated subquery" (google for it) that cannot be rewritten in SQL, and instead requires some degree of procedural manipulation to simulate.

MySQL Query

[NewbieSpaz]

Select * from "http://www.mysql.com/downloads/" where "version" = "mysql-4.0.12"

Is it worth the switch?

[m0i]

According to the crash-me comparison page, there's not much differences with the previous stable release. Some current benchmarks would probably be more significant, performance-wise.

Re:Is it worth the switch?

[loco123]

I believe it is.

We're using it in production since gamma. Our site delivers over 2.5 mln pageviews per day. Database size is about 25GB.

Major differences compared to 3.x:
- Row level locking - no more glitches on big updates
- Instant crash recovery thanks to InnoDB
- Hot backup ($400)
- Query cache:
Queries Avg/Sec: 388.45
Cache Hits Avg/Sec: 108.04 Ratio: 27.81%

Slash

[ericdano]

The big question is: When Slashdot is going to start using it?

Having had experience with Oracle, MySQL is still lacking a lot of the plush features that Oracle has. But, having run it for about 3+ years on my own slash type sites, the thing is ROCK solid. The feature set in MySQL increases with every version.

Now, look at the costs. Oracle - an Arm, leg, and your children. MySQL - Free. Gee, that is a no brainer.....

Re:Slash

[tuffy]

Looks like they do already.

SELECT version FROM mysql WHERE ready='true';

[Anonymous Coward]

Error : no rows returned.

VIEWS! I said VIEWS, son!

[jot445]

We need views. While much of my work can be done in MySQL, until there are views I cannot switch completely from SQL Server 2K. Too many PHB's that need features like views to be overcome. Must control fist-of-death!

Waiting for maturity

[acostin]

We are also using MySQL for many web projects, but to create a complex CMS the future features in MySQL (that also exist in other current database systems - like postgreSQL and probably others) are needed.

We have initially created Komplete - http://komplete.interakt.ro/ only for PostgreSQL, and our users attitude indicated us that MySQL should have been supported. So we are releasing now the Komplete Lite version (GPL), for MySQL - but it's a real pain to simulate subselects, real unions (emulated with temporary tables now), cascaded deletes and stored procedures.

The speed is quite similar, but PostgreSQL is still much better for complex web applications.

Re:Waiting for maturity

[Gortbusters.org]

mmMMMMmmm, stored procedures. I like the command line client of postgresql a lot as well. \d is much better than describe .

Re:Waiting for maturity

[Osty]

And like 4 years ago I was all for MySQL - "Why the hell do we need stored procedures and triggers? They only slow the database down". This is true, but also this make the software actually reach the client in time by helping you avoid coding extra tons of code for data storage tasks...

Of course, if properly implemented, stored procedures don't slow down the database at all. In fact, stored procedures could easily increase your performance. Proper implementation should include compiling the sql code into something that's more efficient internally for the server. Also, you'll no longer be sending free-form query text to the server, so you're both safer (you can be more strict with what you allow in your strings) and faster (fewer trips to the server and less data sent to the server each time). Not only that, but sprocs let you keep your SQL code separate from your client code, and can allow you to make changes to your business logic without having to touch your client code at all. In short, sprocs are much more useful than just being used for triggers, but unfortunately many MySQLers don't realize what they're missing.

Woho!

[Dri]

I work at at the tech development dept. of a major car company and this is great news. We are finally able to throw MySQL onto production servers and give Oracle the boot for small RAD webapps.

What I've heard from MySQL officials in person is that MySQL 5.0 is set to be released late Q4 this year. Then stored procedures, sub selects (4.1) and constraints should be ready for primetime, then we talk real heavy enterprise applications. Hope they keep the schedule! =)

Well, Monty and the rest, Good Job! Keep it up!

Re:Woho!

[NineNine]

You've either got more balls, or less real world experience than me. I'm not even *thinking* about using MySQL until all of these new "features" have been tested for at least a year or so. My databases are the kinds of things I play around with.

Re:3.2x to 4.0

[gid]

There's always the documentation

Alpha Beta Gamma

[L. VeGas]

If it's good enough for such a reputable fraternity, it's good enough for me.

Re:Not much to compete in...

[merlyn]

MySQL is already faster than postgres at the limited amount of things MySQL can do.

Perhaps you believed the benchmark that only the MySQL team has been able to come up with. Every other benchmark I've seen that simulated multiple users always showed Pg to be better even on very simple queries.

Beware benchmarks that show only one thread or from mysql's developers themselves.

Triggers?

[mshiltonj]

Foreign keys -- Pass
Replication -- Pass
Triggers -- FAIL

SO close.....

Re:Triggers?

[mshiltonj]

Yeah, but postgres doesn't have replication to speak of. We use postgres, but replication is a big missing element that we are looking to fill.

So whichever happens first -- postgres gets _good_ replication, or mysql gets stored procedures/triggers -- will probably determine which one leaps ahead of the other in terms of wide-spread adoption, especially as companies migrate from costly proprietary systems.

Re:Triggers?

[nontrivial]

Triggers are so overrated. I maintained a huge app that made extensive use of triggers, and after a few years I couldn't go near them without having them explode. Give me MySQL and Perl instead of triggers and stored procedures any day of the week.

Re:Triggers?

[frostman]

The point of triggers is not to handle every possible database interaction, but to maintain your data integrity and business rules, as far as possible, at the database level.

This is important so that people writing client/web applications don't accidentally weaken the data integrity. Database users (including applications) should never be able to accidentally or maliciously break things. Only the DBA gets to do that. ;-)

If you find your triggers blowing up, it's usually because the database is poorly designed.

If you started with something good and the boss is always changing the business rules on you and forcing you to use application code where you should use triggers, that's a tough situation. But if it's your policy, you would do well to learn a bit more about databases.

BTW, Postgres can do triggers in Perl, for what it's worth.

Multi-table deletes

[digitect]

IMO, the very best new feature of MySQL 4 is multi-table deletes. No more having to query/for each in/delete type constructs across many-to-many relationship tables.

I've been using MySQL 4.0.5/PHP4 on RH8.0 without problems to date. Granted, only on a non-critical intranet for our small (70) office, but still, no problems.

Re:Multi-table deletes

[aldjiblah]

> No more having to query/for each in/delete type constructs
> across many-to-many relationship tables. ... also known as foreign keys and triggers in real databases.

File mirror on Freenet

[Sanity]

Just mirrored the file on Freenet, you can grab it here.

sub selects

[minus_273]

any word on whether we have subselects yet. I couldnt see it in the change log. They are dearly missed..

Re:sub selects

[gid]

I believe these are due for the 4.1 series. There's a little note about it here

Re:VIEWS! I said VIEWS, son!

[HelbaSluice]

But, as the MySQL developers say, nobody appears to want views badly enough to finance their development. That's how MySQL got as developed as it is now--enough corporate users needed specific new pieces of functionality that they could pay MySQL AB to build them. It's one of the best open-source business models I've ever seen.

It's easy to complain. It's easy to preach. I'd rather see you pull out your (or your bosses') wallet.

As for myself, while I'd love the convenience of views, I'm not constrained by legacy code and I don't mind the mild programming burden their absence puts on me.

Uhm, PostgreSQL

[gabe]

I know people really dig MySQL and everything, but seriously, PostgreSQL has all of those features, and it's ready for production use NOW.

Waiting for PHP users to wisen up

[sporkboy]

Just because PHP doesn't let you write a proper database access layer doesn't mean that a database NEEDS those features to exist.

I've written multiple CMS-like applications, and seen several commercial systems which do fine without the features you listed...the key thing is that they are written in Java or even Perl so they can figure things out on their own.

Look in the mirror before throwing stones.

Faster than PostreSQL?

[Jack William Bell]

I am wondering about caparative processing speed myself. MySQL has always been the speed leader in Open Source databases. Now that they have added some industrial strenght features (like ACID compliant transactions and row level locking) via InnoDB, how well does the speed difference hold up? Is it still way faster, or just a little faster or not faster at all?

If the difference isn't significant then there is no reason to choose MySQL over PostreSQL for applications requiring high levels of data integrity. Especially when PostreSQL also brings you stored procedures, views and so on.

Re:Why is the Windows download 20 megabytes when

[dildatron]

statically linked libraries, dude.

Apparently 90% don't need those features.......yet

[JohnDenver]

What about integrity constraints, foreign keys, interval datatypes, full outer joins, subqueries, set operations, VIEWS for god's sake, and triggers? Too hard?

For cryin' out loud, half of these missing features put the "relational" in "relational database"!

First of all, kudos to the MySQL team for atleast getting as far as they have. Just because I'm not fond of thier product, doesn't mean they don't deserve credit.

I've been banging my head a little on this one too trying to figure out why so many people are pushing MySQL and not something stable and complete like PostgreSQL? After all, PostgreSQL has triggers, stored-procedures, functions, referential integrity, and tons of other features to make your life easier. You may not need all of these features now, but can you honestly say your app won't expand and require advanced features?

Is it the MySQL marketing engine? Does PostgreSQL sound intimidating? Are there actually technical advantages that MySQL have over PostgreSQL? If so, what are they?

The most common argument I've heard in defense of MySQLs lack of basic features is: "It's good enough for 90% of the problems out there." However, everytime they implement a basic feature that every other RDBMS has had for decades (like UNION), people respond as if MySQL is getting close to be taken seriously.

Secondly, In my experience, I've found that 90% of the applications I've worked on end up using those advanced features sooner or later. Those features usually save a tremendous amount of time I would have otherwise had to spend writing code to make my database jump through hoops. In addition to saving time, there a lot of features which simply allow me to make my applications more useful or intuitive to the end user, which is the whole point.

Am I missing something here, or is the Emperor not wearing any clothes?

Yes!

[vandan]

As one of the testers of the 4.0.x line, I can say that MySQL AB should be proud of this release.
I've seen some posts here about instability and data loss, but I assume this is from the Postgres 'but WE have the better database - everybody look over here' crowd. I've done some pretty stupid things to our MySQL box - like running Imagemagick's 'convert' on over 200MB of images and running the box out of virtual memory, which made the kernel start killing processes - starting with MySQL. When it came back up - no data loss at all. InnoDB recovers VERY well from this sort of thing.
MySQL also handles multiple MS Access clients far better than MS SQL Server. We have over 10 tables now which basically can't be accessed if placed on SQL Server because of the way MS Access grabs record locks willy nilly. If I place the tables in MySQL as MyISAM tables, I get a little bit (3 or 4 months) use out of them. Then record locking issues start up again. So then I put them in MySQL's InnoDB tables with row-level locking, and I've never had any further issues with those tables. Quite impressive.
And as well as being 100% stable for me, MySQL is so incredibly fast... When we convert standard Acccess queries to pass-through queries we get up to 15x speed increases. We actually use pass-through queries as substitues for views. Works nicely.
The tech support it great. When I was having type-conversion issues with our pass-through queries I got responses from the developers on the same day - often in the same hour. And we haven't paid for any support - just downloaded the source.
The lead-up to MySQL-4.0.x being stable has felt like the lead up to Mozilla-1.0; everyone using it felt it was ready, but the developers insisted on thoroughly testing everything to make sure they could stand by their decision to declare it stable.
Congrats to the MySQL team. I will be compiling 4.0.12 when I get to work...

Why not PostgreSQL?

[ikioi]

I don't mean to start a flame war here, but I have to ask... Why is MySQL so popular when PostgreSQL does more and is also open source and free like beer? Are there any real benefits to MySQL over PostgreSQL?

Re:Why not PostgreSQL?

[Eric Savage]

Three words: performance performance performance.

A few projects NEED the advanced features PostgreSQL has. Most projects COULD USE the advanced features PostgreSQL has. If you have rockstar programmers who know the difference between saving keystrokes and saving cpu time, and know that shifting logic load to your DB server is generally a BAD thing, you're going to find that you can almost always do things faster (often much faster) in MySQL. Stability is a tough one as its so subjective its hard to compare. I know we use dozens of MySQL servers collectively running tens of thousands of queries per second 24/7 and we haven't had a major issue or lost any data in years.

If performance is key and you aren't into using fancy stuff just because its fancy, you'll want MySQL. If you don't really care about performance, you might like the additional features PostgreSQL offers.

Re:Why not PostgreSQL?

[gid]

I often wondered this myself, UNTIL I actually tried to sit down and use PostgreSQL. MySQL permissions and everything just made sense, it's all kept in very nice and neat tables and easy to understand by by looking at the tables without having to read any to little documention.

While on the other hand, permissions for PostgreSQL are scattered everywhere. Half of it is config files for who gets allowed in and what type of authentication to what tables, triggers, etc, some are in special PostgreSQL tables that aren't immediately obvious even how to access if you wanted to edit them directly. It's all very confusing.

PostgreSQL is nice, they just need to go that extra mile to make sure user permissions are easy to understand, etc. Do other little things here and there to make the learning curve is not quite as steep.

Intuitive applications are the ones that succeed.

Re:Why not PostgreSQL?

[Anonymous Coward]

as much as many may hate to admit not everyone is running linux/unix. MySQL has a native Windows install - you download it and run setup.exe. You can then play with it, develop under windows and deploy elsewhere.

When I went looking for Postgress for Windows all I could find was instructions that involve cygwin.

Re:Solaris 2.5.1

[hhnerkopfabbeisser]

MySQL is developed mainly under Solaris and is known to work best there. Threaded performance under Solaris is said to be way better than under Linux.

This may of course not apply to your version of Solaris...but I can't make any qualified comments on this, really, as I have never even seen Solaris from up close.

Re:Apparently 90% don't need those features.......

[MisterFancypants]

MySQL used to be significantly faster than PostgreSQL, mainly because it COULD be faster because it didn't have to worry about pesky features like transactions. Now that PostgreSQL has been better optimized and MySQL actually has some (a couple anyway) of these more advanced features, the speed difference is not a factor anymore. Now I think it is just a matter of inertia -- since MySql had such a long run, getting people to change is hard.

Re:Uhm, PostgreSQL

[kyrre]

Why the hype around MySQL when Postgres has all those features ready for primetime.

MySQL is easier to install on a Windows machine is my guess. I know thats the reason my group went with mysql on a project last year. We could not require that our customer installed a Linux (or similar) server.

Nope the speed thing is still an issue

[rtscts]

MySQL doesn't "have these features" - some table types "have these features." The same MySQL server can use any of MyISAM, BDB and InnoDB tables; the difference is MyISAM doesn't have transactions, but it's twice as fast as InnoDB which does.

Re:Uhm, PostgreSQL

[eyeye]

I really want to use postgresql but i've tried it and its really unfriendly to use.
Maybe i'm dumb but dumb people need databases too :-)

Project Stats

[Skevin]

I've been using MySQL 4.x for a huge stock market analysis program I've been pounding out as my life's work, and unfortunately, I'm finding in some respects, it's slower than MS-SQL Server (same machine, dual boot):

Number of listed NYSE symbols: ~3200
Number of listed NASDAQ symbols: ~4000
Number of total stock quotes from 1980 to today, each including open, close, high, low, and volume: 6.2 Million

Time to fully index those 6.2M records on SQL Server: 0:42:33
Time to fully index those 6.2M records on MySQL: 2:12:27

And using Python...
SQL Server time to pull all quotes within a given date range (no indices): 1min, 28sec.
MySQL time to pull all quotes within a given date range (no indices): 7min, 18sec.

Has SQL Server used implicit indices I am not aware of?

Re:Project Stats

[zm]

I question the relevance of index creation benchmark. In most cases, index creation is done once and then it all just works with that index. Could you provide benchmarks for some big multitable select, update and calculations (averages, sums and that stuff)?
Same goes for unindexed select: avoid it, and give us indexed benchmarks.

zm

Re:VIEWS! I said VIEWS, son!

[Java Ape]

I'll probably get modded troll for this, but why do you need views? I am an oracle DBA who supports a large number of developers, and frankly I'm a bit skittish regarding views. (For the record I also support PostgreSQL and dabble in MySQL . . .).

They can really only do two things: hide columns for security reasons and simplify queries by hiding part of that query.

In general, the first applcation is usually better served by planning, data seperation, and implementing a good security policy. There are times when views are a legitimate solution to problems of this type, and a database is definately better for supporting them in such cases.

The second case, however, is commonly misunderstood by developers, who think a view is some magic incarnation of a snapshot. I frequently see views based on views based upon views, frequently each of which is a poorly-optimized sql statement. The developers seem surprised that performance is abysmal in such cases. A view is a just a convenience, a means to "store" a query, and run that query each time the view is accessed, nothing more.

Since I spend a fair bit of time trying to fix performance problems reusulting from the many myths and rumors about views and their ubiquitous misapplication, I'm not sure that I would consider their omission a bad thing -- it might teach developers better coding habits. . .

Re:ON DELETE CASCADE

[Troll_Kamikaze]

welcome to real *relational* databases

Easy there, you'll scare the puppy.

MySQL 4 is good

[chrysalis]

I've been extensively using MySQL 4 for over one year on very loaded production systems.

It has actually always been faster and more solid than the 3.23.x series.

I only had some small issues with InnoDB (the same issues were in 3.23.x as well). But the InnoDB maintainer, Heiki Turri, is someone that really cares about bug reports. All reported bugs were immediately fixed.

The query cache is efficient, and the fulltext indexing was greatly enhanced (if only it worked with InnoDB tables...) .

I've not installed any 3.23.x version for a while, and I'll never go back.

Probably a lot of system administrators will wait. They will read that MySQL AB blessed 4.x as production-ready, but they will wait, as if it was an 1.0 version that still needs some maturity.

It's not. MySQL 4.x has already received a lot of testing, and it is already being used on large production sites. Just read the MySQL mailing-lists.

Upgrading from MySQL 3.x is also easy. You only need to run a little script to upgrade the grant tables (and even if you don't, everything will work). No need to export/reimport the databases. So upgrading is straight forward.

Just how much faster than Postgresql is it really?

[The Bean]

I keep hearing how postgresql has "caught up" to mySQL plus has all kinds of wonderful features, yet my own testing shows postgresql to be a fair bit slower when you have about an equal mix of selects and updates with a few inserts thrown in here and there. For example, 82 seconds for postgresql, 35 for MyISAM and 49 for InnoDB (not MySQL 4 however) Yes, the postgresql had fsync turned off and the table vacuumed (full & analyze.)

I'd love to use Postgresql, but with mysql adding all these features plus being so much faster, it's hard to move that way, as the fancy features are things I'd use but don't really need. (Previously foreign keys were a reason for me to switch)

Or is there a way to make postgresql keep up to mysql so I can justify using it and right away get access to those cool things like views, triggers, functions, etc ?

PostgreSQL name change?

[cpeterso]

Maybe PostgreSQL justs a name change and a new PR department. Many people don't even know how to pronounce PostgreSQL. Consider the name's awkward evolution: Ingres --> Postgres --> PostgreSQL. They've already got a decent logo (the blue elephant). Presumably, the elephant never forgets your data?

Looks like the PostgreSQL team is taking an active role to update their PR: A Call for PostgreSQL Case Study Participants

Re:Apparently 90% don't need those features.......

[keith73]

If it does the job, why switch. If I ever find MySQL can't handle something that I need to do with an app, then I will certainly start looking at Postgres. Right now, I'm not concerned with a lot of the features that postgres has. I'm using it to do simple table queries in small apps. I first started using it because that's what the hosting company had installed on my web server. So I used it. I've been using it ever since because I haven't had any problems with it. - keith

Re:Apparently 90% don't need those features.......

[Trifthen]

We run postgres and we're doing our damndest to get rid of it. We have some databases that get 50-100% data turnover rate daily, making hourly vacuums essential to not having the Ever Expanding Database problem. Not to mention that vacuum doesn't clean up indexes, so you'll also have to re-index periodically if you don't want those to grow to thousands of times their optimal size.

I should probably say that such reindexes require full table locks, so you could get contention issues under heavy load when reindexing your database. Mysql gets by this by making indexes in a temporary space, and switching when the index is done. This means I can select from a table, with full benefit of an existing index, even while I change an index, or even redo the index. Not that I have to... mysql doesn't require vacuum or reindex to avoid continuous linear bloat.

So... we don't like having to babysit our database to get good performance out of it. We're willing to work around lack of foreign keys to avoid having to do full database import/exports on a weekly basis, and multiple hourly cron jobs to make sure we don't randomly fill our disks. Faster? Slower? Who cares. Postgres is just too annoying to use in production.

What the MySQLer's dont understand

[esconsult1]

The thing that makes Postgresql completely different from MySQL is that it is an *active* RDBMS. By active, I mean that you can set it up so if it gets certain kinds of data, it can operate on that data to create new records, delete records, update other tables etc.

Postgresql has the *intellegence* built in. You can write all sorts of georgous functions to do stuff, especially if, like us, your shop uses several languages... PHP, Perl, Java, Python, C++, etc. Why replicate your data related logic in every client language?

Transaction support and file/record locking are the least of your problems. If you do serious database stuff, at some point, you are *going* to want VIEWS, TRIGGERS, RULES, and STORED PROCEDURES (functions). Having this functionality in the database engine, instead of in your code makes a heck of a lot of difference when the time comes to scale.

Coming from a MySQL backgroud in a multi-language shop, we clearly saw the limitations, and decided to switch the entire database platform over to Postgresql a year ago. We haven't looked back since.

Also, I dont think the developers will be able to make MySQL into an *ACTIVE* database anytime soon, simply because of the current architecture of the system as it is now. They are going to need a heck of a lot of system tables and new code, to accomplish even the simplest stored procedure functionality.

I can see VIEWS being a quick hack, but going beyond that with MySQL as it is, will be quite a stretch, and I don't believe they will finish those features until perhaps the end of next year, as it will require almost a complete rewrite of the base engine IMHO.

Question

[PyroX_Pro]

When I design a view in MSSQL, I can copy and paste the query in my code and use it. That is exactly what I always do. MySQL supports joins correct, so it it just that you cannot save the view that makes so many get their panties in a bunch? Can't these people just write the query? If you need it stored bad enough, can't you just write a function to call your 'view' sql query?

What?

Re:Apparently 90% don't need those features.......

[Ost99]

Point 1:
PostgreSQL lacked many of those features just 2 years ago. Did you ever try to use it before 7.0? You had triggers, but no cascading or outer join.

Point 2:
It was slower, and you had to recompile (according to the readme) to get it to use more than 32 (or was it 64) simulatious connections. Not that that should be a big problem, execpt for the fact that posrgreSQL had serious problems closing a connection after use.

Point 3
(Much) better support for mysql than postgreSQL in PHP. You can argue all you want that php isn't something you would like do develop with, but a whole lot of websites use it.

Point 4:
If you already have started using mysql for a project, it is often more work to change DBMS.

Point 5:
Most ISPs support mysql if they offer hosting on linux servers, and sometimes on ms servers as well. I've not found one ISP (in Norway) who offer PostgreSQL. Our company ended up looking quite stupid when we couldn't find a ISP who would host the site we had developed for our customer. We ended up having to place a server in a server farm somewhere, and admin the server ourself (this was while posrgres was in a 6.x version, after 2 years we tried again after 7.1 was released, but noone were able to provide it). The universities I've attended or know of offer mysql for all students, and oracle for (some of the) comp students.

So the 4.0 release are great for all of us who for some reason *have* to stick with mysql for most of our work. I prefer posrgreSQL in most cases, but if it's web related, I don't allways have that option.

- Ost

Re:OSS fortune!

[Anonymous Coward]

3 = make the JDBC drivers GPLed to make developpers so confused about the differences between LGPL and GPL applied to Java code (there is no difference between static and dynamic linking in Java) that they have to buy a non-GPL version of the driver just to make sure they don't violate the GPL

Re:Apparently 90% don't need those features.......

[pi_rules]

After all, PostgreSQL has triggers, stored-procedures, functions, referential integrity, and tons of other features to make your life easier. You may not need all of these features now, but can you honestly say your app won't expand and require advanced features?


Gimmie a break dude. I'm sick of hearing all this stuff about triggers, sub selects, and stored procedures. I can honestly say that no database really needs these things.

In my 6 months of professional development at a 3 man shop I think I'm perfectly well qualified to say that no RDBMS will ever need these futures. I can't possibly imagine a design so fubar that it would EVER have to rely on the RDBMS to enforce such rules. That's what application level code is for! Sheesh!

Well, maybe such things would be useful if you had more than one application pointing at the same database... or if you planned to maintain the DB's integrity over any length of time. But that kind of shit never happens in the real world. It's a made up story of Slashdot posts and database classes.

Given that text doesn't relay voice inflection very well: The above is sarcasim.

We are currently playing with MySQL...

[puppetman]

to possibly replace some Oracle databases.

Any gurus (or detractors) want to list the downsides?

- no subqueries yet. Ok. Not the end of the world
- are multi-column primary keys still a performance dog?
- how is stability? That's probably what you hear most about w/regards to MySQL
- triggers and stored procs; back-end-logic==bad IMHO

I just ordered Mastering MySQL 4 to speed the jump between Oracle and MySQL. Anyone used that book?

I'd be really interested in hearing some frank and honest appraisals.

Re:We are currently playing with MySQL...

[bartwol]

We've been using MySQL in a production operation for three years, and it's been bulletproof. We've been serving up financial data (50 tables, ~20million rows) in a heavy multi-user environment. We're running it on about 10 boxes right now. Compound indexes work well; everything is fine. My one caveat is that our app is mainly a read-only application which suits MyISAM's weak table locking scheme. However, MySQL 4.0 includes InnoDB which supports transactions and a robust locking scheme that has worked well in my initial tests. I don't know where stability problem reports come from, as we've seen none. FYI: I did several PostgreSQL tests and ran into major query optimizer problems on complex queries against large tables.

Re:Apparently 90% don't need those features.......

[junkgrep]

Can you explain, in layman's terms, what a "real relational database" actually is? And why MySQL isn't one, and what it would have to do to be one? And NOT by saying "well, it's got to have wvcsde and werdfskfk!" I mean, I don't know too much, but people have got to be able to explain these operations, and the theories behind the data structure, in better ways than most people here are doing, reffering to various features. Not all of us here are geniuses, or work with databases regularly. :)

Re:Apparently 90% don't need those features.......

[Billly Gates]

I have used FreeBSD since the linux 2.0 days. FreeBSD was lightyears ahead at the time. I switched back and forth between the operating systems because Linux was more cool and web documentation all touted linux. The Linux distro's were easier to use and more people used them then FreeBSD so I used Linux for awhile as well.

FreeBSD back then had a better VM( still does), better tcp/ip stack( still does), better package management( still does except gentoo), better scsi support, raid card support, volume management, threading, etc.

Its not just the proprietary world where this happens. The most popular opensource apps also reign over technically superior ones.

As mysql gained popularity postgreSQL gained as well. Just not as much. Same is true for FreeBSD. There are a ton of kernel developers for FreeBSD who came from linux and prefered freebsd over linux for a variety of reasons.

Keep in mind Mysql is faster for simple benchmarks and doing things like inserts. Many hackers simply wrote simple perl scripts to see how many inserts a second both could do and found mysql faster. They then falsely assumed it would also be a superior RDBMS.

I heard in Japan the situation is the opposite. If you walked into a bookstore you would only see postgreSQL books and few mysql. Hype is a major factor.

Re:Apparently 90% don't need those features.......

[thing12]

We run postgres and we're doing our damndest to get rid of it. We have some databases that get 50-100% data turnover rate daily, making hourly vacuums essential to not having the Ever Expanding Database problem. Not to mention that vacuum doesn't clean up indexes, so you'll also have to re-index periodically if you don't want those to grow to thousands of times their optimal size.

Vacuuming is just a side effect of MVCC -- the expired rows have to be kept around so that other open transactions can see them. You pay on the backend for better concurrency on the front end. Even if you were continuously vacuuming your overall query latency and CPU usage would probably be lower than an equivalent MySQL database. There are also 3rd party projects that auto-vacuum... this is something that really should just be built in though. If automatic vacuuming were a configurable part of the base distribution, like the statistics collector, I'm sure you wouldn't be complaining. The sad thing is that it would be such an easy thing to add.

As far as reindexing, I would call that a bug with they way B-Tree indexes are designed. Fortunately, the database is able to reuse index pages if the rows contain similar values. But that doesn't help if you're indexing data that's never similar you're going to be screwed. For example, if your indexed column is a sequence and you delete only the oldest values the db won't be able to reuse that space in the b-tree. A solution would be to use random numbers for your keys since you would get an even distribution over time. It might be worth trying one of the other index types though (R-tree, GiST, or Hash) to see if you get the same results.

Why "real DBAs" whine...

[JohnDenver]

I think you guys presented a good case as for trade-offs.

MySQL - Fast, Well Supported, Good for Simplier Problems
PostgreSQL - Limited Support, Needs more Attention, Suited for Complex Problems

Why "real DBAs" whine

I think the problem is that many DBAs and developers have really come to rely on JOINS, stored-procedures, triggers, etc. I've been using these features very actively for the past 4 years and wondered what I did without them all these years.

In the past year alone, I've written almost 300 stored-procedures, about 1/4 of which are 4 or more pages of code that would have run like a dog has it been written on the client side.

Having done a lot of 2-tiered, 3-tiered, n-tiered design, I've come to realize that the middle tier (business rules) doesn't have to outside the database in Perl code. If you can implement your middle tier inside the database, you'll almost always see a significant performance advantage and not have to worry about writing engine code to handle issues like concurrancy and multiple users.

While MySQL is capable of handling a number of problems, it's very ill-suited for most enterprise business problems.

You should notice that these "Real DBAs" mostly have a fit when people start making claims that MySQL can compete with Oracle. They're afraid some pointy haired boss is going to believe these claims, and is going to ask them to convert thier Oracle databases to MySQL.

As for myself, I see MySQL as a nice step forward as far as performance is concerned, but it's a HUGE step backwards as far as functionality.

In other words, It's a great engine for storing/retrieving lots of data, but it doesn't have the tools for manipulating/querying the data.

For a lot of people, that means not cutting the muster.

Thanks for the comment

What the PostgreSQLer's dont understand

[Random Walk]

I would agree that even in the open-source world, many apps are popular because (a) they were the first on the market, or (b) have the better marketing department.

However, having coded in C both for MySQL and PostgreSQL, I have to say that the MySQL docs are clearly better, and that their client library is more feature-rich than PostgrSQL. The MySQL database may lack features, but on the client side it is much easier to get simple things running.

A moratorium on technical religious wars

[camusatan]

This should be an interesting article - hey, new version of xyz is out, now it does feature blah. Great news.

But instead, there's stupid recurring garbage with idiots on both sides trying to explain that one database is better than another. Here's what I think the objective truth is - the fact of the matter is whatever works for you is fine.

If you like using stored procedures, triggers and constraints and think SQL compliance is important, then use Postgresql, and it will work fine for you. If you don't, and you like fast access to your data, use MySQL.

There are use scenarios that one can construct which will make either database look completely ridiculous and terrible (look at some of the comments for some examples). But it doesn't matter, if you can code for MySQL and think in MySQL, it will work fine. If you code for Postgresql and think in Postgresql, it will work fine. I've used both, in heavy and light production environments, and they both have their uses. I've also found scenarios where I can't use either, and have to go to something else.

To all those who say, "But...but...MySQL is just a fake SQL interface to the filesystem!" Well, fine. Where do you store your files, then? I presume, since a filesystem is such a terrible place to put your files, that it's not in a filesystem, eh?

And to those who say, "but, I can SELECT out of MySQL eight hundred bajillion times faster than Postgresql!" Well, fine, but what happens if you try to concurrently insert something? How's your data integrity hold up if some errant SQL inserts data that doesn't refer properly to other tables?

What I'm trying to say is that it's all relative, and trying to phrase things as, "This is the right thing to do, in all cases, for all scenarios" is narrow-minded and simplistic. Use what works for you - and note that a lot of this depends on how you think when you are writing your code. So two different programmers working on the same problem might solve the problem using different databases - and both be right.