cs5226 2002 operating system database performance tuning n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
CS5226 2002 Operating System & Database Performance Tuning PowerPoint Presentation
Download Presentation
CS5226 2002 Operating System & Database Performance Tuning

Loading in 2 Seconds...

play fullscreen
1 / 18

CS5226 2002 Operating System & Database Performance Tuning - PowerPoint PPT Presentation


  • 116 Views
  • Uploaded on

CS5226 2002 Operating System & Database Performance Tuning. Xiaofang Zhou School of Computing, NUS Office: S16-08-20 Email: zhouxf@comp.nus.edu.sg URL: www.itee.uq.edu.au/~zxf. Outline. Part 1: Operating systems and DBMS Part 2: OS-related tuning.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'CS5226 2002 Operating System & Database Performance Tuning' - tayten


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
cs5226 2002 operating system database performance tuning

CS5226 2002Operating System & Database Performance Tuning

Xiaofang Zhou

School of Computing, NUS

Office: S16-08-20

Email: zhouxf@comp.nus.edu.sg

URL: www.itee.uq.edu.au/~zxf

outline
Outline
  • Part 1: Operating systems and DBMS
  • Part 2: OS-related tuning
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

Other software

Operating system

hardware

DBMS

Operating System
scheduling
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
priority inversion

Request X

T1

T2s

T3

Lock x

Priority Inversion
  • Let priorities T1 > T2s > T3

… a solution: priority inheritance

database buffers
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

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.

DATABASE PROCESSES

RAM

DATABASEBUFFER

Paging Disk

LOG

DATA

DATA

Database Buffer Size
buffer size data
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.
buffer size queries
Buffer Size - Queries

Queries:

  • Scan Query

select sum(long) from employees;

  • Multipoint query

select * from employees where lat = ?;

database buffer size1
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.

Database Buffer Size
it s all about
It’s 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 A=$1.76 per access per second
    • RAM  C=$0.5/MB
    • Page size B = 8 KB
    • Page p is accessed every I=200 s
    • Keep page p in memory?
      • Yes: cost C/1024*B = $0.0039 for 8KB RAM
      • No: cost A/I = $0.0088
      • So, p is in memory until its access interval reaches ??? s
multiprogramming levels
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
disk layout and access
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
scan performance data
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.
scan performance queries
Scan Performance - Queries

Queries:

select avg(l_discount) from lineitem;

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.

Usage Factor
prefetching
DB2 UDB v7.1 on Windows 2000

Throughput increases up to a certain point when prefetching size increases.

Prefetching
summary
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