让数据库宕机的7种做法(7 Ways To Crash a Database)

August 13, 2009

7 Ways To Crash a Database

By Sean Hull

 

from: http://www.databasejournal.com/features/mysql/article.php/3832746/7-Ways-To-Crash-a-Database.htm

 

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.