Title: Arial 28pt by T5vwZJ

VIEWS: 0 PAGES: 57

									REP704: RepServer Performance
                   And Tuning




                            Greg Carter
                       Staff Engineer II
              greg.carter@sybase.com
                    August 15-19, 2004
The Enterprise. Unwired.




2
The Enterprise. Unwired.

                              Industry and Cross Platform Solutions

               Manage                         Unwire                        Unwire
             Information                    Information                     People
    • Adaptive Server Enterprise      • Unwired Accelerator     • SQL Anywhere Studio
    • Adaptive Server Anywhere        • Unwired Orchestrator    • M-Business Anywhere
    • Sybase IQ                       • Unwired Toolkit         • Pylon Family (Mobile Email)
    • Dynamic Archive                 • Enterprise Portal       • Mobile Sales
    • Dynamic ODS                     • Real Time Data          • XcelleNet Frontline
    • Replication Server                Services                  Solutions
    • OpenSwitch                                                • PocketBuilder
    • Mirror Activator                                          • PowerBuilder Family
    • PowerDesigner                                             • AvantGo
    • Connectivity Options
    • EAServer
    • Industry Warehouse Studio


                                       Sybase Workspace



3
Complexities Of Replication Performance

   What’s the topology?

   What machines are involved?

   What security services are required?

   What networking mechanisms are used?

   What is the profile of replicated transactions?

   How is the replicate data used?



4
Case Study

   In a simple environment, observe performance with no tuning

   Tune RepServer as intuition, experience or observations
    indicate

   Observe any changes to performance and repeat

   Use RepServer’s Monitor & Counter to make observations

   Use TPC-C Transaction Clients at the Primary Database




5
Hardware Specifics

   Manufacturer:           Sun (Sun Microsystems)
   System Model:           Ultra 4
   Main Memory:            3.0 GB
   Number of Processors:   4
   Processor Type:         UltraSPARC-II
   Processor Speed:        400MHz
   Operating System:       Solaris8 kernel version 108528-07
   Disk Devices:           Twelve 18 GB IBM Ultrastar 36LZX
    DDYS-T18350 10,000 rpm with 3 physical Heads and a 4.9ms
    seek time

   This machine was not dedicated to these tests


6
Software Specifics

   Replication Server/12.6/EBF 11961 ESD#2/Sun_svr4/OS
    5.8/1/OPT/Thu May 20 18:04:38 2004

   Adaptive Server Enterprise/12.5.2/EBF 11941
    ESD#1/P/Sun_svr4/OS 5.8/ase1252/1838/32-bit/FBO/Sat May
    29 09:42:35 2004




7
Topology




            edsprs03




    pds03   prs03      rds03




                Host




8
Topology

    1> admin who
    2> go
     Spid Name         State                  Info
     ---- ----------   --------------------   ----------------------------------------
       20 DSI EXEC     Awaiting Command       101(1) edsprs03.edbprs03
       12 DSI          Awaiting Message       101 edsprs03.edbprs03
        8 SQM          Awaiting Message       101:0 edsprs03.edbprs03
       23 DSI EXEC     Awaiting Command       102(1) pds03.tpcc
       13 DSI          Awaiting Message       102 pds03.tpcc
       16 DIST         Awaiting Wakeup        102 pds03.tpcc
       19 SQT          Awaiting Wakeup        102:1 DIST pds03.tpcc
       10 SQM          Awaiting Message       102:1 pds03.tpcc
        9 SQM          Awaiting Message       102:0 pds03.tpcc
       24 REP AGENT    Awaiting Command       pds03.tpcc
       22 DSI EXEC     Awaiting Command       103(1) rds03.tpcc
       14 DSI          Awaiting Message       103 rds03.tpcc
       11 SQM          Awaiting Message       103:0 rds03.tpcc
       15 dSUB         Sleeping
        6 dCM          Awaiting Message
        7 dAIO         Awaiting Message
       17 dREC         Sleeping               dREC
       18 dSTATS       Sleeping
       26 USER         Active                 sa
        5 dALARM       Awaiting Wakeup


