WO2005116871A1 - Method and apparatus for tuning database configuration parameters usnig multivariate analysis - Google Patents

Abstract

A method optimising the values of configuration parameters in a database management system (3) hosting a database (4), in which a number of tests are designed using a Design of Experiment technique, and are conducted with various combinations of parameter values. The results are analysed using a multivariate analysis technique so as to determine the optimum values of one or more of the parameters with respect to performance of the data processing system. Each test involves sending a predetermined query or set of queries to the database from a test client (6), including paired queries so that database is restored to its original condition after each test. A load generator client (7) simulates load during the tests.

Description

METHOD AND APPARATUS FOR TUNING DATABASE CONFIGURATION PARAMETERS USING MULTIVARIATE ANALYSIS

5 This invention relates to the tuning of data processing system configuration parameters, and particularly but not exclusively in the configuration of database management systems such as Oracle™ or Microsoft SQL Server™ which host databases.

10 Today's businesses are highly dependent on access to their computerised applications. However, ensuring availability and optimal performance can be a daunting task. When a critical component such as a database performs inadequately, business processes are severely compromised. Tuning an application, e.g., a database management system (DBMS) which hosts a database can be achieved in a number of 15 ways, for example by improving the quality of the application, the quality of the SQL statements, improving the database design, indexing, upgrading hardware such as processors and disks, and/or optimising the DBMS configuration by adjusting the parameter settings. The present invention is particularly concerned with optimising configuration parameters. 20 The settings of the configuration parameters for a DBMS directly affect performance. The large number of configuration parameters (approximately 100 for Oracle) and the risk of making wrong settings, often prevent the administrator from making manual changes. Consequently, standard settings are seldom changed and 25 the application will not function in an optimal fashion.

It has been said that the best approach to optimising parameters is never to change more than one parameter at the time, also known as "The Golden Rule" (Database Tuning for Beginners, Joze Senegacnik, Aster d.o.o., Slovenia). If more than one 30 parameter is changed it is impossible to know which parameter had a good or even bad impact on performance. Traditional DBMS performance tuning methodologies are based on so called ratios. This technique is inefficient. Other, more sophisticated approaches have been proposed and are gradually replacing the older hit-ratio methods, but they are still based on a univariate approach.

A method in accordance with the invention is described in this specification in relation to the optimisation of configuration parameters for a DBMS, in the preferred embodiment an Oracle DBMS, but can also be used to improve performance on other database management systems, servers and networks.

For a database system the input is a query, frequently in the form of Structured Query Language (SQL) statements, one or a plurality. The purpose of these is to query the database to retrieve information saved in the database, as well as to add information to the database and modify the structure of the database. The set of factors influencing the process can be divided into controllable and uncontrollable factors. The output from a SQL query is the data that has been asked for. The performance issue of concern in the context of the present invention is not the result of the SQL query but rather the response time, i.e. the time elapsed from when the SQL statement is sent from the user until the result is received, i.e. the "response time".

In a database system, the factors inflating the response time mostly fall in the category of controllable factors, namely the available hardware, the network, the database initialisation parameters and the application software. In accordance with the preferred implementation of the present invention, the focus is on the initialisation parameters of a DBMS and how they affect response time. Other factors are not altered. The focus is on how the parameters in an initialisation file influence the response time for a set of SQL queries. In other words, the focus is that of a user. A typical user is concerned about how long time a SQL query takes, and a database administrator (DBA) is concerned with how this response time can be reduced.

Generally, Oracle DBMS performance analysts have worked under the assumption that there is no objective way to measure Oracle response times. In the perceived absence of objective response time measurements, analysts have settled for the next- best thing: event counts. From event counts come ratios, and from ratios come many arguments about what "tuning" actions are important, and what ones are not However, users are not concerned with event counts, ratios and arguments; they care about response time.

No matter how much complexity is built into timing-free event-count data, there is a problem. One cannot tell how long a task will take by counting how many times it happens.

Thus the focus must be on minimizing the response time based on all relevant data. The response time as experienced by the user consists of service time, wait time and some additional time components. The service time is the time spent by the CPU actively working on a request, and the wait time is the time spent waiting for a resource to respond or become available. When a user executes an SQL statement that is doing an index lookup, the CPU time involved may be in processing blocks in the buffer cache, scanning an index block for a certain value and getting the requested row out of the data block. To do this, the DBMS may have to read the data block from the disk, which incurs a wait time until the disk responds. In more complex cases, one may spend CPU time processing PL/SQL and one may .wait for a lock or for the DBMS to write data to the redo log file when doing a commit. The additional time is time required by the operating system and network to handle the request.

Traditionally, database administrators and consultants doing DBMS tuning have focussed on figures like buffer cache hit ratio and latch free wait time to assist them in optimizing database performance. Several tools, including simple script -based Oracle tools like Statspack™ have been used to calculate such figures, but with the large amount of data produced it is difficult to answer the simple question of where the tuning effort should start. One performance profiling method is to identify in some detail what the components of the service time and the wait time are, and simply to order these. The component at the top is the one that should be the first one to tune.

The present invention is based on the realisation that multivariate design and data analysis can be used to optimize performance of a DBMS. Multivariate analysis has previously been used to study data that has been stored in a database (data mining), but not to optimize the DBMS itself A preferred method in accordance with the invention involves Design of Experiment (DOE) procedures and multivariate statistical analysis. Reference is made, for example, to Design and Analysis of Experiments, Douglas C. Montgomery, 5^th edition 2001, and to Multivariate Analysis of Quality: An Introduction, Harald Martens and Magni Martens, ISBN: 0- 471-97428-5. DOE techniques are used in a number of fields. For example, they have been used to optimise compilers, see "Feedback-Directed Selection and Characterization of Compiler Optimizations", Kingsum Chow and Youfeng Wu, Intel Internal communication.

The Design of Experiment technique was introduced in the early 1920's when a scientist at a small agricultural research station in England, Sir Ronald Fisher, showed how one could conduct valid experiments in the presence of many naturally fluctuating conditions such as temperature, soil condition, and rainfall. The design principles that he developed for agricultural experiments have been successfully adapted to industrial and military applications since the 1940s. Many engineers and scientists perform one-factor-at-a-time (OF AT) experiments. One traditional method of experimentation is to evaluate only one variable (or parameter) at a time. All of the variables are held constant during test runs except the one being studied. This type of experiment reveals the effect of the chosen variable under set conditions; it does not show what would happen if the other variables are also changed. It was Fisher's idea that it was much better to vary all the factors at once using a design which, in which experiments are run for combinations of levels for all of the factors. With such a design, analysis can reveal what the effect of one variable would be when the other factors are changing. DOE is a collection of statistical tools that deal with planning and making sense out of the results of the experiment. Depending on the system under investigation and the purpose of the investigation, different types of DOE techniques are available. Factorial design, Plackett-Burman designs and Central Composite are type of designs, useful in this context, for screening and optimisation.

