1 / 19

What Should a DBMS Do?

What Should a DBMS Do?. Store large amounts of data Process queries efficiently Allow multiple users to access the database concurrently and safely. Provide durability of the data. How will we do all this??. Generic Architecture. Query update. User/ Application. Query compiler.

argo
Download Presentation

What Should a DBMS Do?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. What Should a DBMS Do? • Store large amounts of data • Process queries efficiently • Allow multiple users to access the database concurrently and safely. • Provide durability of the data. • How will we do all this??

  2. Generic Architecture Query update User/ Application Query compiler Query execution plan Execution engine Record, index requests Index/record mgr. Page commands Buffer manager Read/write pages Storage manager storage

  3. Query Optimization Goal: Declarative SQL query Imperative query execution plan: buyer  City=‘seattle’ phone>’5430000’ SELECT S.sname FROM Purchase P, Person Q WHERE P.buyer=Q.name AND Q.city=‘seattle’ AND Q.phone > ‘5430000’ (Simple Nested Loops) Buyer=name Person Purchase Plan:Tree of R.A. ops, with choice of alg for each op. Ideally: Want to find best plan. Practically: Avoid worst plans!

  4. Alternate Plans Find names of people who bought telephony products buyer buyer   Category=“telephony” Category=“telephony” (hash join) (hash join) prod=pname Buyer=name (hash join) (hash join) Product Person Buyer=name prod=pname Person Purchase Product Purchase But what if we’re only looking for Bob’s purchases?

  5. ACID Properties Atomicity:all actions of a transaction happen, or none happen. Consistency: if a transaction is consistent, and the database starts from a consistent state, then it will end in a consistent state. Isolation: the execution of one transaction is isolated from other transactions. Durability: if a transaction commits, its effects persist in the database.

  6. Problems with Transaction Processing Airline reservation system: Step 1: check if a seat is empty. Step 2: reserve the seat. Bad scenario: (but very common) Customer 1 - finds a seat empty Customer 2 - finds the same seat empty Customer 1 - reserves the seat. Customer 2 - reserves the seat. Customer 1 will not be happy; spends night in airport hotel.

  7. The Memory Hierarchy Main MemoryDisk Tape • 5-10 MB/S • transmission rates • 2-10 GB storage • average time to • access a block: • 10-15 msecs. • Need to consider • seek, rotation, • transfer times. • Keep records “close” • to each other. • 1.5 MB/S transfer rate • 280 GB typical • capacity • Only sequential access • Not for operational • data • Volatile • limited address • spaces • expensive • average access • time: • 10-100 nanoseconds Cache: access time 10 nano’s

  8. Tracks Arm movement Arm assembly Disk Space Manager • Task: manage the location of pages on disk (page = block) • Provides commands for: • allocating and deallocating a page • on disk • reading and writing pages. • Why not use the operating system • for this task? • Portability • Limited size of address space • May need to span several • disk devices. Spindle Disk head Sector Platters

  9. DB Buffer Management in a DBMS Page Requests from Higher Levels BUFFER POOL • Data must be in RAM for DBMS to operate on it! • Table of <frame#, pageid> pairs is maintained. • LRU is not always good. disk page free frame MAIN MEMORY DISK choice of frame dictated by replacement policy

  10. Buffer Manager Manages buffer pool: the pool provides space for a limited number of pages from disk. Needs to decide on page replacement policy. Enables the higher levels of the DBMS to assume that the needed data is in main memory. Why not use the Operating System for the task?? - DBMS may be able to anticipate access patterns - Hence, may also be able to perform prefetching - DBMS needs the ability to force pages to disk.

  11. Record Formats: Fixed Length • Information about field types same for all records in a file; stored in systemcatalogs. • Finding i’th field requires scan of record. • Note the importance of schema information! F3 F4 F1 F2 L3 L4 L1 L2 Address = B+L1+L2 Base address (B)

  12. Files of Records • Page or block is OK when doing I/O, but higher levels of DBMS operate on records, and files of records. • FILE: A collection of pages, each containing a collection of records. Must support: • insert/delete/modify record • read a particular record (specified using record id) • scan all records (possibly with some conditions on the records to be retrieved)

  13. Cost Model for Our Analysis As a good approximation, we ignore CPU costs: • B: The number of data pages • R: Number of records per page • D: (Average) time to read or write disk page • Measuring number of page I/O’s ignores gains of pre-fetching blocks of pages; thus, even I/O cost is only approximated.

  14. Sorting • Illustrates the difference in algorithm design when your data is not in main memory: • Problem: sort 1Gb of data with 1Mb of RAM. • Arises in many places in database systems: • Data requested in sorted order (ORDER BY) • Needed for grouping operations • First step in sort-merge join algorithm • Duplicate removal • Bulk loading of B+-tree indexes.

  15. 2-Way Sort: Requires 3 Buffers • Pass 1: Read a page, sort it, write it. • only one buffer page is used • Pass 2, 3, …, etc.: • three buffer pages used. INPUT 1 OUTPUT INPUT 2 Main memory buffers Disk Disk

  16. Two-Way External Merge Sort 6,2 2 Input file 9,4 8,7 5,6 3,1 3,4 PASS 0 1,3 2 1-page runs 2,6 4,9 7,8 5,6 3,4 • Each pass we read + write each page in file. • N pages in the file => the number of passes • So total cost is: • Idea:Divide and conquer: sort subfiles and merge PASS 1 4,7 1,3 2,3 2-page runs 8,9 5,6 2 4,6 PASS 2 2,3 4,4 1,2 4-page runs 6,7 3,5 6 8,9 PASS 3 1,2 2,3 3,4 8-page runs 4,5 6,6 7,8 9

  17. General External Merge Sort • More than 3 buffer pages. How can we utilize them? • To sort a file with N pages using B buffer pages: • Pass 0: use B buffer pages. Produce sorted runs of B pages each. • Pass 2, …, etc.: merge B-1 runs. INPUT 1 . . . . . . INPUT 2 . . . OUTPUT INPUT B-1 Disk Disk B Main memory buffers

  18. Cost of External Merge Sort • Number of passes: • Cost = 2N * (# of passes) • E.g., with 5 buffer pages, to sort 108 page file: • Pass 0: = 22 sorted runs of 5 pages each (last run is only 3 pages) • Pass 1: = 6 sorted runs of 20 pages each (last run is only 8 pages) • Pass 2: 2 sorted runs, 80 pages and 28 pages • Pass 3: Sorted file of 108 pages

  19. Number of Passes of External Sort B: number of frames in the buffer pool; N: number of pages in relation.

More Related