DM209 Maximizing Your Performance and Tuning Efforts

1
DM209Maximizing Your Performance and Tuning
Efforts
Steven J. Bologna Principal Consultant Sybase
Professional Services bologna_at_sybase.com
2
Agenda

• Where to Spend Your Time
• Server Issues
• Networking Issues
• Common SQL Issues
• Common Java Issues
• Stored Procedures
• Common Java Errors
• Common Database Issues

3
Agenda continued

• Methodology for Improvement
• New Features for Improving Performance
• Questions

4
Where to Spend Your Time

• Where do you start?
• sp_sysmon helps identify the location
• provides initial breakdown
• provides a list of potential problem areas
• timing of applications
• provides detailed information on each screen
• 10 levels of possible issues
• This identifies where the issue might exist

5
Where to Spend Your Time

• sp_sysmon helps identify the location
• Task Context Switches Due To
• Voluntary Yields 133.3
  1.2 15997 4.8
• Cache Search Misses 487.4
  4.4 58487 17.5 lt--
• System Disk Writes 6.0
  0.1 717 0.2
• I/O Pacing 20.2
  0.2 2422 0.7
• Logical Lock Contention 2.8
  0.0 331 0.1
• Address Lock Contention 3.8
  0.0 452 0.1
• Log Semaphore Contention 4.0
  0.0 481 0.1
• Group Commit Sleeps 4.1
  0.0 486 0.1
• Last Log Page Writes 103.4
  0.9 12405 3.7
• Modify Conflicts 11.2
  0.1 1341 0.4
• I/O Device Contention 11.3
  0.1 1358 0.4
• Network Packet Received 290.1
  2.6 34809 10.4 lt--
• Network Packet Sent 651.2
  5.9 78143 23.4 lt--
• SYSINDEXES Lookup 0.0
  0.0 0 0.0
• Other Causes 1055.9
  9.6 126710 37.9 lt--

6
Where to Spend Your Time

• Timing of applications
• - first start with "whole" parts of
  the application/screens
• - then ... break it down to
• SQL Statements (timing)
• ping time traceroute
• SQL statements
• application timing
• identify what kind of connectivity is being used
• graph on speed

7
Connectivity Speed

• C/CT-Library
• ODBC
• Java/JDBC/ODBC bridge
• Java/JDBC (type 3)
• Java/JDBC (type 4)

8
Where to Spend Your Time

• 10 levels of "issues"
• application tuning
• network tuning
• server side application tuning
• stored procedures
• logical placement - column location
• locking
• physical placement/index management
• memory tuning
• Sybase kernel/configuration tuning
• system tuning - O/S level tuning

9
Server Issues

• persistent connections vs. reconnecting
• CGI programs, Java code
• connects per second
• how to add capacity?
• caching/named caches
• old/new features
• pre ASE 12.0/ASE 12.0

10
Server Issues

• persistent connections vs. reconnecting
• reconnecting costs between .05 and 2 seconds
• tasks transfer to "idle" engine when first
  command is issued
• extra time is consumed by "useless" work
• the further the distance, the longer the connect
• the context switch "consumes" CPU bandwidth on 2
  CPUs

11
Server Issues

• How to add more connects per second
• create multiple "outside" servers
• use the CIS functionality
• map the "proxy" tables to the original" tables
• move the stored procedures to "outside" servers
• add persistent connections between ASE servers
• this gives you higher connects per second

12
Server IssuesHow to add more connects per second
remote_svr
local_svr
remote_svr
13
Server IssuesCaching/Named Caches

• create separate named cache for tempdb
• use sp_helpdb to help size the cache
• 2/4/16K pools
• create separate named cache for for other
  transaction logs
• this should be small (generally under 50MB)
• should be about as big as
• biggest transaction size number of
  active transactions fudge factor

14
CachingNew Features

• use new 11.9.3/12.0 feature of cachelets
• this should be done for at least the "default
  data cache"
• example of usage
• sp_configure global cache partition, ltngt
• sp_cacheconfig ltcachegt, cache_partitionltngt
• this reduces contention by a factor of 1/(N)
  where N is 1, 2, 4, 8, 16...64

