REP703: Tuning Replication Server 12.5 for Ultra High Performance

1. REP703: Tuning Replication Server 12.5 for Ultra High Performance Dan Graham Senior Consultant Dgraham@sybase.com Joe Wooden Lead Principal Consultant Wooden@sybase.com August 15-19, 2004

2. The Enterprise. Unwired.

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

4. Introduction • Performance and Tuning • The art of balancing resources over time. In a perfect world there would always be enough resources for every process, but in reality often times what is given to one process must be taken away from another and system loads change over time. Balance carefully.

5. Agenda • Architecture • Standard Replication Server • Dual Replication Server • General Notes • Configuration • General Concepts • ASE (for Replication) • Replication Server • Route • Connection • RepAgent • DSI • Case Study

6. Architecture • Standard Replication Server Architecture • One Replication Server per database • Dual Replication Server Architecture • Two Replication Servers (inbound and outbound) per database • General Architecture Notes • Tip for setting up a system

7. Architecture - Standard Replication Server PDB LAN / WAN RDB RepAgent PDB RSSD PDB RSSD Stable Device Stable Device IBQ commit OBQ OBQ MatQ MatQ

8. Architecture - Dual Replication Servers PDB RepAgent PDB (proxy) RS2 RS1 PDB Inbound Data Outbound Data RSSD -1 RSSD - 2 Stable Device Stable Device IBQ commit OBQ OBQ MatQ MatQ

9. Architecture - Dual Replication Servers, Why? • Multiple Stable Devices / Queues • Data Separation (inbound / outbound) • Multiple CPUs • Multiple Replication Server processes • Ability to bind to CPUs separately • Separate RSSDs • Separate Replication Server Configuration • Replication Servers can be configured specific to its function • Multiple logical references to each DB  • Two separate interface file entries • TESTDB_IN, TESTDB_OUT • Increases complexity

10. Architecture – General Notes • Only replicate what is needed (know the application) • Use subscription where clauses to limit replication • Use function replication to improve performance • Don’t publish data that has no subscriptions • Auto-correction • Expands updates increasing number of transactions • Don’t use auto-correction unless you need it • Replicate Minimal Column • Can reduce replicated data • Prevents use of auto-correction • Must replicate intermediate transactions • Don’t waste resources • Use tools like sysmon to make sure that allocated resources are being used

11. Configuration • General Concepts • ASE (for Replication) • Replication Server • Route • Connection • RepAgent • DSI

12. Configuration – General Concepts • Process Optimization • Optimize the process to perform the desired task more efficiently with the available resources • Increase Resources • Give the process more resources to improve performance, possibly at the expense of other processes

13. Configuration – ASE for Replication • Transaction Log • It is vital to good performance that the logging of transactions is efficient since it impacts both the recording of primary activity and the reading of transactions to be replicated • Caches • Can improve performance by keeping frequently used data in memory • Fast Devices • Can improve performance in I/O bound systems

14. Configuration – ASE for Replication • Transaction Log - I/O Size • Logging is most efficient using a 4K buffer, so setup the primary database (PDB) accordingly.  • Example: use PDB go sp_logiosize “4” go

15. Configuration – ASE for Replication • Logonly Cache • Using a log cache can improve performance by keeping recent log records in memory and reducing expensive disk reads. • Setup a buffer pool that matches the log I/O size. • Example: (Create a logonly cache in the PDB database) use master go sp_cachconfig “PDB_logcache”, “32M”, logonly go sp_poolconfig “PDB_logcache”, “31.5M”, “4K” go • Note that the server will have to be restarted to create the cache

16. Configuration – ASE for Replication • Example (continued): (Bind the transaction log to a logonly cache) use master go sp_dboption PDB, “single user”, true go use PDB go checkpoint go sp_bindcache “PDB_logcache”, PDB, syslogs go use master go sp_dboption PDB, “single user”, false go

17. Configuration – ASE for Replication • Named Caches • Using a named caches can improve performance by keeping frequently used data in memory and reducing expensive disk reads • Setup named cache for the RSSD database • Setup a named cache for tempdb • For example, see logonly cache

18. Configuration – ASE for Replication • Fast Devices • Using an SSD (memory disk) for the transaction log can improve performance of the entire system by as much as 50%. • Using disks with hardware cache can give similar results provided there are periods of inactivity during which the cache can be flushed. However during prolonged periods of activity, cache can fill forcing activity to wait until cache is flushed thereby slowing I/O to the speed of the disk. This can appear as alternating periods of fast and slow performance.

19. Configuration – Replication Server • Process Optimization • Cache Frequently Used Data • Increase Resources • Increase Memory • Increase Processing Time

20. Configuration - Replication Server • Cache Frequently Used Data • Additional Memory can be put to good use by caching frequently used data • Allows data that is cached to be retrieved more quickly • Example configuration: rs_configure ‘memory_limit’, ‘64’ rs_configure ‘memory_max’, ‘64’ rs_configure ‘sqt_max_cache_size’, ‘16000000’ -- about 16 Mb rs_configure ‘sts_cachesize’, ‘1000’ rs_configure ‘sts_full_cache_rs_columns’, ‘on’ rs_configure ‘sts_full_cache_rs_objects’, ‘on’ rs_configure ‘sts_full_cache_rs_repobj’, ‘on’ rs_configure ‘sts_full_cache_rs_locator’, ‘on’ -- for inbound repservers -- Maximize the amount of memory available to the Distributor for messages waiting to be written to the outbound queue. rs_configure ‘exec_sqm_write_request_limit’, ‘983040’ -- for outbound repservers