9
Transaction Profile (Over 7 Minutes Period)

    Command Proportions
        Inserts: 31.6%
        Updates: 65.8%
        Deletes: 2.6%
    Average commands per Tran: 15
    Average Memory Consumption per Group: 60,497bytes
    Large Transactions: 0
    Text/image data: 0
    Replicated DDL: 0
    Tables Replicated: 9
    Average Number of Columns per Table: 9




10
Observation Setup

     1> admin statistics,flush_status
     2> go

     RepServer RSSD flushing status for prs03 ran on: 06/07/04 03:17:05 PM


     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




11
Observation Setup

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

     Module_Name   Info                                       Flush_Status
     -----------   ----------------------------------------   ------------
     CM            dCM                                        off
     DIST          102 pds03.tpcc                             off
     DSI           103 rds03.tpcc                             off
     DSI           102 pds03.tpcc                             off
     DSI           101 edsprs03.edbprs03                      off
     REPAGENT      pds03.tpcc                                 off
     SQM           101:0 edsprs03.edbprs03                    off
     SQM           102:0 pds03.tpcc                           off
     SQM           102:1 pds03.tpcc                           off
     SQM           103:0 rds03.tpcc                           off
     SQT           103:0 DSI 103 rds03.tpcc                   off
     SQT           101:0 DSI 101 edsprs03.edbprs03            off
     SQT           102:1 DIST pds03.tpcc                      off
     SQT           102:0 DSI 102 pds03.tpcc                   off
     STS           STS                                        off
     SYNC          SYNC_GLOB                                  off

12
Observation Setup


          1> configure replication server
          2> set stats_daemon_sleep_time to '120'
          3> go
          Config parameter 'stats_daemon_sleep_time' is modified.

          1> admin stats_intrusive_counter, 'on'
          2> go

          1> admin stats_config_module, all_modules, 'on'
          2> go




     These all take effect immediately. Only “stats_daemon_sleep_time” is persistent.

     Be careful that all modules for which stats are to be collected are up and running.



13
Observation Setup

1> admin statistics,flush_status
2> go

RepServer RSSD flushing status for prs03 ran on: 06/09/04 12:49:26 PM


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

 Configuration_Name               Run_Value
 ------------------------------   ----------
 stats_daemon_sleep_time          120
 stats_sampling                   on
 stats_flush_rssd                 off
 stats_reset_afterflush           on
 stats_intrusive_counter          on




14
 Observation Setup

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

 Module_Name   Info                                       Flush_Status
 -----------   ----------------------------------------   ------------
 CM            dCM                                        on
 DIST          102 pds03.tpcc                             on
 DSI           103 rds03.tpcc                             on
 DSI           102 pds03.tpcc                             on
 DSI           101 edsprs03.edbprs03                      on
 REPAGENT      pds03.tpcc                                 on
 SQM           101:0 edsprs03.edbprs03                    on
 SQM           102:0 pds03.tpcc                           on
 SQM           102:1 pds03.tpcc                           on
 SQM           103:0 rds03.tpcc                           on
 SQT           102:1 DIST pds03.tpcc                      on
 SQT           102:0 DSI 102 pds03.tpcc                   on
 SQT           101:0 DSI 101 edsprs03.edbprs03            on
 SQT           103:0 DSI 103 rds03.tpcc                   on
 STS           STS                                        on
 SYNC          SYNC_GLOB                                  on
 15
Making Observation

    Run transaction load for a period of longer than the Stats
     Daemon sleep time - 3 or 4 times longer at least

    Execute “admin statistics,sysmon” for a period of time longer
     than the Stats Daemon sleep time, but short enough so that it
     will complete before the transaction load finishes




16
Making Observation


                 1> admin statistics,sysmon,420
                 2> go




      The session issuing this command will be blocked for 7 minutes.
      The transaction load for these tests lasts at least 15 minutes.




17
 Making Observation
Sybase Replication Server Statistics Report

===============================================================================

RepServer Version:        Replication Server/12.6/EBF 11961 ESD#2/Sun_svr4/OS
5.8/1/OPT/Thu May 20 18:04:38 2004

