370 likes | 574 Views
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
E N D
ASE119 Maximizing Server Performance Steven J. BolognaMaximizing Server performancebologna@sybase.com /248-797-280208/04/2003
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
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….
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
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 % <--
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
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”
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
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….
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
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”
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
Network Issues • ping time • bandwidth issues • tracert • 10Mbit/100Mbit/1GBit • packet size (Sybase packet size)
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+
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)
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
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
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
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
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)
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
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
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
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
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
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
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
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
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
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
Common Database Issues • Mismatched Joined Columns • Missing Indexes • Missing Clustered Index • Statistics not updated frequently enough
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
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”
New Features for Improving Performance • Multiple tempdb • Sp_monitorconfig (new version) • Java Features • Sp_object_stats (old) • 12.5.0.3 features
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
Questions? E-mail bologna@sybase.com Mailing Address Steven J. Bologna Sybase, Inc. 1000 Town Center, Suite 1800 Southfield, MI 48075