LHCB LHCbBookkeeping

Problems of high load on RAC due bulk deletions

The analysis of the issue is:

• you have a "delete job" that you want to run in batch mode to maximize throughput
• you parallelize the job by running multiple threads with simultaneous execution. On Friday you ran about 50 simultaneous threads, 19 on the first test today
• Oracle parallel DML operations were active on Friday and in the first test Today.

After reproducing the situation seen on Friday it was clear that:

• CPU was at 100% with CPU run queues higher than 20 per node (note only 2 CPUs are available on each cluster node).
• Oracle wait events showed that many resources were wasted in handling a large number of "parallel slaves" for Oracle's parallel operations.

The following changes have been implemented after the analysis:

1. I have permanently disabled Oracle parallel operations: alter system set parallel_max_servers=0 scope=both;
2. You have decreased the number of parallel executions to 10 parallel jobs

Results:
After this you ran the job observing "normal" performance throughput. On the Oracle and OS side I could see that the job is still completely CPU bound (100% cpu utilization and nearly no IO). Run queue of the servers are on average at 5 (with peaks around 10).

Comments:
This is a very high load, but consistent with utilizing all the resources of the server and it is not harmful for the clusterware operations. If you had to run other jobs on the DB you would want to reduce even further the number of parallel dele operations (as it is now it's ok). At present Oracle parallel operations (parallel query slaves) are disabled for whole the DB. Should they be needed for other activities, further investigations will have to be done.