Open Server Version:      Sybase Server-Library/12.5.1/P-EBF11759-11758 ESD
#2/DRV.12.5.1.0/SPARC/Solaris 2.8 Native Threads/BUILD1251-002/OPT/Tue Mar 9
22:02:04 2004

Open CLient Version:      Sybase Client-Library/12.5.1/P-EBF11758 ESD
#2/DRV.12.5.1.0/SPARC/Solaris 2.8 Native Threads/BUILD1251-002/OPT/Tue Mar   9
20:37:42 2004

RepServer Name:           prs03

Start Time:               06/21/04 09:04:43 PM

End Time:                 06/21/04 09:11:44 PM

Sample Interval (secs):   421

 18
   Making Observation
RepServer Runtime Configurations
--------------------------------

memory_limit:               20

init_sqm_write_delay:              1000

init_sqm_write_max_delay:          10000

sqm_write_flush:            on

sqt_max_cache_size:                1048576

sqt_init_read_delay:               2000

sqt_max_read_delay:                10000

sts_cachesize:              100

sqm_recover_segs:           1

smp_enable:                 off

   19
  Making Observation
DSI Statistics

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

 Info                                     Instance_ID Instance_Val TransTotal
          NgTransTotal TransSucceeded CmdGroups   TransInCmdGroups
          CmdGroupsCommit CommitsInCmdGroup CmdsSucceed GroupsClosedBytes
          GroupsClosedNoneOrig GroupsClosedTranPartRule PartitioningWaits
          AllThreadsInUse AllLargeThreadsInUse ExecsCheckThrdLock
          TrueCheckThrdLock CommitChecksExceeded GroupsClosedTrans
 ---------------------------------------- ----------- ------------ -----------
  103 rds03.tpcc                                   103            -1        742
                  5952            742         742             5952
                      742              5953           0               741
                             0                        0                 0
                     5242                    0                  0
                          0                    0                 0




  20
  Making Observation
  Info                                      Instance_ID Instance_Val TransApplied
         NgTrans      CmdsApplied TransMaxGroup MemUsedMaxGroup SysTransRead
         InsertsRead UpdatesRead DeletesRead ExecsWritetext TPS_Average
         NgTPSaverage ErrsDeadlock ErrsOutofLock SendTimeAvg DSIEBPSaverage
         TransAvgGroup MemUsedAvgGroup SendRPCTimeAvg DSIEResultTimeAve
         DSIEBatch    DSIEBatchTimeAve DSIEBatchSizeMax DSIEBatchSizeAve
         DSIEOCmdCountAve DSIEICmdCountAve DSIEBFCommitNext DSIEBFMaxCmds
         DSIEBFMaxBytes DSIEFSMapTimeAve DSIESCCTimeAve DSIESCBTimeAve
         DSIETranTimeMax DSIETranTimeAve
---------------------------------------- ----------- ------------ ------------
 103(1) rds03.tpcc                                 103            1          742
                5952        90716             13          65536            0
               24694        52023        2105              0           1
                   13             0              0          0         103623
                     8            60538              0                 7
                 742                11             8192             6143
                        17               18             1484             0
                   3157                 0              0              0
                     196               56




  21
  Analysis

REPAGENT Statistics

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

 Info                                     Instance_ID Instance_Val CmdsTotal
          CmdsApplied CmdsRequest CmdsMiniAbort UpdsRslocater PacketsReceived
          BytesReceived RAYields    RAYieldTimeAve RAWriteWaits
          RAWriteWaitsTimeAve
 ---------------------------------------- ----------- ------------ -----------
          ----------- ----------- ------------- ------------- ---------------
          ------------- ----------- -------------- ------------
          -------------------
   pds03.tpcc                                       102           -1      146211
               126210           0          1024           146           38294
               70727869       23167              0         9617
                            2



        No CPU issue             About 3 of every 7
        for RepAgent.           minutes the RepAgent
                                 spends waiting for
                                 writes to complete.
  22
   Analysis
SQM Statistics

