ATLAS Conditions Replication

Test Plan

For COOL client is used Stefan Stonjek's client as he puts higher load on the server. He stores open IOVs / Interval of Validity / which leads to many updates in order to 'close' these intervals from the next IOV. Overlapping of IOVs is not allowed in COOL single version tables

The goals of the tests are:

- To understand how COOL does data insertion, is it performant, what are the limits, resource usage and so on...
- Are the Oracle Streams good in replicating COOL data? In production mode we can't accept more than 10s of seconds latency from the ATLAS online RAC 'ATONR' to ATLAS offline RAC.
- To get the feeling at what IOV rate the destination sites will start to fall behind from the online RAC. We are starting from the minimum data insert rate of 3 IOVs per 15 minutes into 250 COOL single version folders.
- To get more knowledge in replicating data and prepare plan with actions when something goes wrong with some of the destination sites.

We consider the following setup to be realistic one as we do not have better estimate for the number of the folders, channels, IOV rate and payload size.

- 250 COOL single version folders
- 200 channels per folder /in reality will vary a lot /
- 100 bytes payload
- insertion rate: should be 3 IOVs with iov_until to infinity every 15 min, but for the tests didn't specify any time constrains, because COOL rather slow in storing new IOVs.
- 10 clients doing the job
- We filter out all DML and DDL activity related to the xxx_IOV_SEQ tables on the source site as we consider that we will not need them on the destination sites. That reduces the number of statements to be applied from the APPLY process and hopefully contributes to the performance.
- With that setup one transaction of inserting one IOV in a folder consists of as many single UPDATEs on IOV_UNTIL column as many channels we have. In our case 200 UPDATEs + 1 BULK INSERT of 200 records. The record length is 150 bytes.

FINDINGS:

===============
17th November
===============

Because of the DIRECT IMPORT to INTR from ATONR and remaping tablespaces
impdp USERID=strmadmin@intr SCHEMAS="ATLAS_COOL_3D" NETWORK_LINK=ATONR.CERN.CH remap_tablespace=ATLAS_COOL_GANCHO_DATA:ATLAS_COOL_GANCHO
FLASHBACK_SCN=5992460472145

in all Tier1 sites I got error "ORA-00959: tablespace 'ATLAS_COOL_GANCHO' does not exist", because into the CREATE TABLE ddl appeared the TABLESPACE clause

ddl: CREATE TABLE "ATLAS_COOL_3D"."TRY_F0002_IOVS"
("OBJECT_ID" NUMBER(10,0),
"CHANNEL_ID" NUMBER(10,0), "IOV_SINCE" NUMBER(20,0), "IOV_UNTIL" NUMBER(20,0),
"USER_TAG_ID" NUMBER(10,0), "SYS_INSTIME" VARCHAR2(255), "ORIGINAL_ID"
NUMBER(10,0), "NEW_HEAD_ID" NUMBER(10,0), "A" NUMBER(10,0), "B" NUMBER(10,0),
"C" NUMBER(10,0), "D" NUMBER(10,0), "E" NUMBER(10,0), "F" NUMBER(10,0), "G"
NUMBER(10,0), "H" NUMBER(10,0), "I" NUMBER(10,0), "J" NUMBER(10,0), "K"
BINARY_FLOAT, "L" BINARY_FLOAT, "M" BINARY_FLOAT, "N" BINARY_FLOAT, "O"
BINARY_FLOAT, "P" BINARY_FLOAT, "Q" BINARY_FLOAT, "R" BINARY_FLOAT, "S"
BINARY_FLOAT, "T" BINARY_FLOAT) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT) TABLESPACE "ATLAS_COOL_GANCHO" PARTITION BY RANGE ("IOV_SINCE") (PARTITION "MAX_VAL" VALUES LESS THAN
(MAXVALUE) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ATLAS_COOL_GANCHO" NOCOMPRESS
) ENABLE ROW MOVEMENT

To avoid this, the tablespace names of the accounts created at all Tier1s must be the same as these at CERN !!!

===================
03th November 2006 - 66 IOVs per hour
===================

I have repeated the same test as yesterday 2th of November. I got the same picture and the total latency is again 2 hours. The CPU of the INTR2 had been about 75%
The LCRs rate was quite stable 1400 LCRs/sec, except for a 2 and a half hours when the rate had been 600 -700 LCRs!!! I found out that in thet time window 22:00 to 00:15 the job for collecting object statistics had been running. Still is not clear for me the relation of that to the low LCR rate dring that time window.

===================
02th November 2006 - 66 IOVs per hour
===================

The same as above but for longer time - 50600 seconds took to insert 1000 IOVs /15 hours/
The LCRs rate was quite stable 1400 LCRs/sec, except for a 2 and a half hours when the rate had been 600 -700 LCRs!!! Unfortunatelly for the time of the test we got 2 hours latency. See the snapshots below ...

15hours_20clients_APPLY_rate.tiff 15hours_20 clients_CAPTURE_latency.tiff

I have to repeat the test to see if I will get the same results

===================
01th November 2006
===================

Try very massive load - 250 COOL Folders
20 processes running from 2 "Point 1" machines - 100 IOVs had been inserted for 5700 seconds

The highes LCRs rate I have ever got - 1400 LCRs/sec!!! For the time of the test we got 2 minutes latency

=================
31th  October 2006 -  Idea for solving the APPLY Latency problem
=================

After finding out that having many indexes on the COOL tables slows down the Streams APPLY process at the destinations, Florbela and me started brainstorming to find a solution.

