A database administrator is responsible for managing database growth, data availability and security. Security* aside, they are concerned with how large a database will grow, how fast this will happen and how the consumption of data will be affected. At the highest level this is what drives your decision-making. This, as well as a company’s dependency on the database, is what’s used to estimate risk and design strategies to avoid unplanned data outages, data loss and minimize downtime.
As a DBA you are going to encounter two general types of databases: the ones you personally build and the ones you inherit. In both cases your job is to design strategies to prevent things from happening that negatively affect data availability. When you get to build your own database you have an opportunity to proactively address the stability of the system; however, you also will likely have to work within certain budget constraints that force you to make design tradeoff. When you inherit someone else’s database you need to have the investigative skills and understanding of the architecture to find vulnerabilities in what is likely and unfamiliar environment.
We can look at system vulnerabilities in two ways: points of failure and potential bottlenecks. All bottlenecks have the potential to become points of failure, but not all points of failure are bottlenecks. A drive failure in a RAID 0 array will take a database down, but this is obviously not because RAID 0 arrays are bottlenecks. A poorly written SELECT statement issued against a large, high-volume table can create a bottleneck that will become a point of failure by creating a blocking chain that will slow a database to a crawl and likely force a restart. Both cases result in system outages and possibly data loss.
While points of failures are usually addressed by using redundant components, designing strategies to prevent bottlenecks takes a little more planning and starts with understanding how the system is used. Different database systems will be susceptible to different bottlenecks based on their usage and design. For example, a data warehouse will be susceptible to certain bottlenecks that are not an issue in OLTP environment, and vice versa. A data warehouse with denormalized data is optimized for query performance, and a highly normalized OLTP database is optimized for transactional speed and data integrity. A typical bottleneck in the data warehouse is high processor utilization due to complex queries. A typical bottleneck in an OLTP database is high disk utilization from non-sequential reads and writes because of the high volume of transactions.
Generally speaking bottlenecks fall into 2 different but related categories: hardware bottlenecks and bottlenecks internal to the database. As far as hardware bottlenecks are concerned, there are four main areas where they tend to occur. These include the disk subsystem, processor, memory, and the disk I/O bus. Bottlenecks internal to the database tend to occur because of poorly written queries, stored procedures, read/write conflicts (locking/deadlocks) and improper uses of indexes on large tables. Every internal bottleneck is made worse by hardware bottlenecks.
There are several strategies that can be used to avoid bottlenecks, minimize downtime and data loss. In the next post I’ll discuss how some of these strategies can be applied.
*I’ll talk about security in a separate post. For this I wanted to zero in on non-security related points of failure and bottlenecks.
SQL - Data - Life 