-----------------------------
Info                                         Instance_ID Instance_Val CmdsWritten
              BlocksWritten BytesWritten SegsActive SegsAllocated SegsDeallocated
              TimeAveNewSeg TimeAveSeg UpdsRsoqid WritesTimerPop WriteRequests
              BlocksFullWrite BPSaverage CmdSizeAverage
 ---------------------------------------- ----------- ------------ -----------
              ------------- ------------ ----------- ------------- ---------------
  102:1 pds03.tpcc                                     102            1      146221
                      10040    157641940           1           157             157
Reads right on top      268         369         157              0        146221
  of writes. Many       10040      378204            961
    from cache.
 Info                                     Instance_ID Instance_Val CmdsRead
          BlocksRead BlocksReadCached SleepsWriteQ
 ---------------------------------------- ----------- ------------ -----------
          ----------- ---------------- ------------
  102:1 pds03.tpcc,0,SQT                            102          11      146268
                10043             6196         9526
                                                       Reader having to
                                                      wait for blocks to be
                                                             written.
   23
   Analysis

SQT Statistics

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

 Info                                     Instance_ID Instance_Val CmdsTotal
         OpenTransTotal CmdsAveTran CacheMemUsed MemUsedMaxTran MemUsedAveTran
         TransRemoved TruncTransTotal ClosedTransTotal ReadTransTotal
         OpenTransRmTotal TruncTransRmTotal ClosedTransRmTotal ReadTransRmTotal
 ---------------------------------------- ----------- ------------ -----------
         -------------- ----------- ------------ -------------- --------------
         ------------ --------------- ---------------- --------------
         ---------------- ----------------- ------------------ ----------------
    102:1 DIST pds03.tpcc                            102            1      146268
                    9493          15       964096          46751          18541
                     0            9493             9493           9489
                      9493              9488               9489             9488


No transactions flushed                              Recall
 from cache. Cache is                          sqt_max_cache_size
    sized sufficiently.                       set to 1048576. Cache
                                               is not quite full –
   24                                                 92%.
  Analysis

DIST Statistics

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

 Info                                      Instance_ID Instance_Val CmdsTotal
         TransProcessed SREstmtsInsert SREstmtsUpdate SREstmtsDelete
         SREstmtsDiscard TDbegin      TDclose
 ---------------------------------------- ----------- ------------ -----------
         -------------- -------------- -------------- --------------
         --------------- ----------- -----------
    102 pds03.tpcc                                     102          -1      144497
                    9488          40919            81519          3084
                        0        9487         9488




  25
  Analysis
SQM Statistics

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

 Info                                        Instance_ID Instance_Val CmdsWritten
           BlocksWritten BytesWritten SegsActive SegsAllocated SegsDeallocated
           TimeAveNewSeg TimeAveSeg UpdsRsoqid WritesTimerPop WriteRequests
           BlocksFullWrite BPSaverage CmdSizeAverage
 ---------------------------------------- ----------- ------------ -----------
   103:0 rds03.tpcc                                     103           0      144434
                      3276     52311934          22            51              32
  Reader is            824       11868          51             32        144434
  far behind            3244      126444            245
 Info                                        Instance_ID Instance_Val CmdsRead
             BlocksRead BlocksReadCached SleepsWriteQ
 ---------------------------------------- ----------- ------------ -----------
             ----------- ---------------- ------------
  103:0 rds03.tpcc,0,DSI                               103          10       90683
                    2050                0            0


                                 No reads from
                                cache. No waiting
  26                               for a write.
   Analysis

SQT Statistics

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

 Info                                     Instance_ID Instance_Val CmdsTotal
         OpenTransTotal CmdsAveTran CacheMemUsed MemUsedMaxTran MemUsedAveTran
         TransRemoved TruncTransTotal ClosedTransTotal ReadTransTotal
         OpenTransRmTotal TruncTransRmTotal ClosedTransRmTotal ReadTransRmTotal
 ---------------------------------------- ----------- ------------ -----------
         -------------- ----------- ------------ -------------- --------------
         ------------ --------------- ---------------- --------------
         ---------------- ----------------- ------------------ ----------------
   103:0 DSI 103 rds03.tpcc                         103            0       90683
                    5946          15      1048832          18215           7547
                     0            5946             5946           5947
                      5946              5953               5947             5953


