Notes on Learning MySQL (as an Oracle DBA)

This post originally appeared over at Pythian. There are also some very smart comments over there that you shouldn’t miss, go take a look!

I spent some time last month getting up to speed on MySQL. One of the nice perks of working at Pythian is the ability to study during the workday. They could have easily said “You are an Oracle DBA, you don’t need to know MySQL. We have enough REAL MySQL experts”, but they didn’t, and I appreciate.

So how does an Oracle DBA goes about learning MySQL?
Obviously you start by reading the docs. Specifically, I looked for the MySQL equivalent of the famous Oracle “Concepts Guide”.
Unfortunately, it doesn’t exist. I couldn’t find any similar overview of the architecture and the ideas behind the database. The first chapter of “High Performance MySQL” had a high level architecture review, which was useful but being just one chapter in a book, it lacked many of the details I wanted to learn. Peter Zaitsev’s “InnoDB Architecture” presentation had the kind of information I needed – but covered just InnoDB.

Thats really too bad because I definitely feel the lack – which I can easily tell you what Oracle does when you connect to a database, run a select, an update, commit or rollback – I can’t say the same about MySQL. So far I managed without this knowledge, but I have a constant worry that this will come back and bite me later.

Lacking a concepts guide, I read the documentation I had access to: Sheeri has nice presentations available for Pythian employees (and probably customers too. I’m not sure if she ever released them to the whole world). The official documentation is not bad either – it covers syntax without obvious errors and serves as a decent “how do I do X?” guide.

But reading docs is only half the battle. The easier half too. So I installed MySQL 5.1 on my Ubuntu from ready packages. Then I installed MySQL 5.5 from the tarball – which was not nearly as much fun, but by the time this worked I know much more about where everything is located and the various ways one can mis-configure MySQL.

Once the installation was successfull, I played a bit with users, schemas and databases. MySQL is weird – Schemas are called databases, users have many-to-many relation with databases. If a user logs in from a differnet IP, it is almost like a different user. If you delete all the data files and restart MySQL – it will create new empty data files instead. You can easily start a new MySQL server on the same physical box by modifying one file and creating few directories.

MySQL docs make a very big deal about storage engines. There are only 2 things that are important to rememeber though: MyISAM is non-transactional and is used for mysql schema (the data dictionary), it doesn’t have foreign keys or row level locks. InnoDB is transactional, has row level locks and is used everywhere else.

There are a confusing bunch of tools for backing up MySQL. MySQLDump is the MySQL equivalent of Export. Except that it creates a file full of the SQL commands required to recreate the database. These files can grow huge very fast, but it is very easy to restore from them, restore any parts of the schema or even modifying the data or schema before restoring.
XTRABackup is a tool for consistent backups of InnoDB schema (remember that in MyISAM there are no transactions so consistent backups is rather meaningless). It is easy to use – one command to backup, two commands to restore. You can do PITR of sorts with it, and you can restore specific data files. It doesn’t try to manage the backup policies for you the way RMAN does – so cleaning old backups is your responsibility.

Replication is considered a basic skill, not an advanced skill like in the Oracle world. Indeed once you know how to restore from a backup, setting up replication is trivial. It took me about 2 hours to configure my first replication in MySQL. I think in Oracle Streams it took me few days, and that was on top of years of other Oracle experience.

Having access to experienced colleagues who are happy to spend time teaching a newbie is priceless. I already mentioned Sheeri’s docs. Chris Schneider volunteered around 2 hours of his time to introduce me to various important configuration parameters, innoDB secrets and replication tips and tricks. Raj Thukral helped me by providing step by step installation and replication guidance and helping debug my work. I’m so happy to work with such awesome folks.

To my shock and horror, at that point I felt like I was done. I learned almost everything important there was to know about MySQL. It took a month. As an Oracle DBA, after two years I still felt like a complete newbie, and even today there are many areas I wish I had better expertise. I’m sure it is partially because I don’t know how much I don’t know, but MySQL really is a rather simple DB – there is less to tweak, less to configure, fewer components, less tools to learn.

