Postgresql acquires hot standby in 9.0 (was: The mysqld witch is dead?)

[tyme]

http://www.h-online.com/open/news/item/PostgreSQL-9-0-brings-replication-and-more-1082376.html

http://wiki.postgresql.org/wiki/Hot_Standby
http://wiki.postgresql.org/wiki/Streaming_Replication

Goodnight sweetsour princemysql!

[Nitrogen]

I'll believe it when I see it works.

It can't be any worse than slony was.

[RevDisk]

I'm in the boat with Nitrogen.  I'll let other folks try it out and see how it works.

[AZRedhawk44]

I love MySQL.

Granted, I haven't used it in close to 8 years... I've been an MS weenie since then.  But I cut my teeth on SQL and entity normalization with MySQL, wrote my first DB app using LAMP.

I've always heard that PostresSQL was the retarded stepchild of the two.

[tyme]

I love MySQL.

Granted, I haven't used it in close to 8 years... I've been an MS weenie since then.  But I cut my teeth on SQL and entity normalization with MySQL, wrote my first DB app using LAMP.

I've always heard that PostresSQL was the retarded stepchild of the two.

That's backwards.

Mysql started out as a quick and dirty sql-ish database to solve the data storage needs of a proliferation of web apps in the latter portion of the .com era, which needed a multi-user Excel with an SQL interface and fast performance.  Apps using mysql historically have been targeted at that level: no expectation of ACID, no transactions, no foreign keys, no stored procedures, no triggers, no views, and poor scalability except for nearly pure read-only loads because of locking.

Mysql has been adding those sorts of features, but why?  If it is trying to compete against postgresql based on rdbms features, it's still behind.  99% of webapp deployments don't care about the features mysql has been adding, but they do care about performance.  Even with replication, the rdbms model doesn't scale very well on cheap hardware.  High-traffic websites are moving to no-sql databases like cassandra, leaving only critical data (like account data) in the hands of some sql server.  Medium traffic websites have adopted heavy caching strategies, memcached being the go-to app-level caching solution for almost every app these days, which takes load off of the database server.  Low traffic sites you could run with sqlite and it'd make no difference.

Oracle has committed to keeping MySQL dual-licensed only until 2015.  Continuing to develop MySQL into a full-featured RDBMS will hurt Oracle's market share.  Most of the MySQL developers jumped ship to MariaDB or drizzle.  Draw your own conclusions.

So what is all this ranting, anyway?  I'm just a bitter mysql hater, right?
http://www.youtube.com/watch?v=AL1l9Puh0sk#t=6m00s
"We noticed a lot of random failures" in 5.1... "many of these random failures were actually fatal bugs in MySQL that we have fixed... some of them we have contributed back to Sun and Oracle, some of them we have not because Sun especially Oracle has not been responsive at all... if they are not going to work with the community, why should the community work with them?"
--Monty, co-founder of MySQL AB, now founder of Monty Program which develops MariaDB.