An initial stage in a DOE technique is to choose the factors whose effects and interactions are to be investigated. A matrix of a series of experiments is then established, with various combinations of the selected maximum and minimum values of the factors to be examined. It is widely known how to generate suitable matrices for various numbers of factors. In one convention, generic matrices have the minimum and maximum values denoted by -1 and 1. For a particular experiment, the actual minimum and maximum values are used in the various tests in the matrix. A full factorial design is one which takes into account all possible combinations of maximum and minimum values factors. A full factorial design with 8 factors would mean 2⁸ experiments, a total of 256. The large number of experiments needed in a full factorial design makes it hard to use when the number of factors increase. In these conditions, fractional factorials are used that "sacrifice" some or all of the interaction effects so that main effects may still be computed correctly.

Technical descriptions of how fractional factorial designs are constructed are widely available. A typical fractional factorial design is denoted as a 2 ^(k"p) design in which k is the number of factors and/? is the number of those factors which are generated from products of factors. As a result, the design does not give full resolution . The number of experiments reduced at the expense of the number of high order interactions that may be calculated without confounding with main factors.

When it is desired to screen a large number of factors to identify those that may be important, it is desirable to employ a design that tests the number of potential significant factors with the least number of experiments. Factorial designs on two levels are mainly used for screening, that is, to determine the influence of a factor on a response and to eliminate those factors which are not significant. Plackett-Burman designs are "screening" designs that experimenters use to study a large number of factors in a few experiments. These small designs may require the experimenter to only perform one more experiments than the number of study factors (e.g., twelve experiments for eleven factors). However, the experimenter pays a price for the "efficiency" of Plackett-Burman designs since all the main effects are confounded with interactions. They are, however, useful for identifying the factors to be investigated more thoroughly.

In preferred embodiments of the present invention, a number of database configuration parameters are selected for analysis. This may require an initial screening process if there is a large number of parameters, as with the Oracle DBMS. The selection of parameters to be analysed may use literature, past experience or a screening process using a number of experiments in accordance with a number of fractional factorial designs or a Plackett-Burman design, for example.

Once the parameters that are to be analysed have been selected, they are given reasonable minimum and maximum values, for example based on past experience, recommendations of the DBMS supplier and so forth. For a particular set of parameters, a series of experiments is carried out in which there are different combinations of the minimum and maximum values of the parameters, according to the chosen design of experiments. Performing a multivariate regression analysis of the results makes it possible to identify the parameters which have a significant statistical effect on the performance of the database. The number of significant parameters could range from zero up to as many parameters that are included in the design. The results of the analysis can provide regression coefficients, the values of which can be used to order the parameters and to identify the parameters of greatest significance.

Further analysis and / or experiments are then carried out to optimise the values of at least the parameters) having a significant effect on database performance. The ultimate objective of the invention is to provide, for a particular database installation, optimised values of the configuration parameters and to configure the database management system accordingly.

An important component of the present invention is the realisation that a database environment is dynamic: The load and the database may change during execution of SQL statements. By contrast, compiler performance tuning as described by Kingsum Chow and Youfeng Wu is carried out off-line in a fixed environment. In preferred embodiments of the present invention, there is provided a controllable load representing selected numbers of users and optionally different applications running on the same server as the database being tuned. There is also used a strategy that leaves the database unchanged after each test. In this way it is possible to benchmark the database performance for the new settings against the original settings in a controllable way. This provides the possibility to identify critical configuration parameters and their optimal range as well as the possibility to split quantitatively overall performance problems into parts related to statistics such as CPU and waiting time.

In a preferred embodiment of the invention, for each experiment an identical set of SQL statements is sent to the database and a measure of the performance, such as response time, is recorded. The SQL statements will generally be of types encountered in normal use of the database, for querying data, adding data, deleting data, changing tables and so forth. In accordance with preferred embodiments of the present invention the database under investigation stays the same during the test period. For example, every INSERT statement in a sequence of SQL statements may have a corresponding DELETE statement. This is a matter which is conventionally overlooked when tuning a DBMS. People analyse an Oracle database system, for example, change parameters in the "ora.ini" initiation parameter file, and verify the effect. In the meantime, various things may have changed uncontrollably and, for example table sizes may have increased/decreased.

Restoring the database to its original state after each experiment can also be done in other ways, for example by first saving the original database files and then using this copy to restore the database between each experiment. Of course, such an arrangement is more complicated to perform and is more time consuming.

The important issue is to restore the database to its original state between each test.

According to one aspect of the present invention, there is provided a method of optimising the values of configuration parameters of a database management system hosting a target database, using a data processing system comprising a database server on which the database management system resides, a test client in communication with the database server, and a load client in communication with the database server; wherein :

(a) a number of the configuration parameters are selected, each configuration parameter having specified maximum and minimum values, and a design of experiments is established defining a number of experiments in which there are different combinations of the maximum and minimum values for the selected parameters;

(b) the test client is configured by a test client module to send a predetermined sequence of test commands to the target database for each experiment defined by the design of experiments to obtain a measure of the database performance for each experiment in the design of experiments;

(c) the load client is configured by a load client module to send a sequence of load commands to the database management system so as to simulate a load on the database management system during each experiment in the design of experiments;

(d) the arrangement is such that the target database is the same at the start of each experiment in the design of experiments;

(e) the database server is configured by a server module with instructions to control the database management system so that at the start of each experiment in the design of experiments the configuration parameters of the database management system are in accordance with the design of experiments;

(f) a multivariate regression analysis is carried out on the results of the experiments in the design of experiments, to identify at least one parameter which has a significant effect on target database performance; and

(g) the value of at least one identified parameter is optimised .

In a preferred embodiment, a multivariate regression analysis is carried out on the results of the experiments in the design of experiments, to identify the configuration parameters which have a statistically significant effect on target database performance. Preferably, the result is expressed as a regression model between the database performance measure and the significant (important) configuration parameters.

The regression model can be differentiated partially on each statistically significant configuration parameter to determine the optimal settings of the statistically significant configuration parameters. The remaining configuration parameters investigated in the design are set to their minimum values used in the design of experiments. If desired, the significant configuration parameters can be further optimised by using well-known optimisation methods such as, e.g. the gradient method, or the Simplex method.

