What should I ask at a MySQL DBA interview?

Question

I am a software developer and am helping my team hire a MySQL DBA. The core challenges that we are facing are:

• Slower queries and performance due to Hibernate.

• Database management (backups, tuning, patches, security).

• Scalability due to increase in data from new data sources and accumulation of older data.

• We plan to start data mining and data warehousing in the future. Not sure how but that is the direction.

We usually have programming cases where we ask developers to build something for an interview, but it's a bit hard to do a DBA interview in the same fashion.

Can you give suggestions on how the interview should be conducted?

Answer 1

Not a complete list, but rough list of things I would try to cover. It also depends on whether they will be the first "true" DBA or in a DBA team. Whether the DBA is responsible for the machines, too, or only the database on top of that. etc.

• what RAID configuration should be used
• backup strategies for databases.
• MySQL specific things like differences between MyISAM and InnoDB
• backup strategies and recovery
• Let them do some SQL queries and some query optimization (usage of explain etc. even when using hibernate. Sometimes it's good to bypass hibernate to gain performance)
• did I mention backup strategies
• For scalability a DBA should know about different replication modes (RBR, SBR, mixed mode replication, replication maintenance such as observing replication lag and binlog maintenance)
• InnoDB tuning
• What kind of files are being written by the DB (eg, ibdata and log files) and how they can be arranged (eg, one ibdata file per table, move them around on different partitions, InnoDB compression)
• Discuss monitoring tools. What are you using, do they have experience with that tool or a similar one?
• I'd also look into system tools like iostat/memstat/vmstat/whatever your OS provides. Give them a system with some load and let them find the cause
• And maybe discuss issues in MySQL backup and especially restore ;-)

I'm sure others here can extend this list

Answer 2

I'd suggest looking also at some already established lists regarding DBA interviews:

• Top 10 SQL Server DBA Interview Questions - by our own Brent Ozar

  • favorite questions:
  • "Can you give me references from other DBAs and developers who aren’t at your company?"
  • "A project manager needs a new SQL Server. What do you ask her?"

• Junior DBA Interview Questions - by our own Thomas LaRock

  • favorite question: "If I asked you to learn how to make a query faster, where would you go?"

• Database Screening Questions - by Grant Fritchey

  • favorite question: "You’re the DBA. The phone rings. One of the users is on the line. They say “The database is slow.” Then they hang up. What do you do?"

They are all great SQL Server writers. I know that some of the questions might be SQL Server oriented, but most are not and can be used in a general DBA interview.

Answer 3

I wrote about this a while ago, after I contributed to the interview process at Percona.

I think that to assess someone, you have to try and make them do what they would be doing in regular day-to-day activity. Random questions like "What is a serial data type in MySQL?" or intelligence questions like "why are man holes round?" do not achieve this.

You also want to make sure that you give everyone the same test. If you have an open-ended conversation only interview, the more confident and (slightly manipulative) people will stand out, as they can subtly skirt around your questions and change them into ones they are good at answering. You won't always realize when this is happening but it often contains something like "when I started as a DBA we had 2MB of RAM, and used tapes.. blah blah blah" :P

Having said that, here's my standard list of questions:

• Describe the process by which MySQL replication works?
• What does the D component of ACID mean in practical terms?
• What does innodb_flush_method=O_DIRECT change? (Be careful with this one: the common understanding of this is often wrong.)
• Say I write a query like "INSERT INTO my_table (a,b) VALUES (1,2)". Describe to me in as much detail as you can what happens inside MySQL.

Answer 4

Although I agree that the many seemingly random interview questions (eg, the manhole cover one), aren't really that useful ... (except, maybe for an industrial designer, and just for that one case).

Once you get past the trivia aspect of many of them, they're intended to be stuff that you do not know the answer to, and so you'll have to describe how you'd go about finding the answer. Or not. Eg:

• make up something plausible and/or try to bullshit your way through. (might be useful for sales & marketing? any other field, don't hire.)
• estimate it based on other information
• explain how you'd get the necessary information to solve the problem

...etc.

When I've been in charge of the hiring process in the past, I try to do a completely unscheduled 5 to 15 minute telephone interview (just call 'em up, as them if they could give you a couple minutes of time ... not all could, as some were at the job they were planning on leaving) ... just to assess how much I think they're bullshitting on their resumé.

Eg, when we were hiring for a senior PL/SQL programmer, I'd ask them what the parts of a PL/SQL block were. These days, it comes up first thing in Google ... that wasn't the case back in 2003. Most of the people we interviewed might've used Oracle before, they might've written SQL for Oracle ... but if you can't give me a clue that you know what a PL/SQL block looks like, you're not up for a senior PL/SQL programmer job.

...

And, that being said, my go-to question for any in-person interview is:

Star Wars or Star Trek?

In part, you get to see how they handle a situation where there's no best answer, if they're diplomatic in their answer, or if they think outside of the box (eg, Dr. Who or Firefly are valid answers). Where I currently work (a space physics lab), saying that you've never seen either might be a fail unless you're a foreigner. Saying you don't like either, and explaining why with a good reason would be a pass, though. Geeking out about either one too much might still be a fail (as no one wants to work with that person)