ase133 performance tuning of ase with special emphasis on linux l.
Skip this Video
Loading SlideShow in 5 Seconds..
ASE133: Performance Tuning of ASE with special emphasis on Linux PowerPoint Presentation
Download Presentation
ASE133: Performance Tuning of ASE with special emphasis on Linux

Loading in 2 Seconds...

play fullscreen
1 / 62

ASE133: Performance Tuning of ASE with special emphasis on Linux - PowerPoint PPT Presentation

  • Uploaded on

ASE133: Performance Tuning of ASE with special emphasis on Linux. How to make Penguins Fly. Girish Vaitheeswaran Staff Software Engineer Sybase, Inc. Contents. No Introduction to Linux System Performance 101 De-mystifying Processors Let’s not forget Memory

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

PowerPoint Slideshow about 'ASE133: Performance Tuning of ASE with special emphasis on Linux' - Lucy

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
ase133 performance tuning of ase with special emphasis on linux

ASE133: Performance Tuning of ASE with special emphasis on Linux

How to make Penguins Fly

Girish VaitheeswaranStaff Software EngineerSybase, Inc.

  • No Introduction to Linux
  • System Performance 101
  • De-mystifying Processors
  • Let’s not forget Memory
  • Reading and Writing [Disk I/O]
  • Sending and Receiving [Network I/O]
  • Conclusion
no introduction to linux
No Introduction to Linux
  • Does Linux need an introduction!! 
  • Non Microkernel based architecture (monolithic kernel)
  • Multitasking, Secure virtual memory OS
  • Different flavors of Linux available
    • Redhat, Suse, Redflag etc
  • Supported on various hardware platforms.
    • Intel (Xeon, Pentium, Itanium), AMD (Athlon), IBM, SGI, SUN etc
  • Importantly, appealing to Bean Counters
linux trends
Linux Trends
  • Consolidation from big boxes to commodity hardware
  • Migration to 4CPU P3’s with 4/8GB RAM running some flavor of Linux
  • Exploiting Moore’s law
  • Moving from 750Mhz CPU’s to 1.5GHz CPU’s with hyperthreading
  • Sizing based on Increased clock speeds.
ase on linux
ASE on linux
  • ASE on Linux supported since
  • ASE supports RH2.1
  • ASE 12.5.1 will support RH 3.0
  • SuSE/RedFlag supported as well.
  • More info available at
system performance 101




System Performance 101
  • How do I get my hardware and software to do more work without buying more hardware ?
  • System Performance mainly depends on 3 areas
de mystifying processors
De-mystifying Processors
  • Classes of processors
    • Xeon with HT Technology
    • Pentium with HT Technology
  • What is hyper threading
    • Doing more work in each clock cycle by providing thread level parallelism in each processor
  • Advantages of hyper threading
    • Support for multi-threaded code and multi-tasking operations through better utilization of processor resources.
    • Multiple threads/tasks running simultaneously to increase the number of transactions that can be executed.
    • Improved reaction and response times for end users.
    • Increased number of users a server system can support.
  • Consolidation Guidelines
    • One 1.5GHz CPU does not necessarily yield the same performance as two 750MHz CPU’s. Various parameters to account for are
      • L1/L2/L3 cache sizes (Internal CPU caches)
      • Memory Latencies
      • Cycles per instruction
  • Number of engines to run
    • On HT enabled processors 1 physical CPU can run 2 engines
    • Having a HT enabled processor is not equivalent to having 2 physical processors
processor tips
Processor Tips
  • Identifying Number of processors/Clock speed/Hyper threading

% cat /proc/cpuinfo

processor tips10
Processor Tips
  • Determining cpu usage (mpstat, top, vmstat)

% mpstat 5 5

processor tips11
Processor Tips
  • Enabling Hyperthreading
    • Enabled by default in most processors
    • Note that if “ht” is shown in the cat /proc/cpuinfo output does not mean HT is enabled.
  • Disabling Hyperthreading
    • Can be disabled during BIOS setup
      • This has been useful in cases where there are multiple engines and the load on the cpu’s is close to 100%
ase engines unleashed
ASE Engines Unleashed
  • ASE Engines are Linux processes that schedule tasks
  • ASE Engines are multi-threaded
  • ASE Performs automatic load balancing of tasks
  • ASE has automatic task affinity management
    • Tasks tend to run on the same engine that they last ran on to improve locality
  • Linux does not have an ability to explicitly bind engines to processors. (Not Yet)
  • RH AS 2.1 has built in process-processor affinity
  • Internal benchmarks have demonstrated ASE’s ability to scale to 64 engines.
