Tag Archives: startups

The Art of Resistance

Sometimes, you have to be the bad guy. Be resistant to change. Here’s a story about how stubbornness pays off. As we’ve written about before A 4 letter word divides Dev & Ops.

I had one experience working as the primary MySQL DBA for an internet startup. Turns out they had Oracle for some applications too. And another DBA just to handle the Oracle stuff.

So it came time for Oracle guy to go on vacation. Suddenly these Oracle systems landed on my shoulders. We reviewed everything in advance, then he bid his goodbyes.

Almost as soon as he was out the door I started getting requests to change things.
“Oh we have to add this field”, or “oh we’d like to make this table change”.

I resisted enough to hold off development for a week.

“We’re now getting more heat from the CTO. Apparently certain pages on the site don’t save some very important content properly. It’s costing the business a lot of money. We need to make this change.”

My response –

[quote]No I can’t sanction this. If you want to do it, understand that it could well break Oracle’s replication.[/quote]

Oracle’s multi-master replication notoriously requires a lot of baby sitting. We held off for a few more days, and the Oracle DBA returned from his much needed vacation.

When discussing it afterward he said…

[quote]Am very glad you didn’t change those fields in the database. It would indeed have broken replication and caused problems with the backups![/quote]

Moral of the story…

  • apply the brakes around tight turns
  • don’t be afraid to say, we’re not going to do this before testing
  • you may have to say – we’re not going to do this at all…

The four-letter-word dividing Dev and Ops

devops divide

What’s that word?  RISK

Operations teams are tasked with stability and uptime. That means working against change, limiting or slowing it down where possible.

Developers are tasked with features and delivering business solutions. For that an ORM layer seems appealing for example. It speeds up & simplifies coding. At the same time it eliminates database drudgery.

For ops who are tasked with uptime, an ORM is a force against scalability. I’ve outlined five things toxic to scalability. They work against performance.

The question remains – do devops folks solve the problem?

Consider the banking crisis

Bankers are tasked with making money for their shareholders. To do this they innovate with financial products. Though you may argue they are unscrupulous at times, capitalism and shareholder value drive them to find profit.

Meanwhile the government’s job is to provide a level playing field.  They enact rules, regulate and provide oversight and auditing. As with operations, this is a conservative role, that avoids risk, and seeks stability, growth and avoidance of recessions and depressions.

These tradeoffs exist in many disciplines. The trick is how we find the balance.

There is an equally interesting question of decoupling in internet architectures. I’ll write a future piece on similar parallels I see in the economy at large.

Business lessons from brokering a half million dollar deal

I’ve had my Manhattan loft in the market for some six months now. After dropping the asking price a couple of times, buyers are finally circling, and I’m close to finalizing a deal.  In that time however, I’ve learned some big lessons.  Real Estate, is the epitome of cutthroat capitalism and as such has some very potent lessons to teach about business.

Understand Interests – Two Sides to Every Argument

Brokers have an interest – to make their fee;  buyers have an interest – to lower the selling price, sellers have an interest to get the highest selling price.  On the surface this may seem self evident.  But dig a little deeper and you’ll learn more. Brokers are somewhat aligned with buyer and seller, but like a cab driver preferring lots of short trips, their incentive is to do more deals. Fewer deals with higher margins would actually cost them. A 1%-2% higher selling price may be significant to your bottom line but meaningless to theirs.

[quote]Business Lesson 1 – Pay close attention to each party’s interests and you’ll understand the negotiating dynamic more clearly.[/quote]

In brokering, like war, no one plays fair

Each industry has it’s own ugly side. No one calls back. No one emails back. There are no pleasantries. In some businesses where credibility and reputation are built over time, people do care–little bit. But in brokering a large deal they don’t.  Silence means “Hey, thanks for going out of your way to give us an hour of your time even though we were late.  We don’t care.  We no longer need you & we’re not gonna respond.”  Got it, heard you loud and clear!

[quote]Business Lesson 2 – Be prepared for selfishness and deception. In some cases watch out for cheaters and even fraud.  Don’t let this color all your interactions, but be aware that some parties may not have the same scruples as you do and some may even break the law. [/quote]

