CS5226 - PowerPoint PPT Presentation

Cs5226 hardware tuning l.jpg
Download
1 / 36

(e.g., business analyst, Data architect) Sophisticated. Application ... Part 1: Tuning the storage subsystem. RAID storage system. Choosing a proper RAID level ...

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

Download Presentation

CS5226

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 hardware tuning l.jpg

CS5226 Hardware Tuning


Slide2 l.jpg

ApplicationProgrammer(e.g., business analyst,

Data architect)

Application

SophisticatedApplicationProgrammer(e.g., SAP admin)

QueryProcessor

Indexes

Storage Subsystem

Concurrency Control

Recovery

DBA,Tuner

Operating System

Hardware[Processor(s), Disk(s), Memory]


Outline l.jpg

Outline

  • Part 1: Tuning the storage subsystem

    • RAID storage system

    • Choosing a proper RAID level

  • Part 2: Enhancing the hardware configuration


Magnetic disks l.jpg

1956: IBM (RAMAC) first disk drive

5 Mb – 0.002 Mb/in235000$/year9 Kb/sec

1980: SEAGATE

first 5.25’’ disk drive

5 Mb – 1.96 Mb/in2625 Kb/sec

1999: IBM MICRODRIVE

first 1’’ disk drive340Mb 6.1 MB/sec

tracks

spindle

platter

read/write head

actuator

disk arm

Controller

disk interface

Magnetic Disks


Magnetic disks5 l.jpg

Access Time (2001)

Controller overhead (0.2 ms)

Seek Time (4 to 9 ms)

Rotational Delay (2 to 6 ms)

Read/Write Time (10 to 500 KB/ms)

Disk Interface

IDE (16 bits, Ultra DMA - 25 MHz)

SCSI: width (narrow 8 bits vs. wide 16 bits) - frequency (Ultra3 - 80 MHz).

http://www.pcguide.com/ref/hdd/

Magnetic Disks


Storage metrics l.jpg

Storage Metrics


Hardware bandwidth l.jpg

The familiar

bandwidth

pyramid:

The farther

from the CPU,

the less

the bandwidth.

40

133

422

15

per disk

Hardware Bandwidth

System Bandwidth Yesterday

in megabytes per second (not to scale!)

Slide courtesy

of J. Gray/L.Chung

Hard Disk | SCSI | PCI | Memory | Processor


Hardware bandwidth8 l.jpg

The familiar

pyramid is gone!

PCI is now the

bottleneck!

In practice,

3 disks can reach

saturation using

sequential IO

26

26

160

133

1,600

26

Hardware Bandwidth

System Bandwidth Today

in megabytes per second (not to scale!)

Slide courtesy

of J. Gray/L.Chung

Hard Disk | SCSI | PCI | Memory | Processor


Raid storage system l.jpg

RAID Storage System

  • Redundant Array of Inexpensive Disks

    • Combine multiple small, inexpensive disk drives into a group to yield performance exceeding that of one large, more expensive drive

    • Appear to the computer as a single virtual drive

    • Support fault-tolerance by redundantly storing information in various ways


Raid 0 striping l.jpg

RAID 0 - Striping

  • No redundancy

    • No fault tolerance

  • High I/O performance

    • Parallel I/O


Raid 1 mirroring l.jpg

RAID 1 – Mirroring

  • Provide good fault tolerance

    • Works ok if one disk in a pair is down

  • One write = a physical write on each disk

  • One read = either read both or read the less busy one

    • Could double the read rate


Raid 3 parallel array with parity l.jpg

RAID 3 - Parallel Array with Parity

  • Fast read/write

  • All disk arms are synchronized

  • Speed is limited by the slowest disk


Parity check classical l.jpg

Parity Check - Classical

  • An extra bit added to a byte to detect errors in storage or transmission

  • Even (odd) parity means that the parity bit is set so that there are an even (odd) number of one bits in the word, including the parity bit

  • A single parity bit can only detect single bit errors since if an even number of bits are wrong then the parity bit will not change

  • It is not possible to tell which bit is wrong


Raid 5 parity checking l.jpg

RAID 5 – Parity Checking

  • For error detection, rather than full redundancy

  • Each stripe unit has an extra parity stripe

    • Parity stripes are distributed


Raid 5 read write l.jpg

RAID 5 Read/Write

  • Read: parallel stripes read from multiple disks

    • Good performance

  • Write: 2 reads + 2 writes

    • Read old data stripe; read parity stripe (2 reads)

    • XOR old data stripe with new data stripe.

    • XOR result into parity stripe.

    • Write new data stripe and new parity stripe (2 writes).


Raid 10 striped mirroring l.jpg

RAID 10 – Striped Mirroring

  • RAID 10 = Striping + mirroring

    • A striped array of RAID 1 arrays

  • High performance of RAID 0, and high tolerance of RAID 1 (at the cots of doubling disks)

.. More information about RAID disks at http://www.acnc.com/04_01_05.html


Hardware vs software raid l.jpg

Hardware vs. Software RAID

  • Software RAID

    • Software RAID: run on the server’s CPU

    • Directly dependent on server CPU performance and load

    • Occupies host system memory and CPU operation, degrading server performance

  • Hardware RAID

    • Hardware RAID: run on the RAID controller’s CPU

    • Does not occupy any host system memory. Is not operating system dependent

    • Host CPU can execute applications while the array adapter's processor simultaneously executes array functions: true hardware multi-tasking


Raid levels data l.jpg

RAID Levels - Data

Settings:

accounts( number, branchnum, balance);

