Title: Arial 28pt'

1
ASE 113 How to make Penguins Fly Maximizing
Adaptive Server Enterprise Performance on Linux
Girish Vaitheeswaran Staff Software
Engineer Girish_at_sybase.com August 15-19, 2004
2
The Enterprise. Unwired.
3
The Enterprise. Unwired.
Industry and Cross Platform Solutions
Unwire People
Unwire Information
Manage Information

• Adaptive Server Enterprise
• Adaptive Server Anywhere
• Sybase IQ
• Dynamic Archive
• Dynamic ODS
• Replication Server
• OpenSwitch
• Mirror Activator
• PowerDesigner
• Connectivity Options
• EAServer
• Industry Warehouse Studio

• Unwired Accelerator
• Unwired Orchestrator
• Unwired Toolkit
• Enterprise Portal
• Real Time Data Services

• SQL Anywhere Studio
• M-Business Anywhere
• Pylon Family (Mobile Email)
• Mobile Sales
• XcelleNet Frontline Solutions
• PocketBuilder
• PowerBuilder Family
• AvantGo

Sybase Workspace
4
Contents

• No Introduction to Linux
• System Performance 101
• De-mystifying Processors
• Lets not forget Memory
• Reading and Writing Disk I/O
• Sending and Receiving Network I/O
• Identifying Overall System Performance Issues.
• Known problems and Solutions
• More information
• Conclusion

5
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,
  Opteron), IBM, SGI, SUN etc
• Importantly, appealing to Bean Counters

6
Linux Trends

• Consolidation from big boxes to commodity
  hardware
• Migration to 4CPU P3s with 4/8GB RAM running
  some flavor of Linux
• Exploiting Moores law
• Moving from 750Mhz CPUs to 1.5GHz CPUs with
  hyperthreading
• Sizing based on Increased clock speeds.

7
ASE on linux

• ASE on Linux supported since 11.0.3.3
• ASE 12.5.0.3 supports RH2.1
• ASE 12.5.1/12.5.2 will support RH 3.0
• SuSE/RedFlag supported as well.
• More info available at www.sybase.com/linux/ase

8
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

9
De-mystifying Processors

• Classes of processors
• Xeon with HT Technology (CISC)
• Pentium with HT Technology (CISC)
• Itanium EPIC Architecture
• AMD Opteron
• What is hyper threading
• Doing more work in each clock cycle by providing
  thread level parallelism in each processor.
• Itanium EPIC Architecture
• Explicitly Parallel Instruction Computing implies
  that the compiler decided what sets of
  instructions execute together. Use for high end
  database and ERP applications.
• The AMD Opteron Story
• Provides the ability to run 32 bit applications
  and 64bit applications on the same hardware.

10
Consolidation

• Consolidation Guidelines
• One 1.5GHz CPU does not necessarily yield the
  same performance as two 750MHz CPUs. 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
• Use Hyper threading carefully as it has been
  known to hurt performance in quite a number of
  instances.
• Careful benchmarking needs to be done to evaluate
  if HT works for your application.

11
Processor Tips

• Identifying Number of processors/Clock
  speed/Hyper threading
• cat /proc/cpuinfo

12
Processor Tips

• Determining cpu usage (mpstat, top, vmstat)
• mpstat 5 5

13
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 cpus is
  close to 100

14
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 ASEs
  ability to scale to 64 engines.

15
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

16
Monitoring and Tuning Engines

• sp_sysmons kernel section reports utilization as
  shown
• sp_sysmon 000200, kernel

17
Monitoring and Tuning Engines

• Influencing kernel utilization
• CPU bound tasks
• I/O bound tasks
• Tuning runnable process search count
• I/O polling process count

18
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

19
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

20
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

21
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

22
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 lt 5

23
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/Os per check or Avg
  Net I/Os per check is very low.
• If the avg cpu utilization is between 70-90

24
Lets 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

25
Lets 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

26
Using upto 2.7G in 12.5.1

• 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
27
Using upto 2.7G 12.5.0.3 and below

• In ASE 12.5.0.3 and below, to use upto 2.7G on
  Linux do the following
• Max configurable shared memory
• 2.7GB addressable memory

28
Large Memory Support (12.5.2)

• Configuring more than 2.7GB of memory on 32 bit
  Linux Systems.
• Configuring ASE to use the maximum memory
  available on the system.
• Linux on IA32 with PAE can support upto 64G
• AS 2.1 can go up to 16G
• RHEL 3 can go upto 64G
• Pre- ASE 12.5.2, can only access 2.7G
• To use additional memory use FS devices