It may be possible to identify some or all of the parameters having a significant effect on database performance from one run of the design of experiments. If the performance is still not acceptable, steps (a) to (g) can be repeated for a new set of configuration parameters using the optimal settings for the investigated ones.

It will be appreciated that carrying out an initial design of experiments may result in no parameters being identified as being significant. In that case, the design and / or the parameters selected for investigation may be modified. Preferably, each sequence of test commands includes paired commands such that the target database is unchanged after the sequence has been sent. Preferably, each sequence of test commands is identical.

In a preferred embodiment, the load client simulates a number of virtual load users accessing the database management system. Preferably, the number of virtual load users is selectable. In a preferred arrangement, a separate thread is established for each virtual load user.

In general, at least some of the load commands are sent to the target database, and in preferred embodiments all load commands are directed to the target database. Preferably, each sequence of load commands sent to the target database includes paired commands such that the target database is unchanged after the sequence has been sent. However, a load can also be simulated if at least some of the load commands are sent to a different database hosted by the database management system

In a preferred embodiment the sequence of load commands is constructed by selection at random from a predetermined selection of commands. Preferably, wait times are interspersed between load commands. The wait times may be selected randomly in accordance with a normal distribution of wait times.

In preferred embodiments of the invention, the design of experiments is a fractional factorial design.

As noted above there are various options for optimising parameters identified as having a significant effect on database performance. In one simple arrangement, a parameter identified as having a significant effect on target database performance can be optimised by means of carrying out a number of experiments in which there are different combinations of : (i) values of an identified parameter between and including maximum and minimum values; with

(ii) values of the remainder of the selected parameters, in each combination all having the same type of value.

The maximum and minimum values used in these experiments may extend beyond the ranges used previously or normally recommended.

The experiments involve sending test commands, sending load commands, and noting response times as discussed in relation to the design of experiments described above, using analogous techniques and with the database being unchanged between each experiment.

In a preferred embodiment of such an arrangement, in each experiment combination, each of the remainder of the selected parameters may have its minimum value or each of the remainder of the selected parameters may have its maximum value. Preferably, for any of the remainder of the parameters which have already been optimised, the optimised minimum or the optimised maximum value is used. In one embodiment of the invention, a subset of the selected parameters is established, being parameters which have been identified as having a significant effect on target database performance in the experiments, and which have been given an optimised range of values, and wherein a full factorial design of experiment is carried out using the subset of parameters, and the results analysed to further optimise at least one of the subset of parameters .

Viewed from another aspect, the present invention provides a data processing system for providing data for use in optimising the values of configuration parameters of a database management system hosting a database; the data processing system comprising a database server on which the database management system resides, a test client in communication with the database server, and a load client in communication with the database server; wherein : (a) the test client is configured by a test client module to send a predetermined, identical sequence of commands to the database on a number of experiment instances specified by a design of experiments, the sequence including at least one command which changes the database and which has a paired command to counteract its effect, the arrangement being such that the sequence of commands leaves the database unchanged at the end of each experiment in the design of experiments; the test client application further configuring the test client to record the response time on each experiment instance that the sequence of commands is sent;

(b) the load client is configured by a load client module to send a predetermined sequence of commands to the database management system so as to simulate one or more levels of load on the database management system whilst the test client sends commands and records response times; and

(c) the database server is configured by a server module with instructions to control the database management system to stop after responding to the sequence of commands from the test client on an experiment instance and, in accordance with the design of experiments, to set the configuration parameters of the database management system for the next experiment instance and to re-start the database management system.

Viewed from a further aspect, the present invention provides a method of optimising the values of configuration parameters of a database management system having a plurality of configuration parameters, the database management system hosting a database, wherein:

(a) a number of the parameters are selected;

(b) a parameter in the selected parameters is identified; (c) a series of experiments are carried out in which there are different combinations of values of the identified parameter and values of the remainder of the selected parameters, to enable optimised values for the identified parameter to be determined ;

(d) for each experiment, a series of commands is sent to the database from a test client, the series including paired commands such that the database is unchanged after the series has been sent;

(e) for each experiment, ameasure of the performance of the database is recorded at the test client;

(f) the results of the experiments are analysed using multivariate techniques, and an optimised value for the identified parameter is determined.

Preferably, during each experiment, sequences of load commands are sent to the database management system from a load client to simulate a load on the database management system of a number of virtual users. In one preferred arrangement, the sequence of tests in the design of experiments is repeated for different load levels generated by the load client.

As noted earlier, the invention is not limited to optimising database initialisation parameters. The invention provides a general strategy for performance tuning, for instance, database systems but can be used in other applications as well such as server, network or other system, hardware or software configuration parameters.

Thus, viewed from another aspect, the invention provides method of optimising the values of configuration parameters in a data processing system in which a number of tests are conducted with various combinations of parameter values and the results are analysed using a multivariate analysis technique so as to determine the optimum values of one or more of the parameters with respect to performance of the data processing system. Viewed from another aspect, the invention provides a method of optimising the values of configuration parameters in a data processing system, in which a number of tests are conducted with various combinations of parameter values and the results are analysed using a multivariate analysis technique so as to determine the optimum values of one or more of the parameters with respect to performance of the data processing system.

The methods may be carried out using the aspects of the invention as discussed earlier, with modification having regard to the particular application concerned.

A design of experiments used in aspects of the present invention may include replicate tests and / or centre point tests. Replicate tests are identical tests performed at different times whilst carrying out the designed experiment, and are used to obtain an estimate of the experimental error. Centre point tests can be used if it is suspected that the relationship between the parameters in the design is rather curvilinear, and one or more tests are conducted where all parameters are set at their midpoint.

The selected design usually includes interaction terms that are not confounded with main effects, that is, two factor interactions, such as PlxP2 or even higher order interactions. Occasions may arise when these interaction terms become significant, and even if none of the main factors PI and P2 are. Depending on the direction of the interaction term (the sign of the regression coefficient), optimal values can be set for the PI and P2 parameters.

It will be appreciated that some configuration parameters may not have a true maximum or minimum value as such and may have simple True / False options. In these cases, the True value can be assumed to be the maximum value and the False value the minimum, or vice versa depending on the nature of the parameter. A decision to select one value for centre point tests has to be taken when a parameter does not have possible values between its maximum and minimum.

It is also possible to select designs including more than 2 levels to support cases where this may be useful. In accordance with all aspects of the invention, there may be a further step in which, once optimised values for appropriate configuration parameters have been determined, the database management system, or other system, is configured with those parameters.