Markets are bigger than you are

You may think your house or apartment has some intrinsic value, based on renovations or the time you’ve held it, or some other more personal metric. However the market is ultimately what sets the price of the asset. Market trends can swing wildly and irrationally so be prepared to “get real”, as they say in the business.

[quote]Business Lesson 3 – Market forces set the price of a good or service. The door swings both ways so when there is a greater supply be prepared for the price to go down, and when location, asset or skills are in great demand, be prepared for the price to go up.[/quote]

Buyers move on emotion; facts get you only so far

Salesmen tell stories. It’s their job to engage, stir passions, get people to move. Sometimes these yarns are taller than you or I might like, but if people knew exactly what they wanted, brokers wouldn’t exist. This occurs across the spectrum in sales.

[quote]Business Lesson 4 – Pay less attention to the facts and arguments a salesmen presents to you. Only the underlying interest of each party will tell a clear story of motivations. Weigh that along with the market appetite and you should have a good idea of the right price.[/quote]

Protect your interests and time

Over the months I’ve been on the market, I’ve had countless brokers try to sell me their services. An exclusive seller agreement is valuable to a broker as it allows them to control the process and control the fee distribution. One broker made an appointment to come by with their buyer over a holiday weekend. Following up at the appointed time, they explained that they were running late though frankly the broker sounded hung over. Upon arrival they brought no buyer. Seeing my irritation, they quickly fabricated a story about a phantom buyer and their specific requirements. After wasting a few hours of my time and breaking up my holiday weekend, I never heard from them again. Not fun.

[quote]Business Lesson 5 – Qualify your prospect before you grant them your time. There are many parties who can and will waste your time if you’re not careful. Be diligent about weeding them out before clearing your schedule for them.[/quote]

Business plans are bunk

I meet a lot of people in NYC. Friends, colleagues, small business owners, people with a great idea, artists, musicians, people with a dream, people who hear the startup story, namely the Google, Zynga, Apple, Facebook, or Amazon story.

Sometimes I’ll hear:

“I’m working on a business plan.”

“Why?” I ask.

“I want to make my business grow and get investment money.”

Then I think about this a little, and consider if a business plan would have ever helped me.  Would it help a deli owner run a deli? Or a laundry run a laundry?

[quote]The essence of running a large business is the day-to-day of running a small business.[/quote]

Let’s consider a less savory business – drug dealers. Their business may not be one we respect, but let’s consider the economics behind the scenes.

  • They sell people a product they want
  • They sell at a price people are willing to pay
  • If supply is low they get more to meet demand

That’s a business plan in a nutshell. If you’re not selling any product, that means people don’t want it. It may not be as valuable to the world as it is to you. If it’s valuable, people will buy. That’s the measure of good ideas. When people aren’t buying, they’re voting with their wallets.

Entrepreneur and blogger, Andrew Chen says:

Business models are a commodity. The answers are all obvious.

Sometimes all this talk of business plans sounds like therapy for people who don’t have a successful business.

The hard truth is if people aren’t buying they don’t want the product. Change it or tweak it until they do. A business plan won’t help with that.

Why the Android ecosystem is broken

Six months ago I got this crazy idea. Why not leave the mothership? Give up on iPhone and try Android. This is what being tech-agnostic is about, I thought––not being wedded to a single platform. Besides, the iPhone’s digital keypad just wasn’t working for me.

I got a monthly Boost Mobile plan, which uses Sprint. Service was stellar and I mean really good. I could call anywhere and always had a signal, even inside all these pre-war buildings you find in downtown Manhattan. How is this possible I thought? Service is one thing, but thats where the fun ends. A few months into Android hyperspace and I find myself grappling with a system that just doesn’t seem to understand what users want.

Shock and Awe

On Android – first Samsung Transform Ultra then Sidekick 4G I found the app store was like wild, wild west. Buggy apps sat along well tested ones, and only a very discerning eye and mobile guru might know the difference. Syncing was absolutely horrible. The whole platform assumes you want to sync up with Google accounts. I went with Missing Sync from Mark/Space. My addressbook started getting corrupted, duplicate records appeared, syncs would fail halfway through.