ase engines unleashed13
ASE Engines Unleashed
  • 2 configuration parameters control the number of engines
  • The sp_engine stored procedure can be used to “online” or “offline” engines dynamically
  • Tune the number of engines based on the “Engine Busy Utilization” values presented by sp_sysmon
  • Extra dataserver threads [RH 7.2 only]
    • For Posix aio support
monitoring and tuning engines
Monitoring and Tuning Engines
  • sp_sysmon’s kernel section reports utilization as shown

sp_sysmon “00:02:00”, kernel

monitoring and tuning engines15
Monitoring and Tuning Engines
  • Influencing kernel utilization
    • CPU bound tasks
    • I/O bound tasks
  • Tuning runnable process search count
  • I/O polling process count
logical process management
Logical Process Management
  • Logical process management can be used to influence the priority of tasks or to do load balancing by using engine groups.
  • E.g.Housekeeper tuning for aggressive garbage collection
logical process management17
Logical Process Management
  • I/O bound tasks and cpu bound tasks can be balanced by using engine groups.

E.g. Mixed work load scenario running a resource hogging reporting application and an Online reservation at the same time.

Step1 : Create 2 engine groups and associate engines to engine groups

logical process management18
Logical Process Management
  • Step 2 : Display information about execution objects

exec sp_showcontrolinfo

  • Step 3 : Create 2 execution classes onl_reservation_execlass and reporting_execlass

exec sp_addexeclass onl_reservation_execlass, MEDIUM, 0, onl_reservation_engroup

exec sp_addexeclass reporting_execlass, MEDIUM, 0, reporting_engroup

logical process management19
Logical Process Management
  • Step 4: Bind application logins to the respective execution class

exec sp_bindexeclass “onl_sa”, LG, NULL, onl_reservation_execlass

exec sp_bindexeclass “reporting_sa”, LG, NULL, reporting_execlass

  • Step 5 : Validate binding information

exec sp_showexeclass

some more engine related tunes
Some more Engine related Tunes
  • Runnable process search count determines the number of times ASE engines loop looking for runnable tasks before yielding to the OS.
  • Default value is good in general
  • Tune this parameter only if all of the below are true
    • There are multiple applications running on the same machine and you require ASE to yield to the OS so that the other applications can be scheduled
    • The average cpu busy utilization is < 5%
some more engine related tunes21
Some more Engine related Tunes
  • I/O polling process count determines the number of processes ASE runs before checking for Network or Disk I/O.
  • Tune this parameter only if all the following conditions are met
    • Increase the value if the total I/O checks is very high and the Avg Disk I/O’s per check or Avg Net I/O’s per check is very low.
    • If the avg cpu utilization is between 70-90%
let s take a checkpoint
Let’s take a Checkpoint
  • Hyperthreading is not equivalent to having a physical processor
  • Just clock speed does not give performance
  • Add more engines if Engine busy utilization is high
  • Logical process management for priority scheduling and mixed workloads
let s not forget memory
Let’s not forget “Memory”
  • Memory is a very critical parameter to obtain overall system performance
  • Every disk I/O saved is performance gained.
  • Tools to monitor and manage memory
using large memory
Using Large Memory
  • Users can use 2.7G of memory out of the box by just changing “max memory” parameter in ASE 12.5.1

Note that 2 shared memory segments have been created one for

1.98G and one for 667M

using large memory 12 5 0 3 and below
Using Large memory [ and below]
  • In ASE and below, to use Large memory on Linux do the following
    • Max configurable shared memory
    • 2.7GB addressable memory
monitoring memory
Monitoring Memory
  • View Memory parameters [ in kb]

% free –k

% cat /proc/meminfo

configuring ase memory
Configuring ASE memory
  • sp_configure “max memory” to tune memory configured for ASE. [Dynamic option since 12.5]
  • Tune this parameter based on ASE resource requirements
  • Remaining memory does not go to “default data cache” starting ASE 12.5
  • Do I have extra memory ?
monitoring and tuning ase parameters
Monitoring and Tuning ASE Parameters
  • To tune various ASE memory parameters

sp_monitorconfig “all”

  • If Reused column has “yes” watch out.
memory tuning tips
Memory Tuning Tips
  • Lock Shared memory
  • Guarantees shared memory to be in RAM
  • Improves performance
  • Tune through sp_configure interface
  • Static option
  • Validate through message in errorlog

11:23:08.33 kernel Locking shared memory into physical memory.

named caches
Named Caches
  • Sizing Caches key to improved performance
  • Cache Partitions improve performance and scalability
  • How ?
    • Create the Named cache with required size
    • Bind the cache to the hot table