It may be that the experiments are not carried out on a live database server accessed by normal users, but on a development server running a copy of the live database, the appropriate configuration parameters subsequently being used on the live database server.

Aspects of the present invention could be incorporated within a DBMS so that a self configuration technique could be carried out.

Some embodiments of the invention will now be described by way of example only and with reference to the accompanying drawings, in which:-

Figure 1 shows the test environment;

Figure 2 shows the software modules used in implementation of the invention;

Figure 3 shows the average response time for a number of runs, showing the effect of different levels of a parameter P4;

Figure 4 is a normal plot based on the same information as Figure 3;

Figure 5 shows average response times for various setting of the parameter P4;

Figure 6 shows the regression coefficients for a full factorial design with parameters P2, P4 and PI 4; and

Figure 7 shows an interaction graph for the parameters P2 and PI 4; Figure 1 shows a test environment for optimising the initialisation parameters of a database management system hosting a database. The environment includes a database server 1 having an operating system 2, and a DBMS application 3 which in this case is Oracle, hosting a database 4. The database server 1 is attached to a local area network 5, to which are connected a test client 6 for sending test SQL queries to the database 4, and a load generator client 7 for sending SQL queries to the database 4 to simulate varying loads. The database management system has a number of configuration parameters, stored in an initialisation file. Each parameter has a particular value, which is in a range of recommended (or technically feasible) minimum and maximum values normally provided by the DBMS supplier. The actual values within those ranges will have been specified in accordance with the supplier's recommendations, literature, past experience and so forth.

With the Oracle database operating system the file "init.ora" determines many of the environment attributes, such as memory allocated for data, resources allocated for I/O, and other crucial performance related features. In Oracle 8.1.7, for example, the ora.ini file contains around 100 adjustable parameters. For analysis in accordance with preferred embodiments of the invention, to restrict the number of experiments it is appropriate to choose only those configuration parameters, chosen by reference to literature, experience etc., as possibly having a significant effect on database performance. One possibility is to choose the parameters from a Principal Component Analysis (PCA) based on all the parameters in the ora.ini file, or parameters identified from literature, experience and so forth. A classification of a new case then could indicate which parameters should be included in a DOE and even the starting level of these parameters. Outliers in such a PCA classification may indicate wrong parameter setting.

The result of the actions described above is a list of potential important initialisation parameters for the chosen DBMS. For the purposes of explaining the invention, in this somewhat generic embodiment fifteen hypothetical parameters PI to P 15 are chosen, some of which are shown in Table 1, with associated maximum and minimum values, and the value actually used in a particular implementation. Table 1

The chosen DOE method depends on the number of parameters. For designs involving more than about 10 to 15 parameters, a screening or reduction can be useful or necessary to identify the most significant parameters affecting performance. This can be achieved using a Plackett-Burman design or reduced factorial designs, if necessary being repeated. In either case, a small possibility of losing information exists. In the present embodiment, reduced factorial designs are used.

In the present embodiment of the invention, there is provided a software package having a test client module, a load client module, and a database server module.

Test Client Module.

This module runs as a service on the test client 6 and executes SQL statements and measures the response times. In this mode, for any experiment a predetermined sequence of SQL statements (for example 250) are executed a number of times, typically 50. Each time the response time is measured and recorded, both the average and the median response time being calculated. Thus , for a given experiment the average / median response times over 50 runs are recorded. The SQL statements can consist of any type of statement, for example SELECT, INSERT, UPDATE or DELETE, and are preferably chosen from a client application normally used with the database, to simulate the conditions in actual use.

For each experiment in an experiment design the database is reset to its original state, and where necessary statements are paired so that one cancels out the other. Thus, a set of SQL statements from a typical database application is chosen and supplemented so that the SQL statements are non-updating. For example, each UPDATE is paired with another UPDATE which sets the data changed in the first UPDATE back to its original value. In the same manner each INSERT is paired with a DELETE and vice versa.

For example, in a particular database implementation the following four SQL statements could be included in the set of statements (typically 250) sent from the test client in an experiment.

1. Insert section

(a) INSERT INTO PWEL_SAMPLE (SYSNAM,FACILITY, WELL_NO, DAYTIME, SUMMER_TIME, AVG_SSD_SIGN) VALUES ( 'FJORD', 'SFT', 'A- 06', to_date ('17.09.2004 10:00:00','dd.mm.yyyy hh24:mi:ss'), 'S', .04225)

