ASE 103 Managing and Monitoring Database Servers in an Enterprise

1
ASE 103 Managing and Monitoring Database Servers
in an Enterprise
Aimee Grimes Joe Love Global Applications and
Infrastructure Team aimee_at_sybase.com,
jlove_at_sybase.com August 6, 2003
2
Agenda

• Overview of the Application Infrastructure
  Operational Environment
• Primary Monitoring and Management Tools
• Sybase ASE 12.5.0.x Features
• Sybase ASE 12.5.1 Features
• Future Plans

3
Sybase Application Infrastructure Overview
Sybase Trivia Fact1 The DBA Group is the most
fun group at Sybase.
4
Philosophies Goals

• Uptime, Uptime, Uptime (99.999 Goal)
• No unplanned downtime
• Disaster Recovery (Rep Server 12.6 Multiple Warm
  Standby)
• Performance
• 100 Committed to Drinking our own champagne
• Closed Feedback Loop to Engineering
• Keep It Simple
• Provide Best of Breed Solutions to Enable our
  Business and to provide Information in Action.

5
Staffing Support
Where are those DBAs and what do they do?

• 24x7 Follow the Sun Support Model
• 10 full time DBAs worldwide(East West Coasts
  US, Europe)
• 2 Part Time DBAs in Hong Kong
• 1,400 logged customer requests for 2002
• 7,964 DBA logged events for 2002

6
What Do We Support?
Everything

• 216 Adaptive Server Enterprise Servers
  (11.5.1-12.5.1)
• 57 EAServer/PeopleSoft Application Servers
• 40 Replication and RSM Servers
• 37 Open Servers
• 6 Enterprise Portal Servers
• 12 EAI Components (Adapters, Integrators
  Connectors) (e.g. PeopleSoft ? GEAC Smartstream)
• 2 Message Buses with 4 WebSphere MQ Series
  Servers each

7
Platforms
Now and Future

• Sun Solaris
• HP HP-UX
• Intel MS NT/Win2k
• Intel Linux
• Aging RISC systems to be migrated to CISC/Itanium
  Solutions
• Will Use 12.5.1 Migration Utility to aid Sun
  ?LINUX moves.

8
Applications
Primary Vendors

• PeopleSoft
• GEAC Smartstream (formerly Dun Bradstreet)
• Siebel
• Clarify
• EMA
• Vignette
• Home Grown Apps (c, java, jsp, html, sybperl,
  web.sql, PowerDynamo)
• Primarily installed in Engineering environment

9
Challenges
No Whining

• Variety of Products Supported (Advantages
  Disadvantages)
• Economics
• No Dedicated RD Team
• Daily Operational Support v. Projects and
  Innovation
• Legacy Systems
• Conflicts between Best Solution v. Best
  Solution With Current Resources

10
Why Arent All Our ASEs at 12.5.x?

• Third Party Application Certification
  Restrictions
• If It Isnt Broken, Dont Fix It
• Difficult to Get Buyoff from Business Owners
• Show us the ROI

11
Monitoring our Systems
Arguable Statistic 95 of all monitoring is in
the availability of the server!!!
12
What is Monitoring?

• Uptime Monitoring Most Critical
• Availability Monitoring Quite Critical
• Status Monitoring Very Important
• Performance Monitoring Would be nice, ad hoc.

13
Monitoring System Architecture

• The Basic Application Environment
• The Front End (Application Tier)
• The Middle (Middle Tier(s))
• The Back End (Servers)
• Application Infrastructure (Data Movement)
• Maintenance Utilities
• Degree of Maintenance
• Application of utilities
• Scripts (Bourne shell, perl, sybperl)
• Daemon Type Monitors
• Sybase Replication Server Manager Server

14
We Want Information!

• Getting information in
• Tasks have results
• Push vs. Pull
• Checking that information gets there
• Notifications
• From the job
• From the monitoring system
• Middle Tier(s)
• Front End

15
Visual Diagram of Information Path
GUI
HOST
Scripts
Monitoring Database Host
RSM Server
Adaptive Server
RPC or Special Script
AdaptiveServer
Monitoring Info DB
16
Adaptability
Making a Generic Monitoring System

• General Columns
• Servers Hosts
• Activities, Results Priorities
• Granularity
• Problem Resolution Service Provision
• Dynamic Values Static Values
• Historical Tracking (and saving space to do it)
• Good updates the same row
• Bad spawns a new row
• The Uncertain state

17
Integrating with Other Systems

• Tier based architecture
• Database Remote Procedure Calls (RPCs)

18
User Interfaces

• Web Interface
• The Event Log (What Happened?)
• Baton (Support Availability)

19
12.5.0.x Features Observations
Sybase Trivia Fact2 12.5.0.3 is Super Cool!!
20
Resource Limits tempdb_space
A Solution to a DBA Nightmare

• Control resource allocation in tempdb
• Fantastic solution for ad-hoc query systems where
  people write less than optimal code.
• sp_configure allow resource limits, 1
• sp_add_resource_limit
• name, appname, rangename, tempdb_space,
  num_pages, null, 4

21
SNMP Support
Unlimited Monitoring Solutions

• Sub-agent available with 12.5.0.2 on Mac OS X
• Will be available on all platforms beginning with
  12.5.1 GA
• Java sub-agent communicates with a master snmp
  agent via the AgentX protocol supported by the
  Net-SNMP master agents
• http//www.net-snmp.org/
• Need Net-SNMP version 5 or greater
• Available now as download from Sybase Developers
  Network

22
Quality of Life Features
Those Simple Pleasures