named caches32
Named Caches
  • What to bind
    • Transaction Log
    • Tempdb
    • Hot objects
    • Hot indexes
  • When to use Named caches
    • sp_sysmon “Data Cache Management” section reports > 10% spinlock contention
    • sp_sysmon provides the recommendation to do so.
    • Hot lookup tables, frequently used indexes, tempdb activity, high transaction throughput applications are all good scenarios for using named caches.
  • How to determine what is hot ??
    • Cache Wizard
cache wizard
Cache Wizard
  • A new option to sp_sysmon “cache wizard” has been added in 12.5.1 to help in
    • Identifying hot objects in a cache
    • Evaluating effectiveness of Large buffer pools
    • Sizing data caches.
    • Evaluating effectiveness of APF
cache wizard usage
Cache Wizard : Usage
  • Usage

sp_sysmon interval [, cache wizard [, top_N [, filter] ] ]

    • Ranking Criterion
      • LogicalReads / sec
      • Always in decreasing order of PhysicalReads / sec
    • Filter clause
      • Caches containing ‘filter’ pattern
cache wizard examples
Cache Wizard : Examples
  • sp_sysmon ’00:05:00’, ‘cache wizard’, ‘2’, ‘default data cache’

default data cache

Buffer Pool Information

Object Statistics Cache Occupancy Information

cache wizard recommendations
Cache Wizard : Recommendations
  • Identifying “hot” objects

default data cache

Object Statistics Cache Occupancy Information

  • If Cache Hit% is low

For each Object

    • If LR/sec is high and Obj hit% is low, move object to a new cache
    • OR add memory to the cache.
cache wizard recommendations37
Cache Wizard : Recommendations
    • Effectiveness of large buffer pools, apf

default data cache

Buffer Pool Information

  • If Pool usage is high and Pool Hit% is low, add memory to the buffer pool
  • APF% effectiveness provides information on how many pages brought in on account of APF got used.
    • If Pool hit % is low and APF effectiveness is high, then consider increasing APF percentage.
cache partitions
Cache Partitions
  • Cache Partitions help improve scaling
    • Decomposes the cache spinlock
    • Recommendation is to use as many cache partitions as there are engines.
  • How
named caches vs cache partitions
Named Caches Vs Cache Partitions
  • Which one should I use ?
    • Named caches
      • Easily identifiable hot objects, indexes
      • Transaction Log
      • Tempdb
    • Cache Partitions
      • Complex applications with many objects
      • Named cache with heavy spinlock contention
    • Both
      • Best fit is to have named caches with cache partitions
let s take a checkpoint41
Let’s take a Checkpoint
  • Every disk I/O saved is performance gained
  • sp_monitorconfig to tune procedure cache, worker threads etc
  • Named caches help improve performance and scalability
  • Cache partitions + Named Caches best combination
  • If application has large number of objects have as many cache partitions as engines
  • Tempdb, transaction log, hot indexes, hot objects are ideal candidates
reading n writing disk i o
Reading ’n’ Writing [Disk I/O]
  • I/O avoided is Performance Gained
  • ASE buffer cache has algorithms to avoid/delay/Optimize I/Os whenever possible
    • LRU replacement
    • MRU replacement
    • Tempdb writes delayed [Improved select into performance]
    • Write ahead logging [Only Log is written immediately]
    • Group commit to batch Log writes
    • Coalescing I/O using Large buffer pools
    • UFS support
  • Raw devices and File systems supported
  • Asynchronous I/O is supported
raw devices
Raw Devices
  • Raw devices provide exceptional write performance and good read performance
  • Recommended for Transaction Log
file systems
File Systems
  • File System Caching can be effectively used to improve performance (especially reads).
  • File Systems as Secondary cache for ASE
    • Enables ASE to use > 2.7GB
    • Very useful as pages not fitting in ASE cache are accommodated in FS Cache
    • Helps avoid expensive disk I/O
  • Many File System Flavors on Linux
    • extfs, xfs. IBM's JFS and the Reiserfs
  • Recommended file system
    • EXT2
    • EXT3 with journaling disabled
what file system to use
What file system to use
  • EXT3 with journaling disabled


file system vs raw devices
File system vs Raw devices
  • When to use File system
    • Frequent reads
    • Infrequent writes
    • E.g. tempdb [WITH ‘DSYNC’ off] using sp_deviceattr stored procedure

1> sp_deviceattr “tmpdbdev","dsync","false"

2> go

'dsync' attribute of device ‘tmpdbdev' turned 'off'. Restart Adaptive Server for the

change to take effect.

  • When to use Raw devices
    • Frequent writes
    • Infrequent reads
    • E.g Transaction log
  • How does one compare against the other ?
