Database Administration: The Complete Guide to Practices and Procedures

1. Database Administration:The Complete Guide to Practices and Procedures Chapter 10 System Performance

2. Agenda • The Larger Environment • DBMS Installation and Configuration Issues • System Monitoring • Questions

3. The Tuning Boxes System Database Application

4. The Larger Environment • A DBMS operates within the context of a larger environment. • Other software and hardware components must be installed, configured, and managed effectively for the DBMS to function as required. • Tuning and configuring these components and connections properly can have a dramatic impact on system performance.

5. Interaction with the Operating System • Has sufficient memory been allocated for operating system tasks? • Has sufficient disk space been allocated to the swap area? • The swap area is used when the OS runs out of memory • How were the database files allocated? • Using raw disk for database files can reduce OS and file system overhead. • Has each database-related task been assigned a priority; and is the priority appropriate for that specific task? • Some operating systems allow the administrator to set the priority of tasks that run under the auspices of the OS. • 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?

6. Allied Agents • Transaction processors like CICS and Tuxedo • Application servers such as WebSphere or WebLogic • Integration and ETL software such as SQL Server Integration Services (SSIS) • Networking software such as TCP/IP and SNA • Message queueing software such as MQSeries and MSMQ • Web connectivity and development software such as ColdFusion and Rails • IDEs and ORMs like Visual Studio, Hibernate, Spring and OpenAccess • Programming languages such as Java, COBOL, and C

7. Hardware Configuration The hardware must be installed and set up properly for the DBMS to operate efficiently. • Is the computer hardware and capacity appropriate for the DBMS environment? • Is the computer firmware (e.g., ROM BIOS) up-to-date? • 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?

8. Disk Storage and I/O • One of the biggest bottlenecks for database performance is the physical cost of performing I/O operations. • Data resides on a disk, and a disk is a mechanical device. • Solid state can improve performance. • A solid state device is actually computer memory that is configured to work like a disk drive.

9. Solid State vs. Traditional Disk

10. Components of the DBMS • A DBMS is comprised of multiple programs that deliver the requisite data management functionality. • Each program interoperates with other programs to provide a database management system. • Each DBMS breaks things apart a little bit differently than others. • The DBA must become an expert on the inner workings of the DBMS in order to ensure an optimized environment for database applications.

11. DBMS Installation and Configuration Issues • Every DBMS offers system parameters for configuring various aspects of the database environment. • Configuration is accomplished in a variety of ways, depending on the DBMS. Some popular configuration methods include: • executing system procedures to set and reset values • editing files with parameter settings • issuing commands at a DBMS prompt • assembling parameter specifications using an option within the DBMS. • Regardless of the manner of configuring the DBMS, the DBA will be required to specify various parameters to the DBMS that affect the way the DBMS operates.

12. Types of Configuration • During installation, the DBA has the option to change the configuration parameters or to allow the parameters to default. • Using the defaults is rarely a good idea in the long run. • Depending on the DBMS, parameters may be changed dynamically, nondynamically, or both.

13. Memory Usage • Relational databases love memory. • The single biggest system performance tuning task that a DBA will face is configuring RDBMS memory usage. • The DBMS uses random access memory to cache data and other resources required by the DBMS. • Reading data from memory is much less costly than reading the data from disk. • The more memory you can provide to the DBMS, the better performance will be. • Of course, the DBMS has to be configured properly to use the memory efficiently.

14. Cache DBMS DBMS

15. Types of Caches • Data cache • Procedure cache • Sort cache • Internal structure cache • Database log cache

16. Data Cache

17. Procedure Cache • Stores SQL and program-related structures • Optimized SQL

18. Sort Cache • Used instead of temporary disk storage to store intermediate sort results in memory. • Many relational database operations require sorts, for example, grouping, ordering, UNION operations, and certain types of joins. • The more sorting functionality that can be performed in memory, the better a sort will perform.

19. Internal Structures Cache • To accomplish relational operations, the DBMS may need to create internal structures that are not necessarily visible to the end user. • However, DBAs, and sometimes programmers, will need to know about the internal structures. • These internal structures are often cached into memory as they are used. • Example: DBD (database descriptor)

20. Database Log Cache • Used to buffer log records • Perhaps two, one for log writes and one for log reads. • The log write cache is used to speed up database modifications. By buffering log writes the database log becomes less of a bottleneck to system and application performance. • The log read cache is used for ROLLBACK and RECOVER operations. • A rollback or a recovery needs to access the log to undo or reapply database changes. • As the log records are requested, they will be buffered in memory in the log read cache.

21. Additional Areas of Memory Consumption • User connections. • Each concurrent user connection to the DBMS, regardless of the type of client connection, requires memory for the DBMS to maintain and manage the connection. • Devices. • The devices used by databases may require system memory to maintain and use. • Open databases. • Most DBMSs provide a parameter to specify the maximum number of databases that can be open at any one time. Each open database requires DBMS memory. • Open objects. • Another parameter may exist to identify the maximum number of database objects that can be open at any one time, including tables, indexes, and any other database object in use. Each open database object requires memory. • Locks. • Each concurrently held lock will require memory. The DBMS should provide a configuration parameter for the number of concurrent locks that can be held at one time. • Caches. • The various caches have already been discussed.

