Setup for Performance

1
Setup for Performance
PTW-023

• Adam Backman
• White Star Software
• adam_at_wss.com

2
Outline

• Definition of performance
• Disk Setup
• Database layout
• Memory Utilization
• CPU Utilization

3
Definition of Performance

• Performance is perception
• Performance is relative
• Performance is meeting your service level
  agreements
• Performance is keeping most people happy
• Performance is keeping your paycheck!

4
Goals of Performance Tuning

• To make the application predictable
• To eliminate bottlenecks
• Network
• Disk
• Memory
• CPU

5
Before we look at the system

• Application performance is very important
• A well tuned system can make good code great but
  poor code and make even the best system look bad
• Many systems have more than just Progress on
  them so dont forget to check your other
  applications as well

6
Network Setup

• Use 100MB and Full Duplex. Do not choose
  auto-negotiate
• Limit the number of users per server to 5
• (3 is better on heavy client server setups)
• -Mm 8192 is generally better than the default of
  1024
• Split OLTP and Decision Support (reporting)
  functions to different servers6

7
Using multiple servers
-B
-L
LS
-c
-hash
LS
-aibufs
-bibufs
LCT
SCT
UCT

8
Why is DB Storage So Important?

• Everything starts from the disk
• Disks need to be reliable and fast
• All physical disks are only capable of doing
  approximately 100 operations per second
• Salespeople are weasels

9
What causes disk I/O?

• Operating system (swapping and paging)
• Progress
• Database (DB and BI)
• Application (code and temp files)
• Other applications

10
Tuning your storage

• Balancing Disk I/O
• RAID vs. JBOD
• Database Setup

11
Balancing Disk I/O

• Balancing disk I/O is the process of making sure
  you are using all of the available disk resources
  (file systems, disks and controllers) are working
  equally as hard at load. This is also called
  eliminating variance. A well tuned system will
  have less than a 15 variance across all disks.

12
How can I eliminate variance?

• Warning This is a religious argument. Everyone
  has an opinion and everyone is right.
• There are two schools of thought.
• The system should take care of this.
• I will take care of this.

13
The system will take care of this.

• Disk I/O can be effectively distributed by the
  operating system with the use of smart I/O
  systems. Most notably RAID.
• Basic Definition of RAID Redundant Array of
  Independent Disks

14
What RAID really means

• RAID has many levels. I will only cover a few.
• RAID 0 This level is also called striping.
• RAID 1 This is referred to as mirroring.
• RAID 5 Least understood RAID level
• RAID 10 This is mirroring and striping. Also
  known as RAID 0 1

15
I will take care of this

• Manual spread of data across disks
• Use of Progress features to spread the data
  across the disks
• More attention by system administrator is needed
• Administrator must think about reliability as well

16
Database Setup Options

• Database Considerations
• Storage areas
• Blocksize and Records per block
• Type II areas
• Logging Considerations
• Effect of BI cluster size
• Why are APWs affected by BI Cluster size

17
Storage Areas

• Benefits
• Greater control of location of data
• Minimize downtime for utilities
• Stripe some, leave some on straight disks
• Drawbacks
• More things to break
• More complex to monitor

18
Storage Areas - Control

• A Storage Area can hold 1 or more data objects
  (index, table, schema, )
• Separate schema from data if possible
• Isolate the logs (AI and BI)
• Try to keep the number of areas manageable, only
  add more areas for valid business reasons

19
Database Block Size

• 8k for most (4k for NT)
• More in synch with the operating system
• More stuff per read
• Especially good for index reads
• Watch the number of records per block so you
  dont waste block space

20
Type II Areas

• The table and index blocks from individual
  objects are clustered together
• The cluster size is tunable to 8, 64 or 512
  block clusters
• Benefits Potential performance benefits
• Drawbacks Yet another thing to try to get right,
  potential wasted space

21
How Type II Areas Work

• The data blocks are clustered together and in
  turn these clusters are chained together
• Scan type operations need only find the first
  block and the rest of the blocks will follow
• Example When you do an index rebuild, the first
  step is to delete all of the index blocks. In a
  type 1 area all of the blocks in the area are
  scanned and the index blocks are deleted. In a
  type II area the first cluster is put on the free
  chain and no other updates are needed.

22
BI Cluster size

• The default value of 512 KB is wrong for most
  people
• Monitor the DB during your high update portion of
  the day
• Set the cluster size high enough to keep
  checkpoint lengths greater than 2 minutes
• Generally, 1 MB to 8 MB for most sites

23
Why are APWs Affected by BI Cluster Size

• The BI cluster size determines the length of the
  checkpoint
• The APW efficiency determines the efficiency of
  the checkpoint
• Generally, I start with 1 apw and the correct
  BI cluster size and then add 1 apw if I see
  buffers flushed at checkpoint during my
  important processing times.

24
Memory Setup

• Set DB Buffers as high as practical
• Use -Bp to reduce impact of reporting
• Use -Bt to reduce I/O operations from temp table
  usage.

25
Setting Database Buffers (-B)

• Choose some value x of your DB size or some nice
  round number (10000) to start
• Make increases to try to achieve 95 buffer hit
  percentage
• The difference in I/O between 90 and 95 is a
  50 reduction in read I/O requests to the disk!

26
Temp Table Buffers (-Bt)

• These are essentially the same as -B but for an
  individual user to use on their temporary
  records
• Calculations can be made to determine an exact
  right value but a simple guess is nearly as
  effective
• Light temp table users (100)
• Heavy temp table users (10000)

27
CPU

• CPU is usually a victim of some other bottleneck
  except in the case of shared memory latch
  contention
• Memory latches are used to ensure that two users
  do not update the same memory at the same time.
  They can be thought of as record locks for
  memory.
• -spin is used to alleviate this problem

28
What does a -spin problem look like?

• Generally, you will see a high percentage of
  system CPU relative to user CPU.
• Good host-based (character) ration would be 3
  user to 1 system
• Good client/server may have a ration closer to 1
  to 1 but it is rare to see where system is higher
  than user except at very low CPU utilization
  (50 idle)

29
How to set -spin?

• On single processor systems set it to 1
• On multi-processor systems
• Start at 2000 and work from there (generally up)
• There are many theories on how to best set -spin
  and all are correct in at least one case but
  there is really no policy that works 100 of the
  time

30
How Private Buffers (-Bp) Work

• First, the rules
• Total of all -Bps cannot exceed 25 of -B
• Setting -Bp effectively is a process of looking
  at your most complex query and doing a
  calculation or just quessing
• A separate LRU chain is maintained for NO-LOCK
  reads so the query will evict its own buffers
  from memory rather than other buffers.

31
Conclusion

• Get the best disk setup possible
• No RAID 5
• Isolate logging (AI and BI)
• RAID 0 or 01 for the database
• Split your data up into storage areas
• Use Type II areas where possible
• Set the -Mn high enough to only have 3-4 clients
  per server at capacity

32
Conclusion (continued)

• Set your BI cluster size to at least 1MB but 4MB
  is better in most cases
• Have 1 BIW and at least 1 APW and an AIW (if
  appropriate)
• Get to a 95 buffer hit rate
• Use -spin

33
Questions