No transactions flushed                              Recall
 from cache. Cache is                          sqt_max_cache_size
    sized sufficiently.                       set to 1048576. Cache
                                                    is full.
   27
  Analysis
DSI Statistics

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

 Info                                     Instance_ID Instance_Val TransTotal
         NgTransTotal TransSucceeded CmdGroups    TransInCmdGroups
         CmdGroupsCommit CommitsInCmdGroup CmdsSucceed GroupsClosedBytes
         GroupsClosedNoneOrig GroupsClosedTranPartRule PartitioningWaits
         AllThreadsInUse AllLargeThreadsInUse ExecsCheckThrdLock
         TrueCheckThrdLock CommitChecksExceeded GroupsClosedTrans
 ---------------------------------------- ----------- ------------ -----------
   103 rds03.tpcc                                    103            -1       742
                  5952            742         742              5952
                      742              5953            0               741
                             0                         0                 0
                     5242                    0                   0
                          0                    0                  0


                           Very often, DSI/S is ready to
                           schedule a transaction but all
                              DSI/Es are occupied.
  28
    Analysis
  Info                                           Instance_ID Instance_Val TransApplied
            NgTrans       CmdsApplied TransMaxGroup MemUsedMaxGroup SysTransRead
            InsertsRead UpdatesRead DeletesRead ExecsWritetext TPS_Average
            NgTPSaverage ErrsDeadlock ErrsOutofLock SendTimeAvg DSIEBPSaverage
            TransAvgGroup MemUsedAvgGroup SendRPCTimeAvg DSIEResultTimeAve
            DSIEBatch     DSIEBatchTimeAve DSIEBatchSizeMax DSIEBatchSizeAve
            DSIEOCmdCountAve DSIEICmdCountAve DSIEBFCommitNext DSIEBFMaxCmds
            DSIEBFMaxBytes DSIEFSMapTimeAve DSIESCCTimeAve DSIESCBTimeAve
            DSIETranTimeMax DSIETranTimeAve
---------------------------------------- ----------- ------------ ------------
   103(1) rds03.tpcc                                      103            1          742
                     5952        90716             13           65536            0
                    24694        52023        2105               0           1
  On average, 11        13             0              0           0         103623
1/100ths of a second       8           60538               0                 7
  spent per batch.    742                11              8192             6143
                             17               18              1484             0
                        3157                 0               0              0
                           196              56

On average, less than   On average, less than    On average, 7 1/100ths of a
 1/100th of a second     1/100th of a second      second spent processing
  spent in FS map.      spent sending a batch.     the results of a batch.
    29
   Analysis


STS Statistics

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

 Info                                     Instance_ID Instance_Val SelectsTotal
         InsertsTotal UpdatesTotal DeletesTotal BeginsTotal STSCacheExceed
 ---------------------------------------- ----------- ------------ ------------
         ------------ ------------ ------------ ----------- --------------
    STS                                               11           -1          444
                  1337          208         1318         944              0



                                 sts_cachesize sufficiently
                                          sized.




   30
Analysis

    The RepAgent is obviously capable of shoving commands in
     faster than RepServer is writing them to the IBQ

    Downstream stats (particularly in the DSI) indicate that
     allowing it to do so would offer no benefit other than to drain
     the PDB transaction log faster




31
Analysis

    From OBQ SQM stats the reader (DSI/S) is lagging
     considerably behind the writer (DIST).

    However, OBQ SQT cache is full (and sufficiently sized). So
     DSI/S is not struggling to keep SQT cache full

    Also consider that IBQ SQM stats indicate that about three
     times the number of blocks are being written/read than on the
     OBQ ( 10,040/10,043 vs. 3,276/2,050). (Note that 10,040 blocks
     on IBQ  3,276 blocks on OBQ due to message format
     differences.) So the Stable Device has no trouble in keeping
     up with OBQ I/O requests.



