Apoorly performing system can degrade the performance of all databasesand applications deployed on that system. No amount of database,application, or SQL tuning can improve performance when a poorlyimplemented system is causing performance problems. Applications accessdatabases and both are implemented on an overall system environment, asshown in Figure 10-1.Therefore, a system problem can cause all databases and applications toperform poorly, just like a database problem can cause all applicationsthat access that database to perform poorly.
A system problem can cause all databases and applications to perform poorly. |
Thesystem comprises the hardware and software required for the DBMS tooperate and for applications to access databases using the DBMS. It isimperative that the DBA understands the system and operatingenvironment where the database applications will be run. The DBA mustbe able to facilitate changes to any component of the system to tunethe database environment. Of course, the DBA cannot be expected to bean expert in every aspect of the system, and therefore the DBA willneed to work with other teams within the organization to initiatesystem changes.
Thefollowing sections provide introductory coverage of system-relatedperformance and tuning tactics. They do not provide in-depth treatmentof the subjects because that is not the primary focus of the book.
ADBMS operates within the context of a much larger environment thatconsists of other software and hardware components. Each of thesecomponents must be installed, configured, and managed effectively forthe DBMS to function as required. The DBA needs to understand how theDBMS interacts with the server hardware, the operating system, and anyother required software. Tuning and configuring these components andconnections properly can have a dramatic impact on system performance.
Whenthe operating system experiences a performance problem, all of thesoftware that runs on that operating system may experience performanceproblems. To help ensure an optimal operating system for your databaseapplications, the DBA should ask the following questions.
Has a sufficient amount of memory been allocated for operating system tasks?
Most operating systems have the capability of allocating a specific amount of disk space as a swap area. The swap area is used when the OS runs out of memory. Has a sufficient amount of disk space been allocated to the swap area?
How were the database files allocated when the database was implemented? Interaction with the file system can cause some operating systems to create additional overhead. By changing the database files to use raw disk, OS and file system overhead can be eliminated. (Additional information on raw disk usage can be found in Chapter 17.)
Some operating systems allow the administrator to set the priority of tasks that run under the auspices of the OS. Has each database-related task been assigned a priority? Is the priority appropriate for that specific task?
Is the operating system at the version and release level recommended by the DBMS vendor? Have any bug fixes been shipped for the OS that are applicable for the particular brand of database server you are running?
Have the operating system configuration parameters been modified when installing the DBMS? If so, has sufficient testing been done to ensure that the parameters were modified correctly and do not impact any other processes that run on the database server?
Asdiscussed in previous chapters, the DBMS has to ally itself with manyother software components to deliver service to the end user. Examplesof allied agent software include
Transaction processors like CICS and Microsoft Transaction Server
Networking software such as TCP/IP and SNA
Message queueing software such as MQSeries and MSMQ
Web connectivity and development software such as ColdFusion
Programming languages such as Java, COBOL, and C
The DBMS has to ally itself with many other software components to deliver service to the end user. |
Eachof these allied agents needs to be configured properly to interact withthe DBMS, and it is the DBA's responsibility to understand the setuprequirements. In larger shops the DBA might not perform the actualconfiguration—leaving it, instead, to more skilled professionals whospecialize in administering and managing the software. However, insmaller shops the DBA may have to configure all of the software himself.
TheDBMS runs on computer hardware. That hardware may be a large-scalemainframe, an intermediate Unix system, or a PC running Windows.Regardless of its scale, the hardware must be installed and set upproperly for the DBMS to operate efficiently.
The hardware must be installed and set up properly for the DBMS to operate efficiently. |
Again, here are some questions the DBA should ask to assure an optimal hardware environment for the database applications.
Is the computer hardware and capacity appropriate for the DBMS environment? In other words, does the DBMS vendor recommend this particular hardware implementation?
Has a sufficient amount of memory been installed for all of the system software to be installed (OS, DBMS, and other allied agents)?
Has an appropriate amount of disk storage space been allocated and configured for use by the DBMS?
What type of disk storage is being used and is it appropriate for large data volumes and high-speed database queries?
Are all the network cables connected and functioning properly?
Are all physical connections (e.g., cables, plugs, and board sockets) fully connected and operational?
Is the hardware connected to an uninterruptible power supply?
Is the hardware connected to a surge protection device?
Oneof the biggest bottlenecks for database performance is the physicalcost of performing I/O operations. Data resides on a disk, and a diskis a mechanical device. It requires machine parts that move in order toread encoded data from a spinning platter. This physical movement takestime, and anything that can be done to reduce I/O time can enhanceperformance.
Aconsideration for optimizing disk access is to utilize solid statedevices. A solid state device is actually computer memory that isconfigured to work like a disk drive. When data is read from a solidstate device, there is no physical component to the I/O operation—thedata resides in memory and is transferred from memory to the DBMS andthen to the requester.
A consideration for optimizing disk access is to utilize solid state devices. |
Considerplacing database objects with high performance requirements on solidstate devices instead of physical disk drives, RAID devices, or storagearea networks.
However,implementing solid state devices has some potential problems. The firstis cost. Only recently has the initial cost of solid state devicesbegun to decrease. The second potential problem is persistence. Somesolid state devices require a constant supply of power to prevent thedata from being erased. In such cases, be sure that solid backup andrecovery plans are implemented for database objects.
ADBMS is a very complex system requiring hundreds of thousands of linesof computer code. A DBMS is so complex that multiple programs arerequired to deliver the requisite data management functionality; eachprogram interoperates with other programs to provide a databasemanagement system.
EachDBMS vendor breaks down DBMS functionality into different components.The DBA must study the makeup of the DBMS and gain an understanding ofeach component piece and how it integrates with the other components ofthe DBMS. For a high-level overview of the architecture of the OracleDBMS, refer to the sidebar “The Architecture of Oracle.”
TheDBA must become an expert on the inner workings of the DBMS in order toensure an optimized environment for database applications. A failure orproblem in any single component of the DBMS can cause severeperformance degradation for every application accessing the database.
联系客服