As larger and larger workloads are placed on a database system, performance is eventually limited by one or more hardware resources. Typically one of three resources is exhausted: CPU cycles, memory space, or disk I/O bandwidth. When this happens, it may be because there are inefficiencies in the application or in how the database server is performing. If no inefficiencies are detected, you may need to add additional hardware resources to allow the database server handle larger workloads, and to improve performance on the existing workload. To view a list of common inefficiencies and recommendations for solving them, see Troubleshooting performance problems.
Adding resources may not resolve all scalability problems. Furthermore, even when resources are added, the capability of the computer is rarely improved in a linear fashion. For example, if it appears that the database server is fully using the allotted CPUs, it indicates that more CPU resources must be assigned in order to handle a larger workload. However, doubling the number of CPUs available to the database server likely will not double the amount of work the database server can perform in the same amount of time.
You can examine several database statistics using the Statistics tab in the Application Profiling Details area to detect whether hardware resources are a limiting factor for performance.
Detecting whether CPU is a limiting factor To detect whether CPU as a limiting factor, check the ProcessCPU statistic. If this statistic is not present on the graph, click the Add Statistics button and select ProcessCPU. If the graph shows ProcessCPU increasing at a rate of nearly 1 point per second per CPU assigned to the database server, then the CPU is a limiting factor. For example, for a database server running on two CPUs, if the Process CPU counter increased from 2220 to 2237 in ten seconds, this indicates that CPU usage over that twelve second period was (2237-2220) / 10s * 100 % = 170%, meaning that each CPU is running at 170% / 2 = 85% of its capacity.
Detecting whether memory is a limiting factor To detect whether memory (buffer pool size) is a limiting factor, check the CacheHits and CacheReads database statistics. If these statistics are not present on the graph, click the Add Statistics button and select CacheHits And CacheReads. If CacheHits is less than 10% of CacheReads, this indicates that the buffer pool is too small. If the ratio is in the range of 10-70%, this may indicate that the buffer pool is too small—you should try increasing the cache size for the database server. If the ratio is above 70%, the cache size is likely adequate. Note that this strategy only applies while the database server is running at a steady-state—that is, it is servicing a typical workload and has not just been started.
Detecting whether I/O bandwidth is a limiting factor To detect whether I/O bandwidth is a limiting factor, check the CurrIO database statistic. If this statistic is not present on the graph, click the Add Statistics button and select CurrIO. Look for the largest sustained number for this statistic. For example, look for a high plateau on the graph; the wider it is, the more significant the impact. If the graph has sustained values equal to, or greater than 3 + the number of physical disks used by database server, it may indicate that the disk system cannot keep up with the level of database server activity.
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |