Slashdot Mirror
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."
According to the MySQL site (manual) Gamma status was granted in December with version 4.0.6.
I think the InnoDB allows FK constraints (and InnoDB is included now)
http://www.mysql.com/doc/en/SEC458.html
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.
You can't grep a dead tree.
What other features might there be?
--------
Free your mind.
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.
have you been defaced today?
InnoDB uses row based locking.
Looks like they do already.
Ita erat quando hic adveni.
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.
There's always the documentation
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.
Production for what? For banks.. maybe not. For mom 'n pop online shops.. sure! And oh yes, even for /.
--------
Free your mind.
Just mirrored the file on Freenet, you can grab it here.
The InnoDB table type, which is now a part of the standard production release, supports transactions, row locking, and foreign keys.
Consciousness is an illusion caused by an excess of self consciousness.
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.
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.
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.
Software Wars
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.
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...
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.
I believe these are due for the 4.1 series. There's a little note about it here
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?
"Twice half-assed makes an ass whole." --Solomon K. Chang
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. . .
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 ?
Are you running both MySQL and PostgreSQL with the same optional constraints?
For example, what startup settings (optional parameters) are you using for PostgreSQL?
By default, PostgreSQL uses very conservative settings that favor reliability over speed (like not buffering any writes to disk).
Just flip one or two simple switches in the startup parameters and you might find HUGE speed boosts in PostgreSQL because it would be running under similar constraints as MySQL.
Also, are you using PostgreSQL 7.3.2 or an older version? The 7 series gained a lot of optimizations...
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
---- Sig. gone.
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.
Huh?
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.
The current implementation of PostgreSQL on windows is based on cygwin and isn't exactly said to be stable under higher loads. However, a native port should arrive with 7.4, which hopefully gets out around August.
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.