jsjiang
commented
1 month ago The proc-link-checker-update job runs everyday starting from 1am and lasts 2-3 hours. This process is resource intensive on the RDS. We have noticed resource bottlenecks everyday between 1:30-3:30am
- the available memory drops to below 100MB
- EC2 IPOS reaches to the high limit of 3600
The core SQL statements which is created by the _harvest function to retrieve records from the searchIdentifier and the linkChecker table may need refactoring to improve performance.
Refactor options:
- pagesize:
- reduce pagesize from 100,000 rows to a smaller number (10K or 1K based on performance tests)
- balance load on RDS and EC2
- use of
list()- use list will read in DB output to local (EC2) memory - use of the
len()function - other options
Current code:
def _harvest(self, model, only=None, filter=None):
lastIdentifier = ""
while not self.terminated():
qs = model.objects.filter(identifier__gt=lastIdentifier).order_by("identifier")
if only is not None:
qs = qs.only(*only)
qs = list(qs[: self.opt.pagesize])
if len(qs) == 0:
break
for o in qs:
if filter is None or filter(o):
yield o
lastIdentifier = qs[-1].identifier
yield None
The proc-link-checker-update background job is memory intensive on the RDS server. The core SQL query is identified as one of the long running queries. Refactor the process to make the SQL queries more efficient. Or consider to use OpenSearch as data sources