Database Tuning with Fathom

1
Database Tuning with Fathom
2
Database Tuning with Fathom
Agenda

• Fathom Management defined
• Aspects of database performance
• How Fathom Management can improve performance

3
Fathom Management

• Fathom Management is a system management center
  providing visibility, analysis, and proactive
  monitoring of critical information assets. Fathom
  saves time and money through more efficient use
  of current resources, better insight into system
  utilization, and automatic detection and
  correction of potential problem areas.

4
Resource Relationship
Fathom Management Architecture
5
Multiple Sites
Fathom Management Architecture
6
Local and Remote Trending
Fathom Management Architecture
Remote
Both sites trended here
Local
7
Database Monitor (Remote)
Fathom Management Architecture
Database
Scripted Database
Remote MonitorRuns Here
Trending and Alerting Happens Here
Local
8
Any Mix Possible
Fathom Management Architecture
Remote
Win 32
Solaris
Remote
Linux
HPUX
Local
Remote
9
A Job for Fathom Management
In RUN mode Fathom Management Provides

• A form-based interface for selecting and running
  jobs
• Repetitive functionality provided by templates
• Trend information for the job
• Start and stop time
• Exit code

10
Log File Monitor (LFM)
Fathom Management

• Searches any ASCII file for whatever you want
• Knows where it left off
• Frees the DBA from scanning log files
• Schedulable
• Alerts generated..
• Search criteria created in the Library for re-use
• LFM rule sets shipped with Fathom AppServer,
  Database, Webspeed, NameServer

11
Database Monitoring
Fathom Management

• Capture database performance information and
  store for analysis (vsts, Schema Information,
  dbanalysis)
• Monitor the database availability
• Alert if database comes down abnormally
• Alert if database rules are broken (20 different
  rules)
• Perform actions based on alerts

12
Network Monitor(s)
Fathom Management

• TCP Monitor access and response time
• UDP Monitor access and response time
• ICMP (Ping) Monitor access and response time
• HTTP Monitor content, access, response time,
  change in web page

13
HTTP
Fathom Management

• You can monitor
• Static or Dynamic pages
• Form content
• Header content
• Page content
• Tardy / Timeout intervals
• Re-direction

14
Other Features
Fathom Management

• Built in Scheduler
• Canned Reports
• Real-time Graphing and monitoring
• File Monitors
• Security Implementation

15
Database Tuning with Fathom
Agenda

• Fathom Management defined
• Aspects of database performance
• How Fathom Management can improve performance

16
System Resources

• Disk Capacity (Slowest)
• Quantity Have enough disk space to store what
  you need
• Reliability Have reliable disks so your data
  will remain available to the users
• Performance Have the correct number and maximum
  speed for your disks, to meet your throughput
  needs
• Memory Usage
• CPU Activity (Fastest)

17
Database Tuning with Fathom
Agenda

• Fathom Management defined
• Aspects of database performance
• How Fathom Management can improve performance

18
Current Storage

• Use df to determine free space available on most
  UNIX systems
• k gives result in kilobytes and could vary across
  machines
• s gives summary information
• Use bdf on HPUX to report same information
• Use the disk properties option on Windows to
  provide a graphical display of disk storage
  information

19
Rating (or RAIDing)
Database Performance vs Reliability
20
RAID 0 Striping
Tommy the Tuner Says Stripe size is generally
tunable, from a small size (8K) to a large size
(several megabytes)
1 4 . . .
2 5 . . .
3 6 . . .
Three disks. The information that is stored by
the user is distributed across all three drives.
The amount of information stored on a single
device is called a chunk or a stripe. As shown,
the stripes are distributed across the RAID array.

• High Performance
• Random R/Ws
• Low Reliability
• No Failure Protection
• Increased Risk
• One Fails, Set Fails

21
RAID 1 Mirroring
Tom the Tuner Says Be aware that there is a
performance penalty during the resynchronization
period.
1 2 3 . .
P1 P2 P3 . .
The first disk is the primary disk and the second
disk acts as the parity or mirror disk. The role
of the parity disk is to keep an exact
synchronous copy of all of the information stored
on the primary disk. If the primary disk fails,
the information can be retrieved from the parity
disk

• Medium Performance
• Optimistic Read
• High Reliability
• Lose Disk W/O Outage
• Expensive
• Twice As Many Disks

22
RAID 10 Or 10
Tom the Tuner Says YES!
1 3 .
P1 P3 .
2 4 .
P2 P4 .
Disks on the left-hand side are primary disks.
The primary disks are striped together and then
mirrored to the disks on the right-hand side. All
four disks are acting as both primary and parity
disks

• High Performance
• Good Random R/Ws
• High Reliability
• Mirroring Striping
• Cost?
• Same As RAID 1