An idea appeared for using 2 DB accounts for each sub-detector - one "xxx_3D" account dedicated only for the Streams flow and the other having Materialized views on the top of the tables in the "xxx_3D" schema.

The applications will read the date from the Mat. views, wich will have the full set of indexes. The streams flow will be directly between the xxx_3D accounts, which tables will have only PKs, no other indexes. A job running on regular time will REFRESH the Materialized views on demand.

=================
30th  October 2006
=================

One very important finding was that the latecny is caused by having indexes on the COOL IOV tables. All the 8 APPLY servers on INTR had a lot of I/O because of the necessity to update the relevant indexes. All the wait events "KJC - wait for msg sends to complete" we were observing for quite a lot of time were masking the real problem.

After setting all indexes on the IOVs tables except the PKs as UNUSABLE and starting massive insert for about 20 hours / 250 folders, 10 processes inserting data, Streams rate was about 550 LCRs per sec. / NO LATENCY was observed - only 3-5 seconds between ATONR and INTR. The CAPTURE on ATONR was never paused for flow control.

The disabled INDEXES were of the following type:

xxx_IOVS_CO_2INDX based on (channel_id, object_id)
xxx_CSU_3INDX based on (channel_id, iov_since, iov_until)
xxx_SU_2INDX based on (iov_since, iov_until)

========================
26th - 27th  October 2006
========================

Because of the many wait events of type 'KJC: wait for msg sends to complete' from the Streams APPLY servers we stopped INTR1
With only INTR2 working we do not see such wait events.
The outcome of the tests represented below is that APPLY process keeps up to higher insert speed when there is only Primary Keys on the COOL tables. If all 4 indexes are enabled then we got many I/O and the latency grows a lot.

Try 1.

Try 2.

Try 3.

Try 4.

Try 5 .

Try 6.

Try 7.

Try 8. Activate all COOL indexes - rebuild and collect statistics for them
8 hours data insertion - 240 IOVS
250 COOL tables
10 processes inserting data
========================
Very big latency - after the 4-th hour, about 2500 seconds. At the end it finished with 9000 seconds latency

12_hours_test_APPLY_latency.tiff 12_hours_test_APPLY_LCRs_per_sec.tiff

=================
25th October 2006
=================

2. We have been monitoring very bad performance of the APPLY process on INTR. It starts to fall behind immediately after starting new insertion. We can see "KJC: wait for msg sends to complete" wait event for the all 8 APPLY servers.

APPLY_servers_activity.tiff

- With single APPLY process (no parallelism) the KJC wait event is missing. Another interesting finding is that the KJC wait event is present when both instances are up and not with single running instance. As Florbela pointed out, that might be an Oracle bug with number 4894027.
Its description from the Metalink is the following:
" In a RAC environment Logical Standby or Streams apply can show excessive waits on 'rdbms ipc message' when there is workload to apply"
That will be fixed into next Oracle patch set 10.2.0.3. Hopefully it will be issued soon and will contribute to fix the problem.

=================
20th October 2006
=================

1. COOL insertion performance
The first impression is that COOL is not performant in the data insertion. As average, to store 1 IOV per folder takes about 5 sec. With 10 clients inserting into 250 folders /each client dealing with 25 folders / takes 2 minutes.
What I find is that COOL issues 3 SQL statements per channel in order to close the IOV. That is quite inefficient
Here is an extract from the COOL client log
------------------------------------------------------

Prepared statement : "SELECT MAX("OBJECT_ID")
FROM ATLAS_COOL_3D."TEST3D_F0001_IOVS" "TEST3D_F0001_IOVS"
WHERE "CHANNEL_ID"= :"channel""
Fetches object row from table TEST3D_F0001_IOVS for object_id = xxx
Prepared statement : "SELECT "OBJECT_ID",
"CHANNEL_ID",
"IOV_SINCE",
"IOV_UNTIL",
"USER_TAG_ID",
"SYS_INSTIME",
"ORIGINAL_ID",
"NEW_HEAD_ID",
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T"
FROM ATLAS_COOL_3D."TEST3D_F0001_IOVS" "TEST3D_F0001_IOVS"
WHERE "OBJECT_ID"= :"objectId""
Fetches object row from table TEST3D_F0001_IOVS for object_id = xxx

Update IOV in table row for object 136601
Prepared statement : "UPDATE ATLAS_COOL_3D."TEST3D_F0001_IOVS"
SET "IOV_UNTIL"= :"until"
WHERE "OBJECT_ID"= :"objId""

These 3 statements are issued 200 times for the all 200 channels up

[SUGGESTION]

More elegant solution is to replace all these 400 SELECT by only 1 !!!

SELECT object_id, channel_id, iov_since, iov_until
FROM atlas_cool_3d.test3d_f0014_iovs
WHERE object_id IN
( select max(object_id) from atlas_cool_3d.test3d_f0014_iovs GROUP BY channel_id)

If the case is to set the same IOV_UNTIL for all channels ( this is too simplied case, maybe doesn't corrspond to the COOL logic ),
than ALL 600 statements will be replaced by only 1 as the following.

UPDATE atlas_cool_3d.test3d_f0014_iovs SET IOV_UNTIL = xxx
WHERE (object_id,channel_id) IN
( SELECT object_id, channel_id
FROM atlas_cool_3d.test3d_f0014_iovs
WHERE object_id IN ( SELECT MAX(object_id) FROM atlas_cool_3d.test3d_f0014_iovs GROUP_BY channel_id))
[SUGGESTION END]

We were surprised to hear that Persistency does not support GROUP BY operator, but they promised to add it in their API within the next 2 weeks.

Sites Connected

Sites to be joining