Continuous resource monitoring for selfpredicting DBMS Dushyanth Narayanan

Continuous resource monitoring for self-predicting DBMS Dushyanth Narayanan 1 Eno Thereska 2 Anastassia Ailamaki 2 1 Microsoft Research-Cambridge, 2 Carnegie Mellon University Umar Farooq Minhas 08 Nov 2006 David R. Cheriton School of Computer Science University of Waterloo

Outline Problem: DBMS Administration p Solution: Resource Advisor p Resource Advisor Architecture p Experiments p Summary p Past Present & Future p Discussion/Q&A p 2

Problem: DBMS Administration p p Complex & expensive Key Task: Resource provisioning n n Vital to ensure Qo. S Current Techniques p p p Result: Over-provisioned DBMS n n n p Rules of thumb Concurrent OLTP loads (a nightmare) Wasteful Prohibitively expensive Tuning still a management issue Solution: Self-predicting DBMS 3

Solution: Resource Advisor (RA) p Goals: n Get quantitative answers to “what-if” questions from DBMS p Point predictions § “how will response time change if memory is doubled? ” p Trend forecasting § “what is the shape of memory vs. response-time curve? ” n Provide visualization of system performance 4

RA Architecture 5

RA Resource Monitoring p DBMS instrumentation n Insert check-points that Generate events e. g. Start. Request, End. Request, Suspend. Task, Buffer. Get, Disk. IOComplete, … , etc. p Helps track CPU, Memory and I/O resource usage p p Demand trace extraction n Hardware-independent workload demand trace Associate each event to its transaction Group transactions by type e. g. “new_order” 6

RA Visualization 7

RA Architecture 8

Buffer pool model p Input: n n p Predicts: n p Reference trace Buffer pool size # of blocking & non-blocking I/Os as a function of buffer pool size Implementation: n DB Cache simulator p p Globally shared buffer cache Least Frequently Used (LFU) eviction policy 9

Storage model p Input: n Storage parameters p n p # of I/O requests (buffer pool model) Predicts: n p #of cylinders, seek time, …, queue size etc I/O service time as a function of storage parameters Implementation: n Analytical model p p Shortest Seek Time First (SSTF) scheduling Assumes a random I/O request stream 10

Throughput prediction p Identify the bottleneck resource as one of n CPU, memory, I/O or client p System throughput = bottleneck resource throughput p T = Min { Tmax/io, Tmax/CPU, Tmax/workload } Topen for open loop nio, tio predicted by specified buffer pool &DBA storage model by inversely proportional to CPU speed respectively 11

Response-time prediction p p Response time depends on critical path = time spend on cpu + disk tresponse = t. CPU + tstorage t. CPU is measured predicted by buffer pool model tstorage depends on number of blocking I/Os per transaction and service time per I/O predicted by storage model 12

Experimental Setup p 2 variants of TPC-C workload (open, closed) p 10 GB MS SQL Server Database on Intel Xeon 2. 7 GHz Processor p 200 clients concurrently accessing DB p 64, 128, 256, 512, 1024 MB buffer size p CPU and disks unchanged 13

Experiments p Closed-loop throughput prediction p Saturation throughput prediction p Open-loop response time prediction p Resource advisor overheads n n Higher at lower buffer pool sizes Worse case CPU overhead 6. 2% (online) p n n Reduces to 1. 2% for offline 189 additional LOC required in SQL Server RA itself: 1150 LOC 14

Summary p Problem: n p Solution: n p How to over-come current issues and challenges in DB Administration (particular to resource provisioning)? DBMS should be self-predicting Implementation: n n Resource Advisor to enable self-prediction in DBMS Experimental evaluation 15

RA Past Present & Future p “Towards self-predicting systems: What if you could ask “what-if”? ” n p “Continuous resource monitoring for self-predicting DBMS” n p MASCOTS (Sept 2005) “Informed data distribution selection in a self-predicting storage system” n p DEXA (August 2005) January 2006 “Stardust: Tracking Activity in a Distributed Storage System” n SIGMETRICS/Performance ACM (June 2006) 16

Discussion / Q&A p The storage model & throughput prediction have recursive dependency for calculating response time tio and throughput T, the underlying assumption is that (T, tio) values will converge, what happens if they don’t ? p The number of I/Os are predicted by the buffer pool model by using a DB Cache Simulator, what effect would a different kind of simulator have with the overall performance of RA? How to choose? p RA has been specifically shown to work on TPC-C benchmark under some assumptions, can we really expect to get comparable results in general for other OLTP workloads? Is RA usable at all with other workloads? 17