1 2 3 . . .
23
RAID 5 YUCK!!!!
Tom the Tuner Says Unless your application is
100 read, PSC recommends against using RAID 5.
2 7 12 P13-16 17
3 8 P9-12 13 18
4 P5-8 9 14 19
1 6 11 16 P17-20
P1-4 5 10 15 20
Notice how the parity information is interleaved
with the primary data. If you experience a disk
failure, you will not lose data. However, your
performance will be severely degraded because
information from the lost disk will have to be
extracted from calculated parity.

• Low Performance
• Parity Construction
• High Reliability
• Good Failure Protection
• Cost?
• Less Than RAID 1

1 2 3 . . .
24
Disks Summary
Tom the Tuner Says Be aware of activity of
other hosts in a SAN environment

• Network File System (NFS) supported in 9.1C
• We support use of NFS V3 with NetApp Filers.
• You must have NFS V3 on /both/ NFS client and NFS
  server systems.
• Details explained in white papers you can find on
  Network Appliance web site.
• Storage Area Networks (SANs) grow in popularity
• Disks are your most important resources
• Purchase reliable disk array systems
• Configure them properly to allow consistent fast
  access to data
• Monitor them for performance and fill rate so you
  do not run out of space
• Trend the consumption of storage space to allow
  planning time for system expansion
• Once you get over 50 - 60 usage of your disk,
  performance goes down significantly (can be
  upwards of 25).

25
Memory Usage
Tom the Tuner Says OS will use between 32MB to
256MB of memory. OS buffers reserve 10 to 15
(tunable)

• Primary function to reduce disk I/O.
• Configure system without interleaved memory
• Memory Swapping, avoid at all costs
• Newer systems manage memory with Paging
• Physical Identifies when info is needed in
  memory
• Should level into hundreds of PRPS
• Virtual Occurs when info is moved
• Thousands of PRPS produce minimal impact

26
Progress Memory Budget
Tom the Tuner Says Database broker memory usage
10 of B.

• Demand-paged executables
• Broker Parameters
• Database Buffer Parameter (-B)
• Lock table Entries (-L)
• Index Cursors (-c)
• Remote Client Servers
• 3MB to 5MB each
• Limited by the Mm parameter (default 4)
• Client or User Parameters
• 5MB to 10MB per process (with avg. mmax -Bt)
• 10MB to 20MB for remote users

27
Case Study
Tom the Tuner Says When using left over
memory, always tune the server first!
1 GB of RAM, 50 local users, and one
8KB-block-size database using 10,000 database
buffers.

• Operating System Memory
• 28MB OS
• 100MB OS Buffers
• Progress Memory
• 16MB Executable
• 88MB Database broker ((8KB 10000) 1.1)
• 250MB to 500MB for Users
• Total memory requirements 582MB to 832MB
• No significant paging or swapping
• Consider memory usage by other applications
• Trend memory usage over a period of time

28
CPU Activity
Tom the Tuner Says The ratio of user time to
system time should be approximately 3 to 1!

• User time (UNIX Windows)
• The amount of time spent performing user tasks.
• System time (UNIX) or Privileged time (Windows)
• The amount of time spent devoted to system
  overhead like paging, context switches, etc.
• Wait on I/O time (UNIX) or Idle time (Windows)
• The amount of time the CPU is waiting for another
  resource. Logged as idle time on Windows.
• Idle time (UNIX)
• The amount of unallocated time for the CPU.
• Processor time (Windows)
• An accumulation of other processor metrics.
• 100 processor time idle time.

29
Database Blocks

• Data Blocks
• Most common blocks in the database
• can co-habitate with other tables (unlike index
  blocks)
• Index Blocks
• Can only contain information referring to a
  single index
• Other Block Types
• Master Status info about entire database
• Storage Object First last record of table, by
  each index
• Free Header, but no data
• Empty No header information

30
Layout Of An RM Block
DB Key
Block Type
Chain Type
Backup Counter
DB KeyofNext Blk
Update Counter
Free Spc Pointers
Record Pointers
Block Header
Expansion Space
Record
Fragment Pointer
Length
Skip Table
Field 1
Field 2
Etc
Single Record
Length
Misc. Info.
Data
Single Field
31
Index Block
Index Blocks have the same 16 bytes of header
information as data blocks and can only contain
information referring to a single index

• A database can have up to 32,767 indexes
• All indexes are structured as B-trees and are in
  a compressed format
• One B-tree per index
• Each B-tree starts at the root
• The root is stored in an _storageobject record
• Indexes are multi-threaded, allowing concurrent
  access to indexes
• Only those nodes on the B-tree that are required
  by a process are locked

32
Other Database Blocks

• Master Block
• Always the first block in the database (Fathom)
• Storage Object blocks
• Are pinned in memory
• Free Blocks
• Below the high-water mark
• Empty Blocks
• Above the high-water mark but below the total
  blocks in the area (Fathom)

33
Shared Memory Resources
Tom the Tuner Says Database buffers provide an
excellent performance improvement to the user if
tuned properly.
There are many resources inside of shared memory.
Database buffers account for more than 90 of
shared memory while the latch control structures
account for less than 1 .
-B
-L
-C
-hash
. . .
Latch Control Table
Server Control Table
User Control Table
BI Buffers
BIW
AI Buffers
AIW
APW
APW
34
Optimizing Data Layout

