Q&A - from Oracle to MySQL

obtainablerabbiData Management

Jan 31, 2013 (4 years and 7 months ago)

135 views

DBA Tales from the
Front: from Oracle
to MySQL


Ben Krug

DBA, Adapt Technologies

ben@adapt.com


OSCON July 2007

Who I am:

-
DBA at Adapt Technologies

-
database experience (chronologically): SAS, Mumps (!),
Sybase, *Oracle*, DB2, mSQL,

(PostgreSQL),

now
MySQL

-
As a DBA, helped build an Oracle application that won a
Smithsonian Award for Excellence in Computing

-
Trivia: was once in an ad for Oracle, for the same system

What I’ll talk about:

-

what happened to me? (from Oracle to MySQL)

-

Oracle gestalt vs MySQL gestalt

-

gotchas (battle wounds
-

maybe you can learn from our
mistakes)


Slides are just an outline

-

handouts have more details

-

for soft copies, email ben@adapt.com


DBA at Adapt


from Oracle to MySQL

-

Accepted position looking for RAC mastery... got “Ask
Tom's” book, Tales from the Oak Table, CBO book by
Jonathan Lewis.

-

Read in Tales from the Oak Table how when systems get
heavily instrumented, it’s great, but it shows maturity, and
is a sign newer systems are coming around the corner...
few days later


find out my company is dropping Oracle
for an open source database.


Oracle to MySQL:

from no eyebrows to eyebrows…

Q: Are the differences in their websites
symptomatic?

-

Oracle website (in my experience) was alway slow,
confusing, overblown

-

MySQL website relatively simple, quick, and easy


What’s in a word?

-
Terminology is different

-

(eg, schema vs tablespace vs database)

-

(cheat sheet in handouts)


Differences in getting the scoop:


-

Wading through Oracle PR gobbledy
-
gook vs having to
research each item to find out about its existence or
features.

-

(eg storage engines, to see what they can actually do)


Differences in what they are

-
different products
-

be
-
all end
-
all vs a database

-
oracle seemed more complicated
-

RAC, interconnects,
fusion, etc etc
-

always new pieces and teasers and
mystifying PR

-

MySQL appeared more simple
-

a database. less
confusing PR but also less documentation. (OTOH, user
comments in docs.)

-

oracle
-

which features do you buy
-

DB, which
components, other products (Oracle Identity, etc etc)

-

MySQL
-

buy (or don’t buy!) the DB

Different Philosophies?

-
Open source vs closed

-
Who are they serving

-
What are they trying to achieve

-
Etc…

Different communities

-
Open source community vs proprietary source community

-
Knowledge of inner
-
workings of DB

-
Mood of excitement with MySQL

-
Who are the customers? (web 2.0!)

-
Etc…

Different support experiences

-

Oracle
-

hated it

-

log a tar, if it's not severity 1, good luck getting competent
help if you ever hear back

-

once had to make threats to get help with a mission
-
critical sev 1 (Oracle v7, to be fair)

-

MySQL
-

love it!

-

have always had timely help, almost always very
knowledgeable, helpful, and interested

-

don't need to try to get past level 1 support


Different scaling strategies

-
“scaling out” vs “scaling up”

OK, so go get it!


-
went to mysqlab.com and downloaded community edition
rpm's and installed them.

-

build from source if you're hardcore


Get your developer / DBA tools

-

not like Oracle 10g with its instrumentation (but can set
up advisors, if you pay)

-

MySQL GUI tools
-

MySQL Query Browser, MySQL
Administrator, etc


Our first GOTCHA


storage engines

-

choose a storage engine! Eg…

-

MyISAM
-

default, good performance, no FKs no ACID
transactions

-

NDB (for clustering)
-

in
-
memory only on 5.0

-

InnoDB
-

FKs and ACID
-
compliant transactions
(InnoBase owned by Oracle now)

-

Falcon
-

coming...

-
etc

Scaling strategies (scaling out)

-

replication configurations
-

master/slave

-