(b) INSERT INTO EC_RAW.SEP_S AMPLE (FACLLITY,SEPARATOR_NO,RESOLUTION_TIME, DAYTIME, SUMMER_TLME, MEAS_PRESS) VALUES ( 'SFR^«,'CD_2014',300, to_date(' 17.09.2004 10:05:00','dd.mm.yyyy hh24:mi:ss'), 'S', 64.96930)

2. Reverse Insert Section.

DELETE FROM EC_RAW. SEP_S AMPLE

WHERE FACLLITY=^,SFR AND SEPARATOR_NO = 'CD_2014'

AND DAYTIME = to_date(' 17.09.2004 10:05:00','dd.mm.yyyy hh24:mi:ss') AND SUMMER_TLME = 'S' AND RESOLUTION_TLME = 300

DELETE FROM PWEL_SAMPLE WHERE SYSNAM = 'FJORD' AND FACILITY = 'SFT' AND WELL_NO = 'A-06'

AND DAYTIME = to_date(' 17.09.2004 10:00:00','dd.mm.yyyy hh24:mi:ss') AND SUMMER_T1ME - 'S*

The DBMS is stopped and re-started between each experiment in the present embodiment.. This is to ensure that the new configuration parameters are read into the DBMS. It is conceivable that in a particular DBMS, configuration parameters could be changed without stopping the DBMS. In any event, an advantage of stopping the DBMS is that all database memory buffers are emptied, so that the (identical) query statements in the next experiment do not result in artificially low response times because results are already cached.

Load Client Module.

This module runs as a service on the load client 7 and generates a load consisting of one or more connections (threads) executing SQL statements simulating a specified number of users, for example 5, 10, 15, 20 and so forth.. The load generator sends a sequence of SQL statements during an experiment being conducted by the test client, continuously until the experiment is over. A set of SQL statements from a typical database application is chosen and supplemented so that the SQL statements are non- updating, using the techniques noted above. Preferably, the SQL statements used for load generation differ from those used by the test client, so that during an experiment the test client does not experience an artificially low response time through results having being cached as a result of the same statements having been used already by the load client.

The intention is to simulate load in a realistic manner. Thus, the load statements are selected randomly and delays are introduced between statements being transmitted to the database. Preferably there is a library of possible load statements available to the load client, which are selected randomly by the software running on the load client. However, where any statement changes the database, there will also be selected a paired statement to undo the effect.

To simulate the wait times that occur between queries in normal use of a database, a delay is selected randomly between each statement, for example from 1 second to several seconds. The possible wait times are selected in accordance with a normal distribution so that the wait times most commonly experienced in normal use will, on average, be used more frequently than wait times only experienced rarely in normal use, in accordance with the normal distribution.

Database Server Module.

The third module is a component running on the database server 1, controlling the database execution. This component starts and stops the DBMS between experiments, and control the DBMS initialisation files in accordance with the predetermined tests. In the preferred arrangement, there are stored a number of database configuration files, corresponding to the number of different configurations used in the experiments. Once the database is stopped, the existing configuration file is replaced by the new configuration file corresponding to the next experiment.

In one embodiment, each experiment to be run by the test client has a unique ID, and the corresponding database configuration file is associated with the same ID. The system is controlled so that the appropriate configuration file is loaded for each experiment.

An alternative to loading a different configuration file for each experiment, would be to send commands to change the configuration file dynamically.

It is necessary to synchronise the events on the test client, database server and load client. This could be done by means of software running on any of them or on separate data processing apparatus communicating with them, but in the preferred embodiment, preferably control of the operations is effected by software on the test client.

A suitable sequence of operations is described with reference to Figure 2, by way of example only. In this particular example, operations are controlled from the test client software module 8, which has access to a store 9 of experiments in the design matrix. Each experiment has an ID, and in the design there are associated values for the various database configuration parameters. The combinations of parameter values are set out in configuration (oraini) files which are held on the database server in a store 12 and accessed by the server module 11. The files are linked to the experiment LD's. The server module 11 communicates with the test client module 8, and controls stopping and starting of the DBMS 3, and selection of the appropriate configuration file.

There is also provided a load client software module 13 which communicates with the DBMS 3 and also receives instructions from the test client module. The load client module has access to a store of load SQL statements 14 a and delay generator 15.

Initially, the test client module 8 instructs the server module 11 to select the appropriate configuration file for the first experiment in the design, and to start the DBMS. The test client module waits for an appropriate interval, and then checks that the DBMS is running. Once that has been confirmed, the test client module instructs the load client module to start placing the DBMS under the load determined for the experiment design, which may be zero in some cases. For this example, it will be assumed that the experiment design is to be put into effect with a load of 15 simulated users (plus, of course, one user in the form of the test client). The load client module then continuously selects sequences SQL load statements from the store 14 at random, and sends them to the DBMS using 15 threads to simulate 15 users. The sequences are interspersed with delays selected at random by the delay generator 15 in accordance with a normal distribution as described above. After a suitable delay or positive confirmation that the DBMS is under load, the test client module 8 sends the predetermined test SQL statement sequence 10, which may have 250 statements, 50 times to the DBMS, measures the response times and stores them in the store 16, associated with the ID for the first experiment in the design. The average or median of the 50 response times is calculated and stored, together with other statistical information if desired, such as the standard deviation.

The test client then instructs the load client 13 to cease, and the server module 11 to stop the DBMS. The server module is then instructed to select the configuration file for the next experiment in the design and to re-start the DBMS. The sequence then continues, with the response times being recorded for the new combination of configuration parameters. This continues until all experiments in the design have been carried out.

The operation is entirely automatic. The experiment design can be repeated for different load levels, set up in the test client module, which instructs the load client module to simulate the appropriate load. The operation can be left unattended, to run overnight or at a weekend for example.

The results are analysed using multivariate statistical methods. The final result of the multivariate statistical analysis is a set of important parameters for reducing the response time for the actual systems. These parameters may stem from main factors in the analysis or from interactions among the main factors.

For reference, a series of experiments is conducted with the parameters fixed at their original values, and under various loads. Further experiments for reference purposes could use parameters whose values have been chosen in accordance with alternative tuning techniques.

To optimise the values of the parameters in accordance with the present invention, a first experiment is designed to identify parameters having a significant effect on database performance. A two level reduced factorial design with 15 factors and 64 experiments is used. For fifteen factors, or k = 15, the design uses 2 ^k"9 , a 1/512 fraction, giving 2⁶ = 64 experiments. This design has resolution IV and main effects are confounded with 3 factor terms and higher.

The design matrix was then set up, with the 15 parameters arranged in columns, and the rows being each experiment. The minimum value of a parameter is represented by -1, and the maximum by 1. Table 2 shows the design matrix, created using standard DOE techniques for 15 factor 2 ^k"9 fractional factorial design.

Table 2

Centre points and replicates were added to the design, making a total of 70 experiments. Replicates were provided by randomly selecting experiments and repeating them up to 4 times.

When running an experiment, the appropriate maximum or minimum value for each parameter was set in the ora.ini file, stored for access by the server module.

It is normal to describe experimental data by forming a mathematical relationship between the factors and the independent variable(s) such as the response time. A typical equation for these factors may be of the form:

(1) (response) b₀ + (an intercept or average) bι xι + bι xι + + b_n x_n + (linear terms) bn ι xι + + (quadratic terms) bι₂ xι x₂+ (interaction terms)

The " y" is because the equation estimates its value and is unlikely to give an exact value. Equation (1) is also called a regression equation and bo, bi .. are called regression coefficients. The size and sign of these coefficients indicates the relative importance of the factors x_1; x_2j x_t x₂ in the modelling of the response.

The 70 runs with different parameter settings and the measured response times, made up a result matrix which was analysed using the multivariate method. The result of the analysis is a regression equation similar to equation (1). When using the response time as a dependent variable in a multivariate analysis, there is the possibility of using either the average value (or the sum) or the median value. Due to the fact that a database tends to have some internal activities which may influence the measured response time, using the median value is considered to be the best solution.

To establish that the experimental results are accurate, the results for the replicates are analysed. In one set of experiments, the results were as in Table 3.

Table 3 Replicates - Average Response Times

This standard deviation of 0.01 for load 0, is satisfactory. Load 15, that is 15 simulated users, shows a standard deviation of 0.07 (or 1%) and is also satisfactory. From this it can be concluded that the variation shown in the various runs, is due to the initialisation parameter settings in the ora.ini files and the load. The median response times for the 70 runs were plotted and analysed. Part of the plot is shown in Figure 3, indicating that the results seemed to be divided into two sets, the shaded columns with long response times and the unshaded columns with short response times. Analysis revealed that most of the runs with the shaded columns all had the value of P4 set to -1, its minimum, and most of the other runs had P4 set to 1, i.e. the maximum value.

The assumption that one parameter is dominating the response time analysis is verified by a multivariate analysis. A normal plot (median, 2 component) of probability against regression coefficient (median) as shown in diagrammatic form in Figure 4 verifies the fact that the system only has one significant factor, P4.

An important tool in detecting significant factors in a multivariate design is the use of Normal plot. The basic principle of normal plots is that if a series of numbers is randomly selected they will often form a normal distribution. The Normal plot is a graphical tool to judge the Normality of the distribution of the sample data. If the sample data are near a Normal distribution, the plot will be near to a straight line. Data that deviates from a straight line indicates significant factors in a design. In Figure 4, the oval to the right indicates the region where values for all but one of the parameters and the interaction terms lie, and there is shown a straight line based on those values. The value for P4 deviates significantly from this region. It will be appreciated that in some cases, other parameters could also be identified in this analysis, for example being in the dotted region around P4.

In the present embodiment, the analysis of the results involves human intervention to identify the (or each) parameter having a significant effect on database performance. However, this step could be automated using statistical software techniques. For example, software used to create a Normal plot will try to plot a straight line through the values and will reject any result that prevents this being done. The rejected result will be the parameter that has a significant effect. A second set of experiments was used to enable separate optimisation of the parameter P4, keeping all the other parameters at the same type of values, in one set of runs their minimum and another their maximum, while the parameter P4 was given various values from its minimum 2 to its maximum 64.

Figure 5 shows the median response time for P4 varying from 2 to 64. From this, it was determined that the optimal value for P4 was in the range 16 to 32, possibly 12 to 32, where there is little effect on the response time, with the actual optimised value being 16. This was for load 0, that is no additional activity on the server but the results were verified for other loads. Based on this, the minimum / maximum levels for the parameter P4 for use in further experiments were changed from [2, 16] to [16,32].

Again, analysis of the results from the set of experiments could be automatic, and the experiments could be carried out automatically once the parameter has been identified automatically after the first experiment design.

With these revised values for P4, the 70 experiments in the first design were run again. This time, analysis of the results and a Normal plot established that parameter P2 had a significant effect. A separate optimisation on the parameter P2 was performed in accordance with the second set of experiments. Keeping all the other parameters on their minimum and maximum level, the parameter P2 was varied from 16K to 64K. Based on the plotted results, it was determined that the optimal value for P2 was in the range 20K to 40K, over which there is little effect on the response time, with the actual optimised value being 22K. Based on this, the minimum / maximum levels for the parameter P2 for use in further experiments were changed from [16K, 64K] to [20K, 40K].

With these revised values for P4 and P2, the 70 experiments in the first design were run again. This time, analysis of the results and a Normal plot established that parameter P14 had a significant effect. A separate optimisation on the parameter P14 was performed in accordance with the second set of experiments. Keeping all the other parameters on their minimum and maximum level, the parameter P 14 was varied from 128K to 2560K. Based on the plotted results, it was determined that the lowest response time, was reached for P14 at its maximum of 2560K.

With only one significant variable, P14, increasing PI 4, decreases the response time. From the literature, it was found that the parameter P14 should be 1% of the System Global Area (SGA). The SGA of the Oracle database server installation in this embodiment is 256 MB (half of the total memory on the server), and the block size is 4096 bytes. Accordingly, further investigations were performed to decide if 2560K is the optimal value for the parameter P 14, as the optimal value might be larger. A separate optimisation on the parameter P 14 was performed, with eleven experiments keeping all the other parameters firstly on their minimum level and then at their maximum level, while the parameter P14 was varied in steps between 128000 to 4096000. The experiments were performed with a simulated load of fifteen additional connections. It was determined that an optimal value for the parameter sort area size was indeed 2560000.

Thus the experiments determined that the optimal, maximum and minimum values of three parameters should be as set out in Table 4.

Table 4

The next step is to carry out a Full Factorial Design on these three parameters, P4, P2 and P14 using the optimised maximum and minimum values. A full factorial design with three factors, gives eight experiments. The remaining 12 parameters were set to their minimum values. These experiments were run with respectively load 0, load 5 load 10 and load 15. Running the design with various loads, gave comparable results. Only the results from the experiments run with load 15 are considered below.

A two component model with median response time as the dependent variable, explains 85.1% of the variance.

Figure 6 shows the regression coefficients. The largest regression coefficient is P2. It can also be observed that there are contributions from the interaction terms and the term P lxP3 is the largest.

Figure 7 shows an interaction graph for the interaction term P2 x PI 4. The points on the upper line (circle) are for P 14 at the minimum level, while the lower points (square) are for P 14 at the maximum level. From this, it can be concluded that the minimum response time is achieved when both P2 and P 14 are at their maximum level.

In summary, an embodiment of a method in accordance with the invention has identified, and performed individual optimisation of, three parameters P4, P2 and P14 in this instance. The steps could be carried out automatically using suitable software.

EXAMPLE

The experiments and analysis described in a somewhat generic way in respect of the above embodiment were put into practice using a real database running under Oracle In one example of implementing the invention, parameters considered to be important were selected from those possible in the ora.ini files. The parameters were selected using various sources/criteria, such as Statspack, Quest (Database Analysis), Literature, Tests. Although some of these sources gave conflicting advice, 30 parameters from ora.ini were selected and sorted according to their relevance, as shown in Table 5. Initial minimum and maximum levels for the parameters were selected partly based on the sources above and partly on values already in use.

Table 5

The 30 parameters were split into two. The first 15 (considered to be potentially the most relevant) were placed in a first group, and the remaining 15 parameters in a second. These were given initial minimum and maximum values, taking into account existing values used and information from the above sources.

The first design of experiments was carried out as in the embodiment described above with the parameters PI to P 15 being those listed above. P2 therefore equates to db_block_buffers, P4 to db_file_multiblock_read_count, and P14 to sort_area_ size. The response time varied from 4 to 7.5 seconds. There were some anomalies around experiments 60 and onward, and this was probably due to other activities on the server such as backup operations.

The first set of experiments revealed that that the system only has one significant factor, db_file_multiblock_read_count. The parameter db_file_multiblock_read_count was originally set to vary within the range from 2 to 16. The initial value used in practice was 16. Using Statspack, the recommendation was to reduce this value while software from Quest identifed this parameter to be important, but no recomandation was given. A separate optimisation on the parameter db_file_multiblock_read_count was performed, as described for P4 above, keeping all the other parameters on their minimum and maximum level while the parameter db_frle_multiblock_read_count was varied from 2 to 64.

The optimal value for db_file_multiblock_read_count was found to be in the range 16 to 32 (or possibly from 12). This result is for load 0, that is no additional activity on the server. These results aware verified for other loads.

Based on this the minimum/maximum levels for the parameter db_fιle_multiblock_read_count were changed from [2,16] to [16,32] and thereby removing this parameter from the design. The first experiment design was run again, and this time the parameter db_block_buffers was identified as significant, as with P2 in the embodiment above. The average response time varied from about 4.5 to 7 seconds. Analysis of the replicates showed standard deviation of respectively 0.31 and 0.14, which was less then 5% on average and satisfactory.

A separate optimisation on the parameter db_block_buffers was performed, as with P2. Keeping all the other parameters on their minimum and maximum level, the parameter db_block_buffers was varied from 16K to 64K. Based on the results the minimum/maximum level for the parameter db_block_buffers was changed from [16K, 64K] to [22K, 32K] and thereby removing this parameter from the design. There are the same trends, but the average response time was reduced considerablely . The same experiment for a load of 15 connections gave a corresponding graph, but the minimum value for the average response times was then for db_block_buffers = 22K

The db_block_size for the database is 4096 bytes and the available memory is 512 MB, that is, db_block_buffers = 22K, consitutes 90 MB or 17 % of the total memory.

The book Oracle Performance Tuning (Tips & Techniques), claims that a good choice for this parameter is 15-20 percent of the total memory, but can be increased to 30 - 50 percent if the number of users is less then 100 and the memory is available.

Using the parameter settings achieved so far, with optimized values of db_ftle_multiblock_read_count and db_block_buffers, the search for more significant parameters continued. As before, a 2 level reduced factorial design with 15 factors and 64 experiments was used. This design has resolution IV and main effects are confounded with 3 factor terms and higher. Centre points and replicates were added to the design, making a total of 84 experiments. Replicates are important in order to control the reproducibility of the experiments, so randomly selected experiments were selected and repeated up to 4 times (see the above evaluation of the reproducibility). It was found that the average response time for the new settings, varied from 2.88 to 3.20. Looking closer at the various replicates, reproducibility was very good with a standard deviation of 0.01. The same inspection running the experiments with 15 additional connections (load), shows some more variation among the replicates. The average response time for the new settings, varied from 3.78 to 5.01. Looking closely at the various replicates, reproducibility was very good, with a standard deviation of 0.06.

The average response time increases slightly with increasing numbers of additional connections. With load 30, the average response time varies from 4.31 seconds to 5.48 second, that is, close to 20%. This variation is much larger than the variation observed between replicates, so there is a real variation here.

A three component model, explains 87%) of the variance in the response value. Analysing the results from these experiments, as in the embodiment described above, it was discovered only one significant parameter existed, namely sort_area_size. As with PI 4, an analysis was carried out. A one factor plot of sort_area_size, with other parameters on a minimum was considered. Keeping all the other parameters on their minimum level, the median response time varied from 3.3 seconds to 3.1 seconds. The lowest response time, was reached for sort_area_size at its maximum. In the current design, sort_area_size is varied from 128000 to 2560000 bytes.

