DBMS, July 1997
I enjoyed Charles Thompson's overview of replication approaches in Microsoft SQL Server 6.5, Sybase SQL Server System 11, and Oracle 7. (See "Database Replication," DBMS, May 1997, page 76.) The level of detail was better than I'm used to reading in the general trade magazines. I guess that's why I like DBMS so much. It's a pity Thompson chose not to cover IBM Corp.'s DB2. It has excellent replication facilities. Perhaps next time . . .
As Thompson points out, both Microsoft and Sybase use transaction log-sniffer technology. This results in very little additional load on the server. There is no effect on source transactions, because all work is performed by a separate replication process.
Oracle uses per-row triggers, running additional PL/SQL code for each row updated. Because the additional code is run by the same process that does the source transaction, the entire transaction runs slower. The additional load on the server is substantial. The throughput of a system can be seriously compromised by update-anywhere replication.
An Oracle representative informed me that the best performance for single-row update transactions Oracle has been able to get using update-anywhere replication is 14 transactions per second (tps). This was on hardware that achieved 200 tps in a nonreplicated environment.
Although Oracle did not identify the platform, 200 tps implies serious hardware, even for single-row transactions. It translates to 12,000 transactions per minute (tpm). Although not directly comparable, 12,000 tpm is a respectable TPC-C result for million-dollar systems. On the other hand, 14 tps is the performance I'd expect from a laptop.
I would caution designers to consider the performance impact of replication mechanisms before committing to a particular vendor.
Alan Bardsley
Vancouver, B.C.
70764.356@compuserve.com
You're absolutely correct on the performance issue. I wish I had the time to do a benchmark to gauge relative performance. Instead, I expressly left the whole issue out of my article.
At first (and second and third) glance, the Oracle mechanism, being very reliant on internal database mechanisms, would seem to perform very slowly. Without doing a benchmark on a particular configuration, though, you cannot be sure. The problem is that even though Sybase and Microsoft look only at the logs for replication, the replication processes still need to run somewhere, usually sharing the same CPU as the database engine. At least with these products, you have the option to move the replication server to a separate machine, avoiding potential CPU bottlenecks. Oracle doesn't allow this (yet). On the other hand, Oracle spreads the replication load across all source databases; Sybase and Microsoft concentrate it only on the machine(s) you designate as distributors/replication servers.
Benchmarks in the RDBMS world are notoriously suspect. The official benchmark world (TPC-C / TPC-D) encourages vendors to strip down their products to the bare minimum, then enhance it with third-party "go-fasters" such as Tuxedo (Bea Systems Inc.). Oracle and Microsoft dominate the official statistics -- Oracle at the top end (most tps) and Microsoft at the bottom (least $/tps). Sybase shows up every now and then, but not as often or as decisively as Oracle. Does this mean that Oracle's products are just easier to strip down specifically for benchmarks ? Or that Oracle's team is better at running benchmarks than Sybase's?
As you can see, I think the performance issue is too complex to deal with intelligently and safely without real benchmark results. Perhaps one day I will have the opportunity to do such a benchmark (or I will find someone who has done it).
Regarding DB2, I would have liked to have the chance to investigate IBM and Informix databases as well, but time and experience were against me. I have used the other three products at work, so I had to do a minimal amount of research on them.
--Charles Thompson
With Microsoft and Sybase, transactions on replicated tables do not process additional rows. A separate process performs the replication, not at the time of the source transaction, but when the replication is scheduled.
Because Oracle relies on triggers, every transaction on a replicated table has to process twice as many rows. A transaction that once inserted 10 rows now inserts 20. Ten updates becomes 10 updates plus 10 inserts. The impact on response time and throughput is obvious, even without considering the additional processor load of executing the trigger code once per row.
Also, you need not be limited to theoretical arguments. You should at least report what the vendors claim about performance. I believe that IBM, Sybase, and Microsoft are all quoting figures in the range of 10 percent to 20 percent additional overhead for replication, but quote them, not me. Call your Oracle representative. They can tell you their own benchmark results. Update anywhere replication reduced a 200 update transactions per second machine down to 14 tps. These are their figures, not mine.
For once I can accept performance figures from Oracle without reservations. I didn't even ask if Oracle used discrete transactions. Hardware vendors will be very pleased with Oracle replication.
DBMS readers deserve to have the whole story about replication. They need to know the real-life impact of replication in order to plan for it.
Alan Bardsley
70764.356@compuserve.com
First of all, Rose 4.0 is built around the UML. This is the default notation, although you can toggle to OMT and Booch, unlike the Platinum Paradigm Plus support, as Keuffel mentions.
Keuffel is correct in seeing Rose as a developer's tool. We do spend a lot of time making it very compatible with development environments, which is why we have produced specific versions for Visual Basic, PowerBuilder, and others, rather than just throwing in another code-gen script, as our competitors have done.
Rational is a software company, not a consulting company. Our product license revenue is about 70 percent of our revenue, and much of our service's revenue is actually support subscriptions.
All language-integrated versions of Rose do include DDL and IDL generation for multiple databases.
Adam Frankl
Rational Software Corp.
adamf@rational.com
Rational may now be a software company, but its roots are in consulting. My comment in the article should have read, "Rose was 'originally' developed by a consulting company," which would have made my other comment, regarding why "the tool has not been grown in a consistent and unified manner," a bit clearer.
Regarding the DDL and IDL capabilities, I could not find those on the tool that was sent to me, and I looked hard for them. I was pleased to learn recently that the 4.1 release of Rose will converge the various platform-specific versions of Rose into the properly architected tool that it should have been from the beginning.
--Warren Keuffel