Migration From Oracle to MySQL

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