Replication using Oracle Streams at CERN Eva Dafonte

Replication using Oracle Streams at CERN Eva Dafonte Pérez CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it

Outline • • Scope Oracle Streams Replication Downstream Capture Streams Setups at CERN – Split & Merge Procedure – Streams Optimizations – Lessons Learned Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it • Streams Monitoring • Examples and Numbers • Summary 2

The LHC Computing Challenge • Data volume – high rate x large number of channels x 4 experiments – 15 Peta. Bytes of new data each year stored – much more data discarded during multi-level filtering before storage • Compute power – event complexity x Nb. events x thousands users – 100 k of today's fastest CPUs • Worldwide analysis & funding Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it – computing funding locally in major regions & countries – efficient analysis everywhere – GRID technology 3

Distributed Service Architecture Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it 4

Oracle Streams Replication • Technology for sharing information between databases • Database changes captured from the redo-log and propagated asynchronously as Logical Change Records (LCRs) Source Database Target Database Propagate Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Redo Logs Capture Apply 5

Downstream Capture • Downstream capture to de-couple Tier 0 production databases from destination or network problems – source database availability is highest priority • Optimizing redo log retention on downstream database to allow for sufficient re-synchronisation window – we use 5 days retention to avoid tape access • Dump fresh copy of dictionary to redo automatically • 10. 2 Streams recommendations (metalink note 418755) Source Database Internet Services CERN IT Department CH-1211 Genève 23 Switzerland Redo www. cern. ch/i Logst Target Database Downstream Database Propagate Redo Transport method Capture Apply 6

Streams Setups: ATLAS Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it 7

Streams Setups: LHCb (Conditions and LFC) Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it 8

Split & Merge Procedure • If one site is down – LCRs are not removed from the queue – Capture process might be paused by flow control impact on replication performance • Objective: isolate replicas against each other – Split the capture process • (original) Real-Time capture for sites “in good shape” • (new) normal capture for site/s unstable/s – new capture queue and propagation job – original propagation job is dropped spilled LCRs are dropped from the original capture queue Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it – Merge the capture processes • Resynchronization suggested by Patricia Mc. Elroy ( Principal Product Manager Distributed Systems/Replication) 9

Streams Optimizations • TCP and Network tuning – adjust system max TCP buffer (/etc/sysctl. conf) – parameters to reinforce the TCP tuning • DEFAULT_SDU_SIZE=32767 • RECV_BUF_SIZE and SEND_BUF_SIZE – Optimal: 3 * Bandwidth Delay Product • Reduce the Oracle Streams acknowledgements – alter system set events '26749 trace name context forever, level 2'; Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it 10

Streams Optimizations Rules • ATLAS Streams Replication: filter tables by prefix • Rules on the capture side caused more overhead than on the propagation side • Avoid Oracle Streams complex rules: rules with conditions that include LIKE or NOT clauses or FUNCTIONS Complex Rule condition => '( SUBSTR(: ddl. get_object_name(), 1, 7) IN (''COMP 200'', ''OFLP 200'', ''CMCP 200'', ''TMCP 200'', ’'TBDP 200'', ''STRM 200'') OR SUBSTR (: ddl. get_base_table_name(), 1, 7) IN (''COMP 200'', ''OFLP 200'', ''CMCP 200'', ''TBDP 200'', ''STRM 200'') ) ' Simple Rule Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Avoid complex rules: • LIKE • Functions • NOT condition => '(((: ddl. get_object_name() >= ''STRM 200_A'' and : ddl. get_object_name() <= ''STRM 200_Z'') OR (: ddl. get_base_table_name() >= ''STRM 200_A'' and : ddl. get_base_table_name() <= ''STRM 200_Z'')) OR ((: ddl. get_object_name() >= ’'OFLP 200_A'' and : ddl. get_object_name() <= ''OFLP 200_Z'') OR (: ddl. get_base_table_name() >= ’'OFLP 200_A'' and : ddl. get_base_table_name() <= ''OFLP 200_Z'')) 11

Streams Optimizations Rules 600 80 Internet Services Simple Rules CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Complex Rules 12

Streams Optimizations Flow Control • By default, flow control kicks when the number of messages is larger than the threshold – Buffered publisher: 5000 – Capture publisher: 15000 • 10. 2 + Patch 5093060 = 2 new events Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it – 10867: controls threshold for any buffered message publisher – 10868: controls threshold for capture publisher • Manipulate default behavior 13

Lessons Learned • Apply side can be significantly less efficient – Manipulate the database is slower than the redo generation – Execute LCRs serially => apply cannot keep up with the redo generation rate • SQL bulk operations (at the source db) – May map to many elementary operations at the destination side – Need to control source rates to avoid overloading • System generated names Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it – Do not allow system generated names for constraints and indexes – Modifications will fail at the replicated site – Storage clauses also may cause some issues if the target sites are not identical 14

Streams Recommended Patches Metalink Note: 437838. 1 - Recommended Patch for Streams § MLR for Streams/Logminer bugs § patches: 6081550, 6081547 and 6267873 § Leak in Perm Allocations with library cache comments ora 4031 generated § fix patch 6043052 • ORA-00600 [KRVTADC] IN CAPTURE PARALLEL PROCESS (using compressed tables) § fix patch 4061534 § DEADLOCK BETWEEN 'LIBRARY CACHE LOCK' AND 'LIBRARY CACHE PIN' § fix patch 5604698 Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it § Bug 6163622 SQL apply degrades with larger transactions § Bug 5093060 STREAMS 5000 LCR limit is causing unnecessary FLOW CONTROL at apply site 15

Streams Monitoring • Features: – – – Streams topology Status of streams connections Error notifications Streams performance (latency, throughput, etc. ) Other resources related to the streams performance (streams pool memory, redo generation) • Architecture: – “strmmon” daemon written in Phython – End-user web application http: //oms 3 d. cern. ch: 4889/streams/main Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it • 3 D monitoring and alerting integrated with WLCG procedures and tools 16

Streams Monitoring Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it 17

Examples • Streams setup for ATLAS experiment – – – Online Offline Tier 1 sites (10) Real-Time downstream capture Oracle 10. 2. 0. 3 Use of rules Database size: 1. 44 TB Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it 18

Examples • ATLAS Conditions data – 2 GB per day – 600 - 800 LCRs per second Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it 19

Examples • ATLAS PVSS tests – 6 GB per day – 2500 - 3000 LCRs per second Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it 20

Summary • The LCG Database Deployment Project (LCG 3 D) has set up a wold-wide distributed database infrastructure for LHC – some 124 RAC nodes = 450 CPU cores at CERN + several tens of nodes at 10 partner sites are in production now • Large scale tests have validated that the experiment are implemented by the RAC & streams based set-up – Backup & recovery tests have been performed to validate the operational procedures at all sites • Monitoring of database & streams performance has been implemented building on grid control and strmmon tools – key to maintain and optimise any larger system Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it • Database infrastructure is ready for accelerator turn-on – collaboration with Oracle as part of the CERN openlab has been essential and beneficial for both partners 21

Questions? Internet Services CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it 22