29
Advantages of Large Memory Support

• Extra memory can now be used by data cache.
• Reduced number of physical i/os ( read and write)
• Efficient usage of memory
• Better response time.
• Improved throughput.
• Efficient use of resources.
• Flexibile Usage Dynamicaly configurable
• Auto Tuning

30
Customer (Implementer) Usage

• Configuring /dev/shm
• 8GB memory space reserved to create shm files.
• Configuring extended cache
• extended cache size new config parameter
• Size in 2K pages.
• create, extend, delete are all dynamic
• sp_configure, sp_helpconfig

31
Customer (Implementer) Usage

• Startup
• Cache Manager
• extended cache size 102400 2G
• Run-time
• creation
• sp_configure extended cache size, 102400
• go
• extension
• sp_configure extended cache size, 204800 To
  4G
• go
• deletion
• sp_configure extended cache size, 0
• go

32
Performance Comparison

• Order Processing benchmark
• 144 warehouse 10G database
• Mixed Read and write
• Multiple named caches
• 2.7G to primary data caches
• 3 G of Extended cache
• Devices used
• Raw
• File system

33
Throughput
34
Monitoring Memory

• View Memory parameters in kb
• free k
• cat /proc/meminfo

35
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 ?

36
Monitoring and Tuning ASE Parameters

• To tune various ASE memory parameters
• sp_monitorconfig all
• If Reused column has yes watch out.

37
Monitoring and Tuning ASE Parameters
38
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
• 112308.33 kernel Locking shared memory into
  physical memory.

39
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

40
Named Caches

• What to bind
• Transaction Log
• Tempdb
• Hot objects
• Hot indexes
• When to use Named caches
• sp_sysmon Data Cache Management section reports
  gt 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

41
Cache Partitions

• Cache Partitions help improve scaling
• Decomposes the cache spinlock
• Recommendation is to use as many cache partitions
  as there are engines.
• How

42
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 and cache
• partitions

43
Named Caches Bottom Line
79
44
Auto tuning of cache partitions

• To reduce spinlock contention
• Pre 12.5.2, default cache partitions 1
• With 12.5.2, auto tuning of cache partitions
• Number of engines is greater than or equal to 2.
• Only for default data cache
• When local and global DEFAULT and size gt
  100M.
• sp_helpcache, sp_cacheconfig should show the new
  tuned cache partitions.
• Configuration file will not be written with the
  new value.
• New message added in errorlog.

45
Lets 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

46
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

47
Raw Devices

• Raw devices provide exceptional write performance
  and good read performance
• Recommended for Transaction Log

48
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 gt 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

49
What file system to use

• EXT3 with journaling disabled

24
50
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
• 1gt sp_deviceattr tmpdbdev","dsync","false"
• 2gt 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 ?

51
And the winner is.

• Bottom line Use mix of File System and Raw
  devices.

60
52
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 ??

53
And the Winner is
95
54
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

55
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

56
Disk Tuning Recommendations

• RAID 5 for data
• Independent Data disks with distributed parity
  blocks
• Space utilization is lesser than 01
• Read Throughput is high
• Reliability is high
• Write Ahead Logging helps delaying data writes
• RAID 01 for Transaction Log
• RAID 0 for the striping
• RAID 1 for mirroring
• Very reliable
• High write throughput

57
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

58
Linux I/O Commands

• Identify file system types
• /sbin/mount
• Create an ext3 file system
• /sbin/mke2fs b lt2K4Kgt -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,datawriteback
  /dev/sde1 /tpcc

59
ASE Disk Monitoring

• sp_sysmon 000001, diskio
• Device
  
• /tpcc_2/data/data1.dat
  

60
Lets take a Checkpoint

• Use Kernel Supported aio
• Use Mixture of File systems and Raw devices
• Raid 01 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

61
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

62
Load Balancing using Multiple NICs

• 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
63
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.

64
Network Commands

• netstat -ts
• Optimize tcp performance by using port numbers
  between 1024 and 65000
• echo 1024 65000 gt /proc/sys/net/ipv4/ip_local_p
  ort_range
• Some more tcp tunes to improve performance
• echo 0 gt /proc/sys/net/ipv4/tcp_sack
• echo 0 gt /proc/sys/net/ipv4/tcp_timestamps
• echo 0 gt /proc/sys/net/ipv4/tcp_window_scaling
• Full duplex information
• cat /proc/net/nicinfo/eth0.info grep Duplex
• Duplex full

