Posted
by
ScuttleMonkey
on Tuesday September 16, 2008 @10:19AM
from the something-oracle-should-have-done-long-ago dept.

Robert Hodges, CTO of Continuent, has an interesting blog entry about a new approach to database replication that they are undertaking. The new approach aims to provide easier access to replication for low-end Oracle users in addition to the alpha offering they already have for MySQL. "It's not a coincidence that we chose to implement MySQL and Oracle replication at the same time. MySQL has revolutionized the simplicity and accessibility of databases in general and replication in particular. For example, MySQL users have created cost-effective read scaling solutions using master/slave replication for years. MySQL replication is not free of problems, but there is no question MySQL AB helped by the community got a lot of the basics really right. On the other hand, Oracle replication products offer state-of-the-art solutions for availability, heterogeneous replication, application upgrade, and other problems, albeit for high-end users. For example, Oracle Streams and Golden Gate TDM offer very advanced solutions to the problem of data migration with minimal downtime. The big problem with these solutions is not capabilities but administrative complexity and cost."

Yea that is what I tough. I saw the article I though oh cool something that may be helpful. Then I saw it linked to some guys blog to an app of some unknown company, for a product that is in Alpha. Yea Like I am going to print that out and show it to the DBA.

Keep watching. It won't be alpha for long. On the plus side we are being completely open about the development and will not deceive you about the true state of the software. Robert (the guy that wrote the original blog entry)

Too hard for me. Where is the source code and what is the license? The downloads from https://forge.continuent.org/frs/?group_id=26 [continuent.org] dont contain source and the svn repoistory svn://svn.forge.continuent.org/svnroot/treplicator does not work with my svn client or the browser link on the gforge page.

The license for our current software is GPL v2. This covers replicating MySQL to MySQL as well as from MySQL into Oracle and other databases. Oracle redo log support will be commercial, but there will be a free version for up to 2 hosts. I will get source builds posted today . Finally, if you can tell me what the problem was with SVN I'll try to fix that as well. (SVN is making us a little crazy at the moment--sorry if you are infected by it. The MySQL guys recently switched to Bazaar, which is startin

Definitely. After reading the blog post it just sounds like an extra layer of software that's able to replicate across different brands of database. Since most people don't want to maintain multiple database brands the only advantage I can see is in migration.

There are very good reasons to replicate (or at least synchronize) between disparate DB systems. For example, you have a LAMP website but want to feed it data from your backend ERP system. Another fantastic example is mobility, where you have a mobile database like SQL Anywhere [ianywhere.com] on your PDA and/or laptop synchronizing to your enterprise DBs.

Excellent points. In my experience it's most common to refactor the data or only use a subset when synchronizing disparate databases. So I expect to build a small custom data migration app when dealing with this. But your point is taken.

Can't speak for other technologies, but when it comes to SQL Anywhere and its synchronization technology MobiLink, there is nothing limiting you from synchronizing subsets of the schema and/or synchronize from a variety of sources to an arbitrary mapping of tables.

For example, you can pull data for one table from your consolidated database while pulling data for another table from a web service.

We replicate from Oracle to MySQL using redo-logs and a 143 line perl script. This has worked for about the last year, although there is a 15 minute delay in Oracle 9i, as there is no easy way to query online redo logs. All that changes when our data warehouse goes up to 10g, at that point we will get real time replication.

The MySQL database is only 250gig, around 2.2 billion records, used for all our websites (read-only), while all update systems hit Oracle, the updates are fed to a single MySQL master, wh

Primarily cost, as load on the web site ramped up, we simply added more servers, no oracle licensing cost. Plus our dbadmins seem to have a hard time keeping the oracle boxes up for more than a week at a time, whereas the mysql boxes have been running for 90 days without a glitch, and our sysadmins love the fact that they do not have to do anything with them

Hi, on the contrary, this is not just another layer of software. First, our software is alternative replication that fixes a number of problems with existing mechanisms like MySQL replication [blogspot.com]. Second, it provides replication (or will when we release) where it is sorely needed, namely low-end Oracle implementations. Whoever put this on Slashdot got the title right. Third, cross-database replication is a big problem for a lot of people and there's nothing in open source right now that addresses it effec

