Repository: rucio/rucio
Issue: Many database queries caused by necromancer

Motivation
----------
The necromancer still causes unnecessary many queries (100k a day) on the ATLAS Rucio instance. It is the top CPU query on the database causing 26% of CPU load on some days.

It is query
```
SELECT anon_1.atlas_rucio_replicas_scope,  
 anon_1.atlas_rucio_replicas_name, anon_1.atlas_rucio_replicas_rse_id   
 FROM (SELECT /*+ INDEX(REPLICAS REPLICAS_STATE_IDX) */  
 atlas_rucio.replicas.scope AS atlas_rucio_replicas_scope,  
 atlas_rucio.replicas.name AS atlas_rucio_replicas_name,  
 atlas_rucio.replicas.rse_id AS atlas_rucio_replicas_rse_id  FROM  
 atlas_rucio.replicas JOIN atlas_rucio.dids ON atlas_rucio.dids.scope =  
 atlas_rucio.replicas.scope AND atlas_rucio.dids.name =  
 atlas_rucio.replicas.name  WHERE CASE WHEN (atlas_rucio.replicas.state  
 != 'A') THEN atlas_rucio.replicas.rse_id END IS NOT NULL AND  
 atlas_rucio.replicas.state = :state_1 AND  
 ORA_HASH(atlas_rucio.replicas.name, :total_threads) = :thread_id AND  
 atlas_rucio.dids.availability != :availability_1) anon_1  WHERE ROWNUM  
 <= 1000 
```

which corresponds to https://github.com/rucio/rucio/blob/db5881ad066ef237dc8c391e65264b1fd70ee9f8/lib/rucio/core/replica.py#L527

Modification
------------
This needs to be investigated and reduced. Certainly the necromancer as a "secondary" service should not cause 25% of the load on the database. 

Changed files:
lib/rucio/daemons/badreplicas/necromancer.py

diff --git a/lib/rucio/daemons/badreplicas/necromancer.py b/lib/rucio/daemons/badreplicas/necromancer.py
index ceb2d3342..5cb5ffed8 100644
--- a/lib/rucio/daemons/badreplicas/necromancer.py
+++ b/lib/rucio/daemons/badreplicas/necromancer.py
@@ -69,9 +69,11 @@ def necromancer(thread=0, bulk=5, once=False, sleep_time=60):
 
     while not GRACEFUL_STOP.is_set():
 
+        stime = time.time()
         heart_beat = heartbeat.live(executable, hostname, pid, hb_thread)
         prefix = 'Thread [%i/%i] : ' % (heart_beat['assign_thread'], heart_beat['nr_threads'])
         logger = formatted_logger(logging.log, prefix + '%s')
+        logger(logging.DEBUG, 'Starting new cycle')
 
         # Check if there is a Judge Evaluator backlog
         try:
@@ -121,7 +123,7 @@ def necromancer(thread=0, bulk=5, once=False, sleep_time=60):
             logger(logging.INFO, 'Backlog of bads replica too big. Apply some sharing between different RSEs')
             rses = list()
             cnt = 0
-            for key in sorted(bad_replicas_backlog, key=bad_replicas_backlog.get, reverse=False):
+            for key in sorted(bad_replicas_backlog, key=bad_replicas_backlog.get, reverse=True):
                 rses.append({'id': key})
                 cnt += bad_replicas_backlog[key]
                 if cnt >= bulk:
@@ -131,52 +133,55 @@ def necromancer(thread=0, bulk=5, once=False, sleep_time=60):
         else:
             list_of_rses.append(None)
 
-        stime = time.time()
-        replicas = []
         tot_processed = 0
