Re: Performance comparison of Oracle Vs Aurora MySQL

From: KEVIN CLOSSON <k_closson_at_comcast.net>
Date: Sat, 7 Apr 2018 12:17:31 -0400 (EDT)
Message-ID: <242618994.1.1523117848397_at_localhost>

The AWS DMS service can be used to replicate tables, schemas or whole databases to another database (even heterogeneous). Might be worth considering.


Sent from XFINITY Connect Application


-----Original Message-----

From: gogala.mladen_at_gmail.com
To: raviteja.bellamkonda7_at_gmail.com
Cc: oracle-l_at_freelists.org
Sent: 2018-04-05 8:55:22 PM
Subject: Re: Performance comparison of Oracle Vs Aurora MySQL

Hi!

Comments in-line.


On 04/05/2018 09:44 PM, Ravi Teja Bellamkonda wrote:
This is a pure OLTP Database. The decision makers are under the impression that having multiple read replicas and spreading the load would solve the problems they are facing ( Can you please comment on this).

OLTP databases make numerous small transactions on a single database. Transaction consistency is of paramount importance. That is why we have ACID rules. The only thing that read replicas would help with are reports.  That sounds like you are having problems with the reports on your OLTP database. There are several solutions to that. One is replication from Oracle to MySQL database, like Aurora. There are freeware products like Tungsten replication or SymmetricDS which can do a table level replication. There are commercial products like Golden Gate, SharePlex, Attunity and DbVisit Replicate which can do the same thing, only better. You can also use Perl scripts. That is what I did for one of my employers. Oracle database did all the heavy lifting, all group by clauses to populate a set of materialized views and then, when all was done, Perl script was used to copy the result to a MySQL database, with ENGINE=MEMORY tables. The reason for using MySQL was that x10 did not have decent ODBC driver while MySQL did. So, that was a sort of in-memory database if you want.  The replication mechanism was DBI, with DBD::Oracle and DBD::MySQL and scripting provided by yours truly. Reports were flying and the business analysts were happy.


They are considering that AWS provided tools would make the migration hassle free.

There is no such thing as hassle free migration from one database platform to another. There will be significant amount effort, time and money that would need to be invested. The application will need to be changed and read only replicas are not a panacea. They will help you with  reporting. So could an ordinary standby, to which you are entitled to with EE license. You can turn the standby to a snapshot standby, do reporting, and convert it back to a physical standby. It would literally cost you nothing. On the OLTP side, there is no PL/SQL, the MySQL procedural extension is very bare bones. Locking mechanism is different, so the applications will behave differently. There is no deadlock resolution, nothing like ORA-00060. You will only have "lock timeout exceeded" to go with. Unless we are talking COTS application which is also certified on MySQL, significant development, testing and QA efforts will need to be invested, in order to move from Oracle to Aurora. It's not easy.


They got a Camaro with a slow driver and thinking that switching cars would make them go faster. 

On Thu, Apr 5, 2018 at 5:55 PM, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:
Comments in-line


On 04/05/2018 08:11 PM, Ravi Teja Bellamkonda wrote:
Hi Mladen,

First of all thanks for the response.

The primary reason they are considering this migration is to attain a better performing database considering the scaling capabilities of Aurora would solve all the issues which makes a technological decision not a business decision.

Better performing doing what? OLTP? Reporting? Data Warehouse? Data Mart? Oracle, as opposed to various MySQL variants, is completely instrumented with a very rich set of tools to help you diagnose the performance problems.  So, where is the problem with Oracle? The answer to that question is crucial for answering your question. What kind of workload is bringing the Oracle RDBMS with 40 processors and 160 GB RAM to its knees? And going from Oracle to something else is ALWAYS a business decision, not a technology decision. If the performance was a problem, a good consultant could probably solve it for a small fraction of the migration cost. You will have a very demanding migration project on your hands that is likely to cost some serious money. Just to copy all the data from Oracle RDBMS --> Aurora will take some hard manual labour and scripting. Remember, you need to replicate the table structure, along with all foreign keys, study all the triggers and see how to replace them and copy all the data in a consistent manner, all while keeping the original database running. You are very unlikely to just disconnect the original DB and plough on with Aurora until the development is done. You are also very likely to need a heterogeneous replication software, like Golden Gate. We are talking about a major project and shelling out some real money, before you can even think of switching to Aurora.
I have done a migration project of migrating Oracle 11.2 --> DB2 9.7 and it was hard, despite the fact that DB2 is much more feature rich than any variant of MySQL. It is easier now with DB2 10 and 11 because those versions can execute PL/SQL natively, but it would still be a major undertaking.  Aurora is an order of magnitude more complex.


My disagreement is about the idea of scaling big would fix all the problems.

This is what I think about our scenario:
"If Camaro is not fast enough for you, definitely moving to a 18 Wheeler will not help". I might be completely wrong here.

Well, what it all boils down to is what are you doing with Camaro? What kind of tasks do you expect? Do you need the vehicle to pick up your kids and go shopping or do you need a vehicle to do some serious transport?



--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217




--
Thanks & Regards,
Ravi Teja Bellamkonda

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l
Received on Sat Apr 07 2018 - 18:17:31 CEST

Original text of this message