What’s more there were tons of started services I didn’t even use like Smart Navigation, Group Texting, and so on. These services seemed to run in the background, hog & bleed memory, and slow down my phone til it started crashing. I actually had to download an app called Easy Task Killer. Apparently a very popular app on the Android phones, I wonder why?

Later on I found out that T-mobile was no longer supporting the Sidekick. No wonder it was so buggy. I can’t believe they’d ship something like this.

My full list of beefs:

  • corrupted data
  • slow to non-functional syncing
  • dangerous apps
  • sharing of private data
  • ineffective calendaring
  • no support for addressbook groups
  • apps not remembering context & position
  • no good email app
  • weaker less feature rich apps
  • kludgy interface

I’ve long since quelled my desire for a physical keyboard. I was struggling with every other thing I would do with the device. Sometimes I’d just give up.

I really wanted to get along with my Android phone but my experience with it only gave me an insight into three crucial areas where it falls short.

  1. An Iron Fist
  2. People complain about Apple’s iron fist in app store approval. You can’t have it both ways. Android completely lacks discipline and users suffer hugely because of it. That weakens the platform.

  3. A manageable set of devices
  4. Developers building for Android must test on a huge spectrum of hardware. Smaller shops are likely to choose a few of the biggest ones only. Phones with a smaller user base likely have a lot of bugs just in the Android version they run. All this bodes badly as users just see buggy software, they don’t know why or how. This perception further weakens the platform.

  5. Affordable development

Building apps costs businesses money. Businesses must balance the costs of building features, test, debug, troubleshoot & release. That’s cheaper on the iphone because you have one device that is much more mature. This has a pile on affect as it strengthens the platform, users spend more on their devices, more users pile onto the iphone platform, so more money can be made building an iphone apps.  On Android higher costs, lower margins further weakens the platform.

A new love for Apple

This whole experience has brought me back to the iPhone 4S, and I have a whole new love and appreciation for the platform and the device.

  • calendar reminders make me more productive
  • data is always right, and where I need it
  • I don’t fumble with menus – I’m faster & less frustrated
  • cross-platform apps are more feature rich on the iphone
  • complex features are hidden in plain view – superb interface
  • pulse, hootsuite, yelp, notes & calendar are all integrated & productive

I also learned that sometimes less is more… much more!

Best of Guide – Highlights of Our Popular Content

We cherry pick the top 5 most popular posts of various topics we’ve covered in recent months.

Beware the client

Recently I was searching Google for “startup consultant” to see what I might find. I came across this article by John Greathouse titled Beware the Consultant. No self-respecting, hard-working consultant would sit back and take hits without defense, so in true poetry slam style I thought I’d write a quick retort.

Beware the Client…

  • The one that asks for the world.
  • The one that wants everything done yesterday.
  • The one that drags their feet paying bills or loses your invoices.
  • The one that moves the goal posts halfway through a project.
  • The one whose left hand doesn’t know what the right hand is doing.

 

 

Truth is often stranger than fiction

Of course in the real world things are much more nuanced than this black and white picture. Certainly there are probably some consultants that fit John’s description, but there are others that are loved by their clients.

  • There are tons of “little guys”.
  • There are freelancer’s that do a thankless job.
  • Consultants provide much needed plugin solutions for firms.
  • They work as long or as short as necessary.
  • They’re available, when you can’t find anyone!
  • They’re often self-taught – maintaining skills on their own dime.
  • BYO laptop, they’re plug-n-play.
  • They’re communicators – or they wouldn’t succeed in consulting.
  • They are small business owners – so they relate to your real business pain.

 

As far as performance based compensation goes, I would say two things:

  1. It’s always performance based because in my experience the startups I work with are usually bigger than I am. So if they decide not to pay being unsatisfied with the work, there’s little I can do about it.
  2. I do believe in being open to different types of arrangements, sometimes day or week based on time and materials, and other times project based. However the devil is very often in the details with regards to “quantified, clearly understood results”.

 

