DBA Tales from the Front from Oracle to

DBA Tales from the Front: from Oracle to My. SQL 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*, DB 2, m. SQL, (Postgre. SQL), now My. SQL - 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 My. SQL) - Oracle gestalt vs My. SQL 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 My. SQL - 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 My. SQL: from no eyebrows to eyebrows…

Q: Are the differences in their websites symptomatic? - Oracle website (in my experience) was alway slow, confusing, overblown - My. SQL 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 - always new pieces and teasers and mystifying PR - My. SQL 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) - My. SQL - 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 My. SQL - 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 v 7, to be fair) - My. SQL - 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 10 g with its instrumentation (but can set up advisors, if you pay) - My. SQL GUI tools - My. SQL Query Browser, My. SQL Administrator, etc

Our first GOTCHA – storage engines - choose a storage engine! Eg… - My. ISAM - default, good performance, no FKs no ACID transactions - NDB (for clustering) - in-memory only on 5. 0 - Inno. DB - FKs and ACID-compliant transactions (Inno. Base 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 Inno. DB? – 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… - Inno. DB tables are clustered by PK, other keys point to PK values - beware that FKs in Inno. DB 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 latin 1_swedish_ci - 'a'='A' we use utf 8, utf 8_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 - 3 a: 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 - 3 a: init_connect and auto_commit, and that superusers bypass it - 3 b: 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 3 a. )

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: - My. SQL Error Number 1005 Can’t create table ‘. mydb#sql-328_45. frm’ (errno: 150)

GOTCHA - Locks are different! Inno. DB 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 - Inno. DB 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 Inno. DB 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 My. SQL? - 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 My. SQL by Jay Pipes (Apress) is like an “Ask Tom” type book. Sasha Pachev’s Understanding My. SQL 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 My. SQL, 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