Jonathan Lewis once said that he was lucky to learn Oracle with version 6, because back then it was still relatively simple to learn, but the concepts didn’t change much since so what he learned back then is still relevant today. Maybe in 10 years I’ll be saying the same about MySQL.

BAAG, Best Practices and Multiple Choice Exams

(This post originally appeared at the Pythian blog)

I’ve been following the discussion in various MySQL blogs regarding the sort_buffer_size parameters. As an Oracle DBA, I don’t have an opinion on the subject, but the discussion did remind me of many discussions I’ve been involved in. What’s the best size for SDU? What is the right value for OPEN_CURSORS? How big should the shared pool be?

All are good questions. Many DBAs ask them hoping for a clear cut answer – Do this, don’t do that! Some experts recognize the need for a clear cut answer, and if they are responsible experts, they will give the answer that does the least harm.

Often the harmless answer is “Don’t touch anything, because if you have to ask this question you don’t have the experience to make the correct decision”. As Sheeri noted, it is a rather patronizing answer and it is stands in the way of those who truly want to learn and become experts.

But I can appreciate that it comes from long and bitter experience. Many users read random bits of information off the web and then rush to modify the production database without fully digesting the details. They end up tuning their database in a way no database should ever be tuned. Not even MySQL.

I used to think that users search for those “best practices” out of laziness, or maybe a lack of time. I used to laugh at the belief that there are best practices and clear answers, because if there were – we wouldn’t have a parameter. But now I think the problem is in the way most institutions evaluate intelligence, which affects the way many people approach any problem.

Even though all of us DBAs come from a wide variety of cultures, I’m willing to bet that every one of us had to work his way through a multiple choice test. If you ever took an Oracle certification exam, you know what I mean:

How do you find the name of the database server?
D) none of the above

You run into those in certification exams, job interviews and in slightly different variation when you try to get accepted to a university. You had to learn to succeed at those multiple choice tests at a very early age, or you would be labled “less intelligent”.

Yet those questions are absurd. In the question above, the answer could be A, but A would be wrong if my database is a RAC cluster. Besides a much better way would be to use /etc/oratab because there may be more than one DB on the machine.

But you can’t have a discussion with the exam author. You can’t ask for assumptions and clarifications  and you can’t even explain your assumptions in the test. What’s more, these tests also check for speed, so you don’t have much time to contemplate the options.

What these exams teach you is that every question has a single solution and one that is so obvious that once you see it, you recognize its rightness in less than 30 seconds. They also teach you that someone else knows the right answer (the person grading the test). So finding the right answer can be a matter of getting the “expert” to give you the one and obvious correct answer.

If our society teaches this point of view from a very young age, why are we surprised that DBAs keep looking for the one obvious answer?

I fully support the BAAG cause, and I believe that DBAs should be given a full explanation of the problem involved, the different choices that exist, their meaning, the trade-offs involved and in general give them the tools to make the right decision themselves time after time. But we should realize that undoing years of faulty teaching can take a while.

There is an even worse side effect to those multiple-choice-should-be-obvious tests. You may learn to never ask for clarifications. That asking for clarifications is “bad” or “wrong” in some way. In any real problem, asking for clarifications such as “why are you asking this?”, “what is the real issue you are trying to solve?” and  “How will you use this script?” is the most important part of finding a solution. It is not cheating – it is doing a professional job.

It is a scary thought that the very way we train and evaluate the future DBAs is something that will prevent them from doing a good job of being DBAs.

Meeting Mr. Pythian

Last night I had dinner at San Francisco with Paul Vallee, CEO of Pythian.

Just in case you never heard about Pythian: They are a company specializing in remote DBA services. There are other companies that do remote DBA, but Pythian is special, because other companies don’t contribute to the community nearly as much as Pythian does.
They have a Pythian blog, with useful technical articles. Incidentally that was the second blog I started reading (The first was Tom Kyte’s). Their second contribution to the community is even more unique –  Log Buffer – the weekly compilation of everything that is hot in the DBA blogsphere.

