DBA Tales from the Front: from Oracle to MySQL

1
DBA Tales from the Front from Oracle to MySQL

• Ben Krug
• DBA, Adapt Technologies
• ben_at_adapt.com
• OSCON July 2007

2
Who I am

• DBA at Adapt Technologies
• database experience (chronologically) SAS, Mumps
  (!), Sybase, Oracle, DB2, mSQL, (PostgreSQL), no
  w 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

3
What Ill 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)

4
Slides are just an outline

• handouts have more details
• for soft copies, email ben_at_adapt.com

5
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, its 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.

6
Oracle to MySQL from no eyebrows to eyebrows
7
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

8
Whats in a word?

• Terminology is different
• (eg, schema vs tablespace vs database)
• (cheat sheet in handouts)

9
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)

10
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 dont buy!) the DB

11
Different Philosophies?

• Open source vs closed
• Who are they serving
• What are they trying to achieve
• Etc

12
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

13
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

14
Different scaling strategies

• scaling out vs scaling up

15
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

16
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

17
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

18
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

19
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)

20
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

21
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)

22
Let the users in

• users' connection settings
• _at__at_autocomit - on by default
• can use init_connect settings to change
• beware - superusers bypass this!

23
Our next GOTCHA

• collations
• default is latin1_swedish_ci
• 'a''A'
• we use utf8, utf8_bin
• 'a'ltgt'A'

24
GOTCHA 3

• autocommit1 by default
• wanted autocommit turned off for most code, but
  on for one user

25
GOTCHA 3

• autocommit1 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)

26
GOTCHA 3

• autocommit1 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.)

27
Our next GOTCHA

• SQL_MODEs -
• Inserting inserting bad data by default, rather
  than giving errors
• How 0s can be used for null dates

28
GOTCHA

• error messages that are not informative or are
  misleading
• eg, errors involving foreign keys, indexes, and
  altering tables youd never guess the problem
  from the errors
• MySQL Error Number 1005Cant create table
  .\mydb\sql-328_45.frm (errno 150)

29
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

30
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)

31
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

32
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")

33
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 thats why I
  became a DBA.

34
And where do you go for help?

• Buy support! Its 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
  Pachevs Understanding MySQL internals (OReilly)
  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 youre going to
  read.)

35
QA

• Ben Krug
• DBA, Adapt Technologies
• ben_at_adapt.com
• OSCON July 2007