Many
articles on database administration take the perspective of trying to help you
do your job better. We thought we might take a different tack and poke a
little fun at some of more egregious mistakes we’ve seen over the years at IT
shops.
Taken
together we think these seven areas hit on some of the biggest things that can
go wrong with database systems. Ignore these cautions at your own peril!
1. Don’t Take Backups
Hardware
failure is an ever present theme in the data center, so not taking good regular
backups is one way to make sure this will be a calamity! Hard drives
fail, power fails, plugs get pulled, you name it.
But that’s
not the only risk on the horizon. Errant DBAs and developers delete
data. Yes, they do, and they delete columns, delete tables, even delete
whole databases sometimes with the wrong command or misplaced typo! Unfortunately,
this type of risk can be higher and more common than you might guess. If
you haven’t seen it before you might assume it won’t happen to you. Moreover,
if you don’t take regular backups from which you can recover a table, object,
or whole database, you’re sure to be ruined if that does occur.
2. Don’t Watch the Error Logs
Error
logs can be your early warning system. Some messages indicate trouble to
come, others indicate the first time something is a problem, which will surely
grow in severity if ignored. So definitely if you want your database
systems to crash, ignore the log files.
When it
comes to your high availability environment, you’ll have error logs too.
Want to make sure your secondary database is out of sync with your primary
database, just ignore those logfiles and give it some time. Eventually
some errant SQL will come down the line, or a careless DBA will shutdown the
primary database without issuing "stop slave" first on the slave
database. And you’re cooked. Happens all the time. So if you don’t
want to know about these things, be sure to ignore those log files!
3. Don’t Use Memory Wisely
We know
your systems have huge main memory these days, 8G, 16G, even 32G, sure. So
you don’t need to worry about memory. Just allocate it however you like,
or better yet leave those defaults in place, and assume the MySQL team knows
how much memory your particular server has, or how you’d like to use it. It’ll
all come out in the wash as they say. Believe it or not it’s easy to
misallocate memory, even on these large memory servers, so if you want to make
sure your server crashes, don’t bother to use memory wisely!
4. Don’t Tune Queries
Queries
are always my favorites. If you want to make sure your server crashes,
follow the maxim, I have a big server, lots of memory and fast disk, so I don’t
have to worry. Developers writing code that unnecessarily does full-table
scans? Trashing your query cache, and overloading your Innodb buffer
cache with useless blocks? Hitting disk as much as you can instead of
main memory? No problem because everything is so fast!! That’s
right, even on the largest servers one sure way to watch your server fall to
its knees is to ignore those queries, and write them however you like!
5. Don’t Worry About Indexes
Indexes
are my favorite. Missing indexes cause havoc all the time. Want to
drive your server at 60mph on the highway, but in first gear? Sure, don’t
worry too much about indexing those tables properly. Hey, it worked in
development. Problem is in production you have real datasets and real
users, hitting your production systems with millions and millions of queries
per day. So a few missing indexes in the right places and those queries
will become dogs! So yes, this is yet another way to crash your database
server.
6. Don’t Use Fast or Reliable Disk
The very
best way to make sure disk I/O is your bottleneck is to use a single disk, or a
single mirrored disk. That means that your OS will always be fighting
with your database and vice versa. That’ll also mean your sessions, all
trying to work in parallel will be queued up for a resource that can only serve
one customer at a time. The second best way is to use RAID 5. That’s
right, the common foe of database performance is under the hood of many a
web-backed database. RAID-5 can handle only one disk failure. Unfortunately,
bad batches of disk often come in pairs… But even if only one drive
fails, recovery during that period is abysmal, much much worse than a single
disk. What’s more even when operating properly, RAID-5 only performs reasonably
for reads. And today’s modern databases write all the time, to the binary
log, error log, slow query log, and so on. What’s more most applications
do more than 5% writes anyway, making RAID-5 iffy at best. So don’t
bother with RAID-10, and don’t worry much about the I/O subsystem, if you want
performance to bog down, or even god-forbid crash when you lose a drive or two,
don’t use a fast or reliable disk!
7. Don’t Document Procedures and Configurations
Every
DBA and Developer I’ve spoken with dreads documenting their work. So hey,
don’t bother. Once you’re gone, or someone else needs to come in and
help, not having documentation is a sure way to step on a land mine or two,
delete the wrong thing, cleanup or otherwise miss that backup or maintenance
procedure that needs to happen. Yep, if you want your database to walk on
unstable ground, don’t worry about documenting procedures and configurations.
Conclusion
We hope you’ve
enjoyed our hopefully comical journey through some of the mishaps and troubles
we all want to avoid. Our day-to-day goal of course is keeping systems
reliable. So we hope by turning this goal upside-down, we can more easily
illustrate the risks and dangers that good DBA best practices help to avoid.
»
See All Articles by Columnist Sean Hull