32
Analysis & Adjustment

    Over 5,000 times in the 7 minute observation period, DSI/S
     wanted to schedule a transaction but could not find an
     available DSI/E

    Observation: This implies that Parallel-DSI may help

    Intuition: Before doing that, however, let’s see if we can take
     advantage of the multiple processors. Maybe the DSI/E just
     needs more processor time.
1> configure replication server
2> set smp_enable to 'on'
3> go
Config parameter 'smp_enable' is modified. This change will not take effect
until the Replication Server is restarted.
1> shutdown
2> go

33
Adjustment

    After reboot, we must turn on intrusive counters and module
     flushing (Make sure the RepAgent reconnected)

             1>   admin stats_intrusive_counter, 'on'
             2>   go
             1>   admin stats_config_module, all_modules, 'on'
             2>   go




    Make another observation
                      1> admin statistics,sysmon,420
                      2> go




34
Effects Of SMP

     DSI Stats      SMP Off        SMP On        Change

DSIEBPSaverage        103,623       131,352        26.8% +

TPS_Average                   1             2     100.0% +

NgTPSaverage                  13            16     23.1% +

DSIEResultTimeAve             7             5      28.6% -

DSIEBatchTimeAve              11            9      18.2% -

DSIETranTimeAve               56            46     17.9% -

AllThreadsInUse         5,242         6,342        21.0% +



35
Effects Of SMP

     RepAgent Stats   SMP Off       SMP On       Change

CmdsTotal              146,211       185,800       27.1% +

UpdsRslocater              146           186       27.4% +

RAYields                23,167        29,442       27.1% +

RAYieldTimeAve                  0            0      0.0% +

RAWriteWaits             9,617        12,687       32.0% +

RAWriteWaitsTimeAve             2            1     50.0% -




36
Effects Of SMP

     IBQ SQM Stats     SMP Off    SMP On     Change

BlocksWritten            10,040     12,811     27.6% +

BlocksRead               10,043     12,811     28.3% +
(% of BlocksWritten)   (100.0%)   (100.0%)
(BlocksReadCached)      (6,196)   (10,418)     31.1% +
% of BlocksRead           61.7%      80.9%
SleepsWriteQ              9,526    119,705   1156.6% +

UpdsRsoqid                  157        200     27.4% +

(WritesTimerPop)            (0)        (0)      0.0% +
% of BlocksWritten         0.0%       0.0%



37
Effects Of SMP

     OBQ SQM Stats     SMP Off       SMP On        Change

BlocksWritten             3,276         4,177        27.5% +

BlocksRead                2,050         2,496        21.8% +
(% of BlocksWritten)    (62.6%)       (59.8%)
(BlocksReadCached)          (0)           (0)         0.0% +
% of BlocksRead            0.0%          0.0%
SleepsWriteQ                     0            0       0.0% +

UpdsRsoqid                   51               66     29.4% +

(WritesTimerPop)           (32)          (18)        40.0% -
% of BlocksWritten         1.0%          0.6%



38
Effects Of SMP

     IBQ SQT Stats   SMP Off       SMP On       Change

CacheMemUsed          964,096       614,400       36.3% +

TransRemoved                   0            0      0.0% +



     OBQ SQT Stats   SMP Off       SMP On       Change

CacheMemUsed         1,048,832     1,049,856       0.0% +

TransRemoved                   0            0      0.0% +




39
Analysis

    Observation: While DSI throughput has increased some 26%,
     we also see an increase of some 21% in the number of times
     DSI/S wanted to schedule transactions but the DSI/E was
     already busy with one. Parallel-DSI continues to be implicated
     as an area to pursue.
    SMP enables the RepAgent to push through 27% more
     commands. However the RepAgent continues to wait on
     RepServer to write the commands as fast as they are being
     pushed. Still, the DSI is the limiting factor.
    Observation: For both IBQ and OBQ, updates to rs_oqid
     increased about 28%. We can reduce this RSSD I/O by
     increasing sqm_recover_segs.