With so much community interaction going on, it was not surprising to find out that Paul Vallee is on top of every industry trend and has some unique ideas on where the industry is heading. Talking to him for few hours gave me more stuff to consider than I usually encounter in a week or maybe two.

Paul is a huge believer in the future of MySQL. I know a lot of Oracle DBAs think that MySQL is barely a database, but according to Paul there is a huge demand for DBAs that can manage MySQL, because many organizations find out that in some cases Oracle is too much of a database. I have some experience with MySQL and I can’t say I like it much. On the other hand, I like the MySQL community a lot – it is small, friendly and very helpful to newbies. I’m not sure this nice community realize how much more mature Oracle is. Not just in terms of features, also in things like scientific thinking and optimization methodologies.  MySQL community needs people like Tom Kyte, Cary Millisap and Jonathan Lewis, to give structure to the community knowledge sharing and discussions and add deeper and more methodical content.

As I wrote in the beginning, Paul is working hard to anticipate the trends of the database industry, and he thinks that DBAs that define themselves by vendor (Oracle DBAs vs. SQL Server DBAs) are a thing of the past. The same way that developers tend to work in multiple languages through their careers, DBA careers are heading the same way. I’m not sure I agree with this view, I have a feeling that there is a fundamental reason that makes it sensible for DBAs to stick mostly with one vendor, but it is certainly something interesting to consider.

It was not surprising to hear that Paul believes in hiring the best DBAs he can find, the surprising thing was that he seems to think that a DBA team is about as strong as the best person in the team. A team with one very experienced and competent DBA will be significantly better than the same team without him. It makes sense to me, but it is well known among developers that a team is as good as its *worst* developer, so you really want your team to be as strong as you can get while pretty much everyone is on the same level. Another thing to think about.

As you can tell, it was a pretty interesting dinner 🙂 I’m still a bit stoked that I got to meet a real live CEO, and one that founded a company that I really admire. This is just too cool.

I didn’t post suggested links in a while.  Lutz Hartmann wrote about an interesting  initiative from Oracle and Netapp. I was excited about it for a full hour, before our storage manager told me that since we are moving away from Netapp in the near future (It is not Netapp’s fault! We love Netapp! It is a political decision), we won’t be testing the new initiative.

C developers with SQL code

Jeff Hunter posted an amusing story about how he optimized a query and what the developer did with his suggestions. The story is so cool not because it is one of these horrible WTF stories that are unlikely to happen in any but the most moronic IT organizations, but because this is a rather harmless oddity that happens all the time.

Most developers started their career by studying CS. This means about three years of writing code in Pascal, C, Java or whatever language CS departments prefer these days. After three years of C development you think of every problem in terms of arrays and loops, after three years of Java you think about objects and patterns. You are not even aware that you are doing this, but everything you look at instantly becomes an array with a loop, or a virtual private class.

At least in my days, databases were a highly optional course in the CS department, so the new developer on the team is probably seeing SQL for the first time in her life, after years of converting problems to arrays and loops. Unfortunately, a database table looks exactly the same as an array. The developer’s automatic response is to write a loop that goes over the array. Imagine her horror when she discovers that SQL doesn’t do loops. With few exceptions, even after learning SQL, the developer  will continue thinking of it in terms of loops and arrays. For the procedural developer, everything is a full table scan and nested joins.

At this point, his code will do funny things like fetch entire tables and iterate over the results to count the number of rows. Maybe not that funny, but close enough. In a good development organization, there will be code reviews and his manager or peers will turn him to the path of efficient database development.

In my experience, it is best that if at this fragile stage, no one will tell the developer about PL/SQL. The longer the developer has to work only with set operations within the database,  the more he will get the ideas of database developement and the better DBA he will become. Developers need to be broken of their loop addiction early and brutally. If they find out about PL/SQL too soon, they will always have the crutch of going back to their loops and will never really get DBs, or at least the process will be much delayed.