21. Configuration - Replication Server • Increase Processing Time • Give RS processes more time at the expense of other processes • Example: sp_bindexeclass “RSSD_prim”, “lg”, NULL, “EC1” sp_bindexeclass “USER_maint”, “lg”, NULL, “EC1”

22. Configuration - Route • Process Optimization • Use partition affinity to distribute activity across devices • Increase the rs_packet_size to improve efficiency • Increase Resources • Decrease idle time

23. Configuration - Route • Example: suspend route to RRS go configure route to RRS set rsi_packet_size to ‘4096’ configure route to RRS set rsi_fadeout_time to ‘-1’ configure route to RRS set rsi_sync_interval to ‘60’ configure route to RRS set save_interval to ‘0’ go alter route to RRS set disk_affinity to STABLE_DEV1 go resume route to RRS go

24. Configuration - Connection • Process Optimization • Use partition affinity to distribute activity across devices • Increase the db_packet_size to improve efficiency • Decrease the save interval to free queue space • Example: suspend connection to PDS.PDB go alter connection to PDS.PDB set disk_affinity to STABLE_DEV2 alter connection to PDS.PDB set db_packet_size to ‘4096’ alter connection to PDS.PDB set save_interval to ‘0’ go resume connection to PDS.PDB go

25. Configuration - RepAgent • Process Optimization • In a busy system, RepAgent performance can be improved by sending bigger batches which is more efficient and reduces the overhead between RepAgent and Repserver. For example, RepAgent asks Repserver for a new secondary truncation point after every scan batch is processed. Example: sp_config_rep_agent PDB, ‘scan batch size’, 20000 sp_config_rep_agent PDB, ‘send buffer size’, ‘8K’ • Note that if you increase the ‘send buffer size’ then you may also have to increase the ‘max network packet size’, ‘default network packet size’, and ‘additional network memory’.

26. Configuration - RepAgent • Process Optimization (continued) • Decrease the RepAgent idle time. Example: sp_config_rep_agent PDB, ‘fade timeout’, 60 sp_config_rep_agent PDB, ‘retry timeout’, 30 sp_config_rep_agent PDB, ‘scan timeout’, 15

27. Configuration - RepAgent • Increase Resources • Increase the number of commands a RepAgent can execute before it must yield the CPU. Example: rs_configure ‘exec_cmds_per_timeslice’, 100000’ • Increase the RepAgent priority. Example: sp_config_rep_agent PDB, ‘priority’, ‘4’

28. Configuration - DSI • Bottleneck by Nature • By nature, the DSI thread can be a bottleneck because it must replicate the work of many other potentially independent processes in a dependant sequence. DSI performance can be improved by removing artificial dependencies and allowing it to perform independent transactions in parallel. • Process Optimization • Eliminating unnecessary work • Increase Resources • Increase Priority • Parallel DSI

29. Configuration - DSI • Process Optimization • Turn off triggers to eliminate unnecessary work. Example: alter connection to RDS.RDB set dsi_keep_triggers to ‘off’ • Use subscriptions where clauses to eliminate replicating intermediate transactions. This option cannot be used if replicating minimal columns. Example: create subscription mytable_sub for mytable_rep with replicate at RDS.RDB where status = ‘final’

30. Configuration - DSI • Increase Resources • Improve DSI performance by giving it more time at the expense of other processes. Example: -- Increase the priority of the maintenance user. Sp_bindexeclass “RDB_maint”, “lg”, NULL, “EC1”   • Use parallel DSI

31. Configuration - DSI • Parallel DSI Example: suspend connection to RDS.RDB go alter connection to RDS.RDB set parallel_dsi to ‘on’ go alter connection to RDS.RDB set dsi_num_threads to ‘5’ alter connection to RDS.RDB set dsi_num_large_xact_threads to ‘2’ alter connection to RDS.RDB set dsi_serialization_method to ‘wait_for_commit’ alter connection to RDS.RDB set dsi_sqt_max_cache_size’ to ‘100000’ alter connection to RDS.RDB set dsi_max_xacts_in_group to ‘20’ go alter connection to RDS.RDB set dsi_partitioning rule to ‘user’ go resume connection to RDS.RDB go

32. Configuration - DSI • Parallel DSI • DSI serialization methods: • nisolation_level_3 • nsingle_transaction_per_origin • nwait_for_commit • nnone • DSI partitioning rules • nnone • ntime • nuser • nname 

33. Case Study - Architecture Load Balanced OLTP Systems Semi-Denormalized Staging Database Load Balancing Router SGI Origin 3000 Series DSS System

34. Case Study - Notes • 3.6 million objects generated per 24 hour day • Object composed of one main record plus one or more related rows in multiple subordinate tables • Each object requires 10-20 transactions from creation to completion. That’s over 36 million transactions per day • Objects are machine generated locally and via replication • Growth currently at 5-10% monthly - Yes this is a major challenge!!! • Only “last” transaction triggers replication to staging area • Do the math - That’s about 150,000 objects per hour • Final transaction to complete object fires “archive proc” from trigger • OLTP ‘archive’ proc “collects” several tables worth of columns or parameters