1 / 24

Migration from Oracle to MySQL: An NPR Case Study

Discover the migration journey of NPR from Oracle to MySQL, highlighting challenges, SQL differences, and useful MySQL tools. Learn about the high-level system architecture, database goals, and practical solutions. Explore SQL conversion issues and helpful tips for replacing Oracle features in MySQL.

lesley
Télécharger la présentation

Migration from Oracle to MySQL: An NPR Case Study

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Migration From Oracle to MySQL An NPR Case Study By Joanne Garlow

  2. Overview • Background • Database Architecture • SQL Differences • Concurrency Issues • Useful MySQL Tools • Encoding Gotchas

  3. Background • NPR (National Public Radio) • Leading producer and distributor of radio programming • All Things Considered, Morning Edition, Fresh Air, Wait, Wait, Don’t Tell Me, etc. • Broadcasted on over 800 local radio stations nationwide • NPR Digital Media • Website (NPR.org) with audio content from radio programs • Web-Only content including blogs, slideshows, editorial columns • About 250 produced podcasts, with over 600 in directory • Mobile apps and sites • Syndication

  4. High-Level System Architecture

  5. Limitations of the Oracle Architecture • Reached capacity of single system to support our load • Replication outside our budget • Databases crashes were becoming frequent

  6. Database Architecture Goals • Redundancy • Scalability • Load balancing • Separation of concerns • Better security

  7. High-Level System Architecture

  8. Content Mgmt System Database Architecture Main RO slave Main InnoDB Web Servers • Read and updated only by our website • Low resource contention • Small tables or log tables • Short Transactions Main RO slave • Updated by a nightly script • Read-only by our Content Management System • Need fast full text queries (replacing Oracle Text) • Large tables AMG MyISAM STATIONS InnoDB PUBLIC InnoDB • Updated by our Content Management System • Transaction Oriented • Resource Contention • Highly Normalized • Isolation from main website • Read-only by our webservers • Horizontally scalable • Updated by a quarterly script • Read-only from our website • Some log type information written • Low resource contention • No transactions Backup RO slave Scripts

  9. Issues When Converting SQL • MySQL is case sensitive • Oracle outer join syntax (+) -> OUTER JOIN clause • Oracle returns a zero to indicate zero rows updated – MySQL returns TRUE (1) to indicate it successfully updated 0 rows • MySQL sorts null to the top, Oracle sorts null to the bottom Use “order by – colName desc” for sorting asc with nulls at bottom • MySQL has Limit clause – YAY! • No sequences - DOH! Continued….

  10. Replacing Oracle Sequences • Initialize a table with a single row: CREATE TABLE our_seq ( id INT NOT NULL ); INSERT INTO our_seq (id) VALUES (120000000); • Do the following to get the next number in the “sequence”: UPDATE our_seq SET id=LAST_INSERT_ID(id+1); SELECT LAST_INSERT_ID();

  11. Replacing Oracle Sequences • For updating many rows at once, get the total number of unique IDs you need first: SELECT @totalRows := COUNT(*) FROM... • Then update npr_seq by that many rows: UPDATE npr_seq SET id=LAST_INSERT_ID(id+@totalRows); • and store that ID into another variable: SELECT @lastSeqId := LAST_INSERT_ID(); • Then use the whole rownum workaround described above to get a unique value for each row: INSERT INTO my_table (my_primary_id . . . ) SELECT @lastSeqId - (@rownum:=@rownum+1), . . . FROM (SELECT @rownum:=-1) r, . . .

  12. Converting Functions • NVL() -> IFNULL() or COALESCE() • DECODE() -> CASE() or IF() • Concatenating strings || -> CONCAT() • ‘test’ || null returns ‘test’ in Oracle • CONCAT(‘test’,null) returns null in MySQL • LTRIM and RTRIM -> TRIM() • INSTR() works differently. • Use LOCATE() for Oracle’s INSTR() with occurrences = 1. • SUBSTRING_INDEX() and REVERSE() might also work.

  13. Converting Dates • sysdate -> now() • Adding or subtracting • In Oracle “– 1” subtracts a day • In MySQL “- 1” subtracts a milisecond – must use “interval” • TRUNC() -> DATE() • TO_DATE and TO_CHAR -> STR_TO_DATE and DATE_FORMAT

  14. Update Differences • You can't update a table that is used in the WHERE clause for the update (usually in an "EXISTS" or a subselect) in mysql. UPDATE tableA SET tableA.col1 = NULL WHERE tableA.col2 IN (SELECT tableA.col2 FROM tableA A2, tableB WHERE tableB.col3 = A2.col3 AND tableB.col4 = 123456); • You can join tables in an update like this (Much easier!): UPDATE tableA INNER JOIN tableB ON tableB.col3 = tableA.col3 SET tableA.col1 = NULL WHERE tableB.col4 = 123456;

  15. RANK() and DENSE_RANK() • We really found no good MySQL equivalent for these functions • We used GROUP_CONCAT() with an ORDER BY and GROUP BY to get a list in a single column over a window of data

  16. Collation • You can set collation at the server, database, table or column level. • Changing the collation at a higher level (say on the database) won’t change the collation for preexisting tables or column. • Backups will use the original collation unless you specify all the way down to column level.

  17. Concurrency Issues • In our first round of concurrency testing, our system ground to a halt! • Deadlocks • Slow Queries • MySQL configuration • sync_binlog = 1 // sync to disk, slow but safe • innodb_flush_log_at_trx_commit = 1 // write each commit • transaction_isolation = READ-COMMITTED

  18. Useful MySQL Tools • MySQL Enterprise Monitor http://www.mysql.com/products/enterprise/ • MySQL GUI Tools Bundle: http://dev.mysql.com/downloads/gui-tools/5.0.html • MySQL Query Browser similar to Oracle’s SQL Developer • MySQL Administrator

  19. Innotop and innoDB Status • innotop http://code.google.com/p/innotop • Helped us identify deadlocks and slow queries (don’t forget the slow query log!) • In mysql, use show engine innodb status\G; • Useful for contention and locking issues

  20. Query Profiling • Try the Query Profiler with Explain Plan when debugging slow queries http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

  21. Concurrency Solution • Tuning our SQL and our server configuration helped • Turns out that the RAID card we were using had no write cache at all. Fixing that allowed us to go live.

  22. Encoding Gotcha’s • Switched from ISO-8859-1 to UTF-8 • Migration Tool • Issues with characters that actually were not ISO-8859-1 in our Oracle database • Lack of documentation for the LUA script produced by the migration GUI • Update encoding end to end • JSPs, scripts (Perl), PHP, tomcat (Java)

  23. Continuing Issues • Bugs with innodb locking specific records (as opposed to gaps before records) • Uncommitted but timed out transactions • Use innotop or “show engine innodb status\G; “ and look for threads waiting for a lock but no locks blocking them • Requires MySQL reboot

  24. Questions? Joanne Garlow jgarlow@npr.org http://www.npr.org/blogs/inside

More Related