Migration From Oracle to MySQL
An NPR Case Study By Joanne Garlow
速
npr.org
Overview
Background
Database Architecture
SQL Differences
Concurrency Issues
Useful MySQL Tools
Encoding Gotchas
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
High-Level System Architecture
Limitations of the Oracle Architecture
Reached capacity of single system to support our load
Replication outside our budget
Databases crashes were becoming frequent
Database Architecture Goals
Redundancy
Scalability
Load balancing
Separation of concerns
Better security
High-Level System Architecture
Database Architecture Content Mgmt System
Main RO slave
Main Web Servers • Read and updated only by our website InnoDB • Low resource contention Mainby a nightly script • Updated • Small tables or log tables RO slave • Read-only by our Content • Short Transactions Management System • Need fast full text queries AMG STATIONS PUBLIC (replacing Oracle Text) MyISAM InnoDB InnoDB • Large tables • Isolation Updatedbyfrom bya our main Content website Management System • Updated quarterly script • Transaction Read-onlyfrom byOriented our • Read-only ourwebservers website • Horizontally Resource Contention scalable Scripts • Some log type information written Backup • Highly Normalized • Low resource contention RO slave • No transactions
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!
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();
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 @lastSeqId - (@rownum:=@rownum+1), . @rownum:=-1) r, . . .
. .
. . ) SELECT . FROM (SELECT
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.
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
DATE_FORMAT
TO_CHAR
->
STR_TO_DATE
and
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;
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
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.
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
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
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
Query Profiling ď ą
Try the Query Profiler with Explain Plan when debugging slow queries http://dev.mysql.com/tech-resources/articles/usingnew-query-profiler.html
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. to go live.
Fixing that allowed us
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)
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
Questions?
Joanne Garlow
jgarlow@npr.org
http://www.npr.org/blogs/inside