Although Mr Greathouse may describe *some* real-world or big-corp consultants, they are by no means representative of the lot of us.

“My startup is too cool for your business school”

An article I read on Tech Crunch recently got me thinking about startup culture. In Are You Building A Company, Or Just Your Credentials Geoff Lewis, expressed his distaste for a friend’s plan to get on Y Combinator’s ‘no idea’ startup incubation program. In this experimental approach, groups or individuals with a desire to be part of a startup but who have no product or business idea to begin with, can apply.

The thinking I believe, is that since brilliant ideas aren’t the only factor for startup success (many other factors like organisation skills, business savvy and tenacity matter too) YC will dig into their vault of ideas and match one that’s most suitable to these idea-starved groups.

Firstly, reading this I could sympathize with the discontent.  Venture capitalists exist for people who have an idea and want to realise it. There are already programs for people who don’t have an idea but want to achieve some success. They are called “careers”.

However, if you look at it from an incubator’s perspective, it’s a pretty clever and measured approach. YC knows what sort of group dynamic in startups have a higher chance of success and it is casting its net wide to find them.

But Lewis took issue with the fact that his MBA-qualified, credential-seeking buddy was signing up with YC presumably just to add to his blue ribbon collection.

With such articles what’s usually more interesting is the comments they elicit. Many who reacted responded with the same feelings of contempt; calling paper-chasers ‘hucksters’ and scoffing at their lack of passion and sincerity.

The tone among some carried this notion that startups were a special breed of entrepreneurs burdened with some sort of higher calling to liberate the world; money and honour being an afterthought.

Not being directly part of the startup circle, so-to-speak, I found the reaction amusing and frankly, rather foolish. Are people involved in startups turning into a sort of in-group? Do they really think of themselves as some kind of mutant-strain of businesses that are different from ‘regular’ enterprises?

If we look at the richest Internet companies today, once startups themselves, they are no less motivated by avarice and the bottomline, so why be so judgmental of Mr MBA treating the YC program as a way to gain credentials? I bet many talented individuals are going to have a go at it for the same reason if not a variety of reasons. As the program draws out, I suspect the number of participants will peter out by attrition anyway.

And who’s to say not coming up with a disruptive idea makes you less enterprising? Think of startups as team sports. There are some teams that innovate with the most creative gameplay that catch their opponents off-guard. There are teams that win by consistency and endurance, doing the same thing well over many years to pull ahead of the pack.

To cast aspersions on the motivations of others and to let silly prejudices limit participation seems discordant with the spirit of the Internet itself, where challenging convention is the order of the day and everyone is entitled to a shot at success.

Top MySQL DBA interview questions (Part 2)

Also find Sean Hull’s ramblings on twitter @hullsean.

Continuing from our Top MySQL DBA interview questions (Part 1) here are five more questions that test a MySQL DBA’s knowledge, with two that will help suss out some personality traits.

  1. Disk I/O
  2. Disk performance should be an ever present concern to a DBA.  So although they don’t need to be a storage specialist, they should have a working knowledge.  Ask them about RAID versions, mirroring versus striping, and so forth.  Mirroring combines two disks as a unit.  Every write is duplicated on both disks.  If you lose one disk, you have an immediate copy.  Like a tandem truck that has spare tires running in parallel.  Lose one, and you don’t have to pull over immediately to replace it.  Striping spreads I/O over multiple disks so you on the one hand increase throughput linearly as you add disks.  That’s because you have more disks working for you.  At the same time you increase risk with each new disk you add, because the failure rate is then the sum total of all those disks.

    For relational databases the best RAID level is 10, which is striping over mirrored sets.  You use more disks, but disks are cheap compared to the hassle of any outage.

    If you’re deploying on Amazon, your candidate should be familiar with the Elastic Block Storage offering also known as EBS.  This is virtualized storage, so it introduces a whole world of operational flexibility.  No longer do you have to jump through hoops to attach, add or reconfigure storage on your servers.  It can all be done through command line API calls.  That said EBS suffers from variability problems as with any other shared resource.  Although Amazon guarantees your average throughput, the I/O you get at a given time can swing wildly from low to high.  Consider Linux software RAID across multiple EBS volumes to mitigate against this.

  3. How would you setup master/slave & master/master replication?
  4. A basic replication setup involves creating a full dump of the primary database, while it’s tables are locked.  The DBA should capture the master status, logfile & position at that time.  She should then copy the dump file to the secondary machine & import the full dump.  Finally the CHANGE MASTER TO statement should be run to point this database instance to it’s master.  Lastly START SLAVE should be issued.  If all goes well SHOW SLAVE STATUS should show YES for both of these status variables:

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

    Master-master replication is similar, except one additional step.  After the above steps have run, you know that your application is not pointing at the slave database.  If you’re not sure, verify that fact first.  Now determine the logfile name & position on the slave with SHOW MASTER STATUS.  Return to the primary box, and run the CHANGE MASTER TO command to make it slave from the secondary box.  You’ve essentially asked MySQL to create a circular loop of replication.

    How does MySQL avoid getting into an infinite loop in this scenario?  The server_id variable must be set, and be unique for all MySQL instances in your replication topology.

    For extra credit, ask the candidate about replication integrity checking.  As important as this piece is to a solid reliable replication setup, many folks in the MySQL world are not aware of the necessity.  Though replication can be setup, and running properly, that does not mean it will keep your data clean and perfect.  Due to the nature of statement based replication, and non-deterministic functions and/or non-transactional tables, statements can make their way into the binary logs, without completing.  What this means is they may then complete on the slave, resulting in a different row set on the same table in master & slave instance.

    Percona’s pt-table-checksum is the preventative tool to use.  It can build checksums of all your tables, and then propagate those checksums through replication to the slave.  An additional check can then be run on the slave side to confirm consistency, or show which rows & data are different.

  5. How are Users & Grants different in MySQL than other DBs?
  6. Creating a grant in MySQL can effectively create the user as well.  MySQL users are implemented in a very rudimentary fashion.  The biggest misunderstanding in this area surrounds the idea of a user.  In most databases a username is unique by itself.  In MySQL it is the *combination* of user & hostname that must be unique.  So for example if I create user sean@localhost, sean@server2 and sean@server3, they are actually three distinct users, which can have distinct passwords, and privileges.  It can be very confusing that sean logging in from the local command line has different privileges or password than sean logging in from server2 and server3.  So that’s an important point.

  7. How might you hack a MySQL server?
  8. This is a good opportunity for the candidate to show some creativity with respect to operations and Linux servers.  There are all sorts of ways into a database server:

    a.bad, weak or unset passwords
    b.files with incorrect permissions – modifying or deleting filesystem files can take a database down or corrupt data
    c.intercepting packets – could reveal unencrypted data inside the database
    d.unpatched software – bugs often reveal vulnerabilities that allow unauthorized entry
    e.moving, disabling or interrupting the backup scripts – a possible timebomb until you need to restore
    f.DNS spoofing, could allow login as a different user
    g.generous permissions – may allow an unprivileged user access to protected data

    There are endless possibilities here.  Listening for creative thinking here, reveals how much that person will think thoroughly and effectively about protecting your systems from those same threats.

  9. Brain teasers, riddles and coding problems
  10. Google for a long time was a fan of these types of tests at interviews, but I’m not at all.  For one thing you filter for good test takers, and for another the candidate has no resources – either books or the internet at their disposal.

    Why not instead ask them to tell a story.  Storytelling conveys a lot of things.  It conveys a bit of teaching ability, which extends far beyond internalizing some multiple choice questions.  It tells you more about their personality, which as I’ve said is very important.  It shows how they solve problems, as they’ll take you through their process.  And gives them an opportunity to tell you a real world triumph they presided over.

Personality Questions

In my experience, some of the most important traits of a new hire center around personality traits, and how they might mix with your existing team.  Being punctual for an interview, for instance sets a precedent for many things.  But that door swings both ways, so if you want to hire these types of folks, don’t keep them waiting either!

