Transcript Migration From Oracle to MySQL

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 TO_CHAR -> STR_TO_DATE and
DATE_FORMAT
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/using-new-
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. Fixing that allowed us to go live.
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

[email protected]

http://www.npr.org/blogs/inside