REP704 Effective Use of Replication Server Monitors and Counters

1
REP704 Effective Use of Replication Server
Monitors and Counters
Greg CarterStaff S/W Eng II - Developmentgcarter
_at_sybase.com / (303)413-406708/05/2003
2
MC Highlights

• Introduced in release 12.1
• New counters introduced with practically every
  release and EBF since
• Statistics for single observation periods may be
  requested through the command line interface
• Statistics for multiple observation periods may
  be captured in the RSSD
• Statistics may be turned on or off by RepServer
  module
• Statistics are divided into intrusive and
  non-intrusive
• Supporting analysis tools available at
  http//repserver.codexchange.sybase.com/

3
MC Goals

• Functional Monitoring
• Internal Activity
• Duplicate commands ignored
• Total successful and failed transactions
• Transaction group management
• Transaction partitioning management
• Command batch management
• Thread yield and sleep times
• Message movement across queues
• Stable device segment management
• SQT Transaction Queue management
• SQT Cache management
• RSSD activity

4
MC Goals

• Performance Monitoring
• Bytes processed per time period
• Network packets processed per time period
• Commands processed per time period
• Transactions processed per time period
• Time required to send command batches or making
  RPCs
• Time required to process command batch results
• Time required to perform Function String mapping
• Time required to check proper transaction
  sequencing
• Stable queue write/read rates

5
MC Goals

• Configuration Profiling
• Database operations without replication
  definitions
• Database operations with no matching
  subscriptions
• Lock deficiencies in the RDB
• Transaction log space deficiencies in the RDB
• Connection deficiencies in the RDB
• Requests for rs_locater updates by the RepAgent
• RepAgent connection packet size

6
MC Goals

• Transaction Profiling
• Total begins, inserts, updates, deletes, commits,
  rollbacks
• Total ungrouped transactions
• Total applied and request commands
• Total mini-abort commands
• Average command size includes replication
  overhead
• Count and size of large commands
• Commands per transaction
• Memory consumption per transaction

7
MC Goals

• Tuning Support
• Daemon thread scheduling
• Command batch sizing
• Transaction group sizing
• Transaction partitioning
• Sizing number of threads for parallel-DSI
• Serialization method selection
• Large transaction size selection
• Thread scheduling management
• SQM outstanding write request management
• RSI queue truncation scheduling
• Stable queue sizing
• RSSD interaction control
• Latency control in low volume systems
• SQT cache sizing
• STS cache sizing

8
RepServer Modules

• Each counter is associated with a RepServer
  module
• RepServer modules may be single or
  multi-instanced
• Single-instanced modules include
• CM Connection Manager
• STS System Table Services
• SYNC Synchronization
• Multi-instanced modules include
• DIST Distributor
• DSI Data Server Interface/Scheduler
• DSIEXEC Data Server Interface/Executor
• REPAGENT RepAgent Executor
• RSI RepServer Interface
• SQM Stable Queue Manager
• SQMR SQM Reader
• SQT Stable Queue Transaction Interface
• SYNCELE Synchronization Element

9
RepServer Modules

• For multi-instanced modules, counters are grouped
  by instance ID (for example, database ID) and
  optionally instance value (for example, DSI/E
  thread number when parallel DSI is enabled)
• Notably absent are MEM (Memory Manager) and
  STSTAB (STS Table)

10
RepServer Modules
Memory Manager

• Memory Manager statistics available using admin
  statistics, mem and admin statistics,
  mem_in_use

1gt admin statistics,mem 2gt go
Segment_Size
Number_of_Segments ------------
------------------ 256
36509 512 268502
1024 161082 2048
59558 4096 3507
8192 23 16384
9658 65536 26 1gt
admin statistics, mem_in_use 2gt go
Memory_in_Use
------------- 621606401

11
RepServer Modules
System Table Services

• Some STSTAB statistics using debug commands