65
Lets 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

66
Identifying Overall System Performance Issues

• Use tools such as sp_objectstats, sp_sysmon,
  sp_monitor, sp_monitorconfig, Monitoring Tables
  to identify problem areas
• New options to sp_monitor
• These provide stored procedure wrappers to
  monitoring tables.
• Hides the details pertaining to monitoring tables
  and monitor tables related configuration
  parameters from the end user.
• Useful for performance analysis at a finer
  granularity.
• Helps answer questions like which stored
  procedure consumes max cpu time

67
Existing sp_monitor Usage

• The pre 12.5.2 behavior of sp_monitor has not
  changed.
• When sp_monitor is invoked without any arguments,
  server level statistics such cpu_busy, io_busy,
  packet_errors etc are reported.

68
New options to sp_monitor Setup

• Follow instructions to install the monitoring
  tables. This includes the following set of steps
• Create a loopback server entry using sp_addserver
• Have an entry for the loopback server in the
  interfaces file
• Grant role mon_role
• Run the installmontables script
• Change the max SQL text monitored configuration
  parameter to a value like 2048 bytes (Reboot
  required)
• Now run sp_monitor enable to enable the new
  monitoring options. This enables the
  configuration parameters needed for using the new
  options
• Note that this option is not persistent, across
  ASE reboots.

69
Sp_monitor help

• Provides syntax and examples for different
  options
• sp_monitor help
• Usage sp_monitor connection, cpu diskio
  elapsed time
• sp_monitor "help","statement"
• Usage sp_monitor statement, cpu diskio
  elapsed time
• Examples sp_monitor 'statement', 'elapsed time'

70
Sp_monitor connection

• Provides statistics on the different connections
  to ASE in the descending order of elapsed time on
  connections currently executing.
• Other sorting options can also be used

71
Sp_monitor statement

• Provides statistics similar to sp_monitor
  connection but these statistics are based on
  the statements currently executing.
• Other sorting options can also be used

72
Sp_monitor procedure

• Provides statistics on stored procedures.
• Examples
• sp_monitor "procedure" -- Display summary for all
  stored procedures
• sp_monitor "procedure", "detail" -- Display
  detail for all stored procs
• sp_monitor "procedure", "db1" -- Display avg data
  for database db1
• sp_monitor "procedure", "db1", "detail" -- Detail
  for database db1
• sp_monitor "procedure", "db1", "sproca" --
  Display avg data for sproca
• sp_monitor "procedure", "db1", "sproca", "detail"
  -- Detail for sproca

73
Sp_monitor procedure
74
Sp_monitor procedure

• This procedure uses the monSysStatement
  monitoring table
• This provides only the recently executed
  statements, the number of statements monitored
  depends on the statement pipe max messages
  parameter (internally tuned to 100,000)

75
Sp_monitor event

• Provides wait event metrics for all tasks in the
  system
• This is based on a snapshot collected when the
  sp_monitor "event" stored procedure is executed.

76
Sp_monitor disable

• sp_monitor disable are used to turn on and turn
  off this option. All configuration parameters are
  restored to old state.
• One could run sp_monitor enable, collect a
  number of snapshots using their favorite option
  and then run sp_monitor disable once monitoring
  is done.
• Look into details on Monitoring tables
  documentation to see workings of each of the
  tables used by these stored procedures.

77
Known Problems and Solutions

• Timeslice problems when using AS 2.1 on 12.5.0.x
  and 12.5.1 releases on block devices
• Async I/O system calls occasionally block causing
  timeslice errors to occur.
• Work around is to use traceflag 1639
• Problem will not show if using raw devices.
• Fixed in 12.5.2 as part of CR 322080
• Problems starting ASE glibc version issues
• Verify you have the certified version of glibc
  using rpm qa
• Verify this with the glibc certification matrix
  at www.sybase.com/linux/ase
• Using mapped_base to get to 2.75G in 12.5.1
  causes core dumps
• The mapped_base requirement was removed in 12.5.1
  and above. If you have the following line in
  run_server remove it
• sudo /bin/echo 26843546 gt /proc//mapped_base

78
More information

• www.sybase.com/linux/ase
• http//www.peppler.org/FAQ/linux.html
• ase-linux-list_at_isug.com
• news//forums.sybase.com/sybase.public.ase.linux

79
Conclusion

• With Careful tuning Penguins can actually fly ?