CS5226 2002 Operating System - PowerPoint PPT Presentation

About This Presentation
Title:

CS5226 2002 Operating System

Description:

7. Database Buffer Size. Buffer too small, then hit ratio too small. hit ratio = (logical acc. ... SQL Server 7 on Windows 2000. Scan query: ... – PowerPoint PPT presentation

Number of Views:53
Avg rating:3.0/5.0
Slides: 19
Provided by: compN
Category:

less

Transcript and Presenter's Notes

Title: CS5226 2002 Operating System


1
CS5226 2002Operating System Database
Performance Tuning
  • Xiaofang Zhou
  • School of Computing, NUS
  • Office S16-08-20
  • Email zhouxf_at_comp.nus.edu.sg
  • URL www.itee.uq.edu.au/zxf

2
Outline
  • Part 1 Operating systems and DBMS
  • Part 2 OS-related tuning

3
Operating System
  • Operating system is an interface between hardware
    and other software, supporting
  • Processes and threads
  • Paging, buffering and IO scheduling
  • Multi-tasking
  • File system
  • Other utilities such as timing, networking and
    performing monitoring

4
Scheduling
  • Process vs thread
  • Scheduling based on time-slicing, IO, priority
    etc
  • Different from transaction scheduling
  • The cost of content switching
  • When switch is desirable? And when is not?
  • The administrator can set priorities to
    processes/threads
  • Case 1 the DBMS runs at a lower priority
  • Case 2 different transactions run at different
    priority
  • Case 3 online transactions with higher priority
    than offline transactions

5
Priority Inversion
  • Let priorities T1 T2s T3

a solution priority inheritance
6
Database Buffers
Application buffers
  • An application can have its own in-memory buffers
    (e.g., variables in the program cursors)
  • A logical read/write will be issued to the DBMS
    if the data needs to be read/written to the DBMS
  • A physical read/write is issued by the DBMS using
    its systematic page replacement algorithm. And
    such a request is passed to the OS.
  • OS may initiate IO operations to support the
    virtual memory the DBMS buffer is built on.

DBMS buffers
OS buffers
7
Database Buffer Size
  • Buffer too small, then hit ratio too small
  • hit ratio (logical acc. - physical acc.) /
    (logical acc.)
  • Buffer too large, paging
  • Recommended strategy monitor hit ratio and
    increase buffer size until hit ratio flattens
    out. If there is still paging, then buy memory.

8
Buffer Size - Data
  • Settings
  • employees(ssnum, name, lat, long, hundreds1,
  • hundreds2)
  • clustered index c on employees(lat) (unused)
  • 10 distinct values of lat and long, 100 distinct
    values of hundreds1 and hundreds2
  • 20000000 rows (630 Mb)
  • Warm Buffer
  • Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID
    controller from Adaptec (80Mb), 4x18Gb drives
    (10000 RPM), Windows 2000.

9
Buffer Size - Queries
  • Queries
  • Scan Query
  • select sum(long) from employees
  • Multipoint query
  • select from employees where lat ?

10
Database Buffer Size
  • SQL Server 7 on Windows 2000
  • Scan query
  • LRU (least recently used) does badly when table
    spills to disk as Stonebraker observed 20 years
    ago.
  • Multipoint query
  • Throughput increases with buffer size until all
    data is accessed from RAM.

11
Its All About
  • Buffering is about a trade-off between speed and
    cost
  • A (18 GB) disk offers 170 random access for 300
    ? the access cost A1.76 per access per second
  • RAM ? C0.5/MB
  • Page size B 8 KB
  • Page p is accessed every I200 s
  • Keep page p in memory?
  • Yes cost C/1024B 0.0039 for 8KB RAM
  • No cost A/I 0.0088
  • So, p is in memory until its access interval
    reaches ??? s

12
Multiprogramming Levels
  • More concurrent users
  • Better utilization of CPU cycles (and other
    system resources)
  • Risk of excessive page swapping
  • More lock conflicts
  • So how many exactly
  • Depends on transaction profiles
  • Experiments to find the best value
  • And this parameter may change when application
    patterns change

13
Disk Layout and Access
  • Larger disk allocation chunks improves write
    performance
  • At the cost of disk utilisation
  • Setting disk usage factor
  • Low when expecting updates/inserts
  • Higher for scan-type of queries
  • Using prefetching
  • For non-random accesses

14
Scan Performance - Data
  • Settings
  • lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY,
    L_LINENUMBER , L_QUANTITY, L_EXTENDEDPRICE ,
    L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS ,
    L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE,
    L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT )
  • 600 000 rows
  • Lineitem tuples are 160 bytes long
  • Cold Buffer
  • Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID
    controller from Adaptec (80Mb), 4x18Gb drives
    (10000RPM), Windows 2000.

15
Scan Performance - Queries
  • Queries
  • select avg(l_discount) from lineitem

16
Usage Factor
  • DB2 UDB v7.1 on Windows 2000
  • Usage factor is the percentage of the page used
    by tuples and auxiliary data structures (the rest
    is reserved for future)
  • Scan throughput increases with usage factor.

17
Prefetching
  • DB2 UDB v7.1 on Windows 2000
  • Throughput increases up to a certain point when
    prefetching size increases.

18
Summary
  • In this module, we have covered
  • A review of OS from the DBMS perspective
  • How to optimise OS-related parameters and options
  • Thread
  • Buffer, and
  • File system
  • Next tuning the hardware
Write a Comment
User Comments (0)
About PowerShow.com