create clustered index c on accounts(number);

  • 100000 rows

  • Cold Buffer

  • Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID controller from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows 2000.


Raid levels transactions l.jpg

RAID Levels - Transactions

No Concurrent Transactions:

  • Read Intensive:

    select avg(balance) from accounts;

  • Write Intensive, e.g. typical insert:

    insert into accounts values (690466,6840,2272.76);

    Writes are uniformly distributed.


Raid levels l.jpg

SQL Server7 on Windows 2000 (SoftRAID means striping/parity at host)

Read-Intensive:

Using multiple disks (RAID0, RAID 10, RAID5) increases throughput significantly.

Write-Intensive:

Without cache, RAID 5 suffers. With cache, it is ok.

RAID Levels


Comparing raid levels l.jpg

Comparing RAID Levels


Controller pre fetching no write back yes l.jpg

Controller Pre-fetching No, Write-back Yes

  • Read-ahead:

    • Prefetching at the disk controller level.

    • No information on access pattern.

    • Better to let database management system do it.

  • Write-back vs. write through:

    • Write back: transfer terminated as soon as data is written to cache.

      • Batteries to guarantee write back in case of power failure

    • Write through: transfer terminated as soon as data is written to disk.


Scsi controller cache data l.jpg

SCSI Controller Cache - Data

Settings:

employees(ssnum, name, lat, long, hundreds1,

hundreds2);

create clustered index c on employees(hundreds2);

  • Employees table partitioned over two disks; Log on a separate disk; same controller (same channel).

  • 200 000 rows per table

  • Database buffer size limited to 400 Mb.

  • Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID controller from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows 2000.


Scsi not disk controller cache transactions l.jpg

SCSI (not disk) Controller Cache - Transactions

No Concurrent Transactions:

update employees set lat = long, long = lat where hundreds2 = ?;

  • cache friendly: update of 20,000 rows (~90Mb)

  • cache unfriendly: update of 200,000 rows (~900Mb)


Scsi controller cache l.jpg

SQL Server 7 on Windows 2000.

Adaptec ServerRaid controller:

80 Mb RAM

Write-back mode

Updates

Controller cache increases throughput whether operation is cache friendly or not.

Efficient replacement policy!

SCSI Controller Cache


Which raid level to use l.jpg

Which RAID Level to Use?

  • Data and Index Files

    • RAID 5 is best suited for read intensive apps or if the RAID controller cache is effective enough.

    • RAID 10 is best suited for write intensive apps.

  • Log File

    • RAID 1 is appropriate

      • Fault tolerance with high write throughput. Writes are synchronous and sequential. No benefits in striping.

  • Temporary Files

    • RAID 0 is appropriate.

      • No fault tolerance. High throughput.


What raid provides l.jpg

What RAID Provides

  • Fault tolerance

    • It does not prevent disk drive failures

    • It enables real-time data recovery

  • High I/O performance

  • Mass data capacity

  • Configuration flexibility

  • Lower protected storage costs

  • Easy maintenance


Enhancing hardware config l.jpg

Enhancing Hardware Config.

  • Add memory

    • Cheapest option to get better performance

    • Can be used to enlarge DB buffer pool

      • Better hit ratio

      • If used for enlarge OS buffer (as disk cache), it benefits but to other apps as well

  • Add disks

  • Add processors


Add disks l.jpg

Add Disks

  • Larger disk ≠better performance

    • Bottleneck is disk bandwidth

  • Add disks for

    • A dedicated disk for the log

    • Switch RAID5 to RAID10 for update-intensive apps

    • Move secondary indexes to another disk for write-intensive apps

    • Partition read-intensive tables across many disks

  • Consider intelligent disk systems

    • Automatic replication and load balancing


Add processors l.jpg

Add Processors

  • Function parallelism

    • Use different processors for different tasks

      • GUI, Query Optimisation, TT&CC, different types of apps, different users

      • Operation pipelines:

        • E.g., scan, sort, select, join…

    • Easy for RO apps, hard for update apps

  • Data partition parallelism

    • Partition data, thus the operation on the data


Parallelism l.jpg

Parallelism

  • Some tasks are easier to parallelize

    • E.g., join phase of GRACE hash join

    • E.g., scan, join, sum, min

  • Some tasks are not so easy

    • E.g., sorting, avg, nested-queries


Summary l.jpg

Summary

  • We have covered:

    • The storage subsystem

      • RAID: what are they and which one to use?

    • Memory, disks and processors

      • When to add what?


Database tuning l.jpg

Database Tuning

Database Tuning is the activity of making a database application run more quickly. “More quickly” usually means higher throughput, though it may mean lower response time for time-critical applications.


Tuning principles l.jpg

Tuning Principles

  • Think globally, fix locally

  • Partitioning breaks bottlenecks (temporal and spatial)

  • Start-up costs are high; running costs are low

  • Render onto server what is due onto Server

  • Be prepared for trade-offs (indexes and inserts)


Tuning mindset l.jpg

Tuning Mindset

  • Set reasonable performance tuning goals

  • Measure and document current performance

  • Identify current system performance bottleneck

  • Identify current OS bottleneck

  • Tune the required components eg: application, DB, I/O, contention, OS etc

  • Track and exercise change-control procedures

  • Measure and document current performance

  • Repeat step 3 through 7 until the goal is met


Goals met l.jpg

Goals Met?

  • Appreciation of DBMS architecture

  • Study the effect of various components on the performance of the systems

  • Tuning principle

  • Troubleshooting techniques for chasing down performance problems

  • Hands-on experience in Tuning


  • Login