if you're going to use sharding, beware issues for
auto_increments, FKs, global views (failures if a host
fails), etc


Set up your backups

-

mysqldump (exports)

-

no hot backups for InnoDB?


linux can use LVM

-

can also use mysqldumps and then binary logs to roll
forward

-

(but beware statement
-
based vs row
-
based logging,
especially if you use auto
-
increments)


Set up your permissions

-
No roles

-

can be based on where someone is coming from (what
host or subnet)

-

networking issues? IP
-
based vs name
-
based
authentication


Build your DB! Issues…

-

InnoDB tables are clustered by PK, other keys point to PK
values

-

beware that FKs in InnoDB can cause locking issues
(lock wait timeouts)


Let the users in

-

users' connection settings

-

@@autocomit
-

on by default

-

can use init_connect settings to change

-


beware
-

superusers bypass this!


Our next GOTCHA

-
collations

-

default is latin1_swedish_ci

-


'a'='A'

-

we use utf8, utf8_bin

-


'a'<>'A'

GOTCHA 3

-

autocommit=1 by default

-

wanted autocommit turned off for most code, but on for
one user


GOTCHA 3

-

autocommit=1 by default

-

wanted autocommit turned off for most code, but on for
one user

-
3a: init_connect and auto_commit, and that superusers
bypass init_connect (which can be good)


GOTCHA 3

-

autocommit=1 by default

-

wanted autocommit turned off for most code, but on for
one user

-
3a: init_connect and auto_commit, and that superusers
bypass it

-
3b: security information for users is spread between
information_schema and mysql databases. ‘super’ is in
mysql.user, not in information_schema tables. (In case
you get bitten by 3a.)


Our next GOTCHA


-

SQL_MODEs
-





-
Inserting inserting bad data by default, rather than giving
errors

-
How 0’s can be used for null dates

GOTCHA

-

error messages that are not informative or are misleading

-

eg, errors involving foreign keys, indexes, and altering
tables


you’d never guess the problem from the errors:

-
MySQL Error Number 1005

Can’t create table ‘.
\
mydb
\
#sql
-
328_45.frm’ (errno: 150)


GOTCHA

-

Locks are different! InnoDB locks... deal with it:

-
Writers blocking readers, readers blocking writers

-

(row
-
level, not like Oracle block
-
level)

-
Issues with foreign keys

-

isolation levels

GOTCHA

-

using "force index" in a select doesn't really "force" the
use of the index

-

InnoDB optimizes using a set number of "random dives"
into a table to estimate statistics, and can still decide not
to use a “forced” index

-

(not like histograms or choosing how many rows to
sample for statistics in Oracle)


Care for your database

-

GOTCHA: optimizing for an InnoDB table prevents
updates

-

"large" table can take a long time

-

one multi
-
Gig table took hours to optimize, but then query
times were cut in half


Performance…

-
performance can suffer over time for inserts and updates
in large tables

-

use optimize if possible (in spite of the GOTCHA)

-

"insert on duplicate key update" (like Oracle's "merge")


So why do I love MySQL?

-

you can read the code, so people know how it works
(including support staff)

-

(I can understand it
-

it's not a black box)

-

enjoy the community (including Marten Mickos vs Larry
Ellison)

-

love the support

-

it's a database
-

not middleware, Application server, etc. I
like databases; that’s why I became a DBA.

And where do you go for help?

-
Buy support! It’s worth it


cheap and very useful.

-
Read the docs, the blogs, watch planetmysql.com.

-
Do a lot of googling

-
Books I recommend: Pro MySQL by Jay Pipes (Apress) is
like an “Ask Tom” type book. Sasha Pachev’s
Understanding MySQL internals (O’Reilly) is a nice
introduction to reading the code (if you want to)

-
(With Oracle, you have to work to read the tomes


the
docs and the few good books. With MySQL, you have to
work to find what you’re going to read.)


Q&A

-
Ben Krug

-
DBA, Adapt Technologies

-
ben@adapt.com


-
OSCON July 2007