40
Analysis

    Observation: For both IBQ and OBQ, writes due to the SQM’s
     alarm clock going off are nearly 0. Since in the case of the
     OBQ the SQT cache is full, there is no indication that
     adjustments to init_sqm_write_delay or
     init_sqm_max_write_delay are necessary.
    Observation: And even though the SQT cache is not being
     kept full for the IBQ, there is no perceived delay in processing
     this queue. Therefore, decreasing init_sqm_write_delay and
     init_sqm_max_write_delay, while it may keep cache more full,
     would have no effect on overall throughput.
    Observation: Since RepAgent stats indicate that commands
     are being pushed as fast as RepServer will allow as opposed
     to a “trickle”, no adjustments to sqt_init_read_delay and
     sqt_max_read_delay are indicated.



41
Analysis

    Observation/Experience: There are 9 tables being replicated
     for which 9 repdefs have been created. There are about 100
     columns in total. Given that sts_cachesize is set to 100 (by
     default) we may or may not see advantage to fully caching
     rs_columns. Aside from rs_columns, the other two tables
     generally recommended to be cached are rs_objects and
     rs_functions.




42
   Adjustment



1> configure replication server
2> set sqm_recover_segs to '5000'
3> go
Config parameter 'sqm_recover_segs' is modified. This change will not take effect
until the Replication Server is restarted.
1> shutdown
2> go



       Observation made




   43
   Adjustment
1> configure replication server
2> set sts_full_cache_rs_objects to 'on'
3> go
Config parameter 'sts_full_cache_rs_objects' is modified. This change will not
take effect until the Replication Server is restarted.
1> configure replication server
2> set sts_full_cache_rs_columns to 'on'
3> go
Config parameter 'sts_full_cache_rs_columns' is modified. This change will not
take effect until the Replication Server is restarted.
1> configure replication server
2> set sts_full_cache_rs_functions to 'on'
3> go
Config parameter 'sts_full_cache_rs_functions' is modified. This change will not
take effect until the Replication Server is restarted.
1> shutdown
2> go



       Observation made

   44
  Effects Of sqm_recover_segs & STS Caching

       DSI Stats    SMP On    Recover Segs   STS Caching     Change
                                @ 5,000                    (SMP to STS)
DSIEBPSaverage      131,352       131,976       132,000         0.5% +

TPS_Average              2               2             2        0.0% +

NgTPSaverage            16              17            16        0.0% +

DSIEResultTimeAve        5               5             5        0.0% +

DSIEBatchTimeAve         9               9             9        0.0% +

DSIETranTimeAve         46              46            45        2.2% -

AllThreadsInUse      6,342          6,359         6,428         1.4% +


  45
  Effects Of sqm_recover_segs & STS Caching

       STS Stats   SMP On      Recover        STS           Change
                             Segs @ 5,000   Caching       (SMP to STS)
SelectsTotal           564           568         551            2.3% -

InsertsTotal         1,701         1,450       1,433          15.8% -

UpdatesTotal           268           270         265            1.1% -

DeletesTotal         1,687         1,450       1,410          16.4% -

       SQM Stats

IBQ UpdsRsoqid         200             0              0      100.0% -

OBQ UpdsRsoqid          66             0              0      100.0% -


  46
  Effects Of sqm_recover_segs & STS Caching

   OBQ SQM Stats       SMP On      Recover        STS       Change
                                 Segs @ 5,000   Caching   (SMP to STS)
BlocksWritten           4,177          4,225      4,164        0.3% -

BlocksRead               2,496         2,487      2,526        1.2% +
(% of BlocksWritten)   (59.8%)       (58.9%)    (60.7%)
(BlocksReadCached)        (0)            (0)        (0)        0.0% +
% of BlocksRead          0.0%           0.0%       0.0%
SleepsWriteQ                0              0          0        0.0% +

UpdsRsoqid                 66              0          0      100.0% -

(WritesTimerPop)         (18)           (21)       (14)       50.0% -
% of BlocksWritten       0.6%           0.5%       0.3%


  47