1gt debug stscache,help 2gt go
Please look at the log for
output.
12
RepServer Modules
System Table Services
T. 2003/07/01 095200. (33) Usage debug
stscache, option, tablename, key, links T.
2003/07/01 095200. (33) Parameter 'option'
(optional) T. 2003/07/01 095200. (33)
default to print this help text T. 2003/07/01
095200. (33) "print" to print the
cache T. 2003/07/01 095200. (33)
"invalidate" to invalidate the cache T.
2003/07/01 095200. (33) "stats" to
print cache statistics T. 2003/07/01 095200.
(33) "fillcache" to fillcache with the
specified object or all objects T. 2003/07/01
095200. (33) "hitrate" to print
cachehit-rate (STS_CACHESTATS trace flag should
be ON for this. T. 2003/07/01 095200. (33)
"check" run some consistency checks on STS
stuff. T. 2003/07/01 095201. (33) Parameter
'tablename' (optional) T. 2003/07/01 095201.
(33) default all caches T. 2003/07/01
095201. (33) "all" all caches T.
2003/07/01 095201. (33) "rs_objects"
rs_objects cache only T. 2003/07/01 095201.
(33) Parameter 'key' (optional) T. 2003/07/01
095201. (33) default all objects T.
2003/07/01 095201. (33) "" all
objects T. 2003/07/01 095201. (33)
"tablekey" objects with significant key member
tablekey T. 2003/07/01 095201. (33)
Parameter 'links' (optional)
T. 2003/07/01 095201. (33) default
link caches also T. 2003/07/01
095201. (33) "" link caches also
T. 2003/07/01 095201. (33)
"no_links" skip link caches
13
RepServer Modules
System Table Services
1gt debug stscache,stats 2gt go
T. 2003/07/01 095814. (33) Cache Statistics
T. 2003/07/01 095814. (33)
rs_columns Link Cache Obj_count
91
Objects_In_use 10
Valid 91, Invalid 0
on LRU 0
T. 2003/07/01 095814. (33)
rs_columns Primary Cache Obj_count
10
Objects_In_use 1
Valid 10, Invalid 0
on LRU 9
T. 2003/07/01 095814. (33)
rs_config Primary Cache Obj_count
100
Objects_In_use 0
Valid 100, Invalid 0
on LRU 100

14
RepServer Modules
System Table Services
I. 2003/07/01 093943. Maximum outbound
connections allowed 128. I. 2003/07/01
093943. System outbound connection fadeout
time '30'. I. 2003/07/01 093943. System
table cache size 100 rows. I.
2003/07/01 093943. Maximum number of Open
Server threads allowed 50. I. 2003/07/01
093943. Maximum number of Open Server mutexes
allowed 1024.
I. 2003/07/01 093949. A cached row for system
table 'rs_config' was swapped ou t of the cache
in order to accomodate another row. If this
message appears mult iple times it may be
advisable to increase sts_cachesize from its
present value of '100'.

15
RepServer Modules
System Table Services
1gt trace 'on', 'sts','sts_cachestats' 2gt go
1gt debug
stscache,hitrate 2gt go
Please look at the log for
output.
16
RepServer Modules
System Table Services
I. 2003/07/01 102923. Trace enabled 'sts',
'sts_cachestats'. T. 2003/07/01
102932. (30) Cache Hit Rate
T. 2003/07/01 102932. (30)
tablename hit miss miss_hit
miss_miss
T. 2003/07/01
102932. (30) --------- --- ----
-------- ---------
T.
2003/07/01 103245. (30) rs_classes 0
0 0 0 T. 2003/07/01 103245.
(30) rs_columns 122098 0 0 0
T. 2003/07/01 103245. (30) rs_config
3898 3272 0 3272 T. 2003/07/01
103245. (30) rs_datatype 0 0
0 0 T. 2003/07/01 103245. (30)
rs_databases 1343 0 0 0 T.
2003/07/01 103245. (30) rs_diskaffinity 0
31 0 31 T. 2003/07/01 103245.
(30) rs_functions 25253 0 0 0
T. 2003/07/01 103245. (30) rs_locater
0 0 0 0 T. 2003/07/01
103245. (30) rs_objects 54585 1
0 1 T. 2003/07/01 103245. (30)
rs_queues 2775 0 0 0 T.
2003/07/01 103245. (30) rs_repdbs 0
0 0 0 T. 2003/07/01 103245.
(30) rs_routes 0 0 0 0
T. 2003/07/01 103245. (30) rs_sites
0 0 0 0 T. 2003/07/01
103245. (30) rs_systext 0 0
0 0 T. 2003/07/01 103245. (30)
rs_translation 0 159 0 159 T.
2003/07/01 103245. (30) rs_users 32
0 0 0 T. 2003/07/01 103245.
(30) rs_repobjs 0 0 0
5126 T. 2003/07/01 103245. (30) rs_version
0 0 0 0 T. 2003/07/01
103245. (30) rs_publications 0 0
0 0

17
RepServer Modules
Message Delivery

• MD stats available independent of MC

1gt admin statistics,md
2gt go

Source Pending_Messages
Memory_Currently_Used
Messages_Delivered SQM_Writes Destinations_Delive
red_To Max_Memory_Hit
Is_RSI_Source?
------------------------------ ----------------
---------------------
------------------ ----------- -------------------
------ --------------
--------------
pds01 43
12716 24376
24376 0
0 0

18
RepServer Modules

• Current stats for all instances of all modules
  except STSTAB are displayed by using admin
  statistics,all_modules
• Since admin statistics,all_modules displays
  stats for all instances of SYNCELE, a lot of
  output is generated, much of which is not useful
  to customers

19
Counter Documentation

• The best source of information on specific
  counters is in the RSSD, accessed via the
  rs_helpcounter stored procedure

1gt rs_helpcounter
2gt go
ModuleName

------------------------------
CM
DIST

... SYNCELE

How to Use
rs_helpcounter
---------------------------------------------
------------------- rs_helpcounter intrusive
sysmon rate duration internal
must_sample no_reset keep_old
configure rs_helpcounter ModuleName , type
short long rs_helpcounter
keyword , type short long


20
Counter Documentation
Counter Types

• Total Accumulate a running total
• Average Calculate the average of the total over
  the number of observations sample period
• Maximum Record the maximum value observed over
  the sample period
• Last Record the last observed value for the
  sample period

1gt rs_helpcounter sqm,type
2gt go


Display Name
Module Name Counter Type Counter Status
------------------------------ ------------
------------ -------------- AffinityHintUsed
SQM Total 00000000
BPSaverage SQM
Average 00000044 BPScurrent
SQM Last 00000040
BPSmax SQM
Maximum 00000040 BlocksFullWrite
SQM Total 0000008C
BlocksRead SQM
Total 00000088 BlocksReadCached
SQM Total 00000088
...
21
Counter Documentation
Counter Statuses

• Configure Runtime configuration setting. Not
  implemented.
• Internal Internal support. Not available in the
  RSSD.
• Keep old Current a previous sample period
  values are retained
• No reset The counter is not reset when
  executing the admin statistics, reset command
• Must sample The counter must collect data even
  if sampling has been turned off
• Intrusive Because of the nature of the counter,
  setting it on for sampling may negatively impact
  performance
• Sysmon The counter is reported by the admin
  statistics, sysmon command
• Rate Counter results are reported as a rate
  usually expressed in terms of number of
  observations per second
• Duration Counter results are reported as a
  length of time usually expressed in 100ths of a
  second

22
Counter Documentation
1gt rs_helpcounter BlocksReadCached,short
2gt go


Display Name
Module Name
Counter Description





------------------------------
------------
----------------------------------------------
--------------------- ----------------------------
------------------------------------------------ -
--------------------------------------------------
------------------------- ------------------------

BlocksReadCached SQM
Obsolete.
See CNT_SQMR_BLOCKS_READ_CACHED.





BlocksReadCached SQMR
Total number
of 16K blocks from cache read by an SQM Reader
thread.
23
Counter Documentation
1gt rs_helpcounter BlocksReadCached,long
2gt go



counter_id counter_name

module_name display_name
counter_type
counter_status
description


-----------
--------------------------------------------------
---------- 6010 SQM Blocks read from
cache
SQM
BlocksReadCached
1 136
Obsolete. See
CNT_SQMR_BLOCKS_READ_CACHED.





62004 SQMR Blocks read from cache
SQMR
BlocksReadCached
1 140

Total number of 16K blocks from cache read by
an SQM Reader thread.
24
Statistics Configurations

• All statistics related configurations are
  dynamic. That is, they take effect immediately,
  not requiring a RepServer re-boot.
• The following RepServer-wide configurations set
  with configure replication server are
  persistent
• stats_reset_afterflush should counters be reset
  after flushing, default is on
• stats_daemon_sleep_time each time the
  Statistics Daemon awakens, it calculates any
  rate counters and flushes counters to the RSSD
  if RSSD flushing is configured, default is 120
  seconds
• stats_flush_rssd should counters be flushed to
  the RSSD, default is off
• stats_sampling should counters be actively
  collecting data, default is on
• The following RepServer-wide configurations are
  transient
• stats_intrusive_counter should intrusive
  counters be actively sampling data, default is
  off. Set with admin stats_intrusive_counter,
  'offon

25
Statistics Configurations
Module Flushing Statuses

• Module, connection and route flushing status
  configurations are all transient and all off by
  default
• BUG? Configuring module, connection or route
  flushing statuses requires that both
  stats_sampling and stats_flush_rssd be turned
  on
• In order to view statistics either by flushing
  them to a client connection using admin
  statistics, sysmon or by flushing them to the
  RSSD, you must turn the flushing status on for
  the modules desired
• Configuring flushing status for all modules
• admin stats_config_module, module all_modules
  , 'on' 'off '
• BUG?Valid for all modules except DSIEXEC and
  SQMR which should be set the same as DSI and SQM
  respectively
• BUG? Multi-instanced module SYNCELE is
  configured independently of single-instanced
  module SYNC using admin stats_config_connection

26
Statistics Configurations
Module Flushing Statuses

• Configuring flushing status for multi-instanced
  modules
• admin stats_config_connection, data_server,
  database all_connections , module
  all_modules , 'inbound' 'outbound' 'on'
  'off'
• Valid for modules DSI, SQM, SQT, DIST and
  REPAGENT
• inbound/outbound option only valid for
  modules SQM and SQT
• BUG?Valid for all modules except DSIEXEC and
  SQMR which should be set the same as DSI and SQM
  respectively
• BUG? When all_modules is specified, SYNCELE
  flushing status is configured

27
Statistics Configurations
Module Flushing Statuses

• admin stats_config_route, rep_server
  'all_routes' , module all_modules , 'on'
  'off '
• rep_server is a RepServer to which this one has
  a route
• Valid for modules RSI and SQM

28
Statistics Configurations
Module Flushing Statuses
1gt admin statistics, flush_status
2gt go


RepServer RSSD
flushing status for prs01 ran on 07/10/03
111038 AM


Rep Server Counters Flush
Configuration.
----------------------------------------


Configuration_Name Run_Value
--------------------
---------- ----------
stats_daemon_sleep_time 600
stats_sampling
on
stats_flush_rssd off

stats_reset_afterflush on
stats_intrusive_counter
off

Rep Server module
flushing status.
----------------------------------


Module_Name Info
Flush_Status -----------
----------------------------------------
------------ CM
off
DIST
off DSI
off
29
Statistics Configurations

• admin statistics, reset
• Resets all counters that are allowed to be reset
• Enables a clean start at the beginning of a
  sample period
• Counter resets are automatically done at the
  start of each admin statistics, sysmon
  execution
• Counter resets are automatically done at the end
  of each RSSD flush if stats_reset_afterflush
  has been set on (default)

30
Steps to Collect Statistics

• Decide whether to flush stats to the client
  connection using admin statistics, sysmon or to
  the RSSD
• Set flushing status for modules appropriately
• Decide whether to include intrusive counters
• Began sampling and collecting results

31
Collecting Stats with MC
Using a Client Connection

• Using a client connection such as with isql, you
  can configure for and collect statistics using
  the admin statistics, sysmon command
• With admin statistics, sysmon only those
  counters with sysmon status will be flushed
  see RSSD stored procedure rs_helpcounter sysmon
• admin statistics, sysmon takes a number of
  seconds for the sample period, default is 1 Ex.
  admin statistics, sysmon, 300 for a 300 second
  or 5 minute sample period
• admin statistics, sysmon blocks the client
  session until the sample period has expired at
  which point all sysmon counters for flush
  enabled modules will be written to the client
• admin statistics, sysmon forces a counter reset
  prior to the start of the sample period

32
Collecting Stats with MC
Using a Client Connection
1gt admin statistics,sysmon
2gt go

Sybase Replication Server Statistics
Report








RepServer Version Replication
Server/12.5/SMP EBF 10999/Sun_svr4/OS
5.8/1/DEBUG/Fri Apr 11 011959 PDT 2003


Open Server Version Sybase
Server-Library/12.5/P-EBF10880-10879
ESD9/DRV.12.5.0.6/SPARC/Solaris 2.8 Native
Threads/BUILD125-026/OPT/Sun Jan 12 103048
2003


Open CLient Version Sybase
Client-Library/12.5/P-EBF10879
ESD9/DRV.12.5.0.6/SPARC/Solaris 2.8 Native
Threads/BUILD125-026/OPT/Sun Jan 12 102805
2003


RepServer Name prs01


Start Date 07/10/03
121759 PM

End Date
07/10/03 121801 PM

Sample
Interval (secs) 2



33
Collecting Stats with MC
Using a Client Connection
RepServer Runtime Configurations
-------------------
-------------



memory_limit
900


init_sqm_write_delay 1000


init_sqm_write_max_delay
10000

sqm_write_flush
off


sqt_max_cache_size 209715200


sqt_init_read_delay 2000


sqt_max_read_delay
10000 ...

34
Collecting Stats with MC
Using a Client Connection
1gt configure replication server
2gt set stats_flush_rssd to 'on'
3gt go
Config parameter 'stats_flush_rssd' is modified.

• Must do this to configure module flushing status
  even though we are not wanting to flush to the
  RSSD

1gt admin stats_config_module,sqt,'on' 2gt go
1gt configure
replication server 2gt set stats_flush_rssd
to 'off' 3gt go Config parameter
'stats_flush_rssd' is modified.
35
Collecting Stats with MC
Using a Client Connection
1gt admin statistics, flush_status
2gt go

... Rep Server module flushing status.
---------------------------
-------

Module_Name Info
Flush_Status -----------
----------------------------------------
------------ CM
off DIST
off
DSI
off REPAGENT
off RSI
off
SQM
off SQT 1010 DSI 101
edsprs01.edbprs01 off BUG? SQT
1020 DSI 102 pds01.tpcc off
SQT 1040 DSI 104 rds01.tpcc
off SQT 1030 DSI
103 wsb01.tpcc off SQT
1021 DIST pds01.tpcc
off STS
off SYNC
off
SYNCELE
off
36
Collecting Stats with MC
Using a Client Connection
1gt admin statistics,sysmon,60
... SQT Statistics



-----------------------------


Info
Instance_ID Instance_Val CmdsTotal
OpenTransTotal CmdsAveTran CacheMemUsed
MemUsedMaxTran MemUsedAveTran
TransRemoved TruncTransTotal ClosedTransTotal
ReadTransTotal
OpenTransRmTotal TruncTransRmTotal
ClosedTransRmTotal ReadTransRmTotal
----------------------------------------
----------- ------------ -----------
-------------- ----------- ------------
-------------- --------------
------------ --------------- ----------------
--------------
---------------- -----------------
------------------ ---------------- ... 1040
DSI 104 rds01.tpcc 104
0 1985 131
15 104859136 18204
7563 0 131
153 148
131 127
148 149 1030 DSI 103 wsb01.tpcc
103 0
2240 141 15
104858368 46592 19234
0 141 170
181 141
152 181
181 1021 DIST pds01.tpcc
102 1 12302
815 15 104858368 46620
18137 0
815 815 804
815 804
804 804
37
Collecting Stats with MC
Using a Client Connection
1gt configure replication server
2gt set stats_flush_rssd to 'on'
3gt go
Config parameter 'stats_flush_rssd' is modified.

• Again, we must do this to configure module
  flushing status even though we are not wanting to
  flush to the RSSD

1gt admin stats_config_connection,all_connections,s
qm,'on' 2gt go 1gt
configure replication server 2gt set
stats_flush_rssd to 'off' 3gt go Config
parameter 'stats_flush_rssd' is modified.
38
Collecting Stats with MC
Using a Client Connection
1gt admin statistics,flush_status
...

Module_Name Info
Flush_Status -----------
----------------------------------------
------------ CM
off
DIST
off DSI
off
REPAGENT
off RSI
off
SQM 1010 edsprs01.edbprs01
on SQM 1020
pds01.tpcc on
SQM 1021 pds01.tpcc
on SQM 1040
rds01.tpcc on
SQT
off STS
off
SYNC
off SYNCELE
off

39
Collecting Stats with MC
Using a Client Connection
1gt admin statistics,sysmon,120
...

SQM Statistics


----------------------
-------

Info
Instance_ID
Instance_Val CmdsWritten BlocksWritten
BytesWritten SegsActive SegsAllocated
SegsDeallocated UpdsRsoqid
WritesTimerPop WriteRequests BlocksFullWrite
BPSaverage CmdSizeAverage

----------------------------------------
----------- ------------ -----------
------------- ------------ -----------
------------- ---------------
----------- -------------- -------------
--------------- -----------
--------------
... 1021 pds01.tpcc
102 1
21582 1486 23187074
126 24 5
24 10 21582
1476 0 957

1040 rds01.tpcc
104 0 20469
466 7448675 45 7
1 7 8
20469 458 0
246

40
Collecting Stats with MC
Capturing Stats in the RSSD

• RSSD Tables Involved
• rs_statcounters Descriptive information about
  each counter. Contents are static but may change
  with each release or EBF
• rs_statrun One row per sampling period. Ties
  together all counter values for a given sampling
  period.
• rs_statdetail Counter values
• By default counters are reset after being flushed
  to the RSSD in anticipation of immediately
  collecting data for the next sampling period
• You can alter that behavior by setting
  stats_reset_afterflush to off. This RepServer
  configuration is persistent.

41
Collecting Stats with MC
Capturing Stats in the RSSD

• Make sure
• 'stats_sampling' set to 'on (default)
• 'stats_flush_rssd' set to 'on (not the default)
• 'stats_reset_afterflush' set to 'on (default)
• Choose setting for
• 'stats_daemon_sleep_time' - default is '120 or
  2 minutes
• stats_intrusive_counter - default is off
• stats_config_module recommend all_modules,
  'on
• stats_config_connection recommend
  all_connections, all_modules, 'on
• stats_config_route recommend all_routes,
  all_modules, on
• Clean out any statistics from earlier RSSD
  statistics flushes
• Set 'stats_flush_rssd' set to 'off
• Truncate RSSD tables rs_statdetail and rs_statrun
• Set 'stats_flush_rssd' set to 'on
• You are now collecting statistics in the RSSD

42
Collecting Stats with MC
Capturing Stats in the RSSD

• When youve got what you want, make sure
• 'stats_flush_rssd' set to 'off (default)
• stats_intrusive_counter set to off
• stats_config_module set to all_modules, 'off
• stats_config_connection set to
  all_connections, all_modules, 'off
• stats_config_route set to all_routes,
  all_modules, off
• To dump statistics out of the RSSD into a CSV
  file
• Used RSSD stored procedure rs_dump_stats
• rs_dump_stats is currently only available from
  script stats_crt_dump_stats located at the SDN
  CodeXchange web site at http//www.codexchange.sy
  base.com/servlets/DomainHome. Look under
  RepServer, Performance Management, Reference
  Implementation, RepPerfRefImpl.zip.

43
Collecting Stats with MC
Capturing Stats in the RSSD

• Sample of rs_dump_stats output

Comment Sample Run May 6 2003
25405653AM May 6 2003 43447920AM 51 2 1638
4 64 SQM 102 1 1021 pds01.tpcc SQM Commands
written to queue CmdsWritten 6000 , ,
102, 1 May 6 2003 25405653AM, 49926
May 6 2003 25606990AM, 17088
May 6 2003 25808256AM, 10888
44
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets

• MC Analysis spread sheets are currently only
  available from the SDN CodeXchange web site at
  http//www.codexchange.sybase.com/servlets/DomainH
  ome. Look under RepServer, Performance
  Management, Monitors Counters Analysis ,
  MonCounters-Analysis.zip.
• The spread sheets present summaries and charts
  based on the CSV file created by rs_dump_stats
• If a chart is not drawn, there is no data for the
  associated counter

45
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
1
2
3
Locate the data file generated by rs_dump_stats.
46
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
Using the Text Import Wizard, select
Delimited and ltNextgt
47
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
For Delimiters, check Comma and choose
ltFinishgt
48
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
The data will load into a new worksheet looking
something like this. Move it into the workbook
containing the analysis spreadsheets.
49
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets

• If the rows in the data file exceed Excels limit
  (65k)
• You can chop the one data file into multiple data
  files
• Usually best to break on module boundaries
• Each data file must have the following rows at
  topand this one at the bottom

50
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
REPAGENT Summary
51
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
REPAGENT 58000 CmdsTotal
6 minute intervals Determined by setting
of stats_daemon_sleep_time
52
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
REPAGENT 58001-58009 CmdsApplied, CmdsRequest,
CmdsSystem, CmdsMiniAbort, CmdsDumpLoadDB,
CmdsPurgeOpen, CmdsRouteRCL, CmdsEnRepMarker,
UpdsRslocater
53
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
SQM Writer Summary
54
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
SQM 6002 BlocksWritten, 6041 BlocksFullWrite
55
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
SQM 6020 SegsActive, 6021 SegsAllocated,
6022 SegsDeallocated
56
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
SQM 6023 TimeLastNewSeg, 6025 TimeMaxNewSeg,
6027 TimeAveNewSeg
57
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
SQM 6029 TimeLastSeg, 6031 TimeMaxSeg, 6033
TimeAveSeg
58
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
SQMR Summary
59
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
SQM 6002 BlocksWritten, SQMR 62002
BlocksRead, 62004 BlocksReadCached
60
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
DIST Summary
61
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
DIST 30024 TDbegin, 30025 TDclose, 30020
SREstmtsInsert, 30021 SREstmtsUpdate, 30022
SREstmtsDelete, 30010 CmdsDump, 30011 CmdsMarker
62
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
DSI/S Summary
63
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
DSI/S 5013 TranTimeLast, 5015 TranTimeMax,
5017 TranTimeAve
64
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
DSI/E Summary
65
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
DSI/E 57074 DSIEBatchTimeAve, 57112
DSIESCBTimeAve, 57041 SendTimeAvg, 57067
DSIEResultRimeAve, 57106 DSIESCCTimeAve
66
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
DSI/E 57000 TransApplied for all DSI/E threads
67
Interpreting and Using MC Stats
Using MC Analysis Spread Sheets
DSI/E 57000 TransApplied for all DSI/E threads
68
Interpreting and Using MC Stats
For Functional Monitoring - REPAGENT
58016 RAYieldTimeAve The average amount of time the RepAgent spent yielding the processor while handling LTL commands each time the processor was yielded. Yield points occur in the RepAgent according to the setting of exec_cmds_per_timeslice. Also see counter 58015 RAYields.
58019 RAWriteWaitsTimeAve The average amount of time the RepAgent spent waiting for the SQM Writer thread to drain the number of outstanding write requests to get the number of outstanding bytes to be written under the threshold. The threshold of outstanding bytes to be written before the RepAgent is allowed to continue is set by configuration exec_sqm_write_request_limit. Also see counter 58018 RAWriteWaits.
69
Interpreting and Using MC Stats
For Functional Monitoring - SQM
6020 SegsActive Total active segments of an SQM queue the number of rows in rs_segments for the given queue where used_flag 1.
6022 SegsDeallocated Total segments deallocated from a queue during the current statistical period.
6027 TimeAveNewSeg Average elapsed time, in 100ths of a second, to allocate a new segment. Timer starts when a segment is allocated. Timer stops when the next segment is allocated.
6033 TimeAveSeg Average elapsed time, in 100ths of a second, to process a segment. Timer starts when a segment is allocated or Repserver starts. Timer stops when the segment is deleted.
70
Interpreting and Using MC Stats
For Functional Monitoring - SQM
6040 WriteRequests Total message writes requested by an SQM client.
6041 BlocksFullWrite Total number of full blocks written by an SQM thread. Individual blocks can be written due either to block full state or to sysadmin command 'slow_queue' (only one message per block). Use (BlocksFullWrite/WriteRequests) to give an average block density value. This may be used in conjunction with counter 62004 BlocksReadCached to judge whether adjustments may be needed to configurations init_sqm_write_delay and init_sqm_write_max_delay.
71
Interpreting and Using MC Stats
For Functional Monitoring - SQMR
62004 BlocksReadCached Total number of 16K blocks from cache read by an SQM Reader thread. While there may appear to be a direct correlation between cached reads and throughput, it is not always clear which one drives the other.
72
Interpreting and Using MC Stats
For Functional Monitoring - SQT
24001 OpenTransTotal Total transactions added to the Open queue.
24005 CacheMemUsed SQT thread memory use. Each command structure allocated by an SQT thread is freed when its transaction context is removed. For this reason, if no transactions are active in SQT, SQT cache usage is zero. Just because this counter may indicate that SQT cache is full all the time, this is not an indication that either dsi_sqt_max_cache_size or sqt_max_cache_size need to be increased. Instead, see counter 24009 TransRemoved.
24011 TruncTransTotal Total transactions added to the Truncation queue.
24012 ClosedTransTotal Total transactions added to the Closed queue.
24013 ReadTransTotal Total transactions added to the Read queue.
73
Interpreting and Using MC Stats
For Functional Monitoring - SQT
24014 OpenTransRmTotal Total transactions removed from the Open queue.
24015 TruncTransRmTotal Total transactions removed from the Truncation queue.
24016 ClosedTransRmTotal Total transactions removed from the Closed queue.
24017 ReadTransRmTotal Total transactions removed from the Read queue.
74
Interpreting and Using MC Stats
For Functional Monitoring - DIST
30018 SREget Total SRE requests performed by a DIST thread to fetch an SRE row. This counter is incremented each time a DIST thread fetches an rs_subscriptions row from RSSD. BUG? An incorrect description. Fetches from rs_subscriptions are not indicated.
75
Interpreting and Using MC Stats
For Functional Monitoring DSI
5007 TransSucceeded Total transactions applied successfully to a target database by a DSI thread. This includes transactions that were committed or rolled back successfully.
5011 TransRetried Total transactions retried to a target server by a DSI thread. Typically most often seen when parallel-DSI is turned on and there is contention among parallel transactions. May indicate a need to adjust configurations dsi_num_threads, dsi_max_xacts_in_group, dsi_serialization_method or dsi_partitioning_rule.
76
Interpreting and Using MC Stats
For Functional Monitoring DSI
5043 GroupsClosedNoneOrig Total trxn groups closed by a DSI due to no open group from the origin of the next trxn. I.e. We have a new origin in the next trxn, or the Sched forced a flush of the current group from the origin leaving no open group from that origin.
5069 GroupsClosedWSBSpec Total transaction groups closed by a DSI thread for a Warm Standby due to the next transaction being special - empty, or a enable replication marker or subscription materialization marker or ignored due to duplication detection, etc. May be an indication that empty transactions are slowing down a WarmStandby replication flow.
77
Interpreting and Using MC Stats
For Functional Monitoring DSI
5051, 5053, 5055 UserRuleMatchGroup, TimeRuleMatchGroup, NameRuleMatchGroup Total times Transaction Partitioning rule USER/TIME/NAME was checked and found to be true for GROUPING decision.
5052, 5054, 5056 UserRuleMatchDist, TimeRuleMatchDist, NameRuleMatchDist Total times Transaction Partitioning rule USER/TIME/NAME was checked and found to be true for DISTRIBUTION decision.
78
Interpreting and Using MC Stats
For Functional Monitoring DSIEXEC
57050 MemUsedAvgGroup Average memory consumed by a DSI/S thread for a single transaction group.
57081 DSIEOCmdCountAve Average number of output commands in a batch submitted by a DSI. The "batch" configuration, set either "on" or "off, indicates whether RepServer is to send multiple commands per batch. When set "off" each batch will contain one command.
57084 DSIEICmdCountAve Average number of input commands in a batch submitted by a DSI. Also see counter 57081 DSIEOCmdCountAve
57086 DSIEBFCommitNext Number of batch flushes executed because the next command in the transaction will be a commit.
57087 DSIEBFMaxCmds Number of batch flushes executed because we have a new command and the maximum number of commands per batch has been reached.
79
Interpreting and Using MC Stats
For Functional Monitoring RSI
4000 BytesSent Total bytes delivered by an RSI sender thread.
4002 PacketsRead Total packets sent by an RSI sender thread. BUG? The name is misleading. This is the number of packets sent, not read.
4004 MsgsSent Total RSI messages sent by an RSI thread. These messages contain the distribute command.
4006 FadeOuts Number of times that a RSI thread has been faded out due to inactivity. This count is influenced by the configuration parameter rsi_fadeout_time. Controlled by configuration rsi_fadeout_time.
4007 BlockReads Total number of blocking (SQM_WAIT_C) reads performed by a RSI thread against SQM thread that manages a RSI queue. May be used to help adjust the settings of init_sqm_write_delay and init_sqm_write_max_delay. Blocking reads may be reduced (and latency reduced as well) by setting these down.
80
Interpreting and Using MC Stats
For Functional Monitoring STS
1101 SelectsTotal Total Select statements sent to RSSD.
11003 InsertsTotal Total Insert statements sent to RSSD.
11004 UpdatesTotal Total Update statements sent to RSSD.
11005 DeletesTotal Total Delete statements sent to RSSD.
11007 CommitsTotal Total Commit Tran statements sent to RSSD.
81
Interpreting and Using MC Stats
For Configuration Profiling
DIST 30023 SREstmtsDiscard Total DIST commands with no subscription resolution that are discarded by a DIST thread. This implies either there is no subscription or the 'where' clause associated with the subscription does not result in row qualification.
REPAGENT 58009 UpdsRslocater Total updates to RSSD..rs_locater where type 'e' executed by a Rep Agent thread.
58012 PacketSize In-coming connection packet size. RepAgent/ASE 12.0 or earlier versions used a hard coded 2K packet size. Later releases will allow you to change the packet size.
82
Interpreting and Using MC Stats
For Transaction Profiling - REPAGENT
58001 CmdsApplied Total applied commands written into an inbound queue by a Rep Agent thread. Applied Commands are applied as the maintenance user.
58002 CmdsRequest Total request commands written into an inbound queue by a Rep Agent thread. Request Commands are applied as the executing request user.
83
Interpreting and Using MC Stats
For Transaction Profiling - SQM
6049 CmdSizeAverage Average command size written to a stable queue.
6050 XNLWrites Total large messages written successfully so far. This does not count skipped large message in mixed version situation.
6054 XNLAverage Average size of large messages written to a stable queue.
84
Interpreting and Using MC Stats
For Transaction Profiling - SQT
24004 CmdsAveTran Average number of commands in a transaction scanned by an SQT thread.
24008 MemUsedAveTran Average memory consumed by one transaction.
85
Interpreting and Using MC Stats
For Transaction Profiling - DIST
30020 SREstmtsInsert Total insert commands encountered by a DIST thread and resolved by SRE.
30021 SREstmtsUpdate Total update commands encountered by a DIST thread and resolved by SRE.
30022 SREstmtsDelete Total deletes commands encountered by a DIST thread and resolved by SRE.
30024 TDbegin Total Begin transaction commands propagated by a DIST thread.
30025 TDclose Total Commit or Rollback commands processed by a DIST thread.
86
Interpreting and Using MC Stats
For Transaction Profiling - DSI
5000 TransTotal Total transaction groups generated by a DSI Scheduler while reading the outbound queue. This counter is incremented each time a new transaction group is started. If grouping is disabled, this is total transactions in queue.
5002 NgTransTotal Total non-grouped transactions read by a DSI Scheduler thread from an outbound queue.
87
Interpreting and Using MC Stats
For Transaction Profiling - DSIEXEC
57007 BeginsRead Total begin transaction records processed by a DSIEXEC thread.
57008 CommitsRead Total commit transaction records processed by a DSIEXEC thread.
57009 SysTransRead Total internal system transactions processed by a DSI DSIEXEC thread.
57010 InsertsRead Total rs_insert commands processed by a DSIEXEC thread.
57011 UpdatesRead Total rs_update commands processed by a DSIEXEC thread.
57012 DeletesRead Total rs_delete commands processed by a DSIEXEC thread.
57013 ExecsWritetext Total rs_writetext commands processed by a DSIEXEC thread.
88
Interpreting and Using MC Stats
For Performance Monitoring - REPAGENT
58000 CmdsTotal Total commands received by a Rep Agent thread. Used to calculate commands per time unit received from a RepAgent.
58010 PacketsReceived Total number of protocol packets rcvd by a Rep Agent thread when in passthru mode. When not in passthru mode, RepServer receives chunks of lang data at a time. For packet size, see counter 'PacketSize'. Lang 'chunk' size is fixed at 255 bytes.
58011 BytesReceived Total bytes received by a Rep Agent thread. This size includes the TDS header size when in 'passthru' mode.
58013 BuffersReceived Total number of command buffers received by a RepAgent thread. Buffers are broken into packets when in 'passthru' mode, or language 'chunks' when not in 'passthru' mode. See counter 'PacketsReceived' for these numbers.
89
Interpreting and Using MC Stats
For Performance Monitoring - SQM
6000 CmdsWritten Total commands written into a stable queue by an SQM thread. Used to calculate commands per time unit written by an SQM.
6002 BlocksWritten Total number of 16K blocks written to a stable queue by an SQM thread
6004 BytesWritten Total bytes written to a stable queue by an SQM thread.
6047 BPSaverage Average byte deliver rate to a stable queue.
90
Interpreting and Using MC Stats
For Performance Monitoring - SQMR
62000 CmdsRead Total commands read from a stable queue by an SQM Reader thread. Used to calculate commands per time unit read by an SQM Reader.
62002 BlocksRead Total number of 16K blocks read from a stable an SQM Reader thread.
91
Interpreting and Using MC Stats
For Performance Monitoring - SQT
24000 CmdsTotal Total commands read from SQM. Commands include XREC_BEGIN, XREC_COMMIT, XREC_CHECKPT. Used to calculate commands per time unit read by an SQT.
92
Interpreting and Using MC Stats
For Performance Monitoring - DIST
30000 CmdsTotal Total commands read from an inbound queue by a DIST thread. Used to calculate commands per time unit read by a Distributor.
30002 TransProcessed Total transactions read from an inbound queue by a DIST thread.
93
Interpreting and Using MC Stats
For Performance Monitoring - DSI
5020 CmdGroups Total transaction groups sent to the target by a DSI thread. A transaction group can contain at most dsi_max_xacts_in_group transactions. This counter is incremented each time a 'begin' for a grouped transaction is executed.
5024 CmdGroupsCommit Total command groups committed successfully by a DSI thread. Used to calculate transactions per time unit committed by a DSI. These are physical transactions.
5026 CommitsInCmdGroup Total transactions in groups sent by a DSI thread that committed successfully. These are logical transactions.
5030 CmdsRead Total commands read from an outbound queue by a DSI thread. Used to calculate commands per time unit read by a DSI.
94
Interpreting and Using MC Stats
For Performance Monitoring - DSIEXEC
57000 TransApplied Total transactions sent by a DSIEXEC thread. You must add all of these for a DSI to get the total for the DSI. Also see counter 5024 CmdGroupsCommit
57001 NgTrans Total transactions in transaction groups sent by a DSIEXEC thread. Also see counter 5026 CommitsInCmdGroup
57002 CmdsApplied Total commands applied by a DSIEXEC thread.
57020 TPS_Average Average transactions per second. TPS rate is calculated each time dSTATS is scheduled.
57026 NgTPSaverage Average nongrouped transactions per second. TPS is calculated when dSTATS is scheduled.
95
Interpreting and Using MC Stats
For Performance Monitoring - DSIEXEC
57041 SendTimeAvg Average time, in 100ths of a second, spent in sending command buffers to the RDS.
57043 DSIEBPSaverage Average bytes per second.
57055 SendRPCTimeAvg Average time, in 100ths of a second, spent in sending RPCs to the RDS.
57061 SendDTTimeAvg Average time, in 100ths of a second, spent in sending chunks of text or image data to the RDS.
57067 DSIEResultTimeAve Average time taken, i