csn wrote:Thanks Sxooter--really useful info.
Wow. How Orwellian. They're that afraid?
BTW, do you know if Mysql has changed much since it (or one of its db engines--I can't remember) was acquired by Oracle?
Well, the two sides of the Oracle story are that A: Yes they're afraid and B: They don't want people doing a drive by benchmarking by configureing oracle poorly and then comparing it to a different, well tuned db. I can see both sides of that. I will say that for most operations, Oracle doesn't really feel any faster than MySQL or PostgreSQL. It does scale pretty well though, assuming you've got it in big enough iron to handle the load you're throwing at it.
As for MySQL / Innodb, it depends on how you look at it. Part of MySQL AB's strategy was to up-sell you to the commercial version. They generally try to say that if you are writing an app JUST for MySQL then it either has to be GPLed (or compatibly licensed) or you have to get a commercial license to go with it when you sell it / release it. I.e. you can't, technically, according to MySQL AB, write a closed source app that hits MySQL and sell it without selling a MySQL license at the same time. They could do this because they had the rights to all the code, including things like innodb. If you're writing GPL code no worries.
With Oracle buying innodb, they technically have the power to not renew that license, which would mean that commercial licensing of MySQL with innodb would not be possible. Which is why the falcon storage engine is being developed to replace innodb. If your app is linking to the MySQL connection libraries, and you're relying on innodb, and you want to release it, you might have some future problems when the license MySQL AB has with innodb runs out.
Of course, that's mostly a theoretical issue. Most apps written for MySQL are GPL compatible, or in house only. But still, it does call into question the future of innodb in MySQL commercial.
MySQL itself was NOT aquired by Oracle btw, just InnoDB. I wouldn't say MySQL the database has changed much, but I don't know about MySQL the company. I would guess that their direction as a company has likely changed a bit, i.e. they're aiming to replace InnoDB eventually. It's a shame, because while InnoDB isn't perfect, there are some applications for which it's a good fit, and losing it (eventually) will not help MySQL the database win any beauty contests.
For an example of one of the things InnoDB does that's quite different from PostgreSQL, this bit of code (in serializable transaction mode) has very different behaviour in both databases:
begin;
select avg(score) from sometable;
update sometable set field=avg_from_above+someformulaihave; (or any other update / insert)
commit;
In MySQL with InnoDB, the whole sometable table will be locked after the select statement with the aggregate, and the transaction is guaranteed to be atomic and do the maths correct. PostgreSQL will not lock the whole table on the aggregate, and there is the possibility of a race condition at that point. In PostgreSQL, you don't do that. But, you can do aggregate after aggregate and the table won't be locked. Selects in postgresql, without a "for update" clause, never ever lock anything in pgsql. It's not better or worse, it just is. But it means that certain operations in MySQL would get the right answer at the cost of locking the whole table. If a client connects and does a select avg(somefield) with no where clause (or with one too I guess) in a serializable transaction, then goes to sleep, you've effectively issued lock table statement.
InnoDB is actually interesting in that all the data is pretty much stored as a series of columns that are semi-independently stored, and each acts as its own individual index.
InnoDB has faster commits, but at the cost of much slower rollbacks. So, if you do a select avg(xx) and then update, and then rollback, the table may be locked for a VERY long time.
There are behaviours to avoid in both dbs here. There are strengths of one or the other depending on what you're doing. Losing innodb is not a good thing, and I doubt the falcon table handler will be mature enough for production in anything less than 2 years. But we'll see how they do.
