Performance testing is an ideal way to generate a workload against a database. Great, now what? How about looking at the performance of the database? Many performance testers treat the database as a black box. Leave it to the experts they say. Unfortunately, if a performance problem is to occur anywhere in an n-tier application, it is most likely to occur in the database. Evaluation of the database is not really that difficult and comes down to understanding just a couple of key concepts.
Physical I/O
This must be minimised as much as possible. Physical I/O to disk is not only slow, it also consumes CPU. There are many ways of reducing Physical I/O, traditionally this would involve:
* Ensuring that the database has sufficient memory available for it to be able to store commonly accessed data in a data cache.
* Ensuring that data retrieving queries efficiently find target data without having to trawl through large tables.
CPU Utilisation
While a low CPU utilisation for a database server may appear to be good, it could by symptomatic of a poorly configured database. If the database is I/O bound, or lacks a sufficiently tuned configuration, it is possible that the database is not unable to service more requests for data but is not receiving those requests very quickly, i.e., those requests for data are queuing.
In a well configured database server, busy periods will see CPU utilisation around 80% with occasional peaks in workload taking the CPU utilisation closer to 100%
CPU utilisation can be reduced by investigating the following areas;
As a request for data arrives at the database, the SQL query must be parsed. This basically means that Oracle investigates the statement and decides how it will access the database tables so as to satisfy the query request. If the query has already been executed, it may still be in the area of the cache where queries are stored. Reusing a query which has already been parsed and is in the cache uses much less CPU than parsing a query for the first time. To ensure this reduction in CPU utilisation, the performance tester should ensure two things:
The cache is large enough to store the required number of queries.
Bind variables should be used so that the where clause of the query is not affected by different data variables; e.g. where name =b1: rather than where name = smith.
Once the query has been parsed, execution of the query will take place. It is essential that the database is able to find the requested data simply and easily. The access method that will be used to locate and retrieve the data is determined when the query is parsed. By using explain plan, the performance tester can determine what the access path is. An efficient query will require only a small number of rows to be read. Hopefully, these rows will be located in the databases buffer cache rather than on disk. An inefficient query will require a large number of rows to be accessed. Even if the much larger number of rows reside in the buffer cache, it still requires a certain amount of CPU to locate, retrieve and possibly sort the data.
Physical I/O
This must be minimised as much as possible. Physical I/O to disk is not only slow, it also consumes CPU. There are many ways of reducing Physical I/O, traditionally this would involve:
* Ensuring that the database has sufficient memory available for it to be able to store commonly accessed data in a data cache.
* Ensuring that data retrieving queries efficiently find target data without having to trawl through large tables.
CPU Utilisation
While a low CPU utilisation for a database server may appear to be good, it could by symptomatic of a poorly configured database. If the database is I/O bound, or lacks a sufficiently tuned configuration, it is possible that the database is not unable to service more requests for data but is not receiving those requests very quickly, i.e., those requests for data are queuing.
In a well configured database server, busy periods will see CPU utilisation around 80% with occasional peaks in workload taking the CPU utilisation closer to 100%
CPU utilisation can be reduced by investigating the following areas;
As a request for data arrives at the database, the SQL query must be parsed. This basically means that Oracle investigates the statement and decides how it will access the database tables so as to satisfy the query request. If the query has already been executed, it may still be in the area of the cache where queries are stored. Reusing a query which has already been parsed and is in the cache uses much less CPU than parsing a query for the first time. To ensure this reduction in CPU utilisation, the performance tester should ensure two things:
The cache is large enough to store the required number of queries.
Bind variables should be used so that the where clause of the query is not affected by different data variables; e.g. where name =b1: rather than where name = smith.
Once the query has been parsed, execution of the query will take place. It is essential that the database is able to find the requested data simply and easily. The access method that will be used to locate and retrieve the data is determined when the query is parsed. By using explain plan, the performance tester can determine what the access path is. An efficient query will require only a small number of rows to be read. Hopefully, these rows will be located in the databases buffer cache rather than on disk. An inefficient query will require a large number of rows to be accessed. Even if the much larger number of rows reside in the buffer cache, it still requires a certain amount of CPU to locate, retrieve and possibly sort the data.
No comments:
Post a Comment