14 May 2012 The 3 DBA Mistakes You Don’t Know You Are Making
Then again, how could you know this? I mean, if you are not doing it, why would you ever think that your inaction could be the very thing that is causing you heartache every time your phone rings in the middle of the night?
I see DBAs make these three mistakes repeatedly. But they are not a mistake as you might think of a mistake: a misstep along a specified process. No, these mistakes are brought about because the person simply didn’t know any better.
But they should.
I’m hoping by listing them here, it will help.
1. You Don’t Have A Recovery Plan
The number one reason for a DBA to exist is this: recovery. It should be your primary focus each and every day. You need to be able to recover from any event, no matter how big or small. Lose an entire data center? You had better be able to recover that data. Lose a handful of LUNs? You had better be able to recover that data. Someone deletes a table? You had better be able to recover that data.
So why then do I see many DBAs spend time focusing on a good backup strategy but never spend an equal amount of time on a recovery strategy? Of course we know that every good recovery strategy must start with a good backup strategy, but so many DBAs (especially novice ones) never bother to test their restore process. Are your backups written to disk, and then saved off to tape? OK, that’s fine, now…quick…how long will it take to recover that tape? If your end user calls and says they need to recover a database from last week, how long before that request can be completed. Here is a better question: how long before your end user become very annoyed that they have to wait any longer?
There is only one way to be certain that the backups you have can even be used for recovery: you need to actually recover them without error to be 100% certain. That’s why I wrote this article for Simple-Talk two years ago on how to use statistical sampling in order to be 95% confident that all your backups are valid.
Taking backups is not even half of what you need to do as a DBA. You need to recover, from a variety of failures, and you need to be able to do it as quickly as possible. Having a plan is good, but practicing that plan is even better.
2. You Pretend That Virtualization Doesn’t Matter
I know that VMWare will tell you that running in a virtual environment can be up to 99% of the performance you have in a physical environment. I also know that when people see that 99% number they decide that they can shove as many guests onto a VMWare host as space will allow for and they still believe they will have 99% performance for each and every instance. This is especially true if those instances are believed to be “lightly used”.
The beauty of running your servers in a virtual environment is that you get to share resources and easily migrate guests between hosts. The ugly truth about running your servers in a virtual environment is that you get to share resources and easily migrate guests between hosts.
As a DBA you are now managing a piece of software that runs inside of a guest O/S that is running on a host, that has a hypervisor, and that likely shares its disks with other hosts as well. What that means is that when someone runs a query, that query has to travel down that stack and back again in order to return a result. Now, if someone comes to you and says that they are having a performance issue what would you do first? Would you look at the query? Or the O/S metrics for that guest? Or something else?
Or would you prefer to spend five minutes examining the virtual layer to see if any bottlenecks exist. For example, if the host is seeing a spike in disk latency, then it is likely the guests are seeing disk latency as well. So, how much time would you want to spend trying to make a query faster when the issue has to do with an overloaded disk subsystem? I know what my answer is: none. What if the bottleneck is host CPU saturation? How long will you want to spend trying to tune a query when you already know that the issue is at the host layer?
Pretending that virtualization doesn’t matter for your database servers is a mistake I see being made by both DBAs and server admins. Those counters hold the key to being efficient at performance tuning in a virtual world. But they are only useful if someone knows to be looking at them.
3. You Don’t Configure Memory Settings Properly
This one drives me crazy.
I still see database servers with the default memory settings. When a customer comes to me and complains about having memory issues one of my first questions is: what are your default settings? At this point I am shocked when someone doesn’t say “we left those alone”. Configuring your default memory setting is one of the easiest thing any DBA can do and should be part of a standard checklist when rolling out a new instance. Yet it still is not happening.
Things get even trickier with memory in a virtual world, as you are competing with other guests for host memory. You simply must know what your memory requirements are for your database server and then work on securing that memory. The easiest way to do this is to configure those memory settings. Even I can’t remember everything, so that’s why I just reference this post when I need general guidelines regarding the configuration of the min and max memory settings.
There you go, three things that you are likely not doing today and are also equally likely to be causing you and your end users lots of pain.