With only one significant variable, sort_area_size, increasing sort_area_size, decreases the response time. From the literature, it is found that the parameter sort_area_size should be 1% of SGA and sort_area_retain_size should be the same.

The System Global Area (SGA) of the Oracle server installation is 256 MB (half of the total memory on the server), and the block size is 4096 bytes.

For a load of 15 connections, the minimum value for the average response times was for db_block_buffers = 22K. The db_block_size for the database was 4096 bytes and the available memory 512 MB. That meant that db_block_buffers = 22K, consituted 90 MB or 17 % of the total memory.

Further investigations were performed into the optimal value for the parameter sort_area_size, as it might be larger. As described for P14 it was determined that an optimal value for the parameter sort_area_size is 2560000.

Thus the experiments determined that the optimal, maximum and minimum values of three parameters should be as for those set out in Table 6 below, which are as determined also for Table 4 and are repeated here for convenience.

Table 6

The next section concerns a Full Factorial Design on these three factors, namely db_file_multiblock_read_count, db_block_buffers and sort_area_size. The procedure was as described for parameters P2, P4 and P 14 earlier.

From all the runs, the lowest observed response time was 2.58 seconds for zero load (with minimum values on the remaining variables). For load 15 the various values are shown in Table 7. Table 7

A two component model with median response time as the dependent variable, explains 85.1% of the variance.