Pay attention to whether or not the candidate takes some lead in the conversation at all.  This can indicate the person is a self starter.  Obviously a great candidate will also listen carefully and patiently to what you have to say, but may then take the ball and run with it somewhat.

Listen for signals that the person is active in the field, posting on forums, and attending conferences, meetups and forums on technology topics.  You might also ask them if they blog, and what topics interest them.

Top MySQL DBA interview questions (Part 1)

MySQL DBA interview questions

Also find Sean Hull’s ramblings on twitter @hullsean.

MySQL DBAs are in greater demand now than they’ve ever been. While some firms are losing the fight for talent, promising startups with a progressive bent are getting first dibs with the best applicants. Whatever the case, interviewing for a MySQL DBA is a skill in itself so I thought I’d share a guide of top MySQL DBA interview questions to help with your screening process.
It’s long and detailed with some background to give context so I will be publishing this in two parts.

The history of the DBA as a career

In the Oracle world of enterprise applications, the DBA has long been a strong career path. Companies building their sales staff required Peoplesoft or SAP, and those deploying the financial applications or e-business suite needed operations teams to manage those systems. At the heart of that operations team were database administrators or DBAs, a catchall title that included the responsibility of guarding your businesses crown jewels. Security of those data assets, backups, management and performance were all entrusted to the DBA.

In the world of web applications, things have evolved a bit differently. Many a startup are driven only by developers. In those smaller shops, Operations tasks are designated to one developer who takes on the additional responsibility of managing systems. In that scenario, Operations or DBA duties becomes a sort of secondary role to the primary one of building the application. Even in cases where the startup creates a specific operations role with one person managing systems administration, chances are they don’t also have DBA experience. Instead, these startups are more likely to manage the database as a typical Linux application.

When I grow up I (don’t) want to be a MySQL DBA

Where do they come from, and why don’t a lot of computer science folks gravitate towards operations, and DBA? This may be in part due to and the romance of certain job roles which we discussed in a past article, The Mythical MySQL DBA. This pattern appeared a lot in the Oracle world as well. Many folks who were career DBAs actually moved to that role from the business side. In fact you’d find that many didn’t have a computer science or engineering background in the first place. In my experience I saw many Linux and Unix administrators with a stronger foundation who would fit into the DBA role but were simply not interested in it. The same can be said of the MySQL side of the house. Computer science grads don’t get out of school aiming for a career in ops or as a DBA because it has never been regarded as the pinnacle. It’s typically the PROGRAMMERS who become the rockstars in a cool startup.

But as the Internet grows into a richer and more complex medium, things are changing. People talk about scalability, high availability, zero downtime and performance tuning. When brief outages cost millions in losses expectations are very high and that requires skilled, experienced DBAs.

We’ve made a list of comprised of skill questions, general questions and ‘good-to-know’ questions. Have fun grilling your candidate with them, although bear in mind that with interviews it’s not about knowing it all, rather how the person demonstrates critical thinking skills.

