MySQL’s white paper on migrating from Oracle to MySQL

I just found this March 2006 white paper on migrating from Oracle to MySQL. Too bad I hadn’t seen this before I gave my talk or started this blog! Go to mysql.com and click on “White Papers” under the “Discover” tab, then search the page for “A Practical Guide to Migrating From Oracle to MySQL”. (Or go directly to http://www.mysql.com/why-mysql/white-papers/ and search for it.)

Good paper on the issues involved that gives tips, references tools and case studies, and has appendices that compare syntaxes, data types, etc. Wish I had found it earlier. Good to keep poking around from time to time, to find things like this.

tips for MySQL newbies

Are you a brand-newbie to MySQL? Coming from another database (or databases)? If so, here are some concepts to read up on right away to ease your confusion down the road. These are the top things I wish someone had told me about before I did anything else with MySQL.

(I’ve had a lot of questions along these lines, and have posted on all of each of these items somewhere, but thought it’d be more helpful to gather the points together into one place.)

MY TOP 2 “BEFORE YOU BEGIN” TOPICS

#1: storage engines. Before you do any big planning, if you don’t know what a “storage engine” is, find out!
I can’t think of a good analogy for what a storage engine is. The best I can come up with is, you know how with Oracle you choose your index types (btree? bitmap? etc)? In MySQL, you choose your table types (and with them, the index types). Your choice determines a lot. Each storage engine (table type) has various features and limitations. You need to know them. For instance, do you want ACID-compliant transactions or foreign-key constraints? You’ll most likely be using the InnoDB storage engine then. In which case, clustering is not supported – that would require the NDB storage engine (which, by the way, is an in-memory storage engine – ie, all your tables must fit into memory).
You can read more in my post on the subject, which includes further links for even more information.

#2: scale-out vs scale-up: plan your strategy (it’ll affect your schema and a lot more)
Plan your scaling strategy. Not only is it crucial to do, it might affect your schema design and much more! (Eg, if you “scale out”, you’ll need to plan your schema accordingly.)
You can read more in my post on that subject, which includes further linds for even more information.

Before you roll anything into production, you’ll need to read up on these topics (among others):

TWO VERY IMPORTANT THINGS THAT ARE DIFFERENT THAN YOU’RE USED TO

#1 backups
- read the documentation (on mysql.com) on mysqldump. It’s something like export, but you can use it with archived binary logs for point-in-time recovery, too.
- or use LVM if you have linux, to take snapshots
- or buy add-ons like MySQLHotCopy (?)
- or all of the above!
more notes with further links in this post.

#2 permissions
there are no roles; permissions are based on a userid and (if desired) the host or hosts they connect from. It’s a bit laborious, and you’ll want to read up on it and play with it. (I got snagged on it a few times; see this for an example.)

AND 5 OTHER THINGS I THINK YOU SHOULD KNOW ABOUT RIGHT OFF THE BAT

#1 collations – by default, if you compare strings, it’s not case sensitive (‘a’='A’). Read up on collations and character sets.
#2 autocommit is turned on by default. If you want to change this, read up on init_connect. Or see this post for more.
#3 the optimizer is not as developed/fine-tuned/complex be sure to test and QA and “analyze” your statements as much as possible. Use the slow query log to find longer-running queries and analyze them. Use “force index” in your select statements where needed (hopefully you know your data better than the optimizer).
#4: SQL_MODE. If you’re concerned about strict data integrity, you should read up on SQL_MODE. MySQL can insert “bad” data silently by default… (0’s for null dates, etc)
#5: isolation levels. If you’re going to use transactions (or even if you’re not), read up on isolation levels. This can affect locking, transactional integrity, etc. The default setting is not quite like Oracle’s.

where to get further info:
- download the software from mysql.com
- dev.mysql.com has the documentation, papers, etc
- planetmysql.com is a blog aggregator for MySQL-related blogs
- do lots of google’ing
- recommended books: Pro MySQL, MySQL 5.0 Certification Study Guide, MySQL Internals (and more…)

- buy MySQL support (it’s actually very good! Not at all like Oracle’s support. Best software support I’ve ever experienced by far, personally.)

I’ve been reading my September-October issue of Oracle Magazine.

And this has been said before but I can’t help but say it again…

In the latest Oracle Magazine, Tom Kyte and Ari Kaplan’s columns both tout one of 11g’s new features – the “server results cache” or “query result cache” (as the authors referred to it, respectively) . They both describe it as a “great new feature”. Neither happens to mention that MySQL has had this since version 4.0. It rankles me. It looks like there are a lot of truly “great new features” in 11g; let’s give credit for this one where it’s due.

backups worked

We had an unscheduled test of our backups last night. Point-in-time recovery using a mysqldump and binary log files worked fine (thank goodness).

I’m used to thinking of (Oracle) exports being one thing, and point-in-time recovery (using hot backups) another. Maybe there’s a way to do a “PITR” in Oracle using an export, rolling forward using timestamps rather than SCNs… Don’t know. I know that with MySQL you can do it.

Last night we had some user-generated data corruption on a production server. The database was relatively small (a few Gig), so, after stopping the database and restarting it with ––skip-networking, I imported it from the latest daily mysqldump. Although I didn’t use ––master-data for the mysqldump (we had some locking issues with that in the past) I knew the time that the mysqldump had been started.

I did a little investigation into the binary log files using mysqlbinlog

mysqlbinlog ––start-datetime=”2007-09-17 11:25:00″ ––stop-datetime=”2007-09-17 11:30:00″ binlog.000003 > my_tempfile

and found out the exact time I wanted to start rolling forward from. (I already knew when to stop rolling forward.) Then I ran it

mysql -p our_database < mysqlbinlog ––start-datetime=”2007-09-17 11:26:00 ––stop-datetime=”2007-09-17 20:45:00″

Checked things out, shut down mysql and restarted it normally, and we were once again off and running.

(Luckily for us, in this case I didn’t need to know an exact or down-to-the-second time to start rolling forward from. Activity was very light around the time of the mysqldump. Also, luckily, we didn’t need a starttime more precise than to-the-second. If we had needed to be more precise, I think I would have needed a mysqldump taken with the ––master-data option.)

oh for the wait interface

We purchased MySQL support, and I installed the advisor software that comes with it, and checked out the tuning advisors. I feel like I’m back in the days before the Oracle wait interface, tuning everything with hit ratios, etc, and basically guessing which changes might help what how much. (Tune, cross your fingers, start over, try again. Repeat.) Like before I had ever read “Optimizing Oracle Performance” by Cary Millsap, or “Oracle Insights:Tales of the Oak Table”.

My #1 wish for MySQL at this point would be that version 6 would be instrumented, so that we could tune using wait events, to identify actual wait times and what contributed to them by how much.

For now, I get recommendations like “Query Cache Has Sub-Optimal Hit Rate“, which is giving me flashbacks… (In our case, I don’t have to worry much about this in particular, and the advisors are good enough to let me know that I might “Evaluate whether the query cache is suitable for your application. If you have a high rate of INSERT / UPDATE / DELETE statements compared to SELECT statements, then there may be little benefit to enabling the query cache.“)

Anyway, it’s hit ratios all over again, making me pine for Oracle’s Automatic Workload Repository. Where I get recommendations about specific queries or indexes, and estimates for what the performance improvements might be if I change them, based on actual times spent doing operations. (And also, eg, how much time is spent waiting for things outside of the database, too, so you might avoid spending 80% of your time tuning something that will give your application an improvement of 2%.)

I think that, for now, the slow query log is my best friend. It gives me actual times spent doing which queries, and from there I can tune those queries. For the database itself, I’m back to comparing various ratios over time and trying to improve “bad” ones and seeing whether it makes a noticeable difference.

Falcoln in the belly

Jeremy Cole’s post “On Falcon and the need to feel wanted” got me thinking…

The MySQL family is gearing up for a new arrival. Falcon is still a baby in the belly, but it seems to already be getting more attention than its older and accomplished siblings (eg InnoDB). Maybe the proud mother-to-be has issues because every time she looks in InnoDB’s eyes she can’t stop thinking about the new stepmother, Oracle.

She seems to think this one will be the best baby ever, and has pinned high hopes on what it will grow up to become. Part of her excitement is because she is rightly taken with the brilliant father, Jim Starkey. If Dad could invent MVCC and BLOBs, what will baby Falcon grow up to accomplish under his care?

Hopefully the proud parents will have a keen eye open to shortcomings as well as strengths. Spare the rod
and spoil the child… Brilliant Dads don’t always make perfect fathers (to say the least). Time will tell, and we look forward to watching baby take its first steps and say its first words, and we hope it will live long and prosper. And that Mom will take an equally strong role as Dad in raising and forming the character of their new blessing. Based on the ultrasounds and other tests and measurements, there may be some issues that will need attention.

(Ultrasounds and commentary available from MySQL Performance Blog at InnoDB vs MyISAM vs Falcon benchmarks – part 1 and Falcon Storage Engine Design Review.)

the slow query log (and lock time, and InnoDB)

MySQL is not so heavily-instrumented as Oracle. (Back to tuning using ratios rather than the wait interface… sigh…) But it does offer (among other tools) the slow query log. Turn it on (put ‘log_slow_queries‘ in your config file), and all queries that take longer than long_query_time seconds (also set in your config file) will be logged to a slow query log file, along with the time they took to execute. Then you can use mysqldumpslow to analyze the output. You can see, for example, which slow queries are taking the most cumulative time on your server, or are being executed most frequently. (The manual doesn’t say much about it, and mysqldumpslow ––help doesn’t give too much help, but I think mysqldumpslow -s t and mysqldumpslow -s c do that.)

If you use InnoDB, though (as we do), be aware that the “lock time” logged in the slow query log only counts time for table-level locks that are taken at the MySQL top level, not InnoDB locks taken at the storage-engine level. (The logging is done at the “top” level.) So the “lock time” in the log is pretty useless for you. (You can use innodb_lock_monitor for that, but that’s another story…)

on collations and character sets

If you’re new to MySQL, you might not know that in the default configuration ‘a’='A’. Ie, string comparisons are by default case-insensitive. If this is a surprise to you, read up on Chapter 9 of the online manual, on character sets and collations.

The default character set is latin1 and the default collation is latin1_swedish_ci (‘ci’ stands for ‘case-insensitive). If you don’t want ‘a’ to equal ‘A’, you can change this by setting the variables ‘character-set-server’ and ‘collation-server’ in your config file or your startup options. For example, we want to support unicode, so we use character-set-server=’utf8′ in our config file. We also added the following (in the [mysqld] section) so that clients will use the right character set and collation, too: init_connect=’set names utf8; set collation_connection=utf8_bin’.

On the other hand, by default in linux, table names are case-sensitive, which might also be new to you if you’re new to MySQL. If you try to mysqldump a database from linux to Windows (with its case-insensitive file-system), you might want to preserve this behaviour by setting lower_case_table_names=1 in your Windows config.)