• A DB is made up of storage areas
• Each can contain one or more DB objects
• A database object is a table or an index
• Sequences and schema are also db objects
• Each SA can contain one or more extents or
  volumes on disk
• Extents are the physical files stored at the OS
  level
• Each extent is made up of blocks
• 1KB, 2KB, 3KB, 8KB sizes, one size per DB
• Each area can have differing values of records
  per block
• All data originates from a storage area
• Mean record size?
• Sequential or random access?
• Frequently used or historical?
• Access frequency?
• Individual records growing or static in size?

Tom the Tuner Says Answers to these questions
will allow you to take the best advantage of
your disks.
35
Sizing Your Database Areas
Tom the Tuner Says The default area should
generally be reserved for schema and sequence
definitions.

• proutil db-name -C tabanalys
• Table Table owner and table name
• Records Total records in the db for the table
• Size Total number bytes used in the db for the
  table
• Min Minimum bytes used by any record for the
  table
• Max Maximum bytes used by any record in the
  table
• Mean Mean number bytes used by any record for
  the table

36
Sizing Your Database Areas
Tom the Tuner Says Splitting information across
areas allows for controlled distribution of I/O,
application segmentation, faster offline
utilities

• 8KB is almost always appropriate
• In Windows, Use 4KB
• 20 bytes of record overhead in each record
• Determine appropriate records per block
• Take the mean record size
• Add 20 to the record size
• Divide 8192 (8KB block size) by the previous
  number
• Records per block must be a binary number from 1
  to 256
• Result is not usually an even number, make best
  estimate
• Too many RPBs, risk of fragmentation
• Too few RPBs, wasted space in the blocks
• Be as accurate as possible w/o over complicating
  DB structure

37
Primary Recovery Area
Tom the Tuner Says If you are checkpointing too
often, you should increase before-image cluster
size.
Cluster
Checkpoint
Cluster
Checkpoint
Cluster
Checkpoint
Cluster
Checkpoint Reuse Decision Point

• Smaller cluster size more frequent checkpoints
• A checkpoint is a sync point between memory
  disk
• Performance must be balanced with recovery
• To determine before-image cluster size..
• Monitor db when most updates are made
• Trend db with Fathom Management
• Review duration of checkpoints throughout the
  week
• Checkpoints should happen no more than every 2 min

38
Optimization Summary

• Optimizing Database Areas
• Data Area Optimization
• Primary Recovery (Before-image) Information
• After-image Information
• Optimizing Memory Usage
• Why is Buffer Hit Percentage Important?
• Increasing Memory Utilization
• Decreasing Memory
• Optimizing CPU Usage
• Understanding the spin Parameter
• CPU Bottleneck Look At Your Disk Drives

39
Data Area Optimization
Rules Make data areas easier to maintainand
provide optimal performance for users

• Split Off The Schema
• Makes future Progress upgrades more transparent
• Choose an Appropriate Block Size
• OS to block size matching more efficient data
  transfer
• Keep Areas Small For Offline Utilities
• Always Have An Overflow Extent For Each Area
• Enable Large Files
• Partition Data

40
Primary Recovery (Before-image) Information
Rules Make read-write access to this DB area
as efficient as possible. Write is more important

• Extent Size Rules (Both BI and Data)
• Do not make them too large
• Always make the last extent variable length
• Enable large files as a safety valve
• Tune for sequential access
• If isolation is possible, then use a mirrored
  disk
• If not, use striped disks
• Use BI grow option
• Proutil dbname C bigrow
• number of clusters you want to add to area

41
After-image Information
Enable recovery to the last transaction or to a
point in time

• Always Use Multi-volume Extents
• Three states Empty, Busy, Full
• Allows support of online backups
• Tune for sequential access (same as BI)
• Isolate for Disaster Recovery
• Sizing After-image Extents
• All can be variable
• Fixed are better performing
• Eliminates formatting of blocks during the
  session
• Allows use of a contiguous portion of the disk

42
Optimizing Memory Usage
Taking advantage of existing memory

• Why Is Buffer Hit Percentage Important?
• Lower disk utilization
• Small change in BH large disk I/O reduction
• Increasing Memory Utilization
• Increase broker parameters first
• Decreasing Memory
• Decrease client parameters first
• Private Buffers (-Bp)
• The general idea is to share memory where you can

43
Optimizing CPU Usage
Application code should consume the majority of
the CPU resource

• Understand the spin Parameter
• Default good for single CPU system
• Default not efficient on a multiple CPU system
• Start with 2000 monitor naps per second
  (Fathom)
• CPU Bottleneck? Check the Disk Drives
• Lack of CPU resources could mask a disk issue
• Ensure that you do not have a runaway process
• Make sure that other resources are working
  efficiently

44
(No Transcript)