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@adapt.com • OSCON July 2007

2. 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

3. 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)

4. Slides are just an outline • handouts have more details • for soft copies, email ben@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, 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.

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. What’s 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 don’t 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 • @@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'<>'A'

24. GOTCHA 3 • autocommit=1 by default • wanted autocommit turned off for most code, but on for one user

25. 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)

26. 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.)

27. Our next GOTCHA • SQL_MODEs - • Inserting inserting bad data by default, rather than giving errors • How 0’s 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 – you’d never guess the problem from the errors: • MySQL Error Number 1005Can’t 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; that’s why I became a DBA.

34. 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.)

35. Q&A • Ben Krug • DBA, Adapt Technologies • ben@adapt.com • OSCON July 2007