Skills Questions

  1. Why are SQL queries so fundamental to database performance?
  2. This is the one question which a DBA should have an answer to. If they can’t answer this question, they’re unlikely to be a good candidate.

    After a MySQL server is setup and running, with many of the switches and dials set to use memory, and play well with other services on the Linux server, queries remain an everyday challenge. Why is this?

    SQL queries are like little programs in and of themselves. They ask the database server to collect selections of records, cross tabulate them with other collections of records, then sort them, and slice and dice them. All of this requires MySQL to build temporary tables, perform resource intensive sorts and then organize the output in nice bite size chunks.

    Unfortunately there are many ways to get the syntax and the results right, yet not do so efficiently. This might sound like a moot point, but with modern websites you may have 5000 concurrent users on your site, each hitting pages that have multiple queries inside them.

    What makes this an ongoing challenge is that websites are typically a moving target, with business requirements pushing new code changes all the time. New code means new queries, which pose ongoing risks to application stability.

  3. Indexes – too many, too few; what’s the difference?
  4. Indexes are very important to the smooth functioning of a relational database. Imagine your telephone book of yore. I can look up all the people with last name of “Hull” in Manhattan because I have the proper index. But most yellow pages don’t include an index for *first* names even though they might occaisionally come in handy, for example with the names “Star” or “Persephone”.

    You can imagine that, if you had a phone book which you maintain and update, everytime you add or remove a name you also have to update the index. That’s right, and the same goes for your relational database.

    So therein lies the trade off, and it’s an important one. When you are *modifying* your data, adding, updating or removing records, you must do work to keep the index up to date. More indexes mean more work. However when you’re looking up data or *querying* in SQL speak, more indexes mean more ways of looking up data fast. One more trade off is that indexes take up more pages in your phonebook, and so too they take up more space on disk.

  5. Backup & Recovery – explain various types & scenarios for restore
  6. Backups come in a few different flavors that the DBA should be familiar with.

    Cold backups involve shutdown down the database server (mysqld) and then backing up all the data files by making a copy of them to another directory. To be really thorough, the entire datadir including binlogs, log files, /etc/my.cnf config file should also be backed up. The cold backup is a database in itself, and can be copied to an alternate server and mounted as-is.

    Logical backups involve using the mysqldump tool. This locks tables while it runs to maintain consistency of changing data, and can cause downtime. The resulting dump file contains CREATE DATABASE, CREATE TABLE & CREATE INDEX statements to rebuild the database. Note the file itself is not a database, but rather a set of instructions which can tell a MySQL server *HOW* to reconstruct the database. Important distinction here.

    Hot backups are a great addition to the mix as they allow the physical database data files to be backed up *WHILE* the server is up and running. In MySQL this can be achieved with the xtrabackup tool, available from Percona. Despite the name, it works very well with MyISAM and InnoDB tables too, so don’t worry if you’re not using xtradb tables.

    There are a few different restore scenarios, and the candidate should be able to describe how these various backups can be restored, and what the steps to do so would be. In addition they should understand what point-in-time recovery is, and how to perform that as well. After restoring one of the above three backup types, the DBA would use the mysqlbinlog utility to apply any subsequent transactions from the binary logs. So if the backup was made at 2am last night, and you restore that backup, the mysqlbinlog tool would be used to dig up transactions since 2am, and apply them to that restored database.

  7. Troubleshooting Performance
  8. Since this is an ongoing challenge with relational databases, a good grasp of it is crucial. One way to challenge the candidate would be to describe a recent performance problem you experienced with your infrastructure, and ask them how they would go about resolving it.

    If they struggle with the particulars of what you ran into, ask them to describe a big performance challenge they solved, what the cause was, and how they performed analysis.

    Typically, first steps involve mitigating the immediate problem by finding out what changed in the environment either operationally or code changes. If there is a bug that was hit, or other strange performance anomaly, the first stop is usually looking at log files. MySQL server error logs, and the slow query log are key files. From there, analyzing those files during the timeframe where problems occurred should yield some clues.

    You might also hope to hear some comment about metrics collection in this discussion. Tools such as cacti, munin, opennms, or ganglia are invaluable tools for drilling down on a past event or outage, and sifting through server stats to find trouble.

  9. Joins – describe a few kinds and how the server performs them
  10. A basic understanding of INNER JOIN and OUTER JOIN would be a great start. A simple example might be employees and departments. If you have four employees and two departments, an INNER JOIN of these tables together will give you the departments employees belong to. Add another employee without assigning her to a department, and the inner join won’t display her. Further adding a new department which doesn’t yet contain employees won’t display either. However performing an OUTER JOIN will give you those matches with null in the department field, and null in the employee field respectively.

    Thought of with another example, take a credit card company. One tables contains cardholders identity, their number, address, and other personal information. A second table contains their account activity. When they first join, they don’t have any monthly statements, so an INNER JOIN of cardholders with statements will yield no rows. However an OUTER JOIN on those two tables will yield a record, with a null for the statements columns.

Feeling like a MySQL expert yet? In Part 2 of Top MySQL DBA Interview Questions we’ll walkthrough four more questions plus a bonus.