The regression coefficients of the model correspond to those of Figure 6, with substitutions for P2, P4, P14. The largest coefficient is db_block_buffers. It can also be observed that there are contributions from the interaction terms, e.g., the term 1x3 (db_block_buffers x sort_area_size) is the largest. The interaction graph for db_block_buffers x sort_area_size is equivalent to that in Figure 7, again with substitutions for P2 and PI 4. The upper points are sort_area_size at the minimum level while the lower points are for sort_area_size at the maximum level. From this, it can be concluded that the minimum response time is achieved when both db_block_buffers and sort_area_size are at their maximum level, and db_file_multiblock_read_count should be set to the minimum value. That is, there was found a lower value of db_frle_multiblock_read_count than was earlier found to be an optimal value. The parameter db_file_multiblock_read_count was varied from 8 to 16, so an optimal value of 12 is acceptable.

Considering the average response time while varying db_block_buffers from 16k to 64k, it was seen that the parameter db_block_buffers did not show a linear behaviour in the region 20000 to 40000. Indeed, the response time did not vary when the db_block_buffers is varied from 22000 to 40000. That is, 22000 is probably a better choice than 40000.

Table 8

In summary, the method in accordance with the invention has performed individual optimisation of three parameters, db_file_multiblock_read_count; db_block_buffers; and sort_area_size.

For db_file_multiblock_read_count the first runs using a setup from the original database gave:

Table 9

For db_block_buffers the runs using a setup with the optimised db_frle_multiblock_read_count gave: 38

Table 10

For sort_area_size, the runs using a setup with the optimised db_file_multiblock_read_count and db_block_buffers gave:

Table 11

There is a total of about 25 % reduction in minimum response time from 3.76 seconds to 2.91 seconds.