• Elimination of vdevno from disk init syntax
• sp_dboption now checkpoints database
• checkpoint all or checkpoint dbname

23
12.5.1 Feature Observations
Sybase Trivia Fact3 12.5.1 is the super coolest
database server yet.
24
High Performance Parallel Recovery
Quick Summary of the Best Reason to Buy 12.5.1

• High Impact Easy to Use For Free Feature
• Value of number of engines on startup brought
  online once system databases recovered.
• User databases recovered in parallel
• of recovery tasks based on max concurrently
  recovered databases
• Databases recovered as specified in
  sp_dbrecovery_order
• Self-Tuned Recovery Cache

25
High Performance Parallel Recovery
Observations Works Fabulously

• 8-60 performance speedup on recovery
• 50 increase in transactions recovered per second
• Leave max concurrently recovered db alone.
• Set number of engines on startup max online
  engines
• Watch your errorlog records
• 0300000000132003/07/31 140034.27 server
  Database 'engcomdb' is now online.
• Configure a large default data cache

26
Statement Cache
Quick Summary of Feature

• Useful for systems with Third Party apps that
  have single user login to system and execute
  ad-hoc sql.
• To enable sp_configure enable statement cache,
  1
• Stores ad-hoc sql statements meeting certain
  criteria as lightweight stored procedures in
  procedure cache.
• Parses, normalizes, computes hash values,
  compares to cache
• Hashes on login, user ID, database ID, and
  session state settings.
• Examples of session state forceplan, transaction
  isolation level

27
How to Monitor

• Use new sp_sysmon info to monitor effectiveness
• With Statement cache enabled
• SQL Statement Cache
• Statements Cached 7.4
  1.4 4410 n/a
• Statements Found in Cache 3.0
  0.6 1783 n/a
• Statements Not Found 7.4
  1.4 4410 n/a
• Statements Not Cached 0.0
  0.0 0 n/a
• With Statement Cache disabled
• SQL Statement Cache
• Statements Cached 0.0
  0.0 0 n/a
• Statements Found in Cache 0.0
  0.0 0 n/a
• Statements Not Found 0.0
  0.0 0 n/a
• Statements Not Cached 15.6
  5.2 9363 n/a

28
Statement Cache Feature Observations

• Using sp_sysmon after enabling statement cache
• Procedure Cache Management per sec
  per xact count of total
• --------------------------- ------------
  ------------ ---------- ----
• Procedure Requests 13.2 2.5
  7948 n/a
• Procedure Reads from Disk 0.1 0.0
  80 1.0
• Procedure Writes to Disk 0.0 0.0
  0 0.0
• Procedure Removals 7.7 1.5
  4622 n/a
• Procedure Recompilations 0.1 0.0
  78 n/a

29
Statement Cache Feature Observations

• 1gt sp_monitorconfig "procedure cache"
• 2gt go
• Usage information at date and time Jul 31 2003
  558PM.
• Name Num_free Num_active Pct_act
  Max_Used Reused
• ------------------ --------- -----------
  ------- ----------- ------
• procedure cache size 58295 44105
  43.07 44143 No
• (return status 0)
• 1gt sp_monitorconfig "open objects"
• 2gt go
• Usage information at date and time Jul 31 2003
  557PM.
• Name Num_free Num_active
  Pct_act Max_Used Reused
• ---------------------- --------- ---------
  ------- --------- ------
• number of open objects 123 6877
  98.24 6877 Yes
• (return status 0)

30
Statement Cache Feature Observations
What Would Help Us

• Limit on the of procedure cache ad-hoc
  statements can consume.
• Examples stored sql cache size, max number of
  stored sql
• Method to determine memory consumption for the
  ad-hoc sql
• Performance numbers for cache lookup scaling

31
Statement Cache Configuration Suggestions
Until Architecture Changes in GA

• Essential to configure open objects appropriately
  to avoid procedure plan removals.
• open objects (num of proc buffers
  sp_countmetadata objects)
• Using sp_sysmon monitor procedure removals,
  procedure reads from disk, and open object usage
  closely.
• Possible strategy reduce free procedure buffers
  in procedure cache, allow procedure cache to heat
  up, enable statement cache.
• Bottom Line Feature is valuable and fulfills an
  important niche

32
New sp_sysmon Information
Replication Agent How Is It Performing?

• Replication Agent
• -----------------
• Replication Agent qts_db
• Replication Server PRPOPS1
• per sec
  per xact count of total
• ------------
  ------------ ---------- ----------
• Log Scan Summary
• Log Records Scanned n/a
  n/a 6914 n/a
• Log Records Processed n/a
  n/a 1659 n/a
• Transaction Activity
• Opened n/a
  n/a 817 n/a
• Committed n/a
  n/a 816 n/a
• Aborted n/a
  n/a 1 n/a
• Prepared n/a
  n/a 1 n/a
• Maintenance User n/a
  n/a 0 n/a
• Network Packet Information

33
Future Plans/Research

• Job Scheduler
• AutoDB Extend
• Monitor Tables (a.k.a. MDA )
• AvantGo technology
• XML Web Services
• SQL Expert

34
Thanks To The Sybase Engineers
Sybase Engineers are the Best!!

• We would like to thank the many Sybase ASE and
  related Engineers who had significant direct
  input and support to this presentation.

35
ASE 103 Managing and Monitoring Database Servers
in an Enterprise
Aimee Grimes Joe Love Global Applications and
Infrastructure Team aimee_at_sybase.com,
jlove_at_sybase.com August 6, 2003