Working for a small company has it's advantages and disadvantages. One of the biggest disadvantages has to be the lack of specialist employees. I wear many hats at work. Most days I'm a software developer. Some days I get to be a network administrator. When a server is down, then I'm the systems administrator. One day I was the janitor. Since I'm big and tall, I'm frequently mistaken for a furniture mover.
It's a blessing and a curse. There is never a dull moment and always something for me to do. I've acquired many skills out of necessity. One of those skills has been database administration. More specifically, Oracle administration. I've learned to hate the database server. When something goes wrong in that box of mystery, everything grinds to a halt and people start getting pissed. So, in no particular order, I'd like to share some nuggets of information I've learned over the years.
- Cheap RAID cards are a bitch. I've had the opportunity to witness the carnage that can happen when good RAID goes bad.
- A fast disk is a fast database. Unless you can keep your entire database in memory, your data is going to need to be read from disk. Disk IO is a major bottleneck.
- RAID is awesome when it works. What's faster than one fast disk? More fast disks and a fast RAID controller. More is better. Just make sure you invest in quality equipment. Good RAID controllers aren't cheap for a reason. The expensive ones don't corrupt your data.
- RAID 10 is better than RAID 5. The only exception is if you never need to write data with any speed. Disks are so cheap that it doesn't make any sense to try and save the money. RAID 10 is just that much faster.
- Indexes are a double edged sword. They are necessary for fast queries, but too many will slow inserts and updates. Also, indexes on large tables should be chosen carefully as the indexes can consume quite a bit of disk.
- A bad query can bring the database to its knees.
- Locks protect multiple requests from stomping on top of each other. They are also good at stopping inserts and updates from happening when one gets stuck.
- Keeping the statistics up to date makes sure that the database choses an optimal execution plan for retrieving your data. The database needs to know how your data looks.
- Full table scans aren't always bad, but most of the time they are. The only time they aren't is generally when you are querying a majority of the data in the table.
- Be careful where you store your LOBs. Storing LOBs that you don't always need access to inline with other data can be slow. It reduces row density per data block which means more disk IO is needed to get your data.
- There are about 42 different ways to query the same data. Most of them are terrible.
Obviously you should take this information for what it is. This is just a few things I've come to believe based upon 5 years of banging my head against a wall. I write software, and somehow I just inherited this database responsibility. I'm not sure how, I think I was just closer to the server when it died the first time.
Most of the problems I've had with databases have been hardware related. A good backup strategy goes a long way to keeping your ass out of the fire. It's a terrible feeling when you lose your own data. It's an even worse feeling when you lose someone else's data. If you are an accidental DBA, I feel your pain. We should all start a support group.