Broughton, P

Burger, Albert Georg

Dempster, Euan

King, Peter John Beaufoy

Taylor, Hamish

Tomov, Neven

Williams, Howard

English

Heriot Watt Pure

Some Results from a New Technique for Response Time Estimation in Parallel DBMS

    Heriot-Watt University Research Gateway                                                          Heriot-Watt UniversitySome Results from a New Technique for Response Time Estimation in Parallel DBMSTomov, Neven; Dempster, Euan; Williams, Howard; Burger, Albert Georg; Taylor, Hamish;King, Peter John Beaufoy; Broughton, PPublication date:1999Link to publication in Heriot-Watt Research GatewayCitation for published version (APA):Tomov, N., Dempster, E., Williams, H., Burger, A. G., Taylor, H., King, P. J. B., & Broughton, P. (1999). SomeResults from a New Technique for Response Time Estimation in Parallel DBMS. 713-721. Paper presented atProceedings of the Seventh International Conference, .General rightsCopyright and moral rights for the publications made accessible in the public portal are retained by the authors and/or other copyright ownersand it is a condition of accessing publications that users recognise and abide by the legal requirements associated with these rights.If you believe that this document breaches copyright please contact us providing details, and we will remove access to the work immediatelyand investigate your claim.Author DetailsNeven Tomov, Euan Dempster (euan@cee.hw.ac.uk, correspondingauthor), M. HowardWilliams (presenting author), Albert Burger, Hamish Taylor, Peter J. B. KingDepartment of Computing and El. EngineeringHeriot-Watt UniversityEdinburghEH14 4ASUKPhil BroughtonInternational Computers LimitedHigh Performance TechnologyWenlock WayWest GortonManchesterM12 5DRUKSome Results from a New Technique forResponse Time Estimation in Parallel DBMSNeven Tomov1, Euan Dempster1, M. Howard Williams1, Albert Burger1,Hamish Taylor1, Peter J.B. King1, and Phil Broughton21 Department of Computing and El. Engineering,Heriot-Watt University, Edinburgh EH14 4AS, UK[neven,euan,howard,ab,hamish,pjbk]@cee.hw.ac.uk2 International Computers Limited,High Performance Technology,Wenlock Way, West Gorton, Manchester M12 5DR, UKpb@wg.icl.co.ukAbstract. The need for tools for performance prediction of paralleldatabase systems is generally recognised. One such tool which has beendeveloped (Steady) is based on analytical techniques to obtain a rapidestimate of performance. The approach to predicting response time in-volves a heuristic approximation coupled with standard queueing so-lutions. This paper reports on preliminary results for both maximumtransaction throughput and response time obtained in comparing thisapproach against actual measurements.1 IntroductionThe growth of commercial interest in the potential and use of parallel comput-ers for running relational database applications has been noted by Norman andThanisch [1]. Such applications oer rich amounts of exploitable parallelism thatdatabase management systems running on parallel platforms can take advantageof. Several well known commercial DBMSs such as Oracle [2], Informix [3], In-gres [4], Sybase [5] and DB2 [6] are now available on popular and dedicated SMPand MPP machines.The ability to predict the performance of parallel relational databases isimportant for their application sizing, capacity planning, data placement andperformance tuning. To assist in these processes, an analytical technique for es-timating the performance of parallel relational database systems has been devel-oped. The technique has been incorporated in a tool [7,8] which can rapidly esti-mate how relational database applications will behave without running lengthysimulations or actually trying them out.Part of the performance estimation technique involves prediction of queryresponse time, which is a non-trivial problem. This paper reports on experimentsin which the results of the technique are compared with actual measurementsobtained from a parallel platform. The experiments are performed as part of theprocess of validation of the analytical approach.2The remainder of this paper is organised as follows. Section 2.1 briey de-scribes the hardware platform (the ICL GoldRush MegaSERVER [9]) and DBMS(Informix XPS [3]) experimental setup. This is followed by a description in sec-tion 2.2 of a subset of the queries and data used in the comparisons. Section 2.3provides a brief introduction to Steady { the parallel DBMS performance pre-diction tool. The response time prediction technique incorporated in Steady isdiscussed in section 2.4. Section 2.5 explains the methods used in obtaining per-formance gures. Some comparison graphs of actual vs. predicted transactionthroughput and query response time are given in section 3. Finally, section 4provides a summary and conclusions.2 Obtaining Performance MeasuresThe ICL GoldRush MegaSERVER [9] is a parallel platform developed as a back-end database server to host several dierent database systems (Ingres, Ora-cle, Informix). Steady is a performance prediction tool designed specically forshared-nothing parallel architectures [10] and calibrated for the ICL GoldRushplatform running Informix XPS.2.1 GoldRush and Informix XPSThe basic GoldRush hardware architecture consists of a number of ProcessingElements (PEs) and Communication Elements (CEs) linked by a high speedDeltaNet network. Each PE is connected to its own disc storage subsystem andruns a UNIX operating system and DBMS code. A CE provides external linksfor GoldRush to clients via LANs. The particular GoldRush setup used here has1 CE, 8 PEs, 6 discs per PE and a cache of 16MBytes on each PE.This type of architecture is an ideal platform for the Informix Extended Par-allel Server(XPS) [3]. It contains a set of internal components called co-serverswhich are installed on each of the PEs of GoldRush. A co-server provides auser entry point to GoldRush on a given PE. Locks on data items and all dataprocessing are managed locally within each co-server. Where deemed suitable,single queries are broken into subtasks and processed concurrently by threadswithin a single co-server and across co-servers. Data can be partitioned acrossdiscs and PEs so that parallel I/O operations can take place. Additionally, cer-tain partitions, known to be irrelevant for a particular query, can be skippedaltogether.2.2 Tables and QueriesThe experiments detailed in this paper are carried out on a subset of the AS3APbenchmark [11] tables. In particular, a number of variations of the uniques rela-tion are used; the attributes of uniques are given in Table 1.There are four uniques relations used. The rst is called uniques30k. It has30000 tuples and is placed on one disc of one PE (PE 7). The second relation3Table 1. Attributes of AS3AP uniques relationkey integer(4) decim numeric(18,2)int integer(4) date datetime(8)signed integer(4) code char(10)oat real(4) name char(20)double double(8) address varchar(20)is called uniques80 and has 80 tuples which are also stored on one disc of PE 7.The other two relations are uniques270k and uniques540k. They contain 270000and 540000 tuples respectively and are placed across all eight PEs. Each of thesetwo tables is fragmented into 8 fragments by a simple hash function on the keyprimary key attribute with one hash fragment placed on a single disc of eachof the PEs. Each tuple has a unique value for attributes key and int. Attributesigned, however, is modied from the benchmark specication so that tupleshave signed values in the range 1 to 10 with an equal number of tuples for eachvalue.A number of experiments have been performed and two will be presented hereas typical. The rst query performs a simple aggregation of a uniques relation,looking for the maximum and minimum values of the int attribute, for thosetuples whose signed value is not 1. Thus the aggregation is carried over 9=10thsof the uniques tuples. Tables uniques30k and uniques270k are used:select max(int), min(int)from uniques30k (uniques270k)where signed not in (1)The second query nds the maximum value of the int attribute from the resultof a join of uniques80 with uniques540k where the int values are the same. Thetuples of uniques80 are taken from uniques540k so that the size of the resultingjoin is 80 tuples:select max(uniques80.int)from uniques80, uniques540kwhere uniques80.int = uniques540k.intAlthough this could be handled with a much simpler query, this form was usedin order to perform a join of this type and which would facilitate measurementsof this operation.Table uniques30k is placed on one PE and queries involving only it are notparallelised. Those involving tables uniques270k and uniques540k, however, areperformed in parallel by all co-servers containing the table data. The secondquery employs a hash-join algorithm, which builds a hash table across all co-servers using uniques80, and subsequently probes this with the tuples fromuniques540k.42.3 SteadySTEADY [7,8] (System Throughput Estimator for Advanced Database sYstems)is an analytical tool for performance estimation of parallel relational databasesystems. It has been designed to handle a range of dierent platforms althoughthus far it has only been calibrated against the GoldRush platform. Apart froma graphical user interface, it consists of ve major modules:1. The Proler is a statistical tool primarily responsible for generating baserelation proles and estimating the number of tuples resulting from dataoperations;2. DPTool is used to generate various data placement schemes for a paralleldatabase using dierent strategies;3. The Modeller is responsible for producing the prole of the tasks requiredfor a particular benchmark or query with assistance from the Proler, queryparalleliser and the cache model [12];4. The Evaluator takes the task proles and produces resource usage proles,maximum system throughput values and system bottlenecks;5. The Response Time Estimator takes the resource usage proles and fromthese estimates response times.Relations are partitioned into fragments by DPTool using declustering meth-ods such as hash. These fragments are then allocated to the PEs using placementmethods such as size, bubba, hua, etc.. These relation fragments can then be as-signed to the discs of PEs according to various methods. Based on the generateddata placement and the chosen DBMS architecture, a prole of the tasks re-quired for a particular benchmark or query is generated by the Modeller whichincludes an estimation of disk I/O requirements in terms of the number of pagesread and written to each disk. This is derived on the basis of the estimated cachehit ratios for the particular relation fragments in the cache of each PE and iscarried out by the cache model. The task proles are then converted by the Eval-uator into resource utilisation proles, from which the system bottlenecks andthe maximum throughput rate are determined. This gives the user an indicationof the upper limit of system capacity in terms of throughput. The resource us-age blocks are then fed into the Response Time Estimator which estimates theresponse time of the query, as described briey in the following section.2.4 Response Time TechniqueThe input to the Response Time Estimator module of Steady consists of theoriginal query task proles represented as patterns of resource consumption in-curred on the various GoldRush resources such as CPUs, discs, and interconnect.This representation is equivalent to an open multi-class queueing network [13].In general, however, the networks obtained are not in product form, due to thenon-exponential service times required at each resource. This means that ex-act solutions for quantities such as the mean response time cannot be obtainedanalytically.5To overcome this problem, a heuristic is proposed in [14]. It species a pro-cedure for labeling each resource in the queueing network as either an M/M/1or an M/G/1 resource. The decision is based on knowledge of the resource'sutilisation and relative visit ratio [13]. With each resource labeled, the networkcan be solved to obtain the mean response time of individual queries.2.5 Taking MeasurementsCalibration of the models required running a selection of queries, using the par-allelised query execution plans to determine the way in which they were brokendown by the system, and reconciling these against measurements obtained usingthe Informix XPS performance measuring tool onstat.Once this was complete, a transaction generator was created to emulate aparallel database system workload with many users independently querying thedata. It was used to re single-query transactions from the communication ele-ment (CE) to a given co-server at a specied rate. Two generators were created.The rst red transactions with constant inter-arrival times and was used todetermine the arrival rate for which the maximum throughput of the machinecan be achieved.The second version of the transaction generator red transactions with ex-ponentially distributed inter-arrival times and was used to obtain transactionresponse times.Figure 1 shows the response times for query 1 on uniques30k. The query isred 100 times with exponentially distributed inter-arrival times at a rate of 0.04transactions per second which corresponds to 11% of the predicted maximumthroughput.3 ResultsFigures 2 and 3 show the throughput results for query 1 with the 30K tupletable and the 270K tuple table, respectively. Note that since the 270K tableis spread across 8 nodes (i.e. each node has approximately 30375 tuples) theperformance achieved is similar to that for the 30K table which is placed on asingle node. Figure 4 shows the results for query 2. Similarly, Figures 5 and 6show the response time results for query 1 with the 30K tuple table and the 270Ktuple table, respectively. Figure 7 shows the response time results for query 2.The gures show that the maximum throughput achieved on GoldRush isslightly less than that predicted by Steady by about 13%. We believe that thisis due to additional operating system overhead which we were not able to isolateduring the calibration process. Similarly response times observed were slightlylarger than those predicted by Steady | less than 20% for arrival rates less than70% of maximum throughput (as predicted by Steady), and less than 30% forarrival rates less than 90% of maximum throughput.A number of other examples which have been investigated produce resultswhich are similar to those for the queries presented here. Error margins areroughly the same for the cases investigated so far.6Fig.1. Individual response times for 100 queries of type 1 with a table of 30K tuples.Query arrivals are Poisson with rate 0.04 (11% of predicted max throughput).4 ConclusionsThis paper has reported on experiments in which the results of an analyticaltechnique for performance estimation in parallel relational DBMS are comparedwith actual measurements obtained from a parallel platform. Both throughputand response time results are presented.The results are encouraging and generally the relative dierence between themeasured and predicted performance is less than 20%. The relative error in thepredictions is largest for arrival rates corresponding to a bottleneck resourceutilisation of 80% and above. In practice, however, one is not likely to load thesystem much beyond 70% utilisation { thus 80% is a reasonable upper limitfor performance prediction beyond which queues rapidly become unmanageable.The fact that Steady tends to predict lower response times and higher maximumthroughputs can be attributed to additional resource consumption by the OSwhich is presently not taken into account by the analytical model.7Fig.2. Throughput of query 1 with a table of 30K tuples.AcknowledgementsThe authors acknowledge the support received from the UK Engineering andPhysical Sciences Research Council (EPSRC) under the PSTPA programme(GR/K40345) and from the Commission of the European Union under theFramework IV programme (Mercury project). They also wish to thank ArthurFitzjohn and Monique Mitchell of ICL(Manchester).References1. Norman, M.G., Thanisch., P., Parallel Database Technology, Bloor ResearchGroup, Netherlands (1995) 1-5462. Oracle Corporation., Oracle7 Parallel Server Concepts & Administration, Release7.3, Oracle, Server Products, California, USA (1996)3. Informix Software Inc., Informix OnLine Dynamic Server administrator's guide(1994)4. The ASK Group Inc., ASK OpenINGRES database administrator's guide (1994)5. Sybase Inc., Sybase, http://www.sybase.com (1998)6. IBM Corp., DB2, http://www.software.ibm.com/data/db2 (1998)8Fig.3. Throughput of query 1 with a table of 270K tuples.7. Zhou, S., Williams, M.H., Taylor, H., Practical throughput estimation for paralleldatabases, IEE Software Engineering Journal (July 1996)8. Williams, M.H., Dempster, E.W., Tomov, N.T., Pua, C.S., Taylor, H., Burger,A., L u, J., Broughton, P., An Analytical Tool for Predicting the Performance ofParallel Relational Databases, submitted to Concurrency: Practice and Experience(1998)9. Watson, P., Catlow, G., The architecture of the ICL GoldRush MegaSERVER,Proceedings of the 13th British National Conference on Databases (BNCOD 13)Manchester U.K. (July 1995) 250{26210. Stonebraker, M., The case for shared nothing, Database Engineering 9(1) (March1986)11. Turbyll, C., Orji, C., Bitton, D., AS3AP: An ANSI SQL Standard Scaleable andPortable Benchmark for Relational Database Systems, The Benchmark Handbook,Second Edition (1993), Gray, J. (editor)12. Zhou, S., Tomov, N., Williams, M.H., Burger, A., Taylor, H.: Cache Modelling ina Performance Evaluator of Parallel Database Systems Proceedings of the FifthInternational Symposium on Modelling, Analysis and Simulation of Computer andTelecommunication Systems (January 1997) 46-5013. Molloy, M.: Fundamentals of Performance Modeling Macmillan Publishing Com-pany (1989)9Fig.4. Throughput of query 2 with a table of 540K tuples.14. Tomov, N.T., Dempster, E.W., Williams, M.H., King, P.J.B., Burger, A.: Ap-proximate Estimation of Transaction Response Time, to appear in The ComputerJournal10Fig.5. Response time of query 1 with a table of 30K tuples.11Fig.6. Response time of query 1 with a table of 270K tuples.12Fig.7. Response time of query 2 with a table of 540K tuples.

Informix Software Inc., Informix OnLine Dynamic Server administrator's guide

Server Products,

Sybase,

Some Results from a New Technique for Response Time Estimation in Parallel DBMS

Abstract

Similar works

Full text

Available Versions

Heriot Watt Pure

Heriot Watt Pure