As described before, as the number of factors (parameters) increases, the number of experiments increases exponential in a factorial design. That was the reason why only 15 parameters were optimised in the first step. With these fifteen parameters on their optimal level, it is possible to proceed with the next 15 parameters. Those are dealt with in an equivalent way. It can be seen from the embodiment and the example that using a system in accordance with the invention allows database configuration parameters to be optimised, in some cases in a way that would not be predicted by conventional knowledge.

A further set of experiments was carried out on a database running originally on a server having a single processor. Using the original configuration file, it was found that adding a second processor could reduce the response times by about 60%. Retaining a single processor, and the optimising the configuration parameters by a method in accordance with the invention gave a reduction in response times of about 80%, a considerable improvement over the hardware option.

An advantage of using multivariate design is its efficiency. The relative efficiency of factorial experiments increases as the number of variables increases. In other words, the effort saved by such internal replication becomes even more significant as more factors are added to an experiment. A factorial design makes it possible not only to determine the main effects of each variable, but also to estimate the interaction (i.e., synergistic effect) between the two factors, a calculation that is impossible with a one- factor-at-a-time experiment design.

DOE methodology eliminates the need to directly test every possible factor combination- thereby saving experimenters time and money. It also finds interactions among factors, something OF AT testing does not. A designed experiment is a more effective way to determine the impact of two or more factors on a response than an OF AT experiment, where only one factor is changed at one time while the other factors are kept fixed, because it requires fewer resources (experiments, time, material, etc.) for the amount of information obtained. Using more observations to estimate an effect results in higher precision (reduced variability). For example, for full and fractional factorial designs, all the observations are used to estimate the effect of each factor and each interaction (a property of hidden replication). The interaction between factors can be estimated systematically. Interactions are not estimable from OF AT experiments. Engineers not using designed experiments often perform a hit-and-miss scattershot sequence of experiments from which it may be possible to estimate interactions, but they usually do not estimate them.

Claims

1. A method of optimising the values of configuration parameters of a database management system hosting a target database, using a data processing system comprising a database server on which the database management system resides, a test client in communication with the database server, and a load client in communication with the database server; wherein :

(a) a number of the configuration parameters are selected, each configuration parameter having a value within a range of maximum and minimum values, and a design of experiments is established defining a number of experiments in which there are different combinations of the maximum and minimum values for the selected parameters;

(b) the test client is configured by a test client module to send a predetermined sequence of test commands to the target database for each experiment defined by the first design of experiments, and to record a measure of the database performance for each experiment in the design of experiments;

(c) the load client is configured by a load client module to send a sequence of load commands to the database management system so as to simulate a load on the database management system during each experiment in the design of experiments;

(d) the arrangement is such that the target database is the same at the start of each experiment in the design of experiments;

(e) the database server is configured by a server module with instructions to control the database management system so that at the start of each experiment in the design of experiments the configuration parameters of the database management system are in accordance with the design of experiments; (f) a multivariate regression analysis is carried out on the results of the experiments in the design of experiments, to identify at least one parameter which has a significant effect on target database performance; and

(g) the value of at least one identified parameter is optimised .

2. A method as claimed in claim 1, wherein at least one identified parameter is provided with optimised minimum and maximum values, and steps (a) to (g) are repeated with the optimised minimum and maximum values being used in the experiments in the design of experiments.

3. A method as claimed in claim 1 or 2, wherein each sequence of test commands includes paired commands such that the target database is unchanged after the sequence has been sent.

4. A method as claimed in claim 1, 2 or 3, wherein each sequence of test commands is identical.

5. A method as claimed in any preceding claim, wherein the load client simulates a number of virtual load users accessing the database management system.

6. A method as claimed in claim 5, wherein the number of virtual load users is selectable.

7. A method as claimed in claim 5 or 6, wherein a separate thread is established for each virtual load user.

8. A method as claimed in any preceding claim, wherein at least some of the load commands are sent to the target database.

9. A method as claimed in claim 8, wherein each sequence of load commands sent to the target database includes paired commands such that the target database is unchanged after the sequence has been sent.

10. A method as claimed in any preceding claim, wherein at least some of the load commands are sent to a different database hosted by the database management system

11. A method as claimed in any preceding claim, wherein the sequence of load commands is constructed by selection at random from a predetermined selection of commands.

12. A method as claimed in any preceding claim, wherein wait times are interspersed between load commands.

13. A method as claimed in claim 6, wherein the wait times are selected randomly in accordance with a normal distribution of wait times.

14. A method as claimed in any preceding claim, wherein the design of experiments is a fractional factorial design.

15. A method as claimed in any preceding claim, wherein a parameter identified as having a significant effect on target database performance is optimised by means of carrying out a number of experiments in which there are different combinations of :

(i) values of an identified parameter between and including its maximum and minimum values; with (ii) values of the remainder of the selected parameters, in each combination all having the same type of value.

16. A method as claimed in claim 15 , wherein in each experiment combination , each of the remainder of the selected parameters has its minimum value or each of the remainder of the selected parameters has its maximum value.

17. A method as claimed in claim 16, wherein for any of the remainder of the parameters which has already been optimised, the optimised minimum or the optimised maximum value is used.

18. A method as claimed in any preceding claim, wherein a subset of the selected parameters is established, being parameters which have been identified as having a significant effect of database performance in the experiments, and which have been given an optimised range of values, and wherein a full factorial design of experiment is carried out using the subset of parameters, and the results analysed to further optimise at least one of the subset of parameters.

19. A data processing system for providing data for use in optimising the values of configuration parameters of a database management system hosting a database; the data processing system comprising a database server on which the database management system resides, a test client in communication with the database server, and a load client in communication with the database server; wherein :

(a) the test client is configured by a test client module to send a predetermined, identical sequence of commands to the database on a number of experiment instances specified by a design of experiments, the sequence including at least one command which changes the database and which has a paired command to counteract its effect, the arrangement being such that the sequence of commands leaves the database unchanged at the end of each experiment in the design of experiments; the test client application further configuring the test client to record the response time on each experiment instance that the sequence of commands is sent; (b) the load client is configured by a load client module to send a predetermined sequence of commands to the database management system so as to simulate one or more levels of load on the database management system whilst the test client sends commands and records response times; and

(c) the database server is configured by a server module with instructions to control the database management system to stop after responding to the sequence of commands from the test client on an experiment instance and, in accordance with the design of experiments, to set the configuration parameters of the database management system for the next experiment instance and to re -start the database management system.

23. A method of optimising the values of configuration parameters in a data processing system in which a number of tests are conducted with various combinations of parameter values and the results are analysed using a multivariate analysis technique so as to determine the optimum values of one or more of the parameters with respect to performance of the data processing system.