Yes - if you want more features, Oracle is expensive. Although that cost includes support. But that has nothing to do with his statement of a hybrid - unless you are saying the only thing that will be merged is cost.

That's part a - then next part b says that the db can be distributed freely with the software. IANAL - but I would think it can be used as the back end for a web site.The limits listed below that in regard to amount of data, ram and processors are also pretty strict. I'm not an Oracle cheer-leader. But I do work with their products daily and I cringe when I see people say that MySQL or even PostgreSQL (which I absolutely love) are on par with Oracle. They aren't. They may be eventually but they

You are right - if they are using Oracle as the back-end for their own apps. But just like MySQL gets all the FOSS love - any company running stuff like PeopleSoft, Siebel (both owned by Oracle now) SAP, etc. wont be looking at MySQL as an option. They will be building on something commercial.Where I work, the biggest MySQL database we have is being moved to Oracle to make it easier to integrate with or Oracle stuff running some of the above mentioned apps.

That's good negotiating on your part - though Oracle Enterprise out of the box does not have the same feature set as SQL Server Enterprise. Partitioned tables are one example. There is also all the extra you have to pay if you want the full functionality of enterprise manager. There are more but those are the ones that I can think of off the top of my head.Oracle has done a lot to be competitive with SQL Server - but they did a lot to bring down cost by splitting functionality off and charging fo

Nice. And Larry can afford to give somebody a break once in a while. I think one thing though that becomes an issue sometimes is that Oracle has made agreements with the Feds that the price they got is the low price and they wont go around selling it for less than that to others.

It can make it interesting. I work for a non-profit so we get some nice deals from them too.

I can't think of anything - seriously. I'm not trying to troll, fill me in on where MySQL is better than Oracle.

MySQL beats Oracle on price.

I'm sure you are creative enough to come up with many examples where MySQL fails miserably compared to Oracle and I accept that. Just don't think Oracle is the one and only solution for every type of problem out there.

It's hands-down easier to use, especially for people who are not database experts. There's not even an installation in the Oracle sense of the word--you just 'yum install' and it's there or maybe it came straight off the distribution you installed. That was part of my point what MySQL really got right.

MySQL is better for small Databases, Oracle is better for Big ones.(Small Databases consist of Millions of records, Big ones Billions) Installing Oracle for average Joe company is overkill, and huge overhead. That is why a lot of Oracle shops switched to Microsoft SQL, and MySQL.It would be nice to have a lot of the features of Oracle with ability to manage small databases.

I'll have to respectfully disagree. I think Oracle brings numerous features to the table that MySQL doesn't have - and running a small database with Oracle does not come with a lot of overhead.I run a number of small oracle installs that use very little in the way of resources. So I'm not sure why you think Oracle can't manage small databases.

I'm not a big fan of Oracle for a few reasons - but those mostly have to do with closed vs. open and some of their high-end stuff- specifically deali

Overhead is not always technical per-say. It is cost, management, etc... MySQL can normally be setup by some guy with little DBA skills and have a good working system. Oracle takes a bit more work. As well the upfront cost of licenses.

What's the long term cost of having a database run by somebody who doesn't know how to run a database?If nothing will ever be done that requires dba knowledge - then a ton of products will work and Oracle Express falls into that category - with SQL Server Express in a similar boat. (Though SQL Server is limited to a single OS.)

I think people really overestimate what it takes to get Oracle installed and running. It's not that hard to do and their are vast resources available to help get one

Oracle isn't worth it, where possible to avoid it, just because it's Oracle. By buying into "A DB is better on Oracle" you just hand them scads of money based only on their brand name.

Having to pay is just a minor part of it - having to pay more based on having a second CPU core, etc is just absolutely unworkable. Any software you don't buy outright is NOT worth the costs of having in your company. They haven't remotely disabled customers accidentally (I think) like VMWare (and Steam, id Software, Microsoft

Huh? Oracle doesn't even have a licensing server, you're just expected to comply with the licensing terms. I guess they probably put in some wording about auditing into the license, but I've never heard of Oracle exercising that option.

Anyone can go to Oracle's web site and download the enterprise version of the database and install it. There is no activation or key necessary. Oracle can't do anything to your database.As you say - you are in violation of the license if you use it that. Also - when you pay it isn't just to license the software. It is for support. And without a current support contract, you can't call oracle support or log in to metalink for support, downloading patches, etc.

For a "small database" this is just as possible with Oracle. It'snever required much expertise to start getting into trouble witha relational database (even Oracle).

As far as the money goes... there are varying degrees of cost andyou will quickly run into "simple" business requirements (likeonline backup) where mysql quickly makes itself appear not quiteup to the job.

A n00b can install Oracle and at least have decent crash recovery.That's not something that you can take for granted with mysql.

Administration is only part of the reason shops switched the SQL Server, the other was cost - MS was giving it away for free sometimes, our company switched to SQL Server when we found MS gave us a redistribution licence, I remember the management wetting themselves in a company update meeting telling us how we were moving to use it and not Oracle because it was a licence to print money.

Our users have been suffering ever since:)

