Transcript DBA Tales from the Front: from Oracle to MySQL

DBA Tales from the
Front: from Oracle
to MySQL
Ben Krug
DBA, Adapt Technologies
[email protected]
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 [email protected]
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 missioncritical 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
-
-
[email protected]
OSCON July 2007