-        try:
-            for rses in list_of_rses:
-                replicas = list_bad_replicas(limit=bulk, thread=heart_beat['assign_thread'], total_threads=heart_beat['nr_threads'], rses=rses)
-
-                for replica in replicas:
-                    scope, name, rse_id, rse = replica['scope'], replica['name'], replica['rse_id'], replica['rse']
-                    logger(logging.INFO, 'Working on %s:%s on %s' % (scope, name, rse))
-
-                    list_replicas = get_replicas_state(scope=scope, name=name)
-                    if ReplicaState.AVAILABLE not in list_replicas and ReplicaState.TEMPORARY_UNAVAILABLE not in list_replicas:
-                        logger(logging.INFO, 'File %s:%s has no other available or temporary available replicas, it will be marked as lost' % (scope, name))
-                        try:
-                            update_rules_for_lost_replica(scope=scope, name=name, rse_id=rse_id, nowait=True)
-                            monitor.record_counter(name='necromancer.badfiles.lostfile')
-                        except (DatabaseException, DatabaseError) as error:
-                            if re.match('.*ORA-00054.*', error.args[0]) or re.match('.*ORA-00060.*', error.args[0]) or 'ERROR 1205 (HY000)' in error.args[0]:
-                                logger(logging.WARNING, 'Lock detected when handling request - skipping: %s', str(error))
-                            else:
-                                logger(logging.ERROR, str(error))
-
-                    else:
-                        rep = list_replicas.get(ReplicaState.AVAILABLE, [])
-                        unavailable_rep = list_replicas.get(ReplicaState.TEMPORARY_UNAVAILABLE, [])
-                        logger(logging.INFO, 'File %s:%s can be recovered. Available sources : %s + Unavailable sources : %s' % (scope, name, str(rep), str(unavailable_rep)))
-                        try:
-                            update_rules_for_bad_replica(scope=scope, name=name, rse_id=rse_id, nowait=True)
-                            monitor.record_counter(name='necromancer.badfiles.recovering')
-                        except (DatabaseException, DatabaseError) as error:
-                            if re.match('.*ORA-00054.*', error.args[0]) or re.match('.*ORA-00060.*', error.args[0]) or 'ERROR 1205 (HY000)' in error.args[0]:
-                                logger(logging.WARNING, 'Lock detected when handling request - skipping: %s', str(error))
-                            else:
-                                logger(logging.ERROR, str(error))
-
-                tot_processed += len(replicas)
-                logger(logging.INFO, 'It took %s seconds to process %s replicas' % (str(time.time() - stime), str(len(replicas))))
-        except Exception:
-            exc_type, exc_value, exc_traceback = exc_info()
-            logger(logging.CRITICAL, ''.join(format_exception(exc_type, exc_value, exc_traceback)).strip())
+        if tot_bad_files == 0:
+            logger(logging.INFO, 'No bad replicas to process.')
+        else:
+            ttime = time.time()
+            replicas = []
+            try:
+                for rses in list_of_rses:
+
+                    replicas = list_bad_replicas(limit=bulk, thread=heart_beat['assign_thread'], total_threads=heart_beat['nr_threads'], rses=rses)
+                    for replica in replicas:
+                        scope, name, rse_id, rse = replica['scope'], replica['name'], replica['rse_id'], replica['rse']
+                        logger(logging.INFO, 'Working on %s:%s on %s' % (scope, name, rse))
+
+                        list_replicas = get_replicas_state(scope=scope, name=name)
+                        if ReplicaState.AVAILABLE not in list_replicas and ReplicaState.TEMPORARY_UNAVAILABLE not in list_replicas:
+                            logger(logging.INFO, 'File %s:%s has no other available or temporary available replicas, it will be marked as lost' % (scope, name))
+                            try:
+                                update_rules_for_lost_replica(scope=scope, name=name, rse_id=rse_id, nowait=True)
+                                monitor.record_counter(name='necromancer.badfiles.lostfile')
+                            except (DatabaseException, DatabaseError) as error:
+                                if re.match('.*ORA-00054.*', error.args[0]) or re.match('.*ORA-00060.*', error.args[0]) or 'ERROR 1205 (HY000)' in error.args[0]:
+                                    logger(logging.WARNING, 'Lock detected when handling request - skipping: %s', str(error))
+                                else:
+                                    logger(logging.ERROR, str(error))
+
+                        else:
+                            rep = list_replicas.get(ReplicaState.AVAILABLE, [])
+                            unavailable_rep = list_replicas.get(ReplicaState.TEMPORARY_UNAVAILABLE, [])
+                            logger(logging.INFO, 'File %s:%s can be recovered. Available sources : %s + Unavailable sources : %s' % (scope, name, str(rep), str(unavailable_rep)))
+                            try:
+                                update_rules_for_bad_replica(scope=scope, name=name, rse_id=rse_id, nowait=True)
+                                monitor.record_counter(name='necromancer.badfiles.recovering')
+                            except (DatabaseException, DatabaseError) as error:
+                                if re.match('.*ORA-00054.*', error.args[0]) or re.match('.*ORA-00060.*', error.args[0]) or 'ERROR 1205 (HY000)' in error.args[0]:
+                                    logger(logging.WARNING, 'Lock detected when handling request - skipping: %s', str(error))
+                                else:
+                                    logger(logging.ERROR, str(error))
+
+                    tot_processed += len(replicas)
+                    logger(logging.INFO, 'It took %s seconds to process %s replicas' % (str(time.time() - ttime), str(len(replicas))))
+            except Exception:
+                exc_type, exc_value, exc_traceback = exc_info()
+                logger(logging.CRITICAL, ''.join(format_exception(exc_type, exc_value, exc_traceback)).strip())
 
         if once:
             break
-        elif tot_processed == 0:
-            daemon_sleep(start_time=stime, sleep_time=sleep_time, graceful_stop=GRACEFUL_STOP)
+        elif tot_processed == 0 or tot_bad_files == 0:
+            daemon_sleep(start_time=stime, sleep_time=sleep_time, graceful_stop=GRACEFUL_STOP, logger=logger)
 
     logger(logging.INFO, 'Graceful stop requested')
     heartbeat.die(executable, hostname, pid, hb_thread)