Analysis

    Fully caching rs_objects, rs_columns and rs_functions AND
     increasing sqm_recover_segs made no impact on this
     RepServer’s performance
    But don’t let that discourage you from trying them in your
     RepServer – particularly if you have a good bit more than 9
     repdefs!
    DSI continues to be the bottleneck as indicated by the OQB
     reads being slower than the writes – behind by nearly 60%
     after 7 minutes
    Experience: Typical DSI tuning, aside from Parallel-DSI,
     includes increasing db_packet_size and dsi_cmd_batch_size




48
  Adjustment

      In replicate ASE
1> sp_configure 'max network packet size',8192
2> go
 Parameter Name                 Default     Memory Used Config Value
         Run Value   Unit                 Type
 ------------------------------ ----------- ----------- ------------
         ----------- -------------------- ----------
 max network packet size                512           0        8192
                 512 bytes                static

(1 row affected)
Configuration option changed. Since the option is static, Adaptive Server must
be rebooted in order for the change to take effect.
Changing the value of 'max network packet size' does not increase the amount of
memory Adaptive Server uses.
(return status = 0)
1> shutdown
2> go



  49
  Adjustment

      In RepServer

1> alter connection to rds03.tpcc
2> set db_packet_size to '8192'
3> go
Config parameter 'db_packet_size' is modified. This change will not take effect
until the connection/route is restarted. A connection/route can be restarted
with the suspend and resume commands.
1> shutdown
2> go




      Make observation




  50
 Adjustment

1> alter connection to rds03.tpcc
2> set dsi_cmd_batch_size to '16384'
3> go
Config parameter 'dsi_cmd_batch_size' is modified. This change will not take
effect until the connection/route is restarted. A connection/route can be
restarted with the suspend and resume commands.
1> suspend connection to rds03.tpcc
2> go
Connection to 'rds03.tpcc' is suspended.
1> resume connection to rds03.tpcc
2> go
Connection to 'rds03.tpcc' is resumed.




     Make observation



 51
  Effects Of Packet Size & Batch Size

       DSI Stats      STS     Packet Size       Batch Size      Change
                    Caching                                  (STS to Batch)
DSIEBPSaverage      132,000      133,298          131,888           0.0% -

TPS_Average               2                 2            2          0.0% +

NgTPSaverage             16            16               16          0.0% +

DSIEResultTimeAve         5                 5            8        60.0% +

DSIEBatchTimeAve          9                 9           18       100.0% +

DSIETranTimeAve          45            44               45          0.0% +

AllThreadsInUse       6,428         6,525           6,432           0.1% +


  52
Analysis

    Corresponding increases in db_packet_size and
     dsi_cmd_batch_size provided no improvements – but
     remember that there is no network involved here

    Similarly, subsequent testing indicates that doubling these
     configurations provides no gain in this topology

    Comparing several observations of these different
     configurations indicate that there is neither gain nor loss so
     that these adjustments may either be retained or discarded
     without impact on performance. They will be retained for
     convenience of testing.



53
    Analysis

                                     Tuning Comparisons
                    140000

                    138000
DSI/E BPS Average




                    136000

                    134000

                    132000
                                                                    db_packet_size
                    130000                                          dsi_cmd_batch_size
                                                                    sts_full_cache
                    128000

                    126000

                    124000

                    122000

                    120000
                             3   4            5             6   7

                                     Tuning Configuration
    54
Analysis

    Experience: Adding a second partition and using the
     disk_affinity configuration to assign each queue to its own
     partition (they will be on the same disk) is generally helpful
     when connections are created before partitions are added.




55
   Adjustment

1> add partition part02 on '/wasatch4/users/gcarter/RefImpl/dev_temp/prs03/part0
2.dat'
2> with size 900
3> go
Partition 'part02' is added.
1> alter connection to pds03.tpcc set disk_affinity to 'part01'
2> go
Config parameter 'disk_affinity' is modified.
1> alter connection to rds03.tpcc set disk_affinity to 'part02'
2> go
Config parameter 'disk_affinity' is modified.




   56
Presentation Version 0.1

    See instructor or e-mail him at greg.carter@sybase.com for
     the latest version of this presentation.




57

								
To top