15
Caching
Example of a performance problem
16
Caching
Example of a performance gain

• Shows several things
• contention does not come down linearly
• contention moved elsewhere
• should continue moving the hash cache to a
  bigger number - use 4 or 8

17
Caching

• I used to recommend using "named cache" first
• now I use "hash cache" first
• sp_configure global cache partition, 2 or 4
• 2 if there are existing "named caches"
• 4 if there are no "named caches"
• then a specific pool
• usually the problem is the "default data cache
• sometimes the problem is the tempdb cache

18
Networking Issues

• ping time
• arp cache (AIX issue)
• bandwidth issues
• tracert
• 10Mbit/100Mbit/1GBit
• packet size

19
Networking Issues

• ping time
• Reply from 10.10.10.120 bytes32 time51ms
  TTL253
• Excellent under 5 ms
• Good between 5-20ms
• Average times 20-75ms
• Sub Average 75-200ms
• Poor times 200ms

20
Network Issues

• arp cache
• issue with AIX 4.3.3
• periodically the IP/DNS hostname will be "lost"
• watch the netstat -a or arp -a output
• server will periodically "disappear"
• client/server would be on different machines
• fixed with AIX 4.3.3.0 maintenance level 2
• IY05739 cant add arp entries
• IY05560 cant use telnet, ping, ftp, etc...

21
Network Issues

• bandwidth issues
• watch out for bandwidth consuming queries
• lots of traffic back and forth to compute/find
  answer
• lots of individual selects working on one problem
• many in-efficient queries
• images/text datatypes
• watch out for these datatypes

22
Network Issues

• tracert (NT)/traceroute (AIX,HP,Sun,Linux)
• this displays the "route or network path
• this also allows you to find the "weak" link
• look for
• lots of "routes"
• significant slowdown in specific areas

23
Networking Issues

• traceroute output...
• 1 10ms 10.10.10.2 ltlt 71ms slow
• 2 81ms detr.rr.com
• 3 90ms bbnplanet.net ltlt 7 links why?
• ... 120ms br1.bbnplanet.net
• 10 150ms www.mycompany.com

24
Networking Issues

• 10Mbit/100Mbit/1GBit
• more is better
• try to have the highest speed on the server as
  possible
• try to match the
• server
• hub/switch/router
• client

25
Networking Issues

• packet size
• look at sp_sysmon for more details
• also do a network trace...
• 11.1.1 CT-Library uses streaming reads
• multiple packets/rows sent for 1 result set

26
Common SQL Issues

• Lots of individual SQL statements
• Non-pivoted results
• select distinct column
• select count()

27
Common SQL Issues

• Lots of individual SQL statements
• this includes SQL statements that should be
  joins...
• many Java developers use good OO techniques but..
  
• forget about stored procedures/Java procedures
• dont spend time debugging SQL statements
• dont persist some of the data
• one application went from over 1,000 lines of
  Java code to about 50 lines of SQL code

28
Common SQL Issues

• Non-pivoted results
• What is pivoting? Why should I do it?
• old characteristic functions
• new case functionality
• Benefits
• reduced network traffic
• fewer network hops
• less fetch operations

29
Common SQL Issues

• Pivoting Example
• select day, value from table_values
• vs.
• select sum(case when day 1 then value
• else 0
• end) as day_1
• sum(case when day 2 then value
• else 0 end ) as day_2 ...
• from table_values

30
Common SQL Issues

• Select distinct column from table
• have seen new programmers fall into this habit
• this causes extra work to be done
• retrieves all of the results
• sorts the results
• the duplicates are eliminated
• the results are shipped back to the client
• make sure that this is needed, else eliminate the
  distinct

31
Common SQL Issues

• select count()
• have seen this issue surface at every client
• most common error
• this actually counts the number of rows that
  match the criteria
• most people only need if exist statement
• similar to upper(lastname)
• another common error
• performs more work than count()
• also, mathematical functions operating on
  columns are frequently done

32
Common Java Issues