So, basically, MySQL (now MariaDB, since that's where Google's and Percona's Mysql patches are being applied) development is just now focusing on getting stable in edge cases.  Monty rants at length later in that video I linked about how mysql development had been stagnating for years, both in features and in bugfixes.

I'll believe it when I see it works.

Why wouldn't it work?  Postgresql developers may not have a flare for marketing or HCI, but they care about postgresql's technology, and they are extremely capable.

[KD5NRH]

Granted, I haven't used it in close to 8 years... I've been an MS weenie since then.  But I cut my teeth on SQL and entity normalization with MySQL, wrote my first DB app using LAMP.

I wish they'd let us use it back when I was building a DB for a bunch of knuckleheads who wanted everything in Access - including the report that should have been an Excel sheet.  They didn't care that it took 90 minutes to run a 1-page report that would have been done in 90 seconds if we'd just had it export to Excel, or that the database had a frickin' novel worth of VBScript to even make the report possible, (I wrote the functions, and had another guy dedicated to sticking them all together) as long as it stayed entirely within Access.

OTOH, they never figured out that we were padding out time estimates by 50%-and-a-unit, (two hours -> three days, one day -> a week and a half, etc.) and they paid by the hour, so we tolerated it.  Funny thing is that the team (at least four that I know of) of degreed and higher paid developers working on a very similar database at their other site started three months ahead of us and still hadn't finished a year after I left the company.  Had we known that, we'd have padded a lot more.

[roo_ster]

How is postgre relative to the big boys like DB2, Oracle, etc.?

[tyme]

DB2 and Oracle are better at sales and marketing, they have an established customer base and reputation from back in the days when open source databases were far inferior, and people naturally think they're worth more because they're very expensive.  You can throw DBAs and lots of hardware at Oracle and DB2 installations that need extreme performance, and Oracle particularly has a reputation for being able to do anything.  Nobody ever got fired for buying Oracle.  The DBAs, the licensing and support costs, and the hardware are all very expensive, however.

Nothing's free; open source databases have significant support costs just like the major commercial databases do, but those costs are cheaper.  Postgresql has much less market share, so there are fewer support companies -- like Command Prompt Inc or EnterpriseDB -- and fewer DBAs, which makes it harder to get help fast on a project.  Oracle DBAs, on the other hand... they're everywhere.

So many database buyers (management types) seem to want a black box and want no part in understanding it or maintaining it.  If that's the case, maybe Oracle is for them (or maybe MSSQL instead), but they should be prepared to pay for their lack of interest, and they're deluding themselves thinking that they can run a business that uses Oracle without any in-house database-related infrastructure or payroll.  A non-trivial database that supports a critical business need needs a real DBA on payroll, and some programmers to write software to interface with the DB, and enough hardware to require at least a small IT staff.  So I question the premise: I don't think it's feasible to treat a database as a black box and depend on outsourcing for all DBA and maintenance needs.  Some of  programmers in that scenario probably have experience doing simple tasks with mysql or postgresql, whereas they probably won't have any experience maintaining Oracle.  The more tech staff you have, the more in-house expertise you have with open source databases.  Oracle support you always have to pay for.

Microsoft and other software megacorps can whine all day about how there are "hidden costs" in open source, but often you can find existing employees with open source experience that you can leverage and -- for small-scale needs -- avoids having to pay consultants or hire extra employees.

The question is not so much "Is Oracle more likely to do everything I need and everything I ever will need?" but rather "Is Oracle the most cost effective?"

http://www.informationweek.com/news/software/linux/showArticle.jhtml?articleID=201001901

If postgresql has all the features you need, then postgresql is better.  If you absolutely need more exotic features from Oracle or DB2, then you are in a small minority of Oracle and IBM customers.  I don't see how anyone can know whether Oracle or DB2 will outperform Postgresql for a specific application until they optimize the app for both databases and benchmark both.  Generic database benchmarks are notoriously unreliable because optimizing SQL is so complex and every database does it differently.

A particular query will always run better on some databases than on others, but a good DBA or even a db-amateur can usually construct a query that will do what the app needs and run fast, it's just going to require a different query, or sometimes additional indexes, for each different database software.

Management types seem to think that if they just buy Oracle or IBM, everything will be better.  Not necessarily.  Google for instance had mysql (using innodb, presumably) running their adwords database, years ago.  Some PHB got the idea that they should switch to a commercial database (not specified) because everyone knows those "real" databases are better.  Over engineers' objections, they switched, and it was so bad they switched back.

Major postgresql users that talk about it: NTT (Japan's major telecom), Fujitsu, CAIXA bank (Brazil), flightaware.com, Skype, Afilias (TLD operator for .org and .info)

[RevDisk]

So a fair amount of the MySQL devs are bailing.  Do you see Postgresql superseding mysql as the generic SQL install of choice?  Or something else?

[roo_ster]

This sort of dovetails with my thread on data mining tools.  I'd rather not stuff those gigbytes of text files into a database, but it might come to that.  My mission will be 80% accomplished if I put the kibosh on any bright idea to dump it into MS Access, The Absolute Worst and Most Pernicious MS Product Ever. 

Our IT folk are getting better, but there was a serious bias against linux and open source tools for many years, even as my part of the industry was abandoning the proprietary unixes and WinNT* and running to linux hollering "Woo-hoo, woo-hoo!"  Instead of IT being the one with open source tool knowledge, all us systems, software, EE, & ME guys were learning while our IT guys were walking around saying, "Does anyone need a Solaris deployment?  Irix?  HPUX?  Bueller?"






* Some tools were like hte Italian military: modernized one generation too soon, before Linux became viable.

[Nitrogen]

Why wouldn't it work?  Postgresql developers may not have a flare for marketing or HCI, but they care about postgresql's technology, and they are extremely capable.

It's not that I don't think it'll work; I've just done this long enough that I know that going bleeding edge on anything will hurt in the end.

I tend to use tools I know work, for me that's MySQL and Oracle.  I've had too many problems with postgresql in the past, but someday it'll fill the hole that MySQL is trying to fill, and fill it better.

So many of my customers are running Oracle and really don't need it...

Oh, and a great site I found: (nsfw language in link which I hid, and a bit within the site)
http://www.how*&@#$%ismydatabase.com

[tyme]

Do you see Postgresql superseding mysql as the generic SQL install of choice?

I think MariaDB, but I'm not an oracle.  MariaDB could implode.

http://drupal.org/node/861192#comment-3236272  (also see post #4 at that link, and an Oracle employee's persepctive at post 60 & 61, and more at 66)
https://wiki.ubuntu.com/Lucid-MariaDB-Inclusion
http://europa.eu/rapid/pressReleasesAction.do?reference=IP/10/40 (documents the EU's concern not only that Oracle could kill MySQL, but that Oracle has no incentive to continue adding enterprise-style sql features to MySQL since doing so would make MySQL a better competitor to Oracle.)

The fact that Postgresql is technically more solid is not a reason for most MySQL users to switch to it.  If [when] Oracle destroys the reputation of the MySQL brand, I think many people will already have switched to MariaDB, more will switch to MariaDB, and some will switch to Postgresql, Drizzle, NoSQL databases, and various other solutions.  After all, most mysql users still use myisam, so they don't care about ACID and don't care as much about occasional munching of their data, as long as the database is fast for typical webapp many-reads few-writes query patterns.

[rcnixon]

Informix!

[RevDisk]

I think MariaDB, but I'm not an oracle.  MariaDB could implode.

From what I understand, MariaDB is supposed to be a drop in replacement for MySQL.  I'd let it grow a bit more before transitioning.  I'll check back into it when they hit v5.3

[tyme]

Huh?  It IS a drop-in replacement.  I run it locally on my machines.  As that first link shows, drupal's test cluster mostly runs mariadb.  Many other people use it: it includes patches from google and Percona that haven't made it into Oracle's official mysql version.  It also contains bug fixes.  It is in every way superior to mysql 5.0 or 5.1.  It is more stable, performs better, and is a drop in replacement.  The binary names are even the same.

But, like any open source project, the community could move elsewhere and let it rot.  If Oracle miraculously gets its act together and accepts the patches that are being merged into mariadb, then mariadb could be sidelined.

[tyme]

Adding more musings here rather than starting another thread.

I've been trying to dig up more stuff on Oracle since I know comparatively little.  It seems there's plenty of dislike for Oracle support, and buying Oracle and paying for support from them, does not absolve you of having to pay for in-house support:
http://searchoracle.techtarget.com/news/1351260/How-to-get-more-from-Oracle-tech-support

Then there's James Gosling's (father of Java) comments about Oracle. (Oracle employees come up to him and say, "This is the latest stupid thing [Oracle has] done [to Java]...").  If they do stupid stuff with regard to Java, what's to say they won't and aren't doing stupid stuff WRT MySQL?  Gosling comments that he doesn't really get SQL at all, that he considers scalable, failure-tolerant NoSQL systems to be good enough.  Obviously there's some disparity between cases he's considering and cases like banks and financial exchanges, where billions of dollars could be at stake in a few transactions.  That's not the usual case for ecommerce, and it's not the usual case for in-house database OLTP systems that are gathering tons of useful data at high transaction rates.  What percentage of Oracle customers are banks and financial exchanges, anyway?
Gosling's interview: http://www.basementcoders.com/transcripts/James_Gosling_Transcript.html

Finally, since there are now flash drives like Fusion-IO's iodrive products, there's no reason to go with Oracle or DB2 for vertical scaling, because you can stick a few IOdrive cards (expensive, at ~$7k and up, but peanuts compared to the real database servers and SANs that you'd otherwise have to use to get high IOPS) in a cheap server, have another identical server as hot standby, and get incredible (apparently >100k iops) performance.  But there's the whole OLAP space which I've been looking into, where Oracle only has a smallish piece of the pie.  I've found an article on NYSE Euronext switching to and using Netezza for OLAP, which suggested all their trade data is going into Netezza immediately.  I'm not sure if they still have a traditional OLTP system recording transactions or whether the entire database infrastructure is Netezza.

Financial exchange transaction volume isn't as high as I initially thought.  It's not a killer app for reliable transactional performance.  As late as 2003, NYSE is documented as having a capacity of only 4000 trades per second.  Even if the trade rate is several times that today, it could easily be handled with an appropriate SAN, even without solid state storage.

I'm still searching for the killer OLTP app, the one where vertical scaling with Oracle is a requirement and Postgresql simply can't scale with its third party solutions like pgpool-II.  What industry/task has the highest transaction rate with the most critical data where losing even a couple transactions would be a catastrophe? (since there's no synchronous replication built into postgresql until 9.1, and even then Oracle will probably beat it handily in performance because they've been optimizing their sync replication for large customers for years)

[Marnoot]

In my position as a software developer I've grown to despise Oracle. It's a workhorse, no doubt, and can handle enormous workloads better than MSSQL (my preferred DB at work), but it's just so plain clunky to use as a developer when there are no in-house Oracle DBAs or anyone to provide assistance. I'm sure to a full-blooded Linux/Unix geek it's easier to get a handle on. I hate the cryptic command-line operations, and I hate the clunky Java GUI (is there any other kind of Java GUI)?

With the exception of a couple customers I deal almost entirely with MSSQL at work, and highly prefer it to Oracle for our application. My experience with MySQL is wholly limited to the personal and family websites I run. It's certainly worked fine for those, but I've never had to use it in a high-load or professional capacity.

[tyme]

http://www.mysql.com/products/

The way I read that, InnoDB is no longer (going to be) part of the free (GPL'd) mysql product.  Of course old released versions of innodb are still GPL, but it seems that Oracle is going to keep InnoDB improvements from being merged into XtraDB (Percona's and MariaDB's fork of InnoDB), because they seem to view InnoDB as a premium extra feature.

Let's see... Oracle is burning bridges on Java, burning bridges on OpenOffice, and now on MySQL.  Larry seems intent on destroying any value from the Sun acquision other than IP.  Oracle doesn't seem to realize that IP alone doesn't mean anything these days.  If people don't like you, they will run off and write their own software (or in this case fork existing GPL'd code for the projects you're trying to close-source), and you'll be left holding the bag.

I read something recently about Larry buying and dismantling Sun as a favor to his long-time pal, Jobs (who doesn't like Java -at all-).  That doesn't seem very far from the truth.

[Nitrogen]

 

I read the bit about InnoDB this morning.  If you happen to find the bit about Jobs, i'd love to read it, myself.

MySQL forks like Percona and MariaDB are looking more and more enticing...

[RevDisk]

MySQL forks like Percona and MariaDB are looking more and more enticing...

Ayep.  I'm going to be looking more closely at MySQL replacements.  So will a lot of folks.