and the winner is
And the winner is….
  • Bottom line : Use mix of File System and Raw devices.


asynchronous i o
Asynchronous I/O
  • Enables ASE to service user tasks after I/O is issued
  • The recommended scheme for doing I/O
  • AIO supported on Raw devices and File Systems on Linux
  • Enabled by default
  • Posix aio and Kernel Supported AIO [RH AS 2.1] are supported
  • What should I use ??
asynchronous i o tunes
Asynchronous I/O tunes
  • fs.aio.max-size specifies the maximum block size performed by one aio read or aio write
  • For optimal create database, alter database performance this should be tuned to 1048676(1MB)
  • To tune this parameter
disk tuning recommendations
Disk Tuning Recommendations
  • More disks in an array improve I/O parallelism
  • Stripe size for OLTP applications
    • Data : 64K
    • Log : 8K
  • Service Times
    • Data : 12-15 ms
    • Log : 2-3 ms
disk tuning recommendations52
Disk Tuning Recommendations
  • RAID 5 for data
    • Independent Data disks with distributed parity blocks
    • Space utilization is lesser than 0+1
    • Read Throughput is high
    • Reliability is high
    • Write Ahead Logging helps delaying data writes
  • RAID 0+1 for Transaction Log
    • RAID 0 for the striping
    • RAID 1 for mirroring’
    • Very reliable
    • High write throughput
i o commands
I/O Commands
  • iostat for device statistics.

$ iostat -d -x /dev/sdb1 /dev/sdc1 /dev/sdd1 5 5

Linux 2.4.9-e.3 (vayu) 06/11/2003

linux i o commands
Linux I/O Commands
  • Identify file system types

% /sbin/mount

  • Create an ext3 file system

% /sbin/mke2fs –b <2K|4K> -j /dev/sdg2

  • View existing raw devices

% raw -qa

  • Create a raw device

% raw /dev/raw/raw1 /dev/sdb1

And add the following in /etc/sysconfig/rawdevices

/dev/raw/raw1 /dev/sdb1

  • Tweaking the file system options

% mount -t ext3 -o rw,noatime,data=writeback /dev/sde1 /tpcc

ase disk monitoring
ASE Disk Monitoring
  • sp_sysmon “00:00:01”, diskio



let s take a checkpoint56
Let’s take a Checkpoint
  • Use Kernel Supported aio
  • Use Mixture of File systems and Raw devices
  • Raid 0+1 for transaction log, RAID 5 for data devices
  • High Speed device for transaction log and tempdb
  • I/O parallelism by having enough disks per array
  • 64KB stripe for data and 8KB stripe for transaction log
sending and receiving network i o
Sending and Receiving [ Network I/O]
  • ASE is capable of handling thousands of users with good throughput and response time
  • 100BASE-T and Gigabit ethernet cards help improve network throughput and response time
  • Full duplex for simultaneous reading and writing to the network is recommended
  • One could also use multiple NIC cards for load balancing
load balancing using multiple nic s
Load Balancing using Multiple NIC’s

Client Machine 1

query ipaddr1 port1

Server Machine

master ipaddr1 port1

master ipaddr2 port2

query ipaddr1 port1

Serviced by listener1

Client Machine 2

query ipaddr2 port2

Serviced by listener2

tuning ase network i o
Tuning ASE Network I/O
  • sp_sysmon “Network I/O Management” section provides information on ASE network performance
  • sp_configure “send doneinproc tokens”, 0 send doneinproc tokens only for select statements.
  • Network packet size tuning both on the client side and on the server side.
    • Server Side
      • “default network packet size”
      • “max network packet size”
    • Client Side
      • “-A” option for isql
      • CS_PACKETSIZE using ct-library
      • “PACKETSIZE” property using JConnect
  • The default value for “tcp no delay” of 1 should improve network performance.
network commands
Network Commands
  • netstat -ts
  • Optimize tcp performance by using port numbers between 1024 and 65000

% echo 1024 65000 > /proc/sys/net/ipv4/ip_local_port_range

  • Some more tcp tunes to improve performance

% echo 0 > /proc/sys/net/ipv4/tcp_sack

% echo 0 > /proc/sys/net/ipv4/tcp_timestamps

% echo 0 > /proc/sys/net/ipv4/tcp_window_scaling

  • Full duplex information

% cat /proc/net/nicinfo/ | grep Duplex

Duplex full

let s take a checkpoint61
Let’s take a Checkpoint
  • 100 BASE-T and Giga bit ethernet help improve network throughput
  • Large packet sizes for bcp, text/image or for any other large data transfer needs
  • Setting send doneinproc tokens to 0 helps improve network performance
  • With Careful tuning “Penguins can actually fly” 