1 / 36

ASE119 Maximizing Server Performance

ASE119 Maximizing Server Performance. Steven J. Bologna Maximizing Server performance bologna@sybase.com /248-797-2802 08/04/2003. Agenda. Tools to monitor performance Monitoring Tools Where to Spend Your Time? Server Issues Networking Issues Common SQL Issues Common Java Issues

cricket
Download Presentation

ASE119 Maximizing Server Performance

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ASE119 Maximizing Server Performance Steven J. BolognaMaximizing Server performancebologna@sybase.com /248-797-280208/04/2003

  2. Agenda • Tools to monitor performance • Monitoring Tools • Where to Spend Your Time? • Server Issues • Networking Issues • Common SQL Issues • Common Java Issues • Common Database Issues • New Features for Improving Performance • Questions

  3. Monitoring Tools • Which One? • Sp_monitor • Sp_monitorconfig(plus 12.5 new monitors) • Sp_sysmon • Sp_object_stats • Sp_lock(or variants) • SQL Queries(sysprocesses, syslocks) • Dbcc pss/dbcc sqltext • Monitor Server/Client • Historical Server • SQL Expert • MDA tables • Scripts with SQL, Perl etc… • Lots more….

  4. Where to Spend your Time • 11 levels of "issues" • application tuning • network tuning • server side application tuning/Middle Tier • stored procedures/SQL/SQLJ • logical placement - column location/de-normalization • locking/blocking • physical placement/index management • memory tuning(caches/OS/cache binding) • Sybase kernel/configuration tuning • system tuning - O/S level tuning • Drive placement/speed/caching

  5. Where to spend your time?-sp_sysmon • 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 % <-- Major % of time • 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 % • Latch Contention 0.1 0.0 12 0.0 % • Log Semaphore Contention 4.0 0.0 481 0.1 % • PLC Lock Contention 0.1 0.0 11 0.0 % • 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 % <-- • Network Packet Sent 651.2 5.9 78143 23.4 % <-- • SYSINDEXES Lookup 0.0 0.0 0 0.0 % No Longer in sysmon • Other Causes 1055.9 9.6 126710 37.9 % <--

  6. Server issues • Persistent connections vs. reconnecting • CGI programs, Java code • Caching/named caches • Old/new features • Pre ASE 12.0/ASE 12.0 • ASE 12.5 features

  7. 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 CPU’s • Just plain useless work…&^*%$ • Pooled Connections • By using pooled connections eliminate connect/reconnect • Some overhead still there due to “proxy”

  8. Caching 12.0 features • use new 11.9.3/12.0/12.5 feature of cachelets • this should be done for at least the "default data cache" • example of usage: • sp_configure ‘global cache partition’, <n> • sp_cacheconfig <cache>, cache_partition=<n> • this reduces contention by a factor of 1/(N) where N is 1, 2, 4, 8, 16...64

  9. Caching • Example of a performance issue: • Cache: Default data Cache • Spinlock Contention n/a n/a n/a 62.0 % • Utilization n/a n/a n/a 8.3 % • This is a 62% drag on I/Os but… • Cache Searches • Cache Hits 8.2 1.4 2447 100.0 % • Found in Wash 6.5 1.1 1943 79.4 % • Cache Misses 0.0 0.0 0 0.0 % • ----------------------- ------------ ------------ ---------- • Total Cache Searches 8.2 1.4 2447 • Don’t make a Mountain out of a Molehill….

  10. Caching • Cache: Default data cache • Spinlock Contention n/a n/a n/a 34.0 % • Utilization n/a n/a n/a 12.3 % • Shows several things: • contention does not come down linearly • contention may be moved elsewhere • should continue moving the “hash cache” to a bigger number - use 4 or 8 • By improving contention you may induce more “turnover” • Number of pages moved out of cache per second • Generally due to poor query/index

  11. Caching • I used to recommend using "named cache" first • now I use "hash cache" first • sp_configure ‘global cache partition’, 2 or 4 • Sp_cacheconfig “default data cache”,”cache_partition=N” • 2: if there are existing "named caches" • 4: if there are no "named caches" • then a specific pool/Cache • usually the problem is the "default data cache” • sometimes the problem is the “tempdb cache”

  12. Caching • 12.0 recommendations for mid to high end system • Default data cache (possibly partitioned) - Low end system • 1 Tempdb cache (possibly partitioned) - Low end system • Log cache (all transaction logs) • Static table cache • Active Table Cache • 12.5.0.3 recommendations-More later • Default data cache (possibly partitioned) – Low end system • 1 tempdb cache (possibly partitioned) – Low end system • Log Cache • Static table cache • Active table cache • Nth tempdb + Nth tempdb cache

  13. Network Issues • ping time • bandwidth issues • tracert • 10Mbit/100Mbit/1GBit • packet size (Sybase packet size)

  14. Network Issues • ping 10.10.10.2 Pinging 10.10.10.2 with 32 bytes of data: Reply from 10.10.10.2: bytes=32 time<10ms TTL=128 Reply from 10.10.10.2: bytes=32 time<10ms TTL=128 • Excellent under 10ms • Good between 11-20ms • Fair between 21-60ms • Sub-Average 61-100ms • Poor times 100ms+

  15. 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 • Java Can send “Large Array type” result set/vector • 12.5 can support Larger varchar(XX)

  16. Network Issues • tracert (NT)/traceroute (AIX...) • this displays the "route” or network path • this also allows you to find the "weak" link • look for: • lots of "routes”(over 5) • significant slowdown in specific areas

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

  18. Networking Issues • 10Mbit/100Mbit/1GBit • more is better • try to have the highest speed on the server as possible • try to match the: • Client Slowest • hub/switch • Server Fastest

  19. 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 • Collisions: • Netstat –I card 30 (O/S dependant) • Look for more than 5% • Sysmon • Avg Bytes Rec'd per Packet n/a n/a 390 n/a • Avg Bytes Sent per Packet n/a n/a 501 n/a

  20. Common SQL Issues • Lots of individual SQL statements, not procedures • Non-pivoted results • select distinct column • select count(*) • Use “Big” result sets in Java (Text, Image,Blob)

  21. Common SQL Issues • This includes SQL statements that should be joins... • Many Java developers use good OO techniques but.. • forget about stored procedures/Java procedures • don’t spend time debugging SQL statements • don’t persist some of the data • One application went from over 1,000 lines of Java code to about 50 lines of SQL code(stored procedure) • Know where it is faster to do the coding and what will produce a faster result

  22. Common SQL Issues • Non-pivoted results • What is pivoting? Why should I do it? • old characteristic functions • new case functionality(11.5/12.0 Java) • Benefits • Reduced network traffic • Fewer network hops • Less fetch operations • Faster applications

  23. 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 … • from table_values • Can use SQL-J to perform case statements • Can use Java Functions/classes to return results

  24. 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(if any) • the results are shipped back to the client • make sure that this is needed, else eliminate the distinct

  25. 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 exists” statement • May be done to “size” the array…. • similar to: upper(lastname) • another common error • performs more work than count(*) • causes index not to be used • also, “mathematical” functions operating on columns are frequently done

  26. 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 server? • Heavyweight (muscular) clients

  27. Common Java Issues • use of "char" fields as inputs to procedures • example: • ...and soc_sec_number = convert(numeric(9,0), @var) • this forces the server to perform additional work • optimizer must use "unknown" optimization • Sometimes “unknown” optimization either good or bad • potential data type mismatch • potentially giving poorer plans • Watch these columns for Skew • Try to use Execute immediate! • Exec immediate has exact values for this

  28. Common Java Issues • mismatched data types • these are character data types for input to a stored procedure or direct SQL statements • by using character data types it increases the likelihood of using a char(xx) data type when it should have been char(yy) or even Numeric • again, the optimizer is "fooled" into potentially poorer plans

  29. 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 • 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/EJB) • most of the time ASE generally has extra cycles

  30. 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,2000, XP) takes at least 96MB - generally 128+MB • Application must be distributed to the Client every time

  31. Common Database Issues • Mismatched Joined Columns • Missing Indexes • Missing Clustered Index • Statistics not updated frequently enough

  32. Common database Issues • Mismatched joined columns • bad data types • convert functions(explicit) • generally causes indexes to be missed • implicit conversion char(5) to char(10) • causes extra work for server to convert • Missing Indexes • this is a common issue • most likely caused by “rushed” schedules • DBA’s generally do not have all the possible queries • new tools for analyzing/forcing queries(abstract plans 12.0) • must find queries that support “new” indexes • use sp_showplan spid,null,null,null (11.5) • system table sysprocesses

  33. Common Database Issues Missing clustered index • Best performing index • use highest unique values on first column • generally application developers choose key • may not know full distribution or all queries Partitioned Tables: • Partitioned tables help with Index build speed • Also can reduce contention on tables that are “Heap”

  34. New Features for Improving Performance • Multiple tempdb • Sp_monitorconfig (new version) • Java Features • Sp_object_stats (old) • 12.5.0.3 features

  35. SDN Presents CodeXchange • Forum for exchanging samples, tools, scripts, etc. • New features enable community collaboration • Download samples created by Sybase or external users • Leverage contributions of others to extend Sybase products • Contribute code or start your own collaborative / open source project with input from other product experts • Any SDN member can participate • Log in using your MySybase account via SDN • www.codexchange.sybase.com • Or via SDN at www.sybase.com/developer • SDN & CodeXchange at TechWave • Visit SDN in the Technology Boardwalk • Learn about CodeXchange in the Sybase Booth Theater

  36. Questions? E-mail bologna@sybase.com Mailing Address Steven J. Bologna Sybase, Inc. 1000 Town Center, Suite 1800 Southfield, MI 48075

More Related