[MySQL]: Top MySQL DBA Interview questions
The first questions I would ask when interviewing a potential DBA would be one that would help me find out about his/her skills and in particular if they are aware of MySQL specific idiosyncrasies. Instead of asking about specific query, I would ask a practical question such as:
“There are 3 tables: product catalog (the primary key is the product SKU), invoice (contain the date information), and invoice line items (contains the invoice number and product SKU). Can you write a query to find which products didn’t sell during a given time period? Please explain.”
What I look for in the candidate’s answer is: a) does the candidate have basic SQL skills, b) is he/she favouring some constructs over others (ie. JOINs over subselects), c) does he/she have an understanding of and experience with MySQL query plans (see b), and
d) does he/she know how to use different JOIN stategies (MySQL JOIN cheat sheet).
The second question would be a tricky one:
“How do you backup a MySQL server?”
Any candidate with some experience with the MySQL world should follow up with one or more questions since there is no right answer without some context informations. At this point, there are 3 main techniques to take backups:mysqldump, hot backup (the official Oracle MySQL Enterprise Backup or Percona’s Xtrabackup), and storage snapshots. For the latter, there are as many alternatives as they are storage systems, starting with LVM and Amazon EBS volumes, followed by several shared storage systems. The candidate should explore whether to take the backups from the master or one of the slaves. I’d favor a candidate who could discuss the pros and cons of each method for a given environment.
The following question has two parts and is a situational question.
“On your first day of your new job, your boss approaches you and says; “the backend server support The Application is having problems, can you look into it?” What would you do next?”
Again, without further information, I expect the candidate to follow up with questions such as “how are you monitoring your MySQL servers?”, and from there, a conversations on what kind of parameters or KPIs is he/she foing to look into during his/her first pass, and possible, what he would do on a second pass. Again, depending on the depth of his previous experience with MySQL: Nagios, Cacti, Zabbix, Graphite, Munin are good examples of the ones i’ve come across several times in recent years. Keep in mind that with the exception of MONyog, the other ones need to use MySQL plugins.
The second part to this situational questions should then focus on server tuning and which steps to follow to make a full health check of a given server. More often than not, the analysis should start with a schema and query review and then look for obvious issues in the server configurations. Engage the candidate in a discussions of a real production case or ask him how he has dealt with a similar situation in the past. This will allow the interviewer to assess the candidates skill level and experience. Different people could have different approaches for the same problem. The candidate should be able to elaborate why his/her approach would work.
Replication is a fundamental component in almost all MySQL installations. No interview would be complete without inquiring about the candidates experience on this item. I would start with an open ended question. This would allow the interviewer to follow the candidate’s line of reasoning in trying to solve the problem. There’s nothing wrong with asking “Replication is broken in one of the production slaves, how would you get the slave online again in the shortest time?” An experienced candidate would ask questions such as “what is the role of the slave?” It is not the same if you are using the slave to scale out reads, take backups at night, high availability stand-by, running batch reports, etc. The sense of urgency and tolerance for data inconsistency (maybe the application can handle some temporary data mismatch on a slave) should be taken into account, and presenting these facts to the candidate while discussing his strategy can be a useful way to get some insight into his experience in regards of your own infrastructure.
The next question took me by surprise during an interview, but I’m glad I had an answer for it: “What is your MySQL toolchain?” If you followed the order of this article with your questions, it is likely that the candidate might’ve mentioned a number of tools and scripts to use for diagnosis. Every MySQL DBA should be familiar with the Percona Toolkit, but there are a number of others and everyone has his favorites. The list might include others like: Common Schema andOpenark Kit; mysqltuner and mysqltuner v2; innotop, mytop or mtop; Anemometer or Query Digest UI, mydumper,MySQL Sandbox, and many other. The length of the candidate’s list should be taken as an indication of his experience, as long as he can elaborate on their use in production, staging and/or development servers.
Last but not least, the MySQL eco-system is constantly changing, so my last question is always: “How do you keep up with the MySQL eco-system?” Improvements like Index Condition Pushdown and Global Transaction ID for example have been implemented in both MySQL and MariaDB but have differences between the two. MySQL Utilities were introduced not long ago by Oracle and lets not forget the Percona Server with all the performance enhancements (many of them ported into MariaDB and many other products that make up the eco system: TokuDB, Galera Cluster, sharding solutions and many more. Planet MySQL is the main source of news from the MySQL community at large. Others include the MariaDB Knowledge Base, Percona’s Performance blog and the OurSQL community podcast (the last 2 are aggregated in Planet MySQL).
At this point of the interview process, I would expect the candidate to ask whether the company will cover the expenses to attend either the Percona Live event in Santa Clara, CA or London, and/or MySQL Connect in San Francisco. The 3 events are great opportunities to meet with key members of the MySQL community and learn new skills from colleagues and developers for all the tools mentioned before. This could be the single perk that would guarantee that your MySQL DBA stays up to date with the technology and possibly win his or her loyalty for years to come.