• use of "char" fields as inputs to procedures
• mismatched datatypes
• system.out.println
• writing excessive information to console log
• debug vs. production code
• calculations on client or cerver?
• heavyweight (muscular) clients

33
Common Java Issues

• use of "char" fields as inputs to procedures
• example
• ...and soc_sec_number convert(numeric(9,0),
  _at_var)
• this forces the server to perform additional work
• optimizer must use "unknown" optimization
• potential datatype mismatch
• potentially giving poorer plans
• Java 2 supports more datatypes than JDK 1.0.2 or
  1.1

34
Common Java Issues

• mismatched datatypes
• these are character datatypes for input to a
  stored procedure or direct SQL statements
• by using character datatypes it increases the
  likelihood of using a char(xx) datatype when it
  should have been char(yy)
• again, the optimizer is "fooled" into potentially
  poorer plans

35
Common Java Issues

• system.out.println
• seems to be "non-critical"
• usually this is put in for "debugging" purposes
  and "forgotten"
• used for displaying SQL statements or status
  information
• also used for "monitoring" of the application
• this can slow the application down by 50

36
Common Java Issues

• writing excessive information to console log
• usually this is left in the application for
  debugging purposes
• intentionally left in for troubleshooting
• again this can cause up to 50 drop in
  performance
• debug vs. production code
• PowerJ has a feature for builds that can
  eliminate the Debug.log() for the production
  build. This is useful for development.

37
Common Java Issues

• Calculations on Client or Server?
• this is common question
• no absolute answer
• better to move calculations to where there is
  more idle cycles
• better to use an Application Server (i.e. EAS)
• most of the time ASE generally has extra cycles

38
Common Java Issues

• Heavyweight (Muscular) Clients
• same as a client/server issues
• memory is consumed by the application
• now the application has the additional "weight"
  of a JVM (1MB)
• minimum Windows 95 configuration should be at
  least 64MB memory
• Windows (95,98,NT) takes at least 64MB -
  generally 70MB
• sometimes shutdown browser (IE or Netscape)

39
Common Database Issues

• Mismatched Joined Columns
• Missing Indexes
• Missing Clustered Index/Locking Contention

40
Common Database Issues

• Mismatched joined columns
• common issue with new developers
• joined columns always should match datatypes
• look for poor plans (sp_showplan, abstract plans)
• also, in the code look for the convert()
  statement
• once the mismatched column is in the design it
  will be a permanent loss of performance
• catch it early in the design stage

41
Common Database Issues

• Missing Indexes
• this is a common issue
• most likely caused by rushed schedules
• DBAs generally do not have all the possible
  queries
• new tools for analyzing queries(abstract plans
  12.0)
• must find queries that support new indexes
• use sp_showplan spid,null,null,null
• system table sysprocesses

42
Common Database Issues

• Missing clustered index/locking contention
• previous versions 11.5.X and below sometimes
  needed
• with 11.9.2 can use page or row level locking for
  this
• usually start like this
• partition the table (11.0.X)
• switch the clustered index(11.X)
• partition the table with cluster(11.5.X)
• switch to row level locking
• clustered index is the fastest at retrieval

43
Methodology for Improvement

• Identify the issues
• start with the "worst" performing screen/page
• list out as many issues as can be identified
• order the issues in ease of implementation
• also, factor in the potential gain
• give solutions to each of the problems
• start at the highest level (of the 10) then move
  down the layers
• start peeling off the layers
• start with the most common problems listed
  previously

44
Methodology for Improvement

• Problem solving
• Can the "problem" be moved to another "level" and
  be solved?
• Is this a textbook solution?
• Is there a solution described here?

45
Features for Improving Performance

• New features in ASE 12.0 for improving
  performance
• Java stored procedures/Java procedures
• Vectors instead of temp tables
• Global variables
• Create your own functions!!
• Many more not listed here!!!

46
Questions

• E-mail Address
• bologna_at_sybase.com
• Mailing Address
• Steven J. Bologna
• Sybase, Inc.
• 1000 Town Center, Suite 1800
• Southfield, MI 48075