DBAs working with development teams really need to take care in educating their developers. This is really preventing performance issues before they are even written, and it is much easier to solve problems at this stage.

How MySQL snuck up on us

In some parts of the geek universe, MySQL is pretty much the only database in existence. If you are a web developer, using python, perl or ruby and want to develop a web site of almost any size, you will almost certainly choose MySQL. In the web development world, it is the obvious choice. It is cheap, it does the job and everyone else is using it too.

In the enterprise application world, MySQL is barely being acknowledged. I spent 30 longs minutes on the phone with Netapp support before they finally realized that I’m talking about MySQL, which is not SQL Server and they don’t have a snapshot based backup solution for us. I’m not disparaging Netapp support. Netapp has the best support I’ve ever encountered, but MySQL is simply not part of their world.

So, we have a world of free databases and cheap storage, and we have the world of Oracle and Netapp. Paul Vallee of Pythian made an excellent case of why Oracle DBAs should start learning MySQL, but I kind of suspect that once the organization got used to free databases, they may be a bit cheap on the DBA salaries too.

MySQL is difficult to avoid these days and it is sneaking up into the most conservative enterprise organizations. Developers like it a lot, for some reason it seems to be easier for them to learn and use than Oracle. Maybe because installing it is a single rpm command and it usually arrives pre-installed with their Linux boxes?

Of course, what developers like ultimately finds its way into production. We are an ITIL organization with very detailed deployment procedures. However, by the time the DBAs are involved, it usually looks like this:
Application Owner: Hey DBAs, we are deploying a brand new application tomorrow. We need you to set up the database.
DBAs: Sure. SQL Server or Oracle?
Application Owner: MySQL. Is that a problem?

Finally we reached a common ground: The application owner understands that his database will get shaky support from us for now, Management knows that we need to purchase MySQL support, and the Oracle DBAs need to learn MySQL. I just hope this won’t decrease my salary.

MySQL on Netapp

One of the databases we need to support is a MySQL database, installed on a windows server. The DBA team collectively has about 3 days of experience with MySQL. The system is technically production, but it is used only by internal users. It is a rather large database (200G) and gets tons of traffic because the application does lots of automatic data collection and reports. In addition it is an ultra sensitive application, which will crash if we stop the database for more than a minute.

But the worse thing is that the developers can’t keep their hands off the database. So every day contains a new surprise – new version, new storage system, the data files move to a new directory, all our backup scripts were deleted, etc etc.

I placed the MySQL data files on a Netapp LUN (connected to windows via iScsi) and I wrote a very nice script that locked tables, flushed them to disk, took netapp snapshot and unlocked the tables. I was rather proud of this script because it was really a good use of Netapp and allowed us to take backup without upsetting our sensitive application.

Well, few weeks ago we noticed that snapshots stopped on the relevant volume. Farther investigation revealed that the backup scripts are gone. Well, I’m used to that and I immediately uploaded a new copy of the scripts.

Only today I noticed that at some point our developers changed MySQL storage from MyISAM to InnoDB. InnoDB tables are not affected by lock and flush commands, which means that we have no valid backups.

I looked into InnoDB backup possibilities a bit and there seem to be two solutions, both of them are not really compatible with Netapp snapshot capabilities, which mean that they will be really really slow and take up too much storage. I’m still checking our options with Netapp support, but we may need to resort to cold backups.

In the meantime my opinion of MySQL just got a bit more negative. It appears to give DBAs (or developers) two choices for storage:
1. MyISAM – which is quick and can be easily backed up, but doesn’t support transactions, ACID or point-in-time recovery, so it can barely be called a real DB.
2. InnoDB – which can be called a real DB, except that it is very difficult to back up.

I love Open Source, but I prefer my databases to be Oracle.