22. How Much Memory is Enough? • This is a difficult question to answer. • Have to balance cost of memory versus ROI of the applications using the DBMS. • At any rate, after calculating what you will need to use based on the size and usage of your database objects, it is always a good idea to leave some breathing room. • That is, add a little more memory than you think you will need.

23. Monitoring & Tuning the Data Cache • Efficiency of the data cache relies upon proper sizing • Too large: wastes memory • Too small: frequent writes and swapping • Some DBMSs, such as DB2, provide multiple buffer pools that can be configured and tuned independently with multiple parameters. • Others, such as SQL Server, are more basic, with a data cache per database. But regardless of the DBMS, the DBA should monitor the read efficiency of each data cache or buffer pool. • The read efficiency of the data cache is a percentage that tracks how well the cache is performing its primary duty—to avoid physical disk I/O operations:

24. Read Efficiency and I/O • Read efficiency is important because it shows the percentage of times a data page is found in the data cache (or buffer pool). • Shoot for 80% or better read efficiency. • The higher this percentage is, the more efficient the buffer pool is. When data pages can be found in buffers in memory without requiring a physical I/O, performance will be enhanced. • The actual numbers for I/O requests and actual physical I/O operations can be found by examining DBMS trace records or by using a database performance monitor. • Be sure to examine all I/O requests, those made synchronously as well as those made asynchronously. • Many DBMSes are capable of anticipating I/O requirements and scheduling reads of multiple blocks prior to their being requested.

25. What if… • Read efficiency is consistently below 80%? • Consider: • increasing the size of the data cache • modifying the number or type of concurrent processes using the data cache • reducing the number of tables and indexes assigned to the data cache • create an additional backup cache (if possible) • peg specific pages in memory • automatically increase cache size based on processing (if available)

26. Monitoring & Tuning the Procedure Cache • The procedure cache must be sized properly to accommodate all the SQL that may be run concurrently. • The read-efficiency calculation we used for gauging the effectiveness of the data cache can be used for the procedure cache also. • In the case of the procedure cache, the read efficiency calculates how often the DBMS needs to reoptimize SQL. • Procedure cache read efficiency usually ranges from 60% to 80%.

27. Additional Memory Tuning • Open Database Objects • DBMS likely has a configuration setting. • Cane be difficult to determine originally. • As time progresses, the database system can be monitored and better values can be provided. • Database Logs • Avoid disabling database logging for any database or database system where the data is valuable. • Set proper system checkpoint intervals.

28. Database Logging

29. Database Log Configuration • Input buffers • Output buffers • Log offloading • Log file definition • Set up dual logs • Can avoid disasters if a log file fails • The other one takes over and processing continues

30. Log Offloading • Over time, the log file will fill up with data. • Log offloading is the process of archiving an active log to an archival log and switching log writes to a new active log. • DBMS may perform automatic log offloading or you may have to perform it manually (that is, by DBA command). • AS a DBA you must learn how each DBMS you use handles backing up the database log.

31. Are all Database Operations Logged? • Depending on the DBMS, some operations may not be logged. • Examples: • CREATE INDEX • REORG • Bulk Copy • SELECT INTO • There may be options for turning logging on and off. • Proceed with caution.

32. Locking and Contention • Balance the need for concurrency with the need for performance. • Lock suspensionsoccur when an application process requests a lock that is already held by another application process and cannot be shared. The suspended process temporarily stops running until the requested lock becomes available. • Timeouts occur when an application process is terminated because it has been suspended for longer than a preset interval. This interval can usually be set by using a configuration parameter. • Deadlocks occur when two or more application processes hold locks on resources that the others need and without which they cannot proceed. The deadlock detection cycle—that is the time interval between checking for deadlocks—can also be set by using a configuration parameter.

33. The System Catalog • A relational DBMS requires a system catalog, but it may be called something different depending upon the particular DBMS you are using. • In Oracle, it is referred to as the Data Dictionary. • In SQL Server it is referred to as the System Catalog. • In DB2 it is referred to as either the DB2 Catalog or System Catalog. • In MySQL, it is referred to as the Information Schema

34. System Catalog Issues • The physical location and setup of the system catalog will have an impact on system performance. • The DBA must decide where it will be installed, on what type of disk, and how much space to allocate. • These decisions typically are made at installation time. • Place the system catalog on a separate disk device so that it can be managed and tuned independently from other application data.

35. Other Configuration Options • Nested trigger calls. • A nested trigger call is when one trigger causes another trigger to fire. Some DBMSs may provide additional control over trigger nesting by providing a maximum value for it. By setting this value, the DBA can control how many levels of nested trigger calls are allowable. • Security options. • The functionality of security and authorization can be controlled by DBMS configuration options. Some DBMSs allow database security to be turned over to external security and control software. • Identity values. • The identity property can be assigned to a column such that the DBMS automatically assigns numerically sequential values when data is inserted to the table. The DBMS can allow the configuration of the pool size from which identity values are obtained. • Distributed database. • To configure a distributed database implementation, the DBMS most likely will provide options for connecting databases at various locations.

36. General Advice • Avoid default configuration options. • Beware of configuration options that change the behavior of the DBMS.

37. System Monitoring • Use a system monitor. • Built-in or third party • A DBA must be able to operate and understand the output of the monitoring solutions available to him. • The performance monitor acts as a window into the efficiency of the database system.

38. Questions