(not that I have that much against SQL Server, but you do need to write a lot o

I'd say simplicity of management is debatable.I think the server install for Oracle is reasonable and very configurable. You are 100% right about the client side though. It is a huge pain with Oracle. The thin client is small - but doesn't work too well for a lot of things.

Yes - if you are going FOSS, MySQL has a lot of momentum. That would not be helped my the creation of an Oracle/MySQL hybrid though.

What does MySQL have that Oracle needs or would improve it? I can't think of anything - seriously. I'm not trying to troll, fill me in on where MySQL is better than Oracle.

Ease of use and cost.

Sure, you think Oracle is easy, but you are an Oracle DBA. MySQL is trivially easy for a developer to set up and get running. I mean, 'apt-get install mysql-server' easy. I just typed 'apt-get install oracle' and, surprise, I have no Oracle. So Oracle is harder.

Oracle is also obscenely expensive. Don't get me wrong--if you need it, it's worth every penny. But 3 of my last 4 clients could not stop bitching about the cost of Oracle licenses and Oracle DBAs, yet MySQL or PostgreSQL w

When I sat down to price out Oracle, SQL Server, and MySQL a few years ago I was surprised to find out the costs were roughly equivalent for a small/medium installation.

MySQL is free because it's open source but by the time you opt for useful add-ons like hot backups and support the costs get up there. People opt for SQL Server due to Windows familiarity, but frankly Oracle is rock-solid on Windows too. From a functionality perspective neither offer any functional advantage over that Oracle, so it beco

And we know that while MySQL replication has many wonderful features like simple set-up, it also has many deficiencies that have persisted for a long time. Monty Widenius, a widely respected MySQL engineer, summarized some of the key problems last April:

- replication is not fail safe- no synchronous options- no checking consistency option- setup and resync of slave is complicated- single thread on the slave- no multi-master- onl

Its an interesting read:Change MySQL development model to attract outside developers-Give outside developers commit and decision rights to the MySQL server code base (similar constraints as for internal developers)-Do this by aiming at a high target: "MySQL should within 2 years have as many outside core contributors as PostgreSQL".Yes, we have a lot to learn from how PostgreSQL is developed!

Hi! Speaking as the author of the aforesaid blog, MySQL clearly got some things very right--mostly around about basic usability. It's nice to have replication baked in, for example. That's a fundamental reason why so many people use it.
The synchronization and failure issues are pretty serious. As another poster pointed out we are equally serious about fixing them. If you read my blog we are pretty clear about what they are. One issue we can't solve completely involves problems with consistency be

I'm suprised Oracle is letting this fly--it probably somehow violates the EULA, by reverse engineering Oracle redo logs.
Basically, it seems like they are targetting smaller shops, who don't want to buy Oracle Enterprise Edition (and hire a f/t DBA).
My only question would be, how big is the market that needs to do replication, but can't spend the $ or â on Oracle Enterprise (which can be cheaper than you think for a small shop)

I am sure there is a huge market for Replication. Small Databases need to be rock solid too. Just because you may have a lot of data doesn't mean it isn't important or mission critical. Paying an extra $2000 for a server for a fail over is well worth the cost of your only SQL (Any Brand) server being down for a few hours to days.

It depends on what type of replication one needs. The simplest means of Oracle replication is to create another database somewhere else and simply copy the logs over and apply them. We have done that for years for one simple reason... for databases that don't require 5 nines service, I don't want an 'up-to-date' copy.

A major cause of database failures in my experience has been due to programmer or administrator error (i.e. program bug or someone typing truncate table wrong.) Creating scripts to copy the logs to another server as soon as they are created, and applying them after they are a few hours old, we maintain an environment where it only takes a few minutes to switch over and prevent the 'uh-oh' moments from corrupting both databases.

We have found that the decision to switch over to a remote database takes time. No one wants to do it because then we have to copy that whole database back to the primary site eventually, and that takes a lot of effort and time. When we have a failure, someone is tasked at getting the remote database ready while the discussions are held whether or not to fix or fail-over.

One benefit of this has been that when we have 'uh-oh' moments, the old data is still available and we can correct it, sometimes without the end-users even being impacted.

So.. for all of you database admins out there, replication is possible without the fancy software *IF* you can write scripts, create a somewhat simple system to repoint to the new one (i.e. DNS), and can live with a few minutes of downtime a couple of times a year.

No machine is 'up-to-date' as there is no such thing as instantaneous data transfers *unless* data is applied and committed to both machines at the same time. This is very costly outside of a local area. Before Oracle had replication services, we used this and were able to keep the remote machine (which was 2,000 miles away) within 5 minutes of the primary 95% of the time. It dawned on us one day how dangerous this was and changed it to not apply the logs for 60 minutes since it would only take us about 10 minutes to catch it up and switch it from stand-by, longer than it would take to decide to fail over and change DNS.

I did not mean to imply this was the best solution for replication, only that if someone is using replication software for this type of purpose, i.e. disaster recovery, then this is a perfectly acceptable solution that probably will work for any database and does not require additional licenses *except* for the remote machine. Oracle allowed us to run the remote machine this way without a license since we did not use it in a production capacity.

A backup solution??? I guess it is a backup of the primary, but many replicate machines are this way since 2-way replication difficulties often limits the usability of the remote server beyond read-only access. The data is never used to 'restore' the original machine, it is an exact, fully usable copy. If the primary machine crashed, the choices are to restore from tape, or make the remote machine the primary. The remote machine can serve as the production server for as long as is necessary. To restore the primary machine after the remote server has been used for an extended period, a backup of the remote has to be taken and applied to the primary. This can take a few days as the amount of data needed to be transferred is very high. A backup is taken to tape, overnighted, applied, and then additional log files are applied until the two are in sync and a point is reached where a controlled fail-back can occur.

In fact, at one facility, we 'swapped' machines every 30 days to make sure they would function if one failed.

Continuent? Continuent? Who thinks up this stuff? All I can think of is some suit explaining it. "We harness the synergistic energy of the word 'Continue' while simultaneously fulfilling our carnal, earthy ambitions by joining in sympathy with the Ents who will, perhaps in some future time or far-away land, form our products' user base."

One of the first comments I got on the original blog posting for MySQL replication was whether we could replicate in and out of SQLite. We are still thinking about that-if there's demand we'll do it. Plus there are guys within our team that think we should be using it to store replication events.

SQLite doesn't scale very well. I set it up as the backend database for Bacula [bacula.org], and it struggled with a database of only a few hundred megabytes, and that's really not a "big" database. When I replaced it with MySQL, all the performance problems vanished.

SQLite is cool and has its place, but I wouldn't use it for storing any more than a few megs.

The other issue is that SQLite uses a single lock on the database, so if you have more than a small number of concurrent updates, it won't be pretty. Still, if there's interest in replicating to/from we would be up for it or would hope that somebody might see their way to add a plug-in for it. In the meantime, it looks like a really handy embedded database.

Nobody said it is the best database for all cases. My point was simply that it embodies simplicity and accessibility for most applications (these days